$30 off During Our Annual Pro Sale. View Details »

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

ゆい
September 25, 2022

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

ゆい

September 25, 2022
Tweet

More Decks by ゆい

Other Decks in Programming

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