Y. Furui's daily report

2018年1月20日土曜日

2018/01/08(月) 計算機環境の整備

●卒業研究の指導
・卒研生3名のそれぞれに卒業論文の執筆について指導した

●計算機環境の整備
★ゼミナール室内の録画用ノートPC rain (ThinkPad X60) の保守
・昨日のアップデートの続きを行った

★研究室内仮想サーバの保守
・aurora, dawn, dusk, lightning, nimbus, thunderhead で yum update と reboot -n を実行した

●学科Webサイトの保守
・もう一度 yum update を試したが実行できない
・CNC にメールで質問を投げた

●学生への修学指導
・非常勤のHi先生からメールで、1年次生のレポート課題実施状況について連絡をうけた
・自分なりに検討したうえで返信した
・学生本人と電話で連絡をとった。明日10:10から面談することになった

●Javaプログラミング教科書
・目次案を更新した

2018/01/07(日) Javaプログラミング教科書

午後から。

●計算機環境の整備
★研究室内のPCの保守
・デスクトップPC crescent, ノートPC hail, 2-in-1 PC tornade で Windows Update を実行した

★ゼミナール室内のPCの保守
・ノートPC mist, rain およびデスクトップPC fullmoon で Windows Update 等を実行した
・rain のアップデートは退勤までに完了しなかった

●Javaプログラミング教科書
・目次案を更新した
・K先生と自室で、この目次案やpk1で新しく採用する教科書などについて話した

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() として採用する。

2018/01/05(金) ほとんどオフ

●da2 試験準備
・Yo先生から受け取った問題案を解いてみた
・メールで回答した

2018年1月5日金曜日

2018/01/04(木) 昨日の続き

夕方からオフィスに。

●Javaプログラミング教科書
・K先生からメールで、2018年度のpk1に新しいJavaの教科書を採用したい旨の連絡あり
・この本の目次のみを Amazon で確認した
・後日検討すること、ついでにJavaプログラミング教科書の執筆について意見が欲しいことを返信した

●PHPの自作関数ql()の検証
昨日は関数 ql を修正して研究室内サーバ dusk, dawn (CentOS 7) で動作を確認した。今日はサーバ midnight (Windows Server) でも確認した。

Apache HTTP Server 2.2.14
PHP 5.3.1
MySQL 5.1.41

（参考）
Web検索で次のようなページを見つけた。このページの下のほうに同じ問題への解がある。「LIKE addcslashes」で検索すると上のほうに出てくるので、結構参照されているページなのではないかと思う。私自身では試していない。

・miau: LIKE のエスケープと addcslashes(), miau's blog?, (2009).

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

●その他
・Windowsサーバ midnight およびファイルサーバ moonlight 内に溜まった2017年度分の消費電力データを圧縮するなど整理した
・midnight から2015/10/17(土)時点の MySQL データベースのバックアップを削除した
・midnight のCドライブの空き容量が9 GBから20 GBに増えた

2018年1月3日水曜日

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(土)に再検討した。

2017/12/29(金)-2018/01/02(火) 帰省

4泊5日で帰省した。

●卒業研究の指導
・Tsさんの卒業論文第1稿を12/29(金)の昼に受け取った
・帰省中に添削した

●基礎演習（1年次向け）学生対応
・12/31(日)から1/02(火)にかけて、受講生1名からメールで、冬季休業期間中に4F演習室を使用したい旨の連絡があった
・数度のやりとりを経て、1/06(土) 11時頃に彼らの来室し、私が解錠などの対応を行うことになった

登録: 投稿 (Atom)