プリペアドステートメントおよびストアドプロシージャ
より成熟したデータベースの多くは、プリペアドステートメントという 概念をサポートしています。プリペアドステートメントとはいったい何の ことでしょう? これは、実行したい SQL をコンパイルした 一種のテンプレートのようなものです。パラメータ変数を使用することで SQL をカスタマイズすることが可能です。プリペアドステートメントには 2 つの大きな利点があります。
- クエリのパース (あるいは準備) が必要なのは最初の一回だけで、 同じパラメータ (あるいは別のパラメータ) を指定して何度でも クエリを実行することができます。クエリを実行するには、準備として クエリの解析やコンパイル、そして実行プランの最適化が行われます。 クエリが複雑になると、この処理には時間がかかるようになります。 同じクエリを異なったパラメータで何度も実行すると、アプリケーションの 動作は目に見えて遅くなるでしょう。 プリペアドステートメントを使用すると、この 解析/コンパイル/最適化 の繰り返しを避けることができます。 端的に言うと、プリペアドステートメントは使用するリソースが少なくいため 高速に動作するということです。
- プリペアドステートメントに渡すパラメータは、引用符で括る必要は ありません。それはドライバが自動的に行います。 アプリケーションで明示的にプリペアドステートメントを使用するように すれば、SQL インジェクションは決して発生しません (しかし、もし信頼できない入力をもとにクエリの他の部分を構築している のならば、その部分に対するリスクを負うことになります)。
プリペアドステートメントは非常に有用な機能なので、もしドライバが サポートしていなくても、例外的に PDO がこの機能をエミュレートします。 これにより、データベースの機能にかかわらず同じ仕組みで データベースへのアクセスができることが保証されます。
例1 プリペアドステートメントを使用して、繰り返し挿入処理を行う
この例は、name および value を名前つきプレースホルダで置き換えて INSERT クエリを実行します。
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// 行を挿入します
$name = 'one';
$value = 1;
$stmt->execute();
// パラメータを変更し、別の行を挿入します
$name = 'two';
$value = 2;
$stmt->execute();
?>
例2 プリペアドステートメントを使用して、繰り返し挿入処理を行う
この例は、name および value をプレースホルダ ? で置き換えて INSERT クエリを実行します。
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
// 行を挿入します
$name = 'one';
$value = 1;
$stmt->execute();
// パラメータを変更し、別の行を挿入します
$name = 'two';
$value = 2;
$stmt->execute();
?>
例3 プリペアドステートメントを使用してデータを取得する
この例では、フォームで入力したキーの値に応じたデータを取得します。 ユーザーの入力内容は自動的に引用符で括られるので、SQL インジェクション攻撃の 恐れはありません。
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
while ($row = $stmt->fetch()) {
print_r($row);
}
}
?>
例4 出力パラメータを指定してストアドプロシージャをコールする
データベースドライバがサポートしていれば、入力パラメータだけでなく 出力パラメータもバインドすることが可能です。出力パラメータは、 一般にストアドプロシージャから値を受け取るために使用します。この場合、 返される値の大きさがどの程度になるのかをバインド時に知っておく必要が あります。指定した大きさよりも大きな値が返されると、エラーが発生します。
<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
// ストアドプロシージャをコールします
$stmt->execute();
print "プロシージャが返した値は $return_value です\n";
?>
例5 入出力パラメータを指定してストアドプロシージャをコールする
入出力の両方に使用するパラメータを指定することもできます。 このパラメータの書式は、出力パラメータと同じです。 次の例では、ストアドプロシージャに文字列 'hello' を渡しています。 プロシージャの結果が返ってくると、 この文字列はプロシージャの返す値に置き換えられます。
<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
// ストアドプロシージャをコールします
$stmt->execute();
print "プロシージャが返した値は $value です\n";
?>
例6 プレースホルダの間違った使用法
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name']));
// プレースホルダは、値全体に対して使用しなければなりません
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
?>
忘却曲線を使ってこの知識を確実に記憶に残す
