プリペアドステートメントおよびストアドプロシージャ

より成熟したデータベースの多くは、プリペアドステートメントという概念をサポートしています。プリペアドステートメントとはいったい何のことでしょう? これは、実行したい 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";
?>

入出力の両方に使用するパラメータを指定することもできます。このパラメータの書式は、出力パラメータと同じです。次の例では、ストアドプロシージャに文字列 'hello' を渡しています。プロシージャの結果が返ってくると、この文字列はプロシージャの返す値に置き換えられます。

例5 入出力パラメータを指定してストアドプロシージャをコールする


<?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]%"));
?>