PHPでのSQLインジェクション対策 - プレースホルダ編: MDB2

PEAR::MDB2

MDB2はPHPでデータベースを操作するためのPEARのパッケージの１つです。pg_query_params、PDOなどと比較すると動作が低速ではありますが、

文字エンコーディングを意識している
integer、float、boolean型などの値を、文字列としてではなく数値・論理値としてやりとり出来る
bytea型のカラムを、ラージオブジェクト（blob）型として遜色なく扱える
別のデータベース管理システム（MySQL、Oracleなど）から、あるいはそちらへの移行がしやすい

などの理由で、MDB2を使うことを推奨します。

データベースを扱うPEARパッケージには他にもPEAR::DB、PEAR:MDBがありますが、これらの新規の開発はすでに終了しているようなので、できるだけMDB2を使用してください。なおインストールは、権限のあるユーザーで以下のようにします。

# pear install MDB2

# pear install MDB2_Driver_pgsql
// または
# pear install MDB2#pgsql

PostgreSQLへの接続（connect、factory、singleton）時には、クライアントの文字エンコーディングを設定しましょう。また、Extendedモジュールをロードしておくと、プリペアドステートメントの準備と実行の両方を１度に発行してくれる便利なメソッド群を使うことができるようになります。

require_once('MDB2.php');
$mdb2 = MDB2::connect(array(
  'phptype'  => 'pgsql',
  'username' => 'user',
  'password' => 'pass',
  'hostspec' => 'localhost',
  'port'     => 5432,
  'database' => 'testdb',
  'charset'  => 'utf8'
));
$mdb2->loadModule('Extended');
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);

「MDB2_FETCHMODE_ASSOC」は検索結果の取得方法をカラム名をキーとした連想配列で受け取りたい場合に指定しますが、番号をキーとした数値添字配列で受け取りたい場合は「MDB2_FETCHMODE_ORDERED」を、カラム名をプロパティとしたオブジェクトで受け取りたい場合は「MDB2_FETCHMODE_OBJECT」を指定してください。

名前付きプレースホルダ

まずはSQLに埋め込むそれぞれのプレースホルダに":****"という名前をつける例を見てみましょう。

$sth = $mdb2->prepare(
  'SELECT userid, username, profile FROM users WHERE username LIKE :username',
  array('username' => 'text'),
  array('integer', 'text', 'text')
);
$res = $sth->execute(
  array('username' => '%' . $mdb2->escapePattern($_REQUEST['username']) . '%')
);
$all = $res->fetchAll();
$sth->free();

$sth = $mdb2->prepare(
  'UPDATE users SET profile = :profile WHERE userid = :userid',
  array('profile' => 'text', 'userid' => 'integer'),
  MDB2_PREPARE_MANIP
);
$sth->execute(
  array(
    'profile' => $_REQUEST['profile'], 
    'userid' => $_SESSION['me']['userid']
  )
);
$sth->free();

疑問符プレースホルダ

つぎはSQLに埋め込むそれぞれのプレースホルダを疑問符"?"で示す例です。

$sth = $mdb2->prepare(
  'SELECT userid, username, profile FROM users WHERE username LIKE ?',
  array('text'),
  MDB2_PREPARE_RESULT
);
$res = $sth->execute('%' . $mdb2->escapePattern($_REQUEST['username']) . '%');
$all = $res->fetchAll();
$sth->free();

$sth = $mdb2->prepare(
  'UPDATE users SET profile = ? WHERE userid = ?',
  array('text', 'integer'),
  MDB2_PREPARE_MANIP
);
$sth->execute(array($_REQUEST['profile'], $_SESSION['me']['userid']));
$sth->free();

PEARモジュール「MDB2」のメソッドprepareでは、１つめの引数にはプレースホルダを含んだSQL本体を、２つめにそれぞれのプレースホルダのデータ型を配列の形式で、３つめに検索された結果を変換するデータ型を配列の形式で指定します。

上の例でも示していますが、プレースホルダには名前付きのものと疑問符が使えます。疑問符のほうが楽に見えますが、２つめの引数やexecute実行時にプレースホルダの順番を意識しなくて済むという意味では、名前付きプレースホルダの方が有利です。

２つめの引数は、プレースホルダのデータ型です。この引数は省略可能で、その場合は全てのパラメータが文字列として扱われます。ただこの時、SQLサーバではテキスト型からカラムのデータ型へ暗黙のキャストが行われるため、数値精度の劣化や性能低下を引き起こす可能性があります。できるだけ指定するようにしましょう。

指定できるデータ型の詳細は、「PEAR::データ型の処理の概要」を参照してください。

３つめの引数は、文字列として渡された検索結果を、PHPの変数としてどのようなデータ型へ変換するかを指定できます。これも省略可能ですが、省略した場合と「MDB2_PREPARE_RESULT」を指定した場合には、全ての結果は文字列（あるいはNULL値）として返ってきます。もちろん文字列を後に手動で変換することも可能ですが、integer、float、booleanなどのカラムを、文字列としてではなくPHP的にもinteger、float、booleanなどで受け取りたい場合には指定したほうが便利です。なお、SELECT以外のステートメント（INSERT、UPDATE、DELETE）には「MDB2_PREPARE_MANIP」を指定してください。

