RitoLabo

PHP7とPDOでデータベース操作を行う~プリペアドステートメントを用いたセキュアなDB処理~

  • 公開:
  • カテゴリ: PHP Basics
  • タグ: PHP,7.2,7.1,Basics,DB,PDO

PDO(PHP Data Objects)は、PHPがデフォルトで提供しているデータベース操作の為の拡張モジュールです。

アジェンダ
  1. 開発環境
  2. 基本形
  3. データベース接続
  4. トランザクション
  5. 基本的なCRUD
    1. SELECT
    2. INSERT
    3. UPDATE
    4. DELETE
  6. プリペアドステートメント
    1. SELECT
    2. INSERT
    3. UPDATE
    4. DELETE

開発環境

今回の開発環境は以下の通りです。

  • Linux CentOS 7
  • Apache 2.4
  • 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、第二引数にユーザ名、第三引数にパスワードを渡します。

わかりやすく1つにすると以下のようになります。

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()メソッドで結果データを1件取得します。引数に 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;

こちらも同様に、

  1. SQL文の組み立て
  2. SQLステートメント準備
  3. パラメータをバインド
  4. クエリ実行

の順で処理してきます。

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コードでの処理は覚えておいて損はないので是非試してみてください。