Slide 1
Slide 1 text
Copyright© M&AΫϥυ
LaravelͰLIKE۟ͷSQLΠϯδΣΫγϣϯରࡦΛ͢Δ
PHP Conference Japan 2022
Slide 2
Slide 2 text
Copyright© M&AΫϥυ 2
Profile
גࣜձࣾM&AΫϥυ
Ώ͍
fyui001
@fyui_001
Slide 3
Slide 3 text
Copyright© M&AΫϥυ 3
खͳSQLΠϯδΣΫγϣϯҰൠతͳWebϑϨʔϜϫʔΫΛ༻͢Εجຊతʹൃੜ͠·ͤΜɻ
͔͠͠ɺLIKEݕࡧΛߦ͏߹DoS߈ཱܸ͕ͯ͠͠·͏͜ͱ͕͋Γ·͢ɻ
LIKE "%a%b%c%d%e%e%f%g%@%.%"
্هͷΑ͏ͳΫΤϦSQLΤϯδϯʹେ͖ͳෛՙΛ͔͚·͢ɻ
LIKE۟ͷϝλจࣈΤεέʔϓ͢Δඞཁ͕͋Γ·͕͢ɺ
$query->where('hoge', 'LIKE', '%' . $value . '%');
ͱʹॻ͍ͯ͠·͏έʔεଟ͍ͱࢥ͍·͢ɻ
Slide 4
Slide 4 text
Copyright© M&AΫϥυ 4
ରࡦ
LaravelͰͷ͜ͷLIKE۟ͷΠϯδΣΫγϣϯରࡦ͓ͦΒ̏͘௨Γ΄Ͳ͋Δͱࢥ͏ͷͰ
ͦΕͧΕͷιϦϡʔγϣϯΛ͝հ͍ͯ͜͠͏ͱࢥ͍·͢ɻ
Slide 5
Slide 5 text
Copyright© M&AΫϥυ 5
1. macroΛ༻ҙ͢Δ
·ͣBlueprintͷmacroΛఆٛ͢ΔͨΊʹ
αʔϏεϓϩόΠμΛ৽͘͠࡞Γ·͢ɻ(AppServiceProvider.phpʹॻ͖ࠐΉํ๏͋Δɻ
php artisan make:provider BlueprintServiceProvider
Slide 6
Slide 6 text
Copyright© M&AΫϥυ 6
1. macroΛ༻ҙ͢Δ
Slide 7
Slide 7 text
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);
});
}
Slide 8
Slide 8 text
Copyright© M&AΫϥυ 8
2.ΫΤϦείʔϓΛ͏
ModelͰҎԼͷΑ͏ʹఆٛ͠·͢ɻ
"{$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);
}
Slide 9
Slide 9 text
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Ͱ࣮͢Δํ๏͕͋ͬͨΒڭ͑ͯԼ͍͞ɻ
Slide 10
Slide 10 text
Copyright© M&AΫϥυ 10
3.TraitͰ͍ճͤΔύʔπͱͯ͠༻ҙ͢Δ
Ϙπ
"{$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);
}
}
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
Copyright© M&AΫϥυ 12
·ͱΊ
macro͔ΫΤϦείʔϓΛ࣮͢Δ͜ͱͰEloquentͷwhere۟Λॻ͘ͷͱಉ༷ͷه๏ͰҎԼͷ͕ؔ༻Ͱ͖·͢ɻ
● whereLike
● orWhereLike
͋ͱɺwhere۟ʹੜͷLIKE͕۟ࠞೖ͠ͳ͍Α͏ʹίʔυͷ࣭ΛΩʔϓ͢ΕղܾͰ͖·͢ɻ
લड़ͷ௨ΓmacroΫΤϦείʔϓͰIDEࢧԉ͕ޮ͔ͳ͍ͷͰɺ
LaravelʹิɾܕใΛ༩ͯ͘͠ΕΔϥΠϒϥϦLaravel IDE Helper GeneratorͳͲΛ༻͢Δͱศརͩͱࢥ͍·͢ɻ
Slide 13
Slide 13 text
Copyright© M&AΫϥυ
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ʂ
13