お前らのLike検索は間違っている (QueryBuilder/Eloquent)

煽りタイトルすみません。
Laravelタグをつけてはいますが、特定の言語/フレームワークに限らない、Like検索時のエスケープの注意点についてお話します。
(@wand_ta 指摘ありがとうございます! 修正しました)

本記事の対象読者

• 安易に以下のようなコードを書いてしまう方
  • $query->where('name', 'like', "%{$keyword}%")
• addcslashes ? ナニソレ? 旨いの？ って方
• 1つのバックスラッシュを含むデータを検索するためにコードでは8個のバックスラッシュを書かなければならない場合があることに驚きを隠せない方
  • DB::select("SELECT * FROM items WHERE name LIKE '%\\\\\\\\%'")

よくある間違い

商品テーブルに「100%りんご」という名前のデータが登録されているとします。
あなたの実装した部分一致検索APIは「%」を検索キーワードに指定して正しく検索できるでしょうか？
また、「 _ 」や「 \ 」を指定して正しく検索できるでしょうか？

間違った実装

商品テーブルに対し、指定されたキーワードをSQLのLIKEを使って部分一致で検索するAPIを作るものとします。
とても素朴に、'%' . $keyword . '%'のような感じでリクエストパラメタから得たキーワードの両端に%をつけてLIKE検索した実装をしました。

app/Http/Controllers/ItemController.php

class ItemController extends Controller
{
    public function index(Request $request): JsonResponse
    {
        $keyword = $request->input('keyword') ?? '';

        // キーワードで商品名を検索 ***間違った実装***
        $rows = Item::where('name', 'like', '%' . $keyword . '%')->get();

        return response()->json($rows);
    }
}

何がまずいか？

テーブル

まず、こんな商品テーブルがあるとします。
商品名として「%」や「_」や「\」といった文字が含まれています。

itemsテーブル

mysql> SELECT * FROM items;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 100%りんご      |
|  2 | abc_コーラ      |
|  3 | CC\1000       |
+----+---------------+
3 rows in set (0.00 sec)

curlで確認 (意図した挙動をしない)

先程のLaravel APIにキーワードとして「100%」を指定して検索してみましょう。
「100%りんご」がヒットすることを期待しますが、
実際には「CC\1000」という商品もヒットしてしまいました。
※curlコマンドでは、キーワード「100%」をURLエンコードした?keyword=100%25というクエリ文字列が指定されています。

# 「100%」を含む商品を探したいが。。。
% curl -s 'http://localhost/api/items?keyword=100%25' | jq .
[
  {
    "id": 1,
    "name": "100%りんご"
  },
  {
    "id": 3,
    "name": "CC\\1000"  # ※「CC\1000」のエスケープ文字列表現
  }
]

また、今度はキーワード「\」を含む商品を探したいとき、「CC\1000」がヒットすることを期待しますが、実際には何もヒットしません。
※キーワード「\」をURLエンコードした?keyword=%5cというクエリ文字列が指定されています。

# 「\」を含む商品を探したいが。。。
% curl -s 'http://localhost/api/items?keyword=%5c' | jq .
[]

解説

前置き

• 本記事は主にLIKE演算子のワイルドカードのエスケープについて説明します
• 照合順序、Locale等については言及しません
  • 大文字小文字、バイナリ比較、ロケールに応じた照合、など
• いわゆる「全文検索」機能、形態素解析等については言及しません
• いわゆるSQL組み立てにおけるエスケープ、SQLインジェクションといったセキュリティについては直接言及しません。
• 本記事は以下の環境で検証しています。
  • Laravel 7.4
  • MySQL 8.0
  • PostgreSQL 11.5
• 他のバージョン、他のDB(Oracle, SQL Serverなど)、他の言語/フレームワークでも基本的な考え方は同じですが、具体的な差異については言及しません。

SQLで検索するとき

1. 単純なLIKE検索

単純に「100」という文字列を検索したい場合は、ワイルドカード%で挟んで検索パターン文字列を作り、LIKE演算子で比較します。

単純なLIKE検索

-- 「100」を含むものを探す
mysql> SELECT * FROM items WHERE name LIKE '%100%';

結果

+----+---------------+
| id | name          |
+----+---------------+
|  1 | 100%りんご      |
|  3 | CC\1000       |
+----+---------------+
2 rows in set (0.00 sec)

2. ワイルドカード文字そのものを検索したいとき (要エスケープ)

