2018/01/03(水) 進路指導Webサイトの再開発

午後からオフィスに。


●卒業研究の指導
・Tsさんの卒業論文の添削結果を本人の机の上に返した
・その旨をSlackで連絡した

●進路指導Webサイトの再開発、PHPサンプルコード集の更新
★PHPの自作関数ql()の修正
先月、進路指導Webサイト再開発版に、円記号(yen sign, ￥)すなわちASCIIにおけるバックスラッシュ (reverse solidus, ＼)で検索できない不具合があることが判明した。原因が自作の関数 ql にあることも判っていたが、緊急度が低いので放置していた。これを今回解決した。なお、ソフトウェアのバージョンは次の通りである。

• Apache HTTP Server 2.4.6
• PHP 5.4.16
• MariaDB 5.5.56

（前提）
まず、修正前のソースコードを次に示す。肝心なところ以外は適当に省略する。最後の関数 ql は、LIKE節のなかで使う文字列をエスケープするために自作したもので、今回の問題の原因である。

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

また、ここに示した関数の使用例を次に示す。

$db = beginMySQLi();

//検索キー $str と column が一致する行を探す
$sql1 = "SELECT * FROM table WHERE column='" . q($str) . "'";
$result1 = $db->query($sql1);

//検索キー $str が column に含まれる行を探す
$sql2 = "SELECT * FROM table WHERE column LIKE '%" . ql($str) . "%'";
$result2 = $db->query($sql2);

LIKE節ではアンダスコア(_)とパーセント記号(%)がワイルドカードとして扱われるので、検索キー $str の中にこれらの記号がある場合には円記号(\)でエスケープしなくてはならない。このエスケープ処理のために mysqli::real_escape_string() と addcslash() を組み合わせる関数 ql を宣言して使用する。

例えば、検索キー $str の値が \500なら100% という文字列であるとき、これを引数として関数 ql を呼び出すと、この関数は \\500なら100\% を返す。その結果、変数 $sql2 の値は SELECT * FROM table WHERE column LIKE '%\\500なら100\%%' になる。

このSQL文において、LIKE節の右辺にある文字列 %\\500なら100\%% の最初と最後の%はエスケープされていないからワイルドカードと見なされる。一方、2番目の%は\によってエスケープされているから通常の文字とみなされる（その際に\は外される）。したがって、このSQL文は \500なら100% という文字列が column の途中のどこかに含まれる行を探す。

（問題）
上に示したコードは検索キーの最後に円記号(\)があるときにはうまく動かない。
例えば、検索キー $str の値が \ という1文字のみの文字列であるとき、関数 ql は \\ を返すので、変数 $sql2 の値は SELECT * FROM table WHERE column LIKE '%\\%' になる。

このSQL文において、LIKE節右辺の最初のパーセント記号(%)はエスケープされていないからワイルドカードとして扱われる。一方、最後の%の前には\\があり、これは\という通常の文字とみなされる……のではなくエスケープ記号として解釈され、その後の%はワイルドカードではなく通常の文字とみなされるようである。このSQL文は % という文字列が column の途中ではなく右端に含まれる行を探す。

なぜこうなるのかは解らない。PHP の文字列リテラルには PHP と MariaDB のために二重のエスケープを要するのは解るが、この問題の場合には MariaDB のエスケープのみを考えればよいはずである……のに。

（検討）
関数 ql を次のように変更したが良くなかった。

/**
  * SQL用エスケープ(ワイルドカードを含めて)。LIKEの後ではこれを使う。
  */
function ql($s) {
  $db = beginMySQLi();
  return addcslashes($db->real_escape_string($s), '%_\\'); //←これもNG
}

この変更された関数 ql は円記号(\)を二重にエスケープ対象にする。例えば、検索キー $str の値が \ という1文字のみの文字列のとき、関数 ql は \\\\ を返すので、変数 $sql2 の値は SELECT * FROM table WHERE column LIKE '%\\\\%' になる。四つ続く\は最終的に1文字の\とみなされ、最初と最後のパーセント記号(%)はワイルドカードとして扱われる……ようである。これだけを見れば前述の問題は解決している。

しかし、検索キーにシングルクォート(')が含まれるときには正しくないSQL文が生成されることになる。例えば、検索キー $str の値が i'mlucky という文字列であるとき、関数 ql は \\' を返し、変数 $sql2 の値は SELECT * FROM table WHERE column LIKE '%i\\'mlucky%' になる。このとき2番目のシングルクォート(')の前には\\があり、これはエスケープ記号として解釈されないために、2番目のシングルクォート(')は閉じカッコとして扱われる。そのあとに mlucky%' という意味不明な部分がくっついたこのSQL文は、MariaDB に実行させようとしてもエラーになる。この現象は理解できる。

（解決方法）
関数 ql を次のように修正して解決した。

/**
  * SQL用エスケープ(ワイルドカードを含めて)。LIKEの後ではこれを使う。
  */
function ql($s) {
  $db = beginMySQLi();
  return addcslashes($db->real_escape_string(addcslashes($s, '\\')), '%_'); //←これはOK
}

テスト用PHPスクリプトにいろいろな検索キーを入力して MariaDB の動作を試し、その限りでは正しく検索できることを確認した。

自作の関数 ql はあちこちで使っている。さしあたり、研究室内サーバ dawn に置いているPHPサンプルコード集と進路指導Webサイト再開発版システムのものを更新した。日報ブログ相互点検システムも更新したが、このシステムではそもそも ql の呼び出しがなかった。他にも更新するべきシステムがあるかもしれないが、普段使っているものの中には思い当たらない。

（追記）
2018/01/06(土)に再検討した。