like演算子のエスケープ

SQLのlike演算子では、ワイルドカードとして解釈させたくない「%」や「_」をエスケープする必要があります。

じゃあエスケープ文字として何を使えばいいんだろうか?という話になりますが、それに関して昔自分がやった失敗を思い出しました。

自分がやった失敗というのは、エスケープ文字として「%」を使おうとしたことです。SQL文で「'」を「'」でエスケープするのと同じようにすればよいだろうと考えた訳です。

例えば「20%以内」を含むデータを検索するSQL文を以下のように書きました。

... WHERE foobar LIKE '%20%%以内%' ESCAPE '%';

データベースの種類にもよりますが、これはこれで期待したとおりに動きます。

しかし、このSQL文を見てふと思ったのが、「%」を含むデータを検索したい場合にどうなるんだろうか?ということです。

ためしに書いてみると、こんな感じになるはずです。

... WHERE foobar LIKE '%%%%' ESCAPE '%';

青字の「%」はワイルドカードとして解釈させたい「%」です。

ですが、実際にはワイルドカードとしては解釈されず、このSQL文は「%%」と完全一致するレコードしか抽出してくれません。

似たような例ですが、①先頭が「%」であるデータを検索するSQL文、②末尾が「%」であるデータを検索するSQL文、の2つについて考えてみます。

①先頭が「%」であるデータを検索するSQL文
... WHERE foobar LIKE '%%%' ESCAPE '%';

②末尾が「%」であるデータを検索するSQL文
... WHERE foobar LIKE '%%%' ESCAPE '%';

なんと、どちらも同じSQL文になってしまいました。

上の例を見て判るように、likeのエスケープ文字として「%」を使うことは基本的に無理ということになります。同じような理由で「_」もエスケープ文字として使えません。

ちなみに、上の①②や、一番最初の例の「%20%%以内%」をOracleDB2で実行するとSQLエラーになります。

【Oracle】
ORA-01424: missing or illegal character following the escape character

【DB2】
SQL0130N The ESCAPE clause is not a single character, or the pattern string contains an invalid occurrence of the escape character. SQLSTATE=22025

このエラーは、エスケープ文字を「%」や「_」以外の文字にした場合にも発生します。仮にエスケープ文字を「!」にしたとすると、「!%」「!_」「!!」とは解釈できない、裸の「!」がlikeに出現した場合には、SQLエラーになります。

検索系の機能の脆弱性検査をしている時に、この手のSQLエラーが内部で発生していると推測できる時がたまにあります(これ自体はSQLインジェクションではないので、基本的にはほうっておくことが多いですが)。

蛇足ですが、DB2のエラーメッセージに「single character」と書いてあるように、エスケープ文字は単一の文字でなければなりません。「ESCAPE 'AB'」のような文字列を指定すると、大抵のデータベースでSQLエラーになるようです。

これを利用すると、MySQLでも実行時のSQLエラーを起こすことが可能です。ESCAPEを、「ある文字列の1文字目のASCIIコードがNNで無いときは'ABC'のような値を返す」ような式にしておくと、うまいことBlind SQLインジェクションができる場合があります。

MySQLではゼロ除算や無理な型変換などを行なってもSQLエラーになりません。MySQLで実行時のSQLエラーを起こすのはかなり難しいのですが、その方法の一つとしてlike演算子が使えます(他にも金床本(isbn:9784887189409)に載っている方法などがあります)。

実行時SQLエラーを発生させる必要があるケースは余り多くはありませんが、知っておくと使えるときがあるかもしれません。