2018/01/06(土) LIKE節のなかで使う文字列をエスケープするための関数に関する検討

●基礎演習（1年次向け） 学生対応
・事前に連絡のあった受講者2名が4F演習室に入室するのを許可した
・事前連絡では11時頃という話であったが、実際に来たのは11時半を廻ってから
  - 結構待たされた
  - 遅れる場合は連絡するようにと言っておいたにもかかわらずその連絡なし
・PowerDirector で動画を挿入する手順を質問されたので指導した

●C3PO メール処理
・K先生からメールで事業中間報告と今後の実施計画書の案を受け取った
・目を通し、修正案を作って返送した
・一度書いた案を保存し損ねたらしく消えてしまって、書き直すのが面倒であった

●卒業研究の指導
・研究室内Wikiに記載していた1月の日程に追加・修正を行った
・MLを通じて卒研生に連絡した
・Smくんからの卒業論文執筆に関する質問に回答した

●卒業研究発表会の準備
・今年度のグループ幹事に当たっていた
・少し早いがプログラム作成の準備を始めた。同じグループの他の先生方にメールで連絡した

●PHPの自作関数 ql() の検証
・結局 $mysqli->real_escape_string(addcslashes($str, "%_\\")); にした
・詳細は後述

●シラバス案
・新da1, pe3 についてYo先生からメールで案を受け取ったので確認して返信した
・気付いたことがあり、pk2, pe2 のシラバス案を修正してメールで送付しなおした



◎LIKE節のなかで使う文字列をエスケープするための関数
（概要）
PHP + MySQLプログラミングにおいて、LIKE節を使ってテーブル中の文字列を検索する場合について考える。

SELECT * FROM liketest_t WHERE lttext LIKE '%$str%'

この $str の部分に次のような文字が含まれるとき、これらの文字はエスケープしなくてはならない。

• 円記号(yen sign, ￥)すなわちASCIIにおけるバックスラッシュ (reverse solidus, ＼)
• シングルクォート
• パーセント記号(%)
• アンダスコア(_)

下の二つは mysqli::real_escape_string() ではエスケープされない。そこで、このエスケープ処理のためのPHP関数 ql() を作る。

/**
  * SQL用エスケープ。LIKEの後ではこれを使う。
  */
