WebアプリケーションにおけるPDOの使い方入門 / phpcon odawara 2024

WebアプリケーションにおけるPDOの使い方入門 PHPカンファレンス小田原2024

株式会社PR TIMES Backend Engineer (PHP/Python/Go) X: @app1e_s GitHub: @meihei3 Bluesky:
@meihei.bsky.social 直近の登壇 meihei / 江間洋平自己紹介 2

※1 2023年11月自社調べ。「利用企業社数」とは、過去にサービスを利用した実績がある社数の累計を指します。国内主要プレスリリース配信サービス5社を比較。他社データは、2023年11月時点で開示している情報より取得。 ※2 2023年8月実績。「サイト閲覧数」とは、配信されたプレスリリースが表示されるサイトにおける、月間のサイト閲覧数を指します。国内主要プレスリリース配信サービス5社を比較。サイト閲覧数は、自社含めて、Similarwebを用いて推定、比較を実施。 ※3 2023年11月自社調べ。「提携メディア数」とは、登録したプレスリリース情報を原文のまま掲載することが可能なメディアの件数を指します。国内主要プレスリリース配信サービス5社を比較、他社データは、2023年11月時点で開示している情報より取得。

私と小田原実家から今住んでいるマンションまで、小田急を使った移動が一番安い。乗り換えで何度か小田原に降りていた。 ※画像はYahoo!マップより

本日のゴール PDOの基本的な使い方を知って実際のWebアプリケーションで使われているような PDOの使い方・Repositoryの実装の仕方を学ぼう

アジェンダ 1. PDOを触ってみよう 2. Webアプリケーションの中のPDO 3. まとめ

PDOとは

PDO (PHP Data Objects) • PDOとはデータアクセスの抽象化レイヤを提供する、PHPの拡張モジュールです。 • クエリの発行やデータの取得が同じメソッドで行える
• データベースの抽象化を行うのではない ◦ DBごとの差分を吸収してくれるわけではない ◦ 同じメソッドでもDBごとで挙動が変わる事がある

データアクセスの抽象化共通のインターフェースが用意されている SQLはDB特有のものが必要

LaravelのEloquentと、どう違うのか • EloquentはORM（Object-Relational Mapping） ◦ オブジェクト指向なメソッド呼び出しから、SQL発行を自動的に生成する ▪ データベースの抽象化を行っていて、基本的にはデータベースを意識したコードを書かなくても良い
• 対してPDOは、SQLを自分で書く • 必要最低限の機能しかなく、軽量に動く

データベースへ接続する

データベース接続で必要な情報 • dsn - Data Source Name • username /
password - DNS接続の認証情報 • options - ドライバ固有の接続オプション

// SQLiteへ接続する $dsn = 'sqlite::memory:'; $pdo = new PDO(dsn: $dsn);
SQLiteへ接続する（インメモリ）

// SQLiteへ接続する $dsn = 'sqlite::memory:'; $pdo = new PDO(dsn: $dsn);
SQLiteへ接続する（インメモリ）接続先のDSN情報

// SQLiteへ接続する $dsn = 'sqlite:./phpoda.sqlite3'; $pdo = new PDO(dsn: $dsn);
SQLiteへ接続する（ファイル）例えばファイルに変えてみる

// PostgreSQLへ接続する $dsn = 'pgsql:host=localhost;dbname=phpoda;options=\'--client_encoding=UTF8\'', $username = 'odawarakko'; $password =
'odawarakko'; $pdo = new PDO(dsn: $dsn, username: $username, password: $password); PostgreSQLへ接続する

'odawarakko'; $pdo = new PDO(dsn: $dsn, username: $username, password: $password); PostgreSQLへ接続する接続先のDSN情報 host, dbname, charsetを書く

'odawarakko'; $pdo = new PDO(dsn: $dsn, username: $username, password: $password); PostgreSQLへ接続する DSNへ接続するための認証情報

'odawarakko'; $pdo = new PDO(dsn: $dsn, username: $username, password: $password); PostgreSQLへ接続する

