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 は、クライアント側でのプリペアドステートメントのエミュレートをサポートしていません。
プリペアドステートメントと、準備しないステートメントの比較
以下の表は、サーバサイドのプリペアドステートメントと、 準備しないステートメントの比較を示しています。
| プリペアドステートメント | 準備しないステートメント | |
|---|---|---|
| クライアントとサーバ間の通信の回数。SELECT を一度だけ実行する場合 | 2 | 1 |
| ステートメントの文字列を、クライアントからサーバに送信する回数 | 1 | 1 |
| クライアントとサーバ間の通信の回数。SELECT を (n) 回実行する場合 | 1 + n | n |
| ステートメントの文字列を、クライアントからサーバに送信する回数 | 1 テンプレートに対して、存在する場合、n回パラメータをバインド | n 回、しかも毎回パースが行われる |
| 入力パラメータをバインドするAPIのサポート | Yes | No, 手動での入力のエスケープが必要 |
| 出力値をバインドするAPIのサポート | Yes. | No. |
| mysqli_result API のサポート | Yes, mysqli_stmt::get_result() | Yes |
| バッファリングされた結果セットのサポート | Yes. mysqli_stmt::get_result() または mysqli_stmt::store_result() を使ってバインドする。 | Yes. mysqli::query() のデフォルト |
| バッファリングしない結果セットのサポート | 出力をバインドするAPIを使えば可能 | Yes. mysqli::real_query() を mysqli::use_result() と一緒に使う |
| データ転送の際に用いられる、クライアント/サーバ間プロトコル | バイナリプロトコル | テキストプロトコル |
| SQL のデータ型に対応した、結果セットの値 | 取得時の値が保存される | 文字列に変換するか、取得時に値を保存するかが選択可 |
| 全てのSQLステートメントをサポートしているか | 最近のMySQLバージョンはほとんどをサポートしているが、全てではない | Yes |
参照