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

Transcript

1. Copyright© M&AΫϥ΢υ
   LaravelͰLIKE۟ͷSQLΠϯδΣΫγϣϯରࡦΛ͢Δ
   PHP Conference Japan 2022
   

   View Slide

2. Copyright© M&AΫϥ΢υ 2
   Profile
   גࣜձࣾM&AΫϥ΢υ
   Ώ͍
   fyui001
   @fyui_001
   

   View Slide

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 . '%');
   ͱ௚ʹॻ͍ͯ͠·͏έʔε͸ଟ͍ͱࢥ͍·͢ɻ
   

   View Slide

4. Copyright© M&AΫϥ΢υ 4
   ରࡦ
   LaravelͰͷ͜ͷLIKE۟ͷΠϯδΣΫγϣϯରࡦ͸͓ͦΒ̏͘௨Γ΄Ͳ͋Δͱࢥ͏ͷͰ
   ͦΕͧΕͷιϦϡʔγϣϯΛ͝঺հ͍ͯ͜͠͏ͱࢥ͍·͢ɻ
   

   View Slide

5. Copyright© M&AΫϥ΢υ 5
   1. macroΛ༻ҙ͢Δ
   ·ͣBlueprintͷmacroΛఆٛ͢ΔͨΊʹ
   αʔϏεϓϩόΠμΛ৽͘͠࡞Γ·͢ɻ(AppServiceProvider.phpʹॻ͖ࠐΉํ๏΋͋Δɻ
   php artisan make:provider BlueprintServiceProvider
   

   View Slide

6. Copyright© M&AΫϥ΢υ 6
   1. macroΛ༻ҙ͢Δ
   
   
   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()
   
   
   {
   
   
   //
   
   
   }
   ͢ΔͱҎԼͷΑ͏ͳϑΝΠϧ͕ੜ੒͞Ε·͢ɻ
   

   View Slide

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);
   
   
   });
   
   
   }
   

   View Slide

8. Copyright© M&AΫϥ΢υ 8
   2.ΫΤϦείʔϓΛ࢖͏
   ModelͰҎԼͷΑ͏ʹఆٛ͠·͢ɻ
   
   
   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);
   
   
   }
   

   View Slide

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Ͱ࣮૷͢Δํ๏͕͋ͬͨΒڭ͑ͯԼ͍͞ɻ
   

   View Slide

10. Copyright© M&AΫϥ΢υ 10
    3.TraitͰ࢖͍ճͤΔύʔπͱͯ͠༻ҙ͢Δ
    Ϙπ
    
    
    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);
    
    
    }
    
    
    }
    

    View Slide

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
    

    View Slide

12. Copyright© M&AΫϥ΢υ 12
    ·ͱΊ
    macro͔ΫΤϦείʔϓΛ࣮૷͢Δ͜ͱͰEloquentͷwhere۟Λॻ͘ͷͱಉ༷ͷه๏ͰҎԼͷؔ਺͕࢖༻Ͱ͖·͢ɻ
    ● whereLike
    ● orWhereLike
    ͋ͱ͸ɺwhere۟ʹੜͷLIKE͕۟ࠞೖ͠ͳ͍Α͏ʹίʔυͷ඼࣭ΛΩʔϓ͢Ε͹ղܾͰ͖·͢ɻ
    લड़ͷ௨Γmacro΍ΫΤϦείʔϓͰ͸IDEࢧԉ͕ޮ͔ͳ͍ͷͰɺ
    Laravelʹิ׬ɾܕ৘ใΛ෇༩ͯ͘͠ΕΔϥΠϒϥϦLaravel IDE Helper GeneratorͳͲΛ࢖༻͢Δͱศརͩͱࢥ͍·͢ɻ
    

    View Slide

13. Copyright© M&AΫϥ΢υ
    ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ
    13
    

    View Slide