// MySQLへ接続する $dsn = 'mysql:host=localhost;dbname=phpoda;charset=utf8mb4', $username = 'odawarakko'; $password =
'odawarakko'; $options = [ PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO(dsn: $dsn, username: $username, password: $password, options: $options); MySQLへ接続する

'odawarakko'; $options = [ PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO(dsn: $dsn, username: $username, password: $password, options: $options); MySQLへ接続する charsetはutf8mb4を指定する

'odawarakko'; $options = [ PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO(dsn: $dsn, username: $username, password: $password, options: $options); MySQLへ接続するドライバオプションを書く

'odawarakko'; $options = [ PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO(dsn: $dsn, username: $username, password: $password, options: $options); MySQLへ接続する MySQLの場合、エミュレートモードをOFFが推奨

'odawarakko'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO($dsn, $username, $password, $options); PHP8.0未満はエラーモードに注意

'odawarakko'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO($dsn, $username, $password, $options); PHP8.0未満はエラーモードに注意デフォルトでERRMODE_SILENTなので、ちゃんと指定する必要がある

CRUD処理を書いてみよう

// users テーブルから全件取得 $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->query('SELECT * FROM users'); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 全件取得

$stmt = $pdo->query('SELECT * FROM users'); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 全件取得データベースへ接続

$stmt = $pdo->query('SELECT * FROM users'); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 全件取得 SQLを発行

$stmt = $pdo->query('SELECT * FROM users'); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 全件取得結果を連想配列の形で取得

$stmt = $pdo->query('SELECT * FROM users'); $data = []; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $data[] = $row; } 全件取得１件ずつ取得する書き方も出来る

// users テーブルから1件取得 $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); $stmt->bindValue(1, $id, PDO::PARAM_INT); $stmt->execute(); $data = $stmt->fetch(PDO::FETCH_ASSOC); 変数を指定して１件取得

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); $stmt->bindValue(1, $id, PDO::PARAM_INT); $stmt->execute(); $data = $stmt->fetch(PDO::FETCH_ASSOC); 変数を指定して１件取得 SQLに直接変数を入れず?に置き換えている（プレースホルダ）

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); $stmt->bindValue(1, $id, PDO::PARAM_INT); $stmt->execute(); $data = $stmt->fetch(PDO::FETCH_ASSOC); 変数を指定して１件取得ここで値をバインドしている

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); $stmt->bindValue(1, $id, PDO::PARAM_INT); $stmt->execute(); $data = $stmt->fetch(PDO::FETCH_ASSOC); 変数を指定して１件取得 Executeのタイミングで実行

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); $data = $stmt->fetch(PDO::FETCH_ASSOC); 変数を指定して１件取得プレースホルダは名前をつける事が可能

// users テーブルに1件挿入 $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)'); $stmt->bindValue(1, $user->name, PDO::PARAM_STR); $stmt->bindValue(2, $user->email, PDO::PARAM_STR); $stmt->execute(); １件挿入

// users テーブルの1件を更新（idはPK） $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->prepare( 'UPDATE users SET name = :name, email = :email WHERE id = :id'); $stmt->bindValue(':name', $user->name, PDO::PARAM_STR); $stmt->bindValue(':email', $user->email, PDO::PARAM_STR); $stmt->bindValue(':id', $user->id, PDO::PARAM_INT); $stmt->execute(); １件更新

// users テーブルの1件を更新（idはPK） $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->prepare( 'UPDATE users SET name = :name, email = :email WHERE id = :id'); $stmt->bindValue(':name', $user->name, PDO::PARAM_STR); $stmt->bindValue(':email', $user->email, PDO::PARAM_STR); $stmt->bindValue(':id', $user->id, PDO::PARAM_INT); $stmt->execute(); if (($rowCount = $stmt->rowCount()) > 1) { throw new \RuntimeException( "Expected to update 1 row, but updated {$rowCount} rows"); } １件更新（例外を投げる） rowCountで実際に作用した行数を取得し、期待通りであるか確認

rowCountはドライバ依存 • PDOStatement::rowCount — 直近の SQL ステートメントによって作用した行数を返す •

// users テーブルの1件を削除（idはPK） $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?'); $stmt->bindValue(1, $user->id, PDO::PARAM_INT); $stmt->execute(); if (($rowCount = $stmt->rowCount()) > 1) { throw new \RuntimeException( "Expected to delete 1 row, but deleted {$rowCount} rows"); } １件削除

