PDOには様々なDBに対する各々のドライバがある。一旦、インストールすれば(PDOを使用してDBへ接続している場合、)コードを殆ど書き換えることなく、DBを変更することが可能になる。
■PDOを使ったデータベースへの接続
<?php $dsn = 'mysql:host=localhost;dbname=dbname'; $dbh = new PDO($dsn, DB_USER, DB_PASS); ?>
通常、PDOはエラーが発生しても何も表示しない(PDO::ERRMODE_SILENT)。これはセキュリティ上安全ではあるが、一方でデバッグを非常に困難にする。従って、例外を発生させ(ファイルに書き込みさせ)るようにすることも可能である。
<?php try { $dsn = 'mysql:host=localhost;dbname=dbname'; $dbh = new PDO($dsn, DB_USER, DB_PASS); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(Exception $e){ //code } ?>
■PDOを使ったデータベースへのクエリ発行
SELECT文
SELECT文のような、結果セットを得ることを主目的としている場合は、以下のようにqueryメソッドを用いる。また、queryメソッドを用いた場合、戻り値はPDOStatementオブジェクトである。
<?php $sql = "SELECT * FROM `person` WHERE `name` = 'John'"; $result = $dbh->query($sql); foreach($result as $row){ print($row['name'] . ': ' . $row['country_id']); } ?>
但し、以下のように書いた場合、$rowには「数字キー」と「カラム名のキー」が両方含まれているので値が重複する。これはデフォルトのデータ取り出しモードがPDO::FETCH_BOTHであり、「数字キー」と「カラム名のキー」での配列になっているためである。
<?php $sql = "SELECT * FROM `person` WHERE `name` = 'John'"; $result = $dbh->query($sql); foreach($result as $row){ foreach($row as $value){ print($value . PHP_EOL); } } /* 1 1 John John 2 2 */ ?>
データ取り出しモードの変更
データ取り出しモードを変更するには、以下のように結果セットが含まれるPDOStatementオブジェクト$resultに対して、setFetchModeメソッドを実行せねばならない。
<?php $sql = "SELECT * FROM `person` WHERE `name` = 'John'"; $result = $dbh->query($sql); $result->setFetchMode(PDO::FETCH_OBJ); foreach($result as $row){ print($row->name . ': ' . $row->country_id); } ?>
ユーザ入力の値をSQL文に組み込むためのメソッド
通常、SQLインジェクションを回避するために、ユーザ入力の値をSQL文に含める際にはサニタイジングしなければならない。以下のように、quoteメソッドを用いることで可能である。
<?php $name = $dbh->quote($_POST['name']); $sql = "SELECT * FROM `person` WHERE `name` = '$name'"; $result = $dbh->query($sql); ?>
但し、いくつかのドライバはこのメソッドを実装していないので、後述のプリペアドステートメントを用いたほうが良い。
INSERT文、UPDATE文、DELETE文
INSERT文、UPDATE文、DELETE文には変更(処理)した行数を返すexecメソッドを用いる。
<?php $sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')"; $num = $dbh->exec($sql); print($num);//1 ?>
■プリペアドステートメント(Prepared Statement)を用いたクエリの発行
プリペアドステートメントとは
- 1回のリクエストに対する処理の中で、SQL文の再利用を可能にする
- DBがプリペアドステートメントをサポートしてない場合、PDOが内部の関数で可能にする
- DBがプリペアドステートメントをサポートしている場合、DBの機能を使ってアプリケーションの性能の向上を可能にする
- (SQLインジェクションを誘発させる)クォート漏れに対するリスク回避を可能にする
<?php $sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)"; $stmt = $dbh->prepare($sql); $stmt->execute(array( $id, $_POST['name'], $_POST['country_id'] )); ?>
INSERT文、UPDATE文、DELETE文には、ユーザ入力の値をSQL文に含めることが非常に多い。とはいえ、以下のようにSELECT文でも当然ながら、プリペアドステートメントを用いることはある。
<?php $sql = "SELECT * FROM `person` WHERE `name` = ?"; $stmt = $dbh->prepare($sql); $stmt->execute(array('John')); //$stmt->setFetchMode(PDO::FETCH_OBJ); $result = $stmt->fetchAll(); var_dump($result); /* array(1) { [0]=> array(6) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["name"]=> string(4) "John" [1]=> string(4) "John" ["country_id"]=> string(1) "2" [2]=> string(1) "2" } } */ ?>
但し、PHPのバージョンによって、(MySQL独自拡張の)LIMIT句に対するプリペアドステートメントにバグがあるため、それ(LIMIT句)以外での使用が望ましいと個人的には思う。また、LIMIT句は数値の値しか許容しないため、PHPで(型変換などを使って)サニタイジングすることは比較的に容易である。さらに、PDOStatementクラスに(結果を1行だけ返す)fetchメソッドが「あるバージョン」と「ないバージョン」があるため、このメソッドの使用はなるべく避けたほうが良い。
■PDOを使ったトランザクションの実行
以下のようにして、トランザクションを利用する。
<?php try { $dsn = 'mysql:host=localhost;dbname=dbname'; $dbh = new PDO($dsn, DB_USER, DB_PASS); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->beginTransaction(); $dbh->exec("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')"); $dbh->exec("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')"); $dbh->commit(); } catch(Exception $e){ $dbh->rollBack(); } ?>
各メソッドについて
- beginTransaction
- トランザクションを開始する
- commit
- 確定する
- rollBack
- 破棄して最初の状態に戻す
トランザクションとは
「全て成功する」か「全て失敗する」かのどちらかしか存在しないことを保証する機能。この機能が必要な例として、たいていの参考書では「銀行の振込み」などが扱われる。