SQLのLIKE演算子のエスケープ

例えば、「\%foo」から始まる文字列を検索する場合には、どのようなSQL文を書けばよいのでしょうか。

条件は以下の通りです。

1. DBMSソフトはMySQL
2. ESCAPE節は使わない

MySQLでESCAPE節を使わない場合、ワイルドカード文字（「%」や「_」）は「\」でエスケープすることになります。

間違った答え

直感的に以下のようなSQL文を書いてしまう人もいると思います。

SELECT * FROM table1 WHERE hoge LIKE '\\\%foo%';

実際に試して見ます。

mysql> SELECT 123 FROM dual WHERE '\\%foo456' LIKE '\\\%foo%';
+-----+
| 123 |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT 123 FROM dual WHERE '%foo456' LIKE '\\\%foo%';
Empty set (0.01 sec)

最初のSQL文では、「\%foo456」という文字列を調べています。レコードが返ってきているので、LIKE演算子の結果は真です。その次のSQL文では、「%foo456」について調べています。LIKE演算子は偽の値を返しており、レコードは返されません。

これだけを見るとうまくいっていっているように思えますが、実際はそうではありません。次のSQLの結果を見ると、それが判ります。

mysql> SELECT 123 FROM dual WHERE '\\xxxfoo456' LIKE '\\\%foo%';
+-----+
| 123 |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

「'\\xxxfoo456' LIKE '\\\%foo%'」の結果は真となっています。なぜそうなるかというと、%のエスケープに失敗しており、「xxx」の部分が%（ワイルドカード）でマッチしてしまっているためです。

正解

「\%foo」から始まる文字列を検索するSQL文は、以下のようになります。

mysql> SELECT 123 FROM dual WHERE '\\%foo456' LIKE '\\\\\\%foo%';
+-----+
| 123 |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT 123 FROM dual WHERE '\\xxxfoo456' LIKE '\\\\\\%foo%';
Empty set (0.00 sec)

ちょっと「\」が多いんじゃないの？と思われるかもしれませんが、期待通り「\%foo456」は真となり、「\xxxfoo456」は偽となっています。

処理の方法

LIKEに与える文字列のエスケープ処理は、以下のように行なえばよいと思います。

1. LIKE演算子のワイルドカード文字のエスケープ
   「%」「_」「\」を「\」でエスケープします（当然、ワイルドカードどして機能させたい「%」「_」はエスケープしない）。
2. SQL文字列のエスケープ
   「\」「'」「"」とNULLや改行文字などを「\」でエスケープします。

この2つの処理を、1、2の順番でやります（DBMS側でのSQL文の解析時には、2→1という順でデコード処理されるはずです）。

1の処理の方はプログラムを自作する必要があると思います。一方2は、バインド機構（Prepared Statement）が使える環境ならばバインドを使い、そうでなければMySQLのエスケープAPI（をラップするもの）を使うだけです。

1と2を分けずにごっちゃにしていたり、1で「\」をエスケープしていなかったり、順番が逆になっていたりというような間違いは、よくありそうな気がします。