文字としての%を検索したいときは\でエスケープして、\%としてパターン文字列を指定します。
部分一致で探したいので、前後を%で挟むと %\%% というパターン文字列になります。
このパターン文字列をLIKE演算子の右辺で指定するときに、シングルクォートで囲んであげましょう。
この際、クォート文字列のリテラル表現のために、さらに\をエスケープして\\とする必要があります。
結果として、クォート文字列表現としては'%\\%%'となります。

エスケープしてLIKE検索

-- 「%」を含むものを探す: 間違った例
mysql> SELECT * FROM items WHERE name LIKE '%%%';

-- 「%」を含むものを探す: 正しい例 (\でエスケープする)
mysql> SELECT * FROM items WHERE name LIKE '%\\%%';

結果

+----+---------------+
| id | name          |
+----+---------------+
|  1 | 100%りんご      |
+----+---------------+
1 row in set (0.00 sec)

同様に、文字としての「_」や「\」を探したいときもエスケープが必要です。
※「\」が増殖しているところに注目

-- 「_」を含むものを探す: 正しい例 (\でエスケープする)
mysql> SELECT * FROM items WHERE name LIKE '%\\_%';

-- 「\」を含むものを探す: 正しい例 (\でエスケープする)
mysql> SELECT * FROM items WHERE name LIKE '%\\\\%';

LIKE検索パターンのエスケープまとめ

• LIKE検索パターン文字列中では、%や_は ワイルドカード と呼ばれる特殊な役割を持ち、これらの文字列そのものを示したい場合はエスケープする必要があります
• エスケープ文字は LIKE演算子のESCAPE句で指定可能ですが、多くのDBでは省略した場合はデフォルトは\です。
• また、\文字そのものを示したい場合も同様にエスケープする必要があります

文字	検索パターン内での役割	エスケープ後文字列
% (パーセント)	任意の長さの文字列(空文字列を含む)	\%
_ (アンダースコア)	任意の1文字	\_
\ (バックスラッシュ)	エスケープ文字	\\

MS SQL Server のLIKE検索パターンのエスケープ

Microsoft SQL Serverは少し特殊で、文字クラス[]を利用してメタ文字そのものを表現します。