// users テーブルを更新し、 user_name_log に挿入する。 $pdo->beginTransaction(); try { $stmt1 =
$pdo->prepare('UPDATE users SET name = :name WHERE id = :id'); ... $stmt1->execute(); $stmt2 = $pdo->prepare( 'INSERT INTO user_name_log (user_id, name) VALUES (:user_id, :name)'); ... $stmt2->execute(); return $pdo->commit(); } catch (\PDOException $e) { $pdo->rollBack(); throw $e; } トランザクションを利用する

$pdo->prepare('UPDATE users SET name = :name WHERE id = :id'); ... $stmt1->execute(); $stmt2 = $pdo->prepare( 'INSERT INTO user_name_log (user_id, name) VALUES (:user_id, :name)'); ... $stmt2->execute(); return $pdo->commit(); } catch (\PDOException $e) { $pdo->rollBack(); throw $e; } トランザクションを利用するトランザクションは複数のSQLをまとめて安全に反映することを保証する（ACID）

$pdo->prepare('UPDATE users SET name = :name WHERE id = :id'); ... $stmt1->execute(); $stmt2 = $pdo->prepare( 'INSERT INTO user_name_log (user_id, name) VALUES (:user_id, :name)'); ... $stmt2->execute(); return $pdo->commit(); } catch (\PDOException $e) { $pdo->rollBack(); throw $e; } トランザクションを利用するトランザクションを初期化トランザクション内の操作を反映させるトランザクション内の操作をキャンセルし、元の状態に戻す

// users テーブルの1件を削除（idはPK） $pdo = new PDO($dsn, $username, $password, $opt);
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?'); $stmt->bindValue(1, $user->id, PDO::PARAM_INT); $stmt->execute(); if (($rowCount = $stmt->rowCount()) > 1) { throw new \RuntimeException( "Expected to delete 1 row, but deleted {$rowCount} rows"); } ちなみに、通常時は自動コミットモード（もしDBがトランザクションをサポートしていたら）暗黙的なトランザクションのもと実行される

安全にPDOをつかう

$pdo = new PDO($dsn, $username, $password, $opt); $stmt = $pdo->query(
"SELECT * FROM users WHERE id = {$id} LIMIT" . $limit); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 🙅 SQLインジェクションの危険性があるコード

"SELECT * FROM users WHERE id = {$id} LIMIT" . $limit); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); SQL内で変数を展開している 🙅 SQLインジェクションの危険性があるコード

"SELECT * FROM users WHERE id = {$id} LIMIT" . $limit); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 🙅 SQLインジェクションの危険性があるコード文字列連結でSQL文を組み立てている

SQLインジェクションを防ぐ • 文字列連結でSQL文を組み立てない ◦ 文字列リテラルを使う • 値はプレースホルダで渡す • （静的プレースホルダを使う） ◦
ATTR_EMULATE_PREPARESの設定が出来る OCI, Firebird, MySQL はで、Falseを設定する PHP+PDO+MySQLの組み合わせではSQLインジェクション攻撃で複文呼び出しが可能 https://blog.tokumaru.org/2013/12/pdo-and-mysql-allow-multiple-statements.html 安全なウェブサイトの作り方 https://www.ipa.go.jp/security/vuln/websecurity/index.html

// MySQLへ接続する $pdo = new PDO($dsn, $username, $password, $opt); //
注意: OCI, Firebird, MySQL でのみ利用可能なオプション $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id LIMIT :limit'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); 🙆 SQLインジェクションの対策がされたコード

配列の可能性を収束させる

