Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

    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