文字	検索パターン内での役割	MS SQL Serverでのエスケープ後文字列
% (パーセント)	任意の長さの文字列(空文字列を含む)	[%]
_ (アンダースコア)	任意の1文字	[_]
[	文字クラスの開始	[[]

SQLのシングルクォート リテラル表現でのメタ文字のエスケープまとめ

SQL中で文字列をリテラル表現する場合はシングルクォートで囲む必要があります。
例: 文字列abc => クォート表現'abc'

この際に シングルクォートそのものなどのメタ文字を含む場合はエスケープ文字として\でエスケープする必要があります。
また、エスケープ文字\を含む場合も同様にエスケープします。
例: 文字列' => クォート文字列表現'\'
例: 文字列\ => クォート文字列表現'\\'

文字	SQL文での役割	エスケープしたクォート文字列表現
' (シングルクォート)	文字列の開始/終了	'\''
\ (バックスラッシュ)	エスケープ文字	'\\'

正しいLike検索の実装

エスケープを意識して検索APIの実装を見直し

API実装

LIKE検索パターンのためにワイルドカード文字、及びエスケープ文字をそれぞれエスケープしてあげます。
phpの addcslashes 関数で指定文字をバックスラッシュでエスケープできます。
(THX @wand_ta)

app/Http/Controllers/ItemController.php

class ItemController extends Controller
{
    public function index(Request $request): JsonResponse
    {
        $keyword = $request->input('keyword') ?? '';

        // キーワードのメタ文字をエスケープして商品名を検索
        $pat = '%' . addcslashes($keyword, '%_\\') . '%';
        $rows = Item::where('name', 'LIKE', $pat)->get();

        return response()->json($rows);
    }
}

※↑キーワードが空だったらエラーにする、結果を返さない、(Like条件で絞り込みせずに)全件を返す、 など実際には仕様に応じて実装すると思いますが、単純化のためここでは簡易な実装にしています。

なお、MS SQL Server の場合は以下のようにする必要があるかもしれません。(未検証)
また、preg_replace()の第1引数は正規表現を指定しますので、ここでもさらにエスケープしています。
もちろん、シングルクォート内の「\」をエスケープしています。

SQLServerの場合

        $pat = '%' . preg_replace('/([\\[_%])/', '[$1]', $keyword) . '%';

curlで確認

こんどは意図した検索ができるようになりましたね！

# 「100%」を含む商品を探して「100%りんご」がヒットする
% curl -s 'http://localhost/api/items?keyword=100%25' | jq .
[
  {
    "id": 1,
    "name": "100%りんご"
  }
]

# 「\」を含む商品を探して「CC\1000」がヒットする
% curl -s 'http://localhost/api/items?keyword=%5c' | jq .
[
  {
    "id": 3,
    "name": "CC\\1000"
  }
]

リファクタリング: likeスコープを提供するTrait

コントローラに複雑な処理を書きたくないので、Model側に処理を移しましょう。

Trait実装

以下のようなscopeを提供するトレイトを作成し、

app/Models/Traits/WhereLike.php

namespace App\Models\Traits;

trait WhereLike
{
    // 部分一致検索
    public function scopeWhereLike($query, string $column, string $keyword)
    {
        return $query->where($column, 'like', '%' . addcslashes($keyword, '%_\\') . '%');
    }

    // 前方一致検索
    public function scopeWhereLikeForward($query, string $column, string $keyword)
    {
        return $query->where($column, 'like', addcslashes($keyword, '%_\\') . '%');
    }
}

ModelでTrait利用

商品モデルで先程のトレイトをuseします。

app/Models/Item.php

namespace App\Models;

use App\Models\Traits\WhereLike;
use Illuminate\Database\Eloquent\Model;

class Item extends Model
{
    use WhereLike;
}

tinkerで確認

これで、Itemモデルに部分一致検索whereLike()や前方一致検索whereLikeForward()といったクエリビルダ用メソッドが追加されました。

tinker

# 名前に「%」を含むものを検索
>>> Item::whereLike('name', '%')->get()
=> Illuminate\Database\Eloquent\Collection {#4324
     all: [
       App\Models\Item {#4317
         id: 1,
         name: "100%りんご",
       },
     ],
   }

# 名前に「100」を含むものを検索
>>> Item::whereLike('name', '100')->get()
=> Illuminate\Database\Eloquent\Collection {#4326
     all: [
       App\Models\Item {#4318
         id: 1,
         name: "100%りんご",
       },
       App\Models\Item {#4328
         id: 3,
         name: "CC\1000",
       },
     ],
   }

# 名前を「100」で前方一致検索
>>> Item::whereLikeForward('name', '100')->get()
=> Illuminate\Database\Eloquent\Collection {#4312
     all: [
       App\Models\Item {#4316
         id: 1,
         name: "100%りんご",
       },
     ],
   }

おまけ: プログラム内で生SQLを利用するときのエスケープ

クエリビルダ/ORマッパーを利用せずに、生SQLを利用したい場合が稀にあります。
※もちろんそのような場合でも、バインド値・プレースホルダなどの安全に利用できる仕組みを極力活用すべきです。

• 再帰クエリ、JSON系の演算子、その他特殊な構文などDB固有の特殊な機能を使いたい場合
• DB固有のDDLを実行したい場合
• PostgreSQLの継承テーブルなどの都合で動的にテーブル名を導出したい場合
• 複雑なサブクエリで、クエリビルダ/ORマッパーでの組み立てがやりづらい場合
• 開発、検証中の一時的な仮実装

生SQLではLIKE文に限らずセキュリティの観点からエスケープを特に注意ください。

以下は、生SQLでLIKE文を書いた時のエスケープの例です。
※呆れるほど「\」が増殖しているところに注目

tinker

# 名前に「\」を含むものを検索
>>> DB::select("SELECT * FROM items WHERE name LIKE '%\\\\\\\\%'");
=> [
     {#4334
       +"id": 3,
       +"name": "CC\1000",
     },
   ]

1. 検索対象としての「\」
2. → LIKE検索パターンにするためにエスケープ → 「\\」 2倍!
3. → SQLのクォート文字列表現のためのエスケープ → 「\\\\」 4倍!!
4. → phpのクォート文字列表現のためのエスケープ → 「\\\\\\\\」 8倍!!!!

そうです、見ての通り バックスラッシュは増えるんです。
※ちなみに、Qiitaのこの記事を書くときに Qiita Markdownのためにエスケープするので、私は↑で16個の連続したバックスラッシュを書いています。

※もちろん、Productionコードで↑のような実装をしてはいけませんし、このような実装が必要な場面は考えにくいですが、あえて想像するなら、seederの実装内でシードデータを別テーブルの情報から何かしら探して使うとき、複雑なサブクエリが必要で、SQLターミナルで検証したクエリをそのまま流用して一時的にコードで使う、というケースでしょうか。。。

最後に

いっぱいバックスラッシュを書いた。
それはともかく良い年末を！