LaravelでLIKE句のSQLインジェクション対策をする

Transcript

1. Copyright© M&AΫϥ΢υ LaravelͰLIKE۟ͷSQLΠϯδΣΫγϣϯରࡦΛ͢Δ PHP Conference Japan 2022
2. Copyright© M&AΫϥ΢υ 2 Profile גࣜձࣾM&AΫϥ΢υ Ώ͍ fyui001 @fyui_001
3. Copyright© M&AΫϥ΢υ 3 ໰୊ ೿खͳSQLΠϯδΣΫγϣϯ͸ҰൠతͳWebϑϨʔϜϫʔΫΛ࢖༻͢Ε͹جຊతʹൃੜ͠·ͤΜɻ ͔͠͠ɺLIKEݕࡧΛߦ͏৔߹͸DoS߈ܸ͕੒ཱͯ͠͠·͏͜ͱ͕͋Γ·͢ɻ LIKE "%a%b%c%d%e%e%f%g%@%.%" ্هͷΑ͏ͳΫΤϦ͸SQLΤϯδϯʹେ͖ͳෛՙΛ͔͚·͢ɻ LIKE۟ͷϝλจࣈ͸Τεέʔϓ͢Δඞཁ͕͋Γ·͕͢ɺ $query->where('hoge', 'LIKE', '%' . $value . '%'); ͱ௚ʹॻ͍ͯ͠·͏έʔε͸ଟ͍ͱࢥ͍·͢ɻ
4. Copyright© M&AΫϥ΢υ 4 ରࡦ LaravelͰͷ͜ͷLIKE۟ͷΠϯδΣΫγϣϯରࡦ͸͓ͦΒ̏͘௨Γ΄Ͳ͋Δͱࢥ͏ͷͰ ͦΕͧΕͷιϦϡʔγϣϯΛ͝঺հ͍ͯ͜͠͏ͱࢥ͍·͢ɻ
5. Copyright© M&AΫϥ΢υ 5 1. macroΛ༻ҙ͢Δ ·ͣBlueprintͷmacroΛఆٛ͢ΔͨΊʹ αʔϏεϓϩόΠμΛ৽͘͠࡞Γ·͢ɻ(AppServiceProvider.phpʹॻ͖ࠐΉํ๏΋͋Δɻ php artisan make:provider BlueprintServiceProvider
6. Copyright© M&AΫϥ΢υ 6 1. macroΛ༻ҙ͢Δ <?php namespace App\Providers; use Illuminate\Support\ServiceProvider; class BlueprintServiceProvider extends ServiceProvider { /** * Register services. * * @return void */ public function register() { // } /** * Bootstrap services. * * @return void */ public function boot() { // } ͢ΔͱҎԼͷΑ͏ͳϑΝΠϧ͕ੜ੒͞Ε·͢ɻ
7. Copyright© M&AΫϥ΢υ 7 1. macroΛ༻ҙ͢Δ ͜ͷbootϝιουʹmacroΛఆ͍͖ٛͯ͠·͢ɻ < /** * Bootstrap services. * * @return void */ public function boot() { Builder::macro('whereLike', function (string $attribute, string $keyword, int $position = 0) { $keyword = addcslashes($keyword, '\_%'); $condition = [ 1 => "{$keyword}%", -1 => "%{$keyword}", ][$position] ?? "%{$keyword}%"; return $this->where($attribute, 'LIKE', $condition); }); Builder::macro('orWhereLike', function (string $attribute, string $keyword, int $position = 0) { $keyword = addcslashes($keyword, '\_%'); $condition = [ 1 => "{$keyword}%", -1 => "%{$keyword}", ][$position] ?? "%{$keyword}%"; return $this->orWhere($attribute, 'LIKE', $condition); }); }
8. Copyright© M&AΫϥ΢υ 8 2.ΫΤϦείʔϓΛ࢖͏ ModelͰҎԼͷΑ͏ʹఆٛ͠·͢ɻ <?php namespace App\Models; use Illuminate\Database\Eloquent\Model as EloquentModel; /** * This class contains shared setup, properties and methods * of all application models * */ class Model extends EloquentModel { public function scopeWhereLike($query, string $attribute, string $keyword, int $position = 0) { $keyword = addcslashes($keyword, '\_%'); $condition = [ 1 => "{$keyword}%", -1 => "%{$keyword}", ][$position] ?? "%{$keyword}%"; return $query->where($attribute, 'LIKE', $condition); } public function scopeOrWhereLike($query, string $attribute, string $keyword, int $position = 0) { $keyword = addcslashes($keyword, '\_%'); $condition = [ 1 => "{$keyword}%", -1 => "%{$keyword}", ][$position] ?? "%{$keyword}%"; return $query->orWhere($attribute, 'LIKE', $condition); }
9. Copyright© M&AΫϥ΢υ 9 3.TraitͰ࢖͍ճͤΔύʔπͱͯ͠༻ҙ͢Δ macroɾΫΤϦείʔϓఆٛͰ͸IDEࢧԉ͕ޮ͔ͳ͍໰୊͕͋Γ·͢ɻ ·ͨɺνʔϜͷن໛ʹΑͬͯ͸ϑϨʔϜϫʔΫͷཧղϨϕϧʹόϥ͖͕ͭग़ͯ͘Δ͜ͱ΋͋Γ·͢ɻ ͳͷͰݴޠϨϕϧͰཧղͷ͠΍͍͢TraitͰػೳΛ༻ҙͯ͋͛͠Δͱ͍͏ղ΋ग़͖ͯ·͢ɻ TraitͳΒIDEͷࢧԉ΋ޮ͘ͷͰɺ৽ϝϯόʔͳͲ͕ίʔυΛݟͨͱ͖ʹίʔυδϟϯϓ౳ͰͨͲΓண͘ίετ͕௿͘ͳΔ͔΋͠Ε·ͤΜɻ ͕͔ͩ͠͠ɺTraitͰ͍͍ײ͡ʹ࣮૷͢Δํ๏͕Θ͔Βͳ͔ͬͨͷͰఘΊͨ Πϝʔδͱͯ͠͸ҎԼͷײ͡Ͱ࣮૷Ͱ͖ͨΒΑ͔ͬͨͷͰ͕͢ɺEloquent\Builder Λ࣋ͯͳ͍ҝ $result = Model::whereLike('hoge', $value)->get() Έ͍ͨͳॻ͖ํ͸ग़དྷͯ΋ $result = Model::where('hoge', $value)->orWhereLike('hoge', $value)->get(); Έ͍ͨͳEloquent\Builderͷؔ਺͔Βݺͼग़ͦ͏ͱ͢Δͱ౰વίέͯ͠·͍·͢ɻ ͳΜ͔͍͍ײ͡ʹTraitͰ࣮૷͢Δํ๏͕͋ͬͨΒڭ͑ͯԼ͍͞ɻ
10. Copyright© M&AΫϥ΢υ 10 3.TraitͰ࢖͍ճͤΔύʔπͱͯ͠༻ҙ͢Δ Ϙπ <?php declare(strict_types=1); namespace App\Libs; trait EloquentQueryBuilder { protected function whereLike(string $attribute, string $keyword, int $position = 0) { $keyword = addcslashes($keyword, '\_%'); $condition = [ 1 => "{$keyword}%", -1 => "%{$keyword}", ][$position] ?? "%{$keyword}%"; return $this->orWhere($attribute, 'LIKE', $condition); } protected function orWhereLike(string $attribute, string $keyword, int $position = 0) { $keyword = addcslashes($keyword, '\_%'); $condition = [ 1 => "{$keyword}%", -1 => "%{$keyword}", ][$position] ?? "%{$keyword}%"; return $this->orWhere($attribute, 'LIKE', $condition); } }
11. Copyright© M&AΫϥ΢υ 11 ࢖͍ํ $result = Model::whereLike('hoge', $keyword)->get(); // or $query = Model::query(); $result = $query::whereLike('hoge', $keyword)->get(); whereLike $result = Model::where('hoge', $value)->orWhereLike('hoge', $keyword)->get(); // or $query = Model::query(); $result = $query::where('hoge', $value)->orWhereLike('hoge', $keyword)->get(); orWhereLike
12. Copyright© M&AΫϥ΢υ 12 ·ͱΊ macro͔ΫΤϦείʔϓΛ࣮૷͢Δ͜ͱͰEloquentͷwhere۟Λॻ͘ͷͱಉ༷ͷه๏ͰҎԼͷؔ਺͕࢖༻Ͱ͖·͢ɻ • whereLike • orWhereLike ͋ͱ͸ɺwhere۟ʹੜͷLIKE͕۟ࠞೖ͠ͳ͍Α͏ʹίʔυͷ඼࣭ΛΩʔϓ͢Ε͹ղܾͰ͖·͢ɻ લड़ͷ௨Γmacro΍ΫΤϦείʔϓͰ͸IDEࢧԉ͕ޮ͔ͳ͍ͷͰɺ Laravelʹิ׬ɾܕ৘ใΛ෇༩ͯ͘͠ΕΔϥΠϒϥϦLaravel IDE Helper GeneratorͳͲΛ࢖༻͢Δͱศརͩͱࢥ͍·͢ɻ
13. Copyright© M&AΫϥ΢υ ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ 13