🙅 好ましくないRepositoryの実装 class UserRepository implements UserRepositoryInterface { ... public function
getById( int $id, ): mixed { $stmt = $this->pdo->prepare( 'SELECT * FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetch(PDO::FETCH_ASSOC); } }

getById( int $id, ): mixed { $stmt = $this->pdo->prepare( 'SELECT * FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetch(PDO::FETCH_ASSOC); } } SELECT * はデータの無限の可能性を持つ

getById( int $id, ): mixed { $stmt = $this->pdo->prepare( 'SELECT * FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetch(PDO::FETCH_ASSOC); } } キー（プロパティ）の情報が無い＝静的解析が効かない

どう辛いのか $repo = new UserRepository($pdo); $user = $repo->getById($_GET['id'] ?? 0);
if ($user === false) { // 404 Not Found } echo json_encode([ 'user_id' => $user['id'], 'user_name' => $user['meme'], ... ]);

どう辛いのか $repo = new UserRepository($pdo); $user = $repo->getById($_GET['id'] ?? 0);
if ($user === false) { // 404 Not Found } echo json_encode([ 'user_id' => $user['id'], 'user_name' => $user['meme'], ... ]); user_nameってKeyあったかな？ user_idはINT型にキャストしたほうがいいかな？なぜかここでテストがFailするぞ！？

連想配列の解像度 // 連想配列② $user = [ 'id' => ..., 'name'
=> ..., 'email' => ..., ]; // カラムを知っている人から見たuserの連想配列 // 連想配列③ $user = [...]; // 何も知らない人から見たuserの連想配列 // 連想配列① $user = [ 'id' => 100, 'name' => 'odawara', 'email' => '[email protected]', ]; // データを知っている人から見たuser の連想配列

連想配列に旅をさせない • PDOの返り値は配列（連想配列） ◦ 連想配列は動的（unset, array_push, ...） ◦ 静的解析が効かない •
DTO（Data Transfer Object）を使う ◦ POPO（Plain Old PHP Object）で型の情報をクラスで表現する ▪ ライブラリなどに依存していない質素なオブジェクト ◦ メソッドの内から連想配列をDTOへ変換 ※1 FETCH MODEによっては連想配列ではない返り値を得られる。 ※2 PHPの最高機能、配列を捨てよう！！を参考に https://speakerdeck.com/uzulla/throw-away-all-php-array-now

🙆 DTOに詰め替えて、型を定義する。 // 連想配列 $user = [ 'id' => ...,
'name' => ..., 'email' => ..., ]; // DTO class UserDto { public function __construct( public int $id, public string $name, public string $email, ) {} }

🙆 DTOに詰め替えて、型を定義する。 class UserRepository implements UserRepositoryInterface { ... public function
getById( int $id, ): ?UserDto { $stmt = $this->pdo->prepare( 'SELECT id, name, email FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); return $row !== false ? new UserDto(...$row) : null; } }

getById( int $id, ): ?UserDto { $stmt = $this->pdo->prepare( 'SELECT id, name, email FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); return $row !== false ? new UserDto(...$row) : null; } } 取りうるデータ（カラム）を制限する

getById( int $id, ): ?UserDto { $stmt = $this->pdo->prepare( 'SELECT id, name, email FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); return $row !== false ? new UserDto(...$row) : null; } } Repository内で DTOに詰め替える

PDOの表現力を上げる

PDOが苦手な事 • IN句などの動的に複数のパラメータを渡したい時 • テーブル名を安全に渡したい時 • ORDER BYのASC/DESC

PDOが苦手な事 • IN句などの動的に複数のパラメータを渡したい時 • テーブル名を安全に渡したい時 • ORDER BYのASC/DESC 面倒だし、文字列結合した SQLを使おうかな…

私たちの答え：TetoSQL • PDO wrapper and SQL Template for PHP ◦
https://github.com/BaguettePHP/TetoSQL • PDOへ引き渡すためのSQLを生成するテンプレートとして活用 ◦ 可変個の配列使ったSQLが書ける ◦ FORやIFなどの制御構文も使える • 更に型チェックもある型安全なSQLテンプレートエンジンを構築する　 https://fortee.jp/phpcon-2023/proposal/5717d4a6-ebf0-47cb-9964-69cfb92e0f56

TetoSQLで使える型・制御構文 • @bool • @int • @int[] • @string •
@string[] • @lob • @ascdesc • %if ... %endif • %for ... %endfor

TetoSQLを使ってSQLを実行 $pdo = new PDO($dsn, $username, $password, $opt); $stmt =
\Teto\SQL\Query::execute( $pdo, 'SELECT * FROM users WHERE id IN (:ids@int[]) ', [ ':ids' => $ids, // [1, 2, 3] ] ); $data = $stmt->fetch(PDO::FETCH_ASSOC);

\Teto\SQL\Query::execute( $pdo, 'SELECT * FROM users WHERE id IN (:ids@int[])', [ ':ids' => $ids, // [1, 2, 3] ] ); $data = $stmt->fetch(PDO::FETCH_ASSOC); 配列を引数にとって値をバインド出来る

\Teto\SQL\Query::execute( $pdo, "SELECT * FROM {$tablename} WHERE id IN (:ids@int[])", [ ':ids' => $ids, // [1, 2, 3] ] ); $data = $stmt->fetch(PDO::FETCH_ASSOC); とは言え、まだ脆弱なコードを書くことが出来る

TetoSQL と PHPStan の合せ技をする • TetoSQLへの入力が文字列である限りは、変数の展開や文字列結合が可能 ◦ そもそも、TetoSQLはSQLインジェクションを防ぐための機能を提供しているわけではない
• 静的解析とCIで、脆弱なコードが混入出来ない仕組みを作る ◦ PHPStanのカスタムルールで実現

• PHPStanは抽象構文木に解析する ◦ カスタムルールはTetoSQLの引数を対象に作成することができる。 • TetoSQLへの入力は文字列リテラルのみに制限する ◦ シングルクォートの文字列、Nowdocのみ ◦
文字列結合や変数展開が出来なくなる TetoSQL 用の PHPStan のカスタムルール PHPStanのカスタムルールを導入しました　 https://developers.prtimes.jp/2024/02/21/introduce_phpstan_custom_rule/

PHPStanのカスタムルール class SqlRule implements Rule { public function processNode(Node $node,
Scope $scope): array { ... if (!$node->args[1]->value instanceof Node\Scalar\String_) { return ['クエリは文字列リテラルである必要があります。']; } $attributes = $node->args[1]->value->getAttributes(); if (!in_array($attributes['kind'], [String_::KIND_SINGLE_QUOTED, String_::KIND_NOWDOC])) { return ['クエリはシングルクォートかNowdocで記述する必要があります。']; } return []; } ... }

まとめ • PDOの基本的な使い方（CRUD）を学びました • プリペアドステートメントを必ず使用して、SQLインジェクション対策をする • Repositoryの実装は連想配列を外に出さないようにする •
PDOだけでは出来ないことは、SQL Templateなライブラリを使うことも検討。PHPStanも使うと効果的

参考文献 • 安全なウェブサイトの作り方 ◦ https://www.ipa.go.jp/security/vuln/websecurity/index.html • PHP+PDO+MySQLの組み合わせではSQLインジェクション攻撃で複文呼び出しが可能 ◦ https://blog.tokumaru.org/2013/12/pdo-and-mysql-allow-multiple
-statements.html • PHPでデータベースに接続するときのまとめ ◦ https://qiita.com/mpyw/items/b00b72c5c95aac573b71 • 憂鬱なSQLのためのアレ、またはPDOと仲良くして枕を高くしてねむる ◦ https://qiita.com/tadsan/items/e615a779baa6eabdab47

余談：もっと色々な実装を見てみたい場合 • ISUCONの問題を見てみよう ◦ PHPはPDOで実装されている ◦ 多言語も同じSQLを書くので、比較しながら読める • PDO, Eloquent,
DoctrineORM の比較（SELECT） ◦ https://blog.okashoi.net/entry/2023/12/09/060000 ◦ ISUCONのPHP移植の実装者の記事

余談：PHP8.4でドライバごとのサブクラスが出来る • PHP RFC: PDO driver speciﬁc sub-classes https://wiki.php.net/rfc/pdo_driver_speciﬁc_subclasses •
PdoSqlite, PdoPgsql, PdoMySql • PDO::connect() • PHP 8.3 のプロポーザルっぽいが、Implementation は PHP 8.4 となっていた

WebアプリケーションにおけるPDOの使い方入門 / phpcon odawara 2024

WebアプリケーションにおけるPDOの使い方入門 / phpcon odawara 2024

More Decks by meihei

Other Decks in Technology

Featured

Transcript