今回取り上げるプリペアドステートメントは、プログラムからデータベースを利用する際、事前に変数を埋め込んだSQLを準備しておき、SQLのバインド変数を利用して、そのSQLを再利用する機能です。
そして、プログラムが見やすく、検索速度が速いうえ、SQLインジェクション対策としても有効など、メリットがたくさんあります。
ここでは、このように便利なプリペアドステートメントを、SQLの視点から解説します。
バインド変数が使われるプリペアドステートメントについて
Webシステムなど、プログラムからSQLを介してデータベースを検索する場合、プリペアドステートメントを利用しているでしょうか。
プリペアドステートメントを使うことで、読み易いプログラムが書けて、検索速度も改善できるといったメリットが得られます。そして、この方法で使われているのが、データベース管理ソフトのバインド変数です。
次から、プリペアドステートメントについて、簡単に解説します。
プリペアドの意味は準備済み
今回紹介するプリペアドステートメントとは、英語で書くと「Prepared Statement」であり、そのまま日本語に翻訳すると「準備済みのステートメント」となります。
そして、この「準備済み」とは、SQLの中に変数を設定し、その変数が指定されたら、SQLを実行する準備ができているという意味です。
なお、プリペアドステートメントで使うSQLの中に設定する変数のことを、「プレースホルダ」(placeholder)と呼びます。
そして、placeholderとはコンピュータ用語で、後で実際の文字や数字などが表れる場所、という意味で使われます。
ネットでプリペアドステートメントを検索すると、その説明で、よく「プレースホルダ」を見かけるので、ぜひ、覚えてください。
プリペアドステートメントはSQLのバインド変数を活用
プリペアドステートメントでは、データベース管理ソフトのバインド変数を使ったSQLと、その変数に値を代入する機能を使って、準備済みのSQLと代入する値とを分けてプログラムする方法です。
なお、この方法は、プログラム側で制御しているので、バインド変数を直接操作するSQLを書く必要はありません。
そのため、SQLのバインド変数を気にすることなく、そのメリットを活用できます。
次から、プリペアドステートメントのメリットと、代表的なプログラム言語であるJavaとPHPにおけるプリペアドステートメントの使用例を紹介します。
プリペアドステートメントのメリット
先ほどSQLのバインド変数を活用したプリペアドステートメントについて簡単に紹介しましたが、これは幾つものメリットがある機能です。次から、このメリットについて紹介します。
読み易いプログラム書ける
SQLを文字列として見れば、プログラムで簡単に合成できます。そのため、データベースを利用するためのSQLを毎回合成し、そのSQLを実行するプログラムを書くことが可能です。
しかし、そこで扱うSQLを修正する場合は、どこを変更するか解り難く、さらにデバックも面倒なケースがたくさんあります。
その点、プリペアドステートメントを利用した場合、準備済みのSQLとそれにバインドする変数が別になっているので見やすく、後で修正する場合も簡単です。
このように、プリペアドステートメントを活用することで、メンテナンス性の高いプログラムを書くことが可能です。
検索速度が速くなる
また、プリペアドステートメントのメリットとして、よく挙げられるのが検索速度の高速化です。なお、通常のSQLとプリペアドステートメントを1つだけで比較した場合は、ほとんど差がありません。
しかし、同じような検索を何度も実行する場合は、キャッシュの効果が効いてくるので、処理が速くなります。
キャッシュは、同じ処理を何度も実行するほど効果が大きいので、このような機能のあるアプリケーションでは、ぜひ、利用してください。
SQLインジェクション対策
SQLインジェクションとは、ユーザーが登録する文字などの中に、アプリケーションが想定しないSQL文を埋め込んで、データベースシステムを不正に操作する攻撃方法です。
この攻撃を防ぐ方法は幾つかありますが、プリペアドステートメントもSQLインジェクションを防止する方法として有効です。
それは、フォームなどによって入力された文字がバインド変数を使うことで、SQLの一部としてではなく、完全に文字列として扱われるためです。
そのため、入力可能な文字の種類を制限できないなど、SQLインジェクション対策が不十分な入力フォームなどでも活用できます。
Javaのプリペアドステートメントの例
Javaでは、PreparedStaetmentクラスを使うことで、プリペアドステートメントを利用したデータベースの検索が可能です。
なお、この場合のプレースホルダとして「?」であり、検索に使うSQLに指定します。
そして、その変数にバインドする値を、setIntやsetStringで指定し、executeQuery()でSQLを実行し、その結果を活用するのが一般的です。
次に、Javaで使われるプリペアドステートメントの例を紹介します。
Connection con = DriverManager(url,user,password); String sql = "SELECT name FROM tableone WHERE id = ?;"; Statement pre = con.preparedStatement(sql); pre.setInt(1, 1); ResultSet rs = pre.executeQuery();
この例のpreparedStatement()メソッドに指定するSQLに注目してください。例えば、Oracle databaseのバインド変数では、変数名の前「:」を付けて「:value」といった変数名にします。
また、MySQLでは、変数名の前に付けるのは「@」です。
しかし、preparedStatement()メソッドで指定するSQLでは、バインド変数として「?」を指定しています。
これにより、データベース管理ソフトによって違う変数名を意識することなく使用することが可能です。
Oracle database
バインド変数は、「:変数名」
例：SELECT name FROM tableone WHERE id = :target ;
MySQL
ユーザー定義変数は、「@変数名」
例：SELECT name FROM tableone WHERE id = @target ;
JavaのPreparedStaetmentクラス
SQLに「?」を埋め込み、これに変数をバインドする
例：SELECT name FROM tableone WHERE id = ? ;
また、バインド変数を複数使っている場合は、SQLに「?」を複数指定、setInt()メソッドやsetString()メソッドで、その順番を指定します。
String sql = "SELECT name FROM tableone WHERE ( id = ? ) AND ( section = ? ) AND ( group = ? ); "; Statement pre = con.preparedStatement(sql); pre.setInt(1, 1); pre.setString(2, 'General Affairs'); pre.setInt(3, 101);
PHPのプリペアドステートメントの例
PHPによるプリペアドステートメントの例として、データベースサーバーとの接続に使われるPDOクラスを用いた例が次のとおりです。
なお、PDOクラスには、prepare()メソッドがあり、ここでバインド変数が記載されたSQLを指定します。
また、その変数にバインドする数値や文字列は、bindValue()メソッドを使い、そのSQLを実行するのはexecute()メソッドです。
PHPによるプリペアドステートメントの例
$dbh = new PDO("mysql:host=localhost; dbname=testdb; charset=utf8", "$user", "$password");
$stmt = $dbh->prepare('SELECT name FROM tableone WHERE id = ?');
$stmt->bindValue(1,1,PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
なお、PDOクラスで複数の変数を使う場合も、bindValue()メソッドで、順番と数字や文字列などを指定します。
PHPによる複数の変数を使用したプリペアドステートメントの例
$stmt = $dbh->prepare('SELECT name FROM tableone WHERE ( id = ? ) AND ( section = ? ) AND ( group = ? );');
$stmt->bindValue(1,1,PDO::PARAM_INT);
$stmt->bindValue(2,General Affairs,PDO::PARAM_STR);
$stmt->bindValue(3,101,PDO::PARAM_INT);
まとめ
プリペアドステートメントは、SQLのバインド変数を活用した便利な機能です。
例えばJavaやPHPなどのプログラムからデータベースを利用する場合、見やすいコードが書けるほか、検索速度が速くなり、SQLインジェクション対策にも有効など、メリットがたくさんあります。
さらに、データベース管理ソフトによってバインド変数の使い方が違いますが、JavaのPreparedStaetmentクラスやPHPのPDOクラスを使うことで、そのような違いを気にしないで使えます。
ぜひ、データベースを活用したシステム開発で活用してください。