function ql($s) {
  $mysqli = beginMySQLi();
  return $mysqli->real_escape_string(addcslashes($s, '%_\\');
}

この関数は次のようにして使用する。

$sql = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . ql($str) . "%'";

（動作確認の方法）
まず、エスケープ処理を行う関数 ql() 候補として5種類を作成した。これらを ql_A()～ql_E() とする。これに mysqli::real_escape_string() のみを実行する関数 q() を加え、6種類を使用した。次にLIKE節を用いて検索を実行し、文字列の検索がうまくいくかどうかを調べた。各関数が行うエスケープ処理を次に示す。

関数	エスケープ処理
q	$mysqli->real_escape_string($s)
ql_A	addcslashes($mysqli->real_escape_string($s), '%_')
ql_B	addcslashes($mysqli->real_escape_string(addcslashes($s, '\\')), '%_')
ql_C	addcslashes($mysqli->real_escape_string($s), '%_\\')
ql_D	$mysqli->real_escape_string(addcslashes($s, '%_'))
ql_E	$mysqli->real_escape_string(addcslashes($s, '%_\\'))

（動作確認用の実行環境）
動作確認のために使用したソフトウェアのバージョンは次の通りである。

• Apache HTTP Server 2.4.6
• PHP 5.4.16
• MariaDB 5.5.56

（動作確認用のデータベース）
MariaDB データベース内に次のようなテーブル liketest_t を作成した。

ltid (INT)	lttext (TEXT)
1	100%
2	i'mlucky
3	\500
4	hoge\\hoge
5	%100
6	foo\%bar

（動作確認用の検索文字列）
テーブル liketest_t を次の文字列で検索する。検索結果として正解となる行のltidを併せて示す。

検索文字列	(空白)	%	\	'	\%	\\
正解の検索結果	1～6	1, 5, 6	3, 4, 6	2	6	4

（動作確認用のコード）
動作確認に使用したPHPのコードを次に示す。

/**
  * MySQLデータベースへの接続を実行する。接続済みならその接続を利用する。
  */
function beginMySQLi() {
  global $mysqli;
  if (!isset($mysqli)) {
    $mysqli = new mysqli(/*** 詳細略 ***/);
  }
  return $mysqli;
}
 
/**
  * SQL用エスケープ。LIKEの後ではql()を使うこと。
  */
function q($s) {
  $mysqli = beginMySQLi();
  return $mysqli->real_escape_string($s);
}
 
/**
  * SQL用エスケープ(ワイルドカードを含めて)。LIKEの後ではこれを使う。
  */
function ql_A($s) {
  $mysqli = beginMySQLi();
  return addcslashes($mysqli->real_escape_string($s), '%_');
}
function ql_B($s) {
  $mysqli = beginMySQLi();
  return addcslashes($mysqli->real_escape_string(addcslashes($s, '\\')), '%_');
}
function ql_C($s) {
  $mysqli = beginMySQLi();
  return addcslashes($mysqli->real_escape_string($s), '%_\\');
}
function ql_D($s) {
  $mysqli = beginMySQLi();
  return $mysqli->real_escape_string(addcslashes($s, '%_'));
}
function ql_E($s) {
  $mysqli = beginMySQLi();
  return $mysqli->real_escape_string(addcslashes($s, '%_\\'));
}

$mysqli = beginMySQLi();

$sql1 = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . q($str) . "%'";
$result1 = $mysqli->query($sql1);

$sql2 = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . ql_A($str) . "%'";
$result2 = $mysqli->query($sql2);

$sql3 = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . ql_B($str) . "%'";
$result3 = $mysqli->query($sql3);

$sql4 = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . ql_C($str) . "%'";
$result4 = $mysqli->query($sql4);

$sql5 = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . ql_D($str) . "%'";
$result5 = $mysqli->query($sql5);

$sql6 = "SELECT * FROM liketest_t WHERE lttext LIKE '%" . ql_E($str) . "%'";
$result6 = $mysqli->query($sql6);

//表示処理は割愛

なお、ここに示した関数のうち q() は本来はLIKE節には使用しない。関数 q() の本来の使用例を次に示す。

//検索キー $str と ltid が一致する行を探す
$sqlX = "SELECT * FROM liketest_t WHERE ltid='" . q($str) . "'";
$resultX = $mysqli->query($sqlX);

//lttext が文字列 $str である行を挿入する
$sqlY = "INSERT INTO liketest_t (lttext) VALUES ('" . q($str) . "')";
$resultY = $mysqli->query($sqlY);

（動作確認の結果）
各関数を使用してエスケープした文字列をLIKE節に使用して検索を行った。エスケープした結果の文字列を次に示す。また、検索結果の行のIDを各セルのカッコ内に示し、この検索結果が正解ではない場合にはそのセルの背景色を灰色にして示す。

	(空白)	%	\	'	\%	\\
q	(1～6)	% (1～6)	\\ (1)	\' (2)	\\% (1,5,6)	\\\\ (3,4,6)
ql_A	(1～6)	\% (1,5,6)	\\ (1)	\' (2)	\\\% (3,4,6)	\\\\ (3,4,6)
ql_B	(1～6)	\% (1,5,6)	\\\\ (3,4,6)	\' (2)	\\\\\% (6)	\\\\\\\\ (4)
ql_C	(1～6)	\% (1,5,6)	\\\\ (3,4,6)	\\' (エラー)	\\\\\% (6)	\\\\\\\\ (4)
ql_D	(1～6)	\\% (1,5,6)	\\ (1)	\' (2)	\\\\% (3,4,6)	\\\\ (3,4,6)
ql_E	(1～6)	\\% (1,5,6)	\\\\ (3,4,6)	\' (2)	\\\\\\% (6)	\\\\\\\\ (4)

（検討）
検索対象の文字列が円記号ないしバックスラッシュ(\)のみであるとき、関数 q(), ql_A(), ql_D() の三つが正解にならないのは、これがSELECT文に結合されたときに後ろにくるパーセント記号(%)をエスケープする働きをしてしまい、右端に % がある行に合致するからである。
全ての検索結果が正解となるのは関数 ql_B() および ql_E() の二つである。両者の違いは検索対象の文字列に % が含まれるときの \ の数であり、 ql_E() のほうが \ が多い。この二つの関数を比較すると、関数自体が単純なのは ql_E() のほうである。

（結論）
関数 ql_E() を ql() として採用する。