プリペアドステートメント

MySQL データベースは、プリペアドステートメントをサポートしています。プリペアドステートメント、またはパラメータ化したステートメントは、同じステートメントを繰り返し、高い効率で実行すると同時に、 SQLインジェクションから守ります。

基本的なワークフロー

プリペアドステートメントの実行は、ふたつの段階を踏んで行われます: 準備と実行です。準備の段階では、ステートメントのテンプレートがデータベースサーバーに送信されます。サーバーは文法のチェックを行い、サーバーの内部リソースを後に再利用するために初期化しておきます。

MySQL サーバーは名前を指定せず、位置を指定できるプレースホルダーを ? によってサポートしています。

準備の後、実行が行われます。実行する間、クライアントはパラメータの値をバインドし、サーバーに送信します。サーバーはステートメントをバインドされた値とともに、以前作成した内部リソースを使って実行します。

例1 プリペアドステートメント

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

/* Prepared statement, stage 2: bind and execute */
$id = 1;
$label = 'PHP';
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string

$stmt->execute();

繰り返し実行させる

プリペアドステートメントは、繰り返し実行させることができます。実行させる度に、バインドされた現在の値が評価され、サーバーに送られます。ステートメントは再度パースされません。ステートメントのテンプレートもサーバーに再度送信されません。

例2 INSERT を一度だけ準備し、複数回実行する

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");

/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");

/* Prepared statement, stage 2: bind and execute */
$stmt->bind_param("is", $id, $label); // "is" means that $id is bound as an integer and $label as a string

$data = [
    1 => 'PHP',
    2 => 'Java',
    3 => 'C++'
];
foreach ($data as $id => $label) {
    $stmt->execute();
}

$result = $mysqli->query('SELECT id, label FROM test');
var_dump($result->fetch_all(MYSQLI_ASSOC));

上の例の出力は以下となります。

array(3) {
  [0]=>
  array(2) {
    ["id"]=>
    string(1) "1"
    ["label"]=>
    string(3) "PHP"
  }
  [1]=>
  array(2) {
    ["id"]=>
    string(1) "2"
    ["label"]=>
    string(4) "Java"
  }
  [2]=>
  array(2) {
    ["id"]=>
    string(1) "3"
    ["label"]=>
    string(3) "C++"
  }
}

プリペアドステートメントごとに、サーバーのリソースが消費されます。ステートメントは、使った後はすぐに閉じるべきです。それを明示的に行わない場合、ステートメントハンドルが PHP によって開放された後、ステートメントが閉じられます。

プリペアドステートメントが、ステートメントを実行するもっとも効率が良い方法とは限りません。プリペアドステートメントが一度しか実行されないと、クライアントとサーバー間の通信が、ステートメントを準備しない場合と比べて余計に行われてしまいます。よって、上の例の SELECT は、プリペアドステートメントを使って実行していません。

また、 MySQL の複数INSERT の文法を使うことも検討してみて下さい。たとえば、複数INSERT によって、サーバーとクライアント間に必須の通信が、上に示すプリペアドステートメントの例よりも少なくなります。

例3 複数INSERTを使い、クライアント・サーバー間の通信を減らす

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");

$values = [1, 2, 3, 4];

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?), (?), (?), (?)");
$stmt->bind_param('iiii', ...$values);
$stmt->execute();

結果セットの値のデータ型

MySQL のクライアントサーバープロトコルは、プリペアドステートメントと、準備しないステートメントとでは異なるデータ転送プロトコルを使います。プリペアドステートメントは、いわゆるバイナリプロトコルを使います。 MySQL は結果セットのデータを、バイナリフォーマットで "そのまま" 送信します。結果セットは送信される前は文字列にシリアライズされていません。クライアントライブラリは、バイナリデータを受け取って値を適切なPHP のデータ型に変換しようとします。たとえば、INT として定義されたカラムからの結果は、 PHP の整数値として提供されます。

例4 ネイティブのデータ型

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));

上の例の出力は以下となります。

id = 1 (integer)
label = PHP (string)

