[PHP]SQLで危険な記号をエスケープする

きっかけになったクエリ生成プログラム
PHPで文字をエスケープする
Laravelの機能でSQLインジェクション対策をする

きっかけになったクエリ生成プログラム

if (!empty($codes)) {
                        $join->where(
                            function (Builder $builder) use ($codes) {
                                $joinedCodes = implode('|', $codes);

                                $builder->whereRaw('JSON_EXTRACT( JSON_EXTRACT( snapshot_as_json, \'$."data"."customers"\'), \'$[*].code\') REGEXP \'"(' .  $joinedCodes . ')"\'');

$codesにstringの配列が渡されてくるので、それを”|”で連結してSQLのREGEXPでOR検索するというものです。

今回の主題ではないのですが、普通のWHEREならIN句を使えばよいのですが、json型のカラムに入っている配列情報を対象にする為に、REGEXPを使ってこのような作りになっています。

$codesが[“aaa”,”bbb”,”ccc”]という内容で渡された場合、REGEXPの部分だけにフォーカスすると、以下のクエリが生成されます。

REGEXP '"(aaa|bbb|ccc)"'

ここで、シングルクォーテーションが渡されてきたり、バックスラッシュが使われてしまうと内容が文字列ではなくクエリの句として認識されてしまうので、SQLインジェクションができてしまうわけですね。

PHPで文字をエスケープする

PHPではaddcslashesというメソッドで、文字列内の指定した文字群をエスケープできるようになっています。これを適用すると、先述のコードは下記のようになります。

if (!empty($codes)) {
                        $join->where(
                            function (Builder $builder) use ($codes) {
                                $joinedCodes = implode('|', $codes);
                                $escapedCodes = addcslashes($joinedCodes, '\'\\');
                                $builder->whereRaw('JSON_EXTRACT( JSON_EXTRACT( snapshot_as_json, \'$."data"."customers"\'), \'$[*].code\') REGEXP \'"(' .  $escapedCodes . ')"\'');

クエリに変数を入れ込む位置がシングルクォーテーションで囲まれた文字列内なので、シングルクォーテーションとバックスラッシュだけエスケープすればよいという判断です。

addcslashes($joinedCodes, ‘\’\\’);