【未解決】ワイルドカードとエスケープ

※MySQLを前提として書いています
※この記事では、MySQLのバージョンは5.6です
※以下、「\」となっているのは半角バックスラッシュです。

SQLのワイルドカードには「%」と「_」があります。

-- 「hoge」で前方一致検索
SELECT * FROM `table` WHERE `column` LIKE 'hoge%';

-- 任意の1文字＋「oge」で検索
SELECT * FROM `table` WHERE `column` LIKE '_oge';

-- 「h」で始まり「e」で終わる4文字で部分一致検索
SELECT * FROM `table` WHERE `column` LIKE '%h__e%';

SQLモードがデフォルト状態の場合、
これらの文字そのもので部分一致検索などを行う際はエスケープが必要です。
また、エスケープ文字である「\」自体もエスケープが必要です。

色々試してみる。

-- 「hoge%」で完全一致検索
SELECT * FROM `table` WHERE `column` LIKE 'hoge\%';

-- 「hoge%」で前方一致検索
SELECT * FROM `table` WHERE `column` LIKE 'hoge\%%';

-- 「_oge」で完全一致検索
SELECT * FROM `table` WHERE `column` LIKE '\_oge';

-- 「\_oge」で完全一致検索
SELECT * FROM `table` WHERE `column` LIKE '\\\\\_oge';
SELECT * FROM `table` WHERE `column` LIKE '\\\\\\_oge';

-- 「\」＋任意の1文字＋「oge」で検索
SELECT * FROM `table` WHERE `column` LIKE '\\\_oge';
SELECT * FROM `table` WHERE `column` LIKE '\\\\_oge';

-- 「%h__e%」で完全一致検索
SELECT * FROM `table` WHERE `column` LIKE '\%h\_\_e\%';

-- 「%h__e%」で部分一致検索
SELECT * FROM `table` WHERE `column` LIKE '%\%h\_\_e\%%';

うーん…

-- 「\_oge」で完全一致検索
SELECT * FROM `table` WHERE `column` LIKE '\\\\\_oge';
SELECT * FROM `table` WHERE `column` LIKE '\\\\\\_oge';

-- 「\」＋任意の1文字＋「oge」で検索
SELECT * FROM `table` WHERE `column` LIKE '\\\_oge';
SELECT * FROM `table` WHERE `column` LIKE '\\\\_oge';

このあたりが分からん。

前者は「\をエスケープ→\\」「_をエスケープ→\_」なので合わせて「\\\_oge」
後者は「\をエスケープ→\\」「_はエスケープしない」なので合わせて「\\_oge」
…と予想していたのですが。

2つずつ書いていますが、両方ともどちらでも動くというのもよく分からん。
もし文字としての「\」は4つにする必要があるとすれば、
前者は「\\\\\_oge」が正しく、後者は「\\\\_oge」が正しい…のでしょうか。うーん分からん。

ちなみに、

SELECT * FROM `table` WHERE `column` LIKE '\\\\\\\\\\\\'; -- 12個

としてみると、「\\\」がヒットしました。4つにするべきということで合ってるのかなあ…

調べてみるとまた違う情報が出てくる上に、同じようにハマっている人しか出てこない…。

参考：MySQLのLikeでバックスラッシュを検索すると・・・。 - Action*3
http://www.terut.net/?p=238

参考：MySQLでバックスラッシュ（\）を検索 - SetucoCMS公式ブログ
http://design1.chu.jp/setucocms-pjt/?p=1390

さて、ここまではSQL単体の話なのですが、
PHPが絡んでくると更にややこしくなります。

「ユーザの入力した文字列で前方一致検索する」処理があるとします。

ここで

// 入力値取得
$word = [ユーザ入力値];

// クエリ組み立て
$sql = "SELECT * FROM `table` WHERE `column` LIKE ? ";
$param[] = $word . "%";

// $sqlと$paramを使ってクエリ実行
…

というような作りにしていると、ユーザが「%hoge」という文字列を入力した際、「%hogeという文字列で前方一致」ではなく「hogeという文字列で部分一致」になります。
プレースホルダを使っている以上深刻なセキュリティホールになることはないと思いますが、開発者にとってもユーザにとっても意図しない動作になってしまいます。
そのため「%」「_」「\」はPHP側で加工しておく必要があります。

結論から言うと、とりあえず以下の処理を加えました。

// エスケープ
$word = str_replace(array("\\", "%", "_"), array("\\\\", "\%", "\_"), $word);

問題になるのが、PHPでも「\」はエスケープが必要であること。
「%」は「\%」にすればいいの？ それとも「\\%」か？ と思って両方試してみると、どちらでも同じ結果でした。う、うーん。
内部的にいろいろ動いてるんでしょうが、最終的に発行するクエリも同じになっている模様。
とりあえず、社内の従来の処理に合わせて「\%」の方を選択しましたが、本来は一体どちらにすべきなのか…。

また、「\」はもっと困るところでした。
そもそも自社のWEBアプリは「\」を入力として受け付けず無視されるので考慮自体しなくて良いといえば良いのですが…
とりあえず暫定的に、検索単語をコードにベタ書きしてお試し。

// 検索する単語
$word = "\\"; // 実質「\」で検索したい

// エスケープ
$word = str_replace("\\", "\\\\", $word);

// クエリ組み立て
$sql = "SELECT * FROM `table` WHERE `hoge` LIKE ? ";
$arg_list = array($word);

// $sqlと$paramを使ってクエリ実行
…

これで「\」がヒットしたんですよねー。
PHPでのエスケープを考えると、「\\」を「\\\\\\\\」にするべきかと思ったのですがそれでは「\」がヒットしませんでした。（「\\」がヒットする模様）
うーん分からん。

しかしこれ、もしかしてフレームワークによっても差が出てくるところ？
（尚、弊社ではZendFrameworkをベースにしています）