PHP7とPDOでデータベース操作を行う～プリペアドステートメントを用いたセキュアなDB処理～
PDO（PHP Data Objects）は、PHP がデフォルトで提供しているデータベース操作の為の拡張モジュールです。
開発環境
今回の開発環境は以下の通りです。
- MySQL 5.7
- PHP 7.2
扱うデータベースの内容については PHP7 と MySQLi 拡張モジュールでデータベース操作を行う を参照してください。
基本形
PDO からの例外が投げられた際にそれらをキャッチしない場合はエラーとしてバックトレースが表示されますが、そこにはデータベースの接続情報なども含まれている為、PDO を利用する時は基本的に try catch の中で実装を行います。例外をキャッチして処理するようにします。
try {
} catch (PDOException $e) {
exit($e->getMessage());
}
データベース接続
PDO でデータベースへの接続は以下のようにして行います。
// DSN 指定
$dsn = sprintf("mysql:host=%s;dbname=%s", DB_HOST, DB_NAME);
// PDO インスタンス化
$pdo = new PDO($dsn, DB_USER, DB_PASS);
PDO クラスをインスタンス化する際に、第一引数には DSN, 第二引数にユーザ名、第三引数にパスワードを渡します。
わかりやすく１つにすると以下のようになります。
new PDO("mysql:host=your_host_name;dbname=your_db_name", "your_db_user", "your_db_password");
トランザクション
トランザクションを張る場合は以下のように行います。
// トランザクション開始
$pdo->beginTransaction();
try {
/*
* DB処理
*/
// コミット
$pdo->commit();
} catch (PDOException $e) {
// ロールバック
$pdo->rollBack();
exit($e->getMessage());
}
トランザクションの開始・コミット・ロールバックを定義しています。
基本的な CRUD
まずは最もベーシックな CRUD（SELECT/INSERT/UPDATE/DELETE）です。以下をベースとして実装していきます。
try {
// DSN指定
$dsn = sprintf("mysql:host=%s;dbname=%s", DB_HOST, DB_NAME);
// PDOインスタンス化
$pdo = new PDO($dsn, DB_USER, DB_PASS);
/*
* ここにCRUD処理
*/
// クローズ
$pdo = null;
} catch (PDOException $e) {
// 適宜エラー処理
exit($e->getMessage());
}
SELECT
SELECT 文を用いてデータを取得します。
// SQL文組み立て
$sql = "SELECT * from `frameworks` WHERE id < 5";
// クエリ実行
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
print_r($row);
}
// クローズ
$stmt = null;
- query() メソッドで SQL 文を実行します。
- fetch() メソッドで結果データを１件取得します。引数に PDO::FETCH_ASSOC を渡していますが、これで取得したカラム名を添字、つまりは key に設定された配列で取得されます。
結果は以下になります。
Array
(
[id] => 1
[name] => Laravel
[type] => 1
)
Array
(
[id] => 2
[name] => CakePHP
[type] => 1
)
Array
(
[id] => 3
[name] => Symfony
[type] => 1
)
Array
(
[id] => 4
[name] => Silex
[type] => 1
)
ちなみに、全ての結果行を取得する場合は fetchAll() メソッドを用いて以下のようにします。
// SQL文組み立て
$sql = "SELECT * from `frameworks` WHERE id < 5";
// クエリ実行
$stmt = $pdo->query($sql);
$all = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($all as $row) {
echo'<pre>'; print_r($row); echo'</pre>';
}
$stmt = null;
$all = null;
クエリを実行した際の全ての結果行をこれで取得できます。
INSERT
INSERT 文を用いてデータを挿入します。
$name = 'new_2';
$type = 2;
// SQL 文組み立て
$sql = sprintf("INSERT INTO `frameworks` (name, type) VALUES ('%s', %d)", $name, $type);
// クエリ実行
$pdo->query($sql);
SQL 文を query() メソッドで実行する事で、処理が行えます。
UPDATE
UPDATE 文を用いてデータを更新します。
$name = 'new_2';
$type = 2;
$id = 32;
// SQL 文組み立て
$sql = sprintf("UPDATE `frameworks` SET name='%s', type=%d WHERE `id` = %d", $name, $type, $id);
// クエリ実行
$pdo->query($sql);
こちらも同じ行く、SQL 文を query() メソッドで実行します。
DELETE
DELETE 文を用いてデータを削除します。
$id = 33;
// SQL 文組み立て
$sql = sprintf("DELETE FROM `frameworks` WHERE `id` = %d", $id);
// クエリ実行
$pdo->query($sql);
プリペアドステートメント
次に、プリペアドステートメントを用いた PDO 実装です。プリペアドステートメントでは、SQL 文の組み立て時にパラメータを直接代入する事はせずにバインドを行う事で、パラメータのエンティティ化などを行う事が出来、SQL インジェクション対策などになります。
SELECT
SELECT 文を用いてデータを取得します。
$id = 5;
// SQL文組み立て
$sql = "SELECT * from `frameworks` WHERE id < :id";
// SQL ステートメントを準備
$stmt = $pdo->prepare($sql);
// パラメータをバインド
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
// クエリ実行
$stmt->execute();
// 結果データ取得
$all = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($all as $row) {
print_r($row);
}
$stmt = null;
$all = null;
- SQL 文の組み立て時に、パラメータ部分に「コロン＋任意の文字列」でマーカーを設定します。
- prepare() メソッドで SQL ステートメントを準備します。
- bindParam() メソッドでパラメータをバインドします。第一引数にはマーカー名を、第二引数にはバインドするパラメータを、第三引数には型を渡しています。
INSERT
INSERT 文を用いてデータを挿入します。
$name = 'new_3';
$type = 3;
// SQL 文組み立て
$sql = "INSERT INTO `frameworks` (name, type) VALUES (:name, :type)";
// SQL ステートメントを準備
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':type', $type, PDO::PARAM_INT);
$stmt->execute();
$stmt = null;
こちらも同様に、
- SQL 文の組み立て
- SQL ステートメント準備
- パラメータをバインド
- クエリ実行
の順で処理してきます。
UPDATE
UPDATE 文を用いてデータを更新します。
$name = 'new_2';
$type = 2;
$id = 32;
// SQL 文組み立て
$sql = "UPDATE `frameworks` SET name=:name, type=:type WHERE `id`=:id";
// SQL ステートメントを準備
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':type', $type, PDO::PARAM_INT);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$stmt = null;
DELETE
DELETE 文を用いてデータを削除します。
$id = 34;
// SQL 文組み立て
$sql = "DELETE FROM `frameworks` WHERE `id`=:id";
// SQL ステートメントを準備
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$stmt = null;
まとめ
PDO は MySQLi と共に PHP でデフォルトで利用できるデータベース操作に関する拡張モジュールです。 Laravel や CakePHP などのフレームワークの台頭で最近これらを扱う機会も少なくなりましたが、フレームワークも裏側では PDO 拡張モジュールを用いてでデータベース操作を行っていたりします。ネイティブな PHP コードでの処理は覚えておいて損はないので是非試してみてください。