この振る舞いが、準備しないステートメントの場合は異なります。デフォルトでは、準備しないステートメントの結果は、全て文字列として返されます。このデフォルトは、接続オプションで変更できます。接続オプションを使うと、この振る舞いの違いはなくなります。

バインドされた値を使って、結果を取得する

プリペアドステートメントからの結果は、出力値をバインドして取得することもできますし、 mysqli_result から取得するようにリクエストすることもできます。

出力変数は、ステートメントの実行後にバインドしなければいけません。ステートメントの結果セットのそれぞれのカラムごとに、ひとつの値をバインドしなければいけません。

例5 バインドされた値を出力する

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$stmt->bind_result($out_id, $out_label);

while ($stmt->fetch()) {
    printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));
}

上の例の出力は以下となります。

id = 1 (integer), label = PHP (string)

プリペアドステートメントは、デフォルトでは、結果セットをバッファリングせずに返します。ステートメントの結果は、暗黙のうちに取得されることはありませんし、サーバーからクライアントに、バッファリングされるためにデータが送信されることもありません。結果セットは、全ての結果がクライアントによって取得されるまで、サーバーのリソースを消費します。よって、結果は必要に応じて取得することが推奨されます。クライアントが全ての結果を取得できなかったり、クライアントが全てのデータを取得する前にステートメントを閉じたりした場合、 mysqli は暗黙のうちにデータを取得しなければいけません。

プリペアドステートメントであっても、 mysqli_stmt::store_result() を使って結果をバッファリングすることが可能です。

mysqli_result インターフェイスを使って、結果を取得する

バインドされた結果を使う代わりに、 mysqli_result インターフェイスを使って結果を取得することもできます。 mysqli_stmt::get_result() は、バッファリングされた結果セットを返します。

例6 結果を取得するために、mysqli_result を使う

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP')");

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();

$result = $stmt->get_result();

var_dump($result->fetch_all(MYSQLI_ASSOC));

上の例の出力は以下となります。

array(1) {
  [0]=>
  array(2) {
    ["id"]=>
    int(1)
    ["label"]=>
    string(3) "PHP"
  }
}

mysqli_result インターフェイスを使うと、クライアント側で結果セットを柔軟に操作することができます。

例7 柔軟に結果を読み取るために、バッファリングされた結果セットを使う

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");

/* Non-prepared statement */
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT, label TEXT)");
$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'PHP'), (2, 'Java'), (3, 'C++')");

$stmt = $mysqli->prepare("SELECT id, label FROM test");
$stmt->execute();

$result = $stmt->get_result();

for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
    $result->data_seek($row_no);
    var_dump($result->fetch_assoc());
}

上の例の出力は以下となります。

array(2) {
  ["id"]=>
  int(3)
  ["label"]=>
  string(3) "C++"
}
array(2) {
  ["id"]=>
  int(2)
  ["label"]=>
  string(4) "Java"
}
array(2) {
  ["id"]=>
  int(1)
  ["label"]=>
  string(3) "PHP"
}

エスケープと SQL インジェクション

バインドされる変数は、クエリとは別にサーバーに送信されます。このことが、変数によって邪魔されることはありません。サーバーはステートメントのテンプレートをパース後、送信された値を実行時に直接使います。バインドされたパラメータをエスケープする必要はありません。サーバーがその値でクエリの文字列を直接置き換えることはないからです。バインドする変数の型について、サーバーにヒントを提供しなければいけません。これは、サーバーが適切な変換を行うために必要です。詳しい情報は、 mysqli_stmt::bind_param() を参照ください。

このように、変数とクエリを別に扱うことが、 SQLインジェクションを防ぐ唯一のセキュリティ上の機能だと見なされることがあります。しかし、全ての値を適切にフォーマットしておけば、それと同程度のセキュリティを準備しないステートメントでも達成できます。正しいフォーマットとは、単純に値をエスケープすることではなく、それ以上のロジックを含むことに注意すべきです。よって、この手のデータベースセキュリティに対しては、プリペアドステートメントが単により便利で、エラーが起きにくいアプローチになっています。

クライアント側でプリペアドステートメントをエミュレートする

API は、クライアント側でのプリペアドステートメントのエミュレートをサポートしていません。

参照