[PHP]SQLインジェクション対策としてMDB2でのプレースホルダの使い方

少しハマったので覚書として。

SQLインジェクションの対策としてエスケープ処理とか色々ありますが、
実際には対策としてはこの「プレースホルダ」が最も効果的。

今夜こそわかる安全なSQLの呼び出し方
　～ 高木浩光氏に聞いてみた

というわけで、MDB2でのプレースホルダの使い方と
少しハマった点を覚書として書いておく。

基本設定

例の処理を例にしていきます。

$url=$_GET['url'];
$sql = "select * from table where exists ( select * from table where url= $url ) and url= $url'";
$res = $mdb2->query($sql);
if (MDB2::isError( $res )) {die($res->getMessage());}

この処理自体はあるテーブル（table）から特定の[url列]に
マッチするデータの存在有無をチェックしています。

この処理をプレースホルダ（疑問符付きプレースホルダ）を使うと
こんな感じになります。

$sql="select * from table where exists ( select * from table where url= ? ) and url= ?";
$types = array('text', 'text');
$sth = $mdb2->prepare($sql, $types, MDB2_PREPARE_RESULT);
$data = array($url,$url);
$res = $sth->execute($data);
$sth->free();
if (MDB2::isError( $res )) {die($res->getMessage());}

「prepare」でSQLを準備しておいて「execute」でデータを入れて実行。
これだけでSQLインジェクションを効果的に防げるならやらない選択を
する理由はないですね。

使い方は他にも「名前付きプレースホルダ」等もありますが、
詳細は以下の参考URLで。

（参考）
Manual :: プリペアドステートメント
PHPでのSQLインジェクション対策 - プレースホルダ編: MDB2

メモ：prepare のパラメータの設定方法

今回の例ではprepareの三番目のパラメータは以下のようになってました。

$sth = $mdb2->prepare($sql, $types, MDB2_PREPARE_RESULT);

が、これはselect文の場合の設定（trueでもOK）です。
MDB2_PREPARE_RESULT の場合は、結果セットの各カラムの データ型 が配列、
TRUE の場合には、結果セットのデータ型を自動判定となるようです。

しかし、DML (data manipulation language - INSERT, UPDATE, DELETE) 文は、
返り値がselectとは異なるため、設定は以下になります。

$sth = $mdb2->prepare($sql, $types, MDB2_PREPARE_MANIP);

メモ：ORDER BY が動かない

これ、結局原因は分からないんですが仕様かな？
以下の例で「ORDER BY」が効きません。

$sql = "select * from table where school=? order by ? DESC";
$types = array('text', 'text');
$sth = $mdb2->prepare($sql, $types, MDB2_PREPARE_RESULT);
$data = array($school, $kyouka);
$res = $sth->execute($data);
$sth->free();
if (MDB2::isError( $res )) {die($res->getMessage());}

コレも例ですが、あるテーブルで「学校名」を指定して
「countname列（教科のイメージ）」にある数（得点）の大きい順に
出力しようとしてます。が、データは取得出来ますがORDER BYが効かない。

が、これなら正しくkokugoの得点の大きい順に正しく出力される。

$sql = "select * from table where name=? order by kokugo DESC";
$types = array('text');
$sth = $mdb2->prepare($sql, $types, MDB2_PREPARE_RESULT);
$data = array($school);
$res = $sth->execute($data);
$sth->free();
if (MDB2::isError( $res )) {die($res->getMessage());}

なので、if分等の分岐でORDER BY以降は変数ではなく
固定値で指定するようにしちゃうのがよさそうです。