execute時は、SQL内のプレースホルダに対応した値を配列で指定します。引数には、名前付きプレースホルダの場合は、プレースホルダ名をキーにした連想配列を、疑問符プレースホルダの場合は、順序を揃えた配列を指定します。要素が１つしかない疑問符プレースホルダの場合、配列ではなくスカラー値も指定できますが、紛らわしいので配列にしておいたほうがよいでしょう。

今回のMDB2ではLIKE演算子の例をとりあげました。LIKE演算子は「%」を0文字以上の文字列とマッチする、「_」はすべての一文字とマッチするパターン文字として判断します。例えば「%」という文字列とマッチさせたいという意味で入力した「%」であっても、これを単純に「%」ではさみ「%%%」でLIKE検索してしまうと、NULL以外の全ての文字列にマッチしてしまいます。これらのパターン文字を無効化、つまりエスケープしてくれるのがescapePatternメソッドです。バックスラッシュがエスケープ記号として設定されていた場合、「%」は「\%」に、「_」は「\_」に、「\」は「\\」にエスケープされます。エスケープされた文字はLIKEでの検索文字列内でも、パターン文字としてではなく、元々の文字として判断されるという仕組みです^※1。

※1 Shift_JISなどの、エスケープ記号（この場合はバックスラッシュ）が通常の文字の２バイト目として含まれている文字エンコーディングでは、なかなか正しくエスケープされないのが実情です。実際（執筆時点、2011-04-03）escapePatternは、SJISを使った場合は正しく動作しません。その他の場合にもShift_JISはエスケープ時に問題が発生することが多いので、UTF-8、EUC-JPなどをPHPの内部エンコーディングとして使うようにしてください。

プリペアドステートメント全般に言えることですが、１つのステートメントに対して、パラメータを変更しながら複数回処理を行いたいような場合にはprepareとexecuteが別れていると便利です。

$sth = $mdb2->prepare(
  'INSERT INTO users (username, profile) VALUE (:username, :profile)',
  array('username' => 'text', 'profile' => 'integer'),
  MDB2_PREPARE_MANIP
);
foreach($users as $user) {
  $sth->execute(array(
    'username' => $user['username'], 
    'profile' => $user['profile']
  ));
}
$sth->free();

SQL本体のパースは準備を行うprepareのとき１回だけで済むので、繰り返しが多くなるほど、全体としては高速に動作するという利点があります。

とは言え、ほとんどのSQLは一度だけ発行することが多いと思います。繰り返し実行するわけでもないのに、いちいちprepareとexecuteの二つのメソッドを発行するのは面倒です。そのような時は、以下で紹介するExtendedモジュールのメソッドを使用してください。

ちなみにMDB2では、ステートメント名は自動的に「mdb2_statement_pgsql_[ランダムなハッシュ値]」という形で採番されます^※2。ただしprepareを発行した時点で、それ以前に設定されていたステートメントは上書きされてしまうので注意してください。また、かなり稀ではありますが、このステートメント名が衝突してしまう可能性もあるようです。ですので、executeした後は速やかにfreeをしたほうが良さそうです。

※2 MDB2のバージョンによっては「mdb2_statement_pgsql_連番+ランダムなハッシュ値」になっていますが、ハッシュ値の部分が連番の部分と重なってしまう可能性もゼロではありません。いずれのケースでもexecute降にfreeをしておくことは有効です。

Extendedモジュールを使って１メソッドで

$all = $mdb2->extended->getAll(
  'SELECT userid, username, profile FROM users WHERE username LIKE :username',
  array('integer', 'text', 'text'),
  array('username' => '%' . $mdb2->escapePattern($_REQUEST['username']) . '%'),
  array('username' => 'text')
);

$mdb2->extended->execParam(
  'UPDATE users SET profile = :profile WHERE userid = :userid',
  array(
    'profile' => $_REQUEST['profile'], 
    'userid' => $_SESSION['me']['userid']
  ),
  array('profile' => 'text', 'userid' => 'integer')
);

SQLがSELECT文の場合には、getAllの他にも、検索されるデータの形式に合わせて、getRow、getCol、getOne、あるいはgetAssocが使用できます。１つめの引数にプレースホルダを含んだSQLを、２つめに検索結果のデータ型を配列で、３つめにプレースホルダに設定する値を配列で、４つめにプレースホルダのデータ型を配列で設定します。

SQLがINSERT、UPDATE、DELETEの場合は、execParamを使います。１つめの引数にプレースホルダを含んだSQLを、２つめにプレースホルダに設定する値を配列で、３つめにプレースホルダのデータ型を設定します。なお、これらのメソッドでは自動的にfreeが発行されます。

※ MDB2では原稿執筆時点のバージョン（MDB2: 2.4.1、MDB2_Driver_pgsql: 1.4.1）では、EXECUTE時にはpg_executeは使わず、引数をクォートしながら、EXECUTE文をMDB2自身で組み立てています。スロークエリの解析をcontrib/pg_stat_statementsで行っている場合、EXECUTE文が別々に集計されてしまい、意図した結果にならないことがありますので注意してください。

PHPでのSQLインジェクション対策 - プレースホルダ編: MDB2

PEAR::MDB2

名前付きプレースホルダ

疑問符プレースホルダ

Extendedモジュールを使って１メソッドで

ブックナビゲーション