【2021/10/15 追記】
この記事は更新が停止されています。現在では筆者の思想が変化している面もありますので，過去の記事として参考程度にご覧ください。PDO に関しては大きく変わっていない部分が多いとは思いますが， PHP 8.x 以降での動作保証はありません。
あらかじめ読んでおきたい記事
初心者がやりがちなミス
以下のどれかに1つでも当てはまるコードは見直す必要があります．付録にリンクを貼っておきましたので，「該当するかも？」という人はクリックして飛んで読んでください．太字にしてあるものは脆弱性に直結する危険度の高いものです．
mysql_queryなどの非推奨関数を利用しているSET NAMESあるいはSET CHARACTER SETなどで文字コードを指定している
そもそもデータベースで使用する文字コードの指定をしていない"SELECT * FROM users WHERE id = '$id'"のように変数展開を使ってSQL文を組み立てている$_POST['id']などの外部入力からきた変数が定義されているかどうか確認していない
それらが文字列であるかどうかの確認をしていない
それらを出力する際にhtmlspecialchars関数を利用していない- SQLの
LIKE演算子を使っているのに%_\のエスケープをしていない - HTMLの
<body></body>の中にデータベース接続処理を書いているecho()やprint()をベタ書きしている
Content-Typeをtext/plainに変更せずにexit()やdie()で強制終了処理を記述している
PDOの基本
PHPでデータベースに接続するためには，現在はPDOを使う方法が主流であると言えるでしょう．PDOの基本的な使い方を確認していきます．内容によって，「前でさらっと書いてあることの定義が後で出てくる」など，どうしても多少前後してしまう部分はありますが，特に気にせずに読み流してください．
データベースに接続
PDO::__construct メソッドを使用してインスタンスを生成します．コンストラクタなので，実際には直接これを呼び出すコードは書かずに， new演算子 を用います．
$pdo = new PDO($dsn, $username, $password, $driver_options);
引数は以下の通りです．
$dsn
データベースに接続するために必要な情報です． (Data Source Name)
以下に各データベース製品に応じたDSNの書き方が掲載されています．
MySQLの基本的な書き方を例に挙げます．
mysql:dbname=test;host=localhost;charset=utf8mb4
- 頭にデータベースの種類を指定して
:で区切る． - 各項目は
項目名=値とし，;で区切る．
dbname
データベース名を指定します．基本的には必須です．但し，データベースを後で USE test のようにSQL文で選択する場合，省略することができます．
host
ホスト名またはIPアドレスを指定します．ローカル環境で動かす場合，省略しても問題ない場合が多いです．localhost は自分自身のホスト名， 127.0.0.1 は自分自身のIPアドレスを指します．どちらを用いても問題が発生する可能性があるので，適宜問題のない方を選択してください．
- Linux環境はホスト名推奨
- Windows環境はIPアドレス推奨
charset
文字セットを指定します．SET NAMESは避けて，ここで指定するべきです．UTF-8ではなくutf8であることに注意してください．ハイフンは入りません．
【2016/12/17 追記】
MySQL5.5.3以降の場合は，4バイトからなる絵文字なども正常に取り扱えるutf8mb4を使用することを強く推奨します。
$username
ユーザー名．ルート権限を使う場合，デフォルトでは root です．
$password
パスワード．ルート権限を使う場合，デフォルトでは空白です．
$driver_options
接続時のオプションを連想配列で渡します．
- キーはあらかじめ用意されている定数を取る．
- 値はあらかじめ用意されている定数以外に，論理値・文字列などの一般的な値も取り得る．
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]
オプションの内容については後で触れます．
接続後にオプションを指定
PDO::setAttribute メソッドを使用します．多くのオプションはコンストラクタの $driver_options で指定してもこちらで指定しても大差はありませんが， PDO::MYSQL_ATTR_INIT_COMMAND PDO::ATTR_PERSISTENT など，一部コンストラクタ専用のものがあります．
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
よく使われるドライバオプションとその値
PDO::ATTR_ERRMODE
SQL実行でエラーが起こった際にどう処理するかを指定します．デフォルトは PDO::ERRMODE_SILENT です．
-
PDO::ERRMODE_EXCEPTIONを設定すると例外をスローしてくれる．これを選択しておくのが一番無難． -
PDO::ERRMODE_WARNINGはSQLで発生したエラーをPHPのWarningとして報告する．
PDOStatement::execute メソッドの返り値がfalseでないかを毎回のように確認する必要がある． -
PDO::ERRMODE_SILENTは何も報告しない．
PDOStatement::execute メソッドの返り値がfalseでないかを毎回のように確認する必要がある．
PDO::ATTR_DEFAULT_FETCH_MODE
PDOStatement::fetch メソッドや PDOStatement::fetchAll メソッドで引数が省略された場合や，ステートメントがforeach文に直接かけられた場合のフェッチスタイルを設定します．デフォルトはPDO::FETCH_BOTHです．
-
PDO::FETCH_BOTH
カラム番号とカラム名の両方をキーとする連想配列で取得する． -
PDO::FETCH_NUM
カラム番号をキーとする配列で取得する． -
PDO::FETCH_ASSOC
カラム名をキーとする連想配列で取得する．これが一番ポピュラーな設定． -
PDO::FETCH_OBJ
カラム名をプロパティとする基本オブジェクトで取得する．
PDO::ATTR_EMULATE_PREPARES
データベース側が持つ「プリペアドステートメント」という機能のエミュレーションをPDO側で行うかどうかを設定します．
- PHP5.1のデフォルトは
false - PHP5.2以降のデフォルトは
true
この設定で，いくつかPDOの挙動に違いが表れます．
- プリペアドステートメントのためにデータベースサーバと通信する必要が無くなるため，エミュレーションを行ったほうがパフォーマンスは向上する．
- 存在しないテーブル名やカラム名をSQL文に持つプリペアドステートメントを発行したとき，エミュレーションOFFの場合はすぐにエラーが発生するが，エミュレーションONの場合は実際にクエリを実行するまでエラーが発生するかどうかわからない．
- エミュレーションがONの場合のみ，
;区切りで複数のSQL文を1つのクエリで実行することができる．
その他，どちらにも利点と欠点があるので，違いは追って見ていき，最後に表にまとめることにします．
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
PDO::ATTR_PERSISTENT (コンストラクタ専用)
true を設定すると，スクリプトが終了してもデータベースへの接続を維持し，次回に再利用します．規模が大きくなってくると設定する恩恵が大きくなりますが，ほそぼそと練習用サイトを作っているうちは役に立たないでしょう．
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (MySQL専用)
true のとき， バッファクエリを使用します．デフォルト値はバージョンによって異なるようで，不明です．PHP5.3の時代には既にデフォルトが true で今も変わっていない…？少なくとも，私が確認できる限りの最近バージョンではデフォルトは true です．
-
バッファクエリ:
全ての情報をデータベースサーバから取得してきておいて，PHPに1件ずつ取り出させる -
非バッファクエリ:
1件ごとにデータベースサーバと通信を行って，PHPに取り出させる
取得してくる情報がメモリに収まりきらない莫大な量である，といった非常に特殊なケースを除けば，バッファクエリを選択しておく方が無難です．サーバ負荷も軽減され，途中までフェッチしたところで突然例外が発生するような事態も避けられます．また，非バッファクエリには複数同時にクエリを実行できないなどの大きな欠点もあります．基本的にこれは，データベースから取得したデータでHTMLを表示する用途ではなく，コマンドラインからバッチ処理を実行する用途で使われます．
PDO::MYSQL_ATTR_INIT_COMMAND (MySQL専用) (コンストラクタ専用)
接続した直後に実行されるクエリをここに書きます．
基本コーディング
書き方のテンプレです．
<?php
try {
/* リクエストから得たスーパーグローバル変数をチェックするなどの処理 */
// データベースに接続
$pdo = new PDO(
'mysql:dbname=testdb;host=localhost;charset=utf8mb4',
'root',
'',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
/* データベースから値を取ってきたり， データを挿入したりする処理 */
} catch (PDOException $e) {
// エラーが発生した場合は「500 Internal Server Error」でテキストとして表示して終了する
// - もし手抜きしたくない場合は普通にHTMLの表示を継続する
// - ここではエラー内容を表示しているが， 実際の商用環境ではログファイルに記録して， Webブラウザには出さないほうが望ましい
header('Content-Type: text/plain; charset=UTF-8', true, 500);
exit($e->getMessage());
}
// Webブラウザにこれから表示するものがUTF-8で書かれたHTMLであることを伝える
// (これか <meta charset="utf-8"> の最低限どちらか1つがあればいい． 両方あっても良い．)
header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Example</title>
</head>
<body>
<!-- ここではHTMLを書く以外のことは一切しない -->
</body>
</html>
参考: コンストラクタでのエラーモードってどうなるの？
PDOのコンストラクタは PDO::ERRMODE_EXCEPTION の有無に関わらず PDOException をスローしますが， ホストに接続できなかった場合などに Warning を発生します．普通は気に留める必要はありませんが，ホストを動的に指定させるようなプログラムを書いているならば， set_error_handler 関数を使って ErrorException に変換しなければ，例外処理を行うことができません．
PDO::query メソッドで直接クエリを実行する
ユーザー入力を伴わないクエリに関しては単に PDO::query メソッドを実行すればいいだけです．返り値は PDOStatement となります．
$stmt = $pdo->query('SELECT * FROM users');
PDO::exec メソッドで直接クエリを実行する
ユーザー入力を伴わないクエリで， INSERT や UPDATE 等で作用した件数を直接返り値に欲しい場合は PDO::exec メソッドを代わりに使います．特に結果を必要としない場合においてもこちらを使用すべきです．後に登場する PDOStatement::execute と紛らわしいので注意してください．
$count = $pdo->exec('UPDATE users SET age = age + 1');
PDO::prepare → PDOStatement::bindValue → PDOStatement::execute の3ステップでクエリを実行する
ユーザー入力を受け取ってSQL文を動的に生成する場合は プリペアドステートメント と プレースホルダ を使わなければなりません．
-
プレースホルダ:
直訳すると「場所取り」．何かユーザ入力を当てはめる場所としてあらかじめ確保しておくもの． -
プリペアドステートメント:
直訳すると「予約文」．文を予約したもの．通常，「予約文」は「場所取り」を使うために作られる．もし「場所取り」が無ければ普通に PDO::query などで実行するだけで十分なためである．
プレースホルダには2種類あり，疑問符プレースホルダ を使う方法と， 名前付きプレースホルダ を使う方法があります．もしこれらが混ざってしまうと
SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
が発生するので，どちらか一方のみを選択してください．
疑問符プレースホルダ
-
?の「番目」は 1 から始まる． -
PDO::PARAM_STRは省略することが出来る． - エミュレーションがONの場合には正しくキャストしてくれないバグのようなものが仕様として存在するため，文字列以外のものを扱う際に明示的なキャストが必要．
-
NULL値に関してはPDO::PARAM_NULLが暗黙的に使用される．
$stmt = $pdo->prepare('SELECT * FROM users WHERE gender = ? AND age = ?');
$stmt->bindValue(1, $gender);
$stmt->bindValue(2, (int)$age, PDO::PARAM_INT);
$stmt->execute();
$stmt = $pdo->prepare('SELECT * FROM users WHERE gender = ? AND age = ?');
$stmt->bindValue(1, $gender);
$stmt->bindValue(2, $age, PDO::PARAM_INT);
$stmt->execute();
- 番目で整数以外を指定した場合，適当な変換が行われる． (但し，こんなコードは書くべきではない)
$stmt->bindValue('2', $age, PDO::PARAM_INT);
$stmt->bindValue('02', $age, PDO::PARAM_INT);
$stmt->bindValue('+02', $age, PDO::PARAM_INT);
$stmt->bindValue('2e0', $age, PDO::PARAM_INT);
$stmt->bindValue('00002.9999', $age, PDO::PARAM_INT);
// SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based
$stmt->bindValue(-2, $age, PDO::PARAM_INT);
// オーバーフローして3になる
// SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
$stmt->bindValue('00002.999999999999999999999999999', $age, PDO::PARAM_INT);
// Notice: A non well formed numeric value encountered
$stmt->bindValue('2a', $age, PDO::PARAM_INT);
名前付きプレースホルダ
-
:を頭につけ，半角英数字とアンダースコアにて構成する． - バインド時の頭の
:は省略することが出来る．
$stmt = $pdo->prepare('SELECT * FROM users WHERE age = :age AND gender = :gender');
$stmt->bindValue(':age', (int)$age, PDO::PARAM_INT);
$stmt->bindValue(':gender', $gender);
$stmt->bindValue('gender', $gender);
- エミュレーションがONの場合のみ，同名のプレースホルダを複数使うことが出来る．
$n = 20;
$stmt = $pdo->prepare('SELECT * FROM users WHERE age = :n AND id = :n');
$stmt->bindValue(':n', (int)$age, PDO::PARAM_INT);
$stmt->execute();
なお， 値を即時にバインドするのではなく，変数を参照的にバインドしておき，実際に値をバインドするのは実行時になる PDOStatement::bindParam メソッドもありますが，原則的にこちらを使う必要はありません．エミュレーションがONの場合，実行後にバインドした変数が文字列型にされる仕様もあるので注意してください．
PDO::prepare → PDOStatement::execute の2ステップでクエリを実行する
PDOStatement::execute メソッドの引数に配列を渡すと，それらを全てバインドしたあとそのままSQLを実行してくれます．但し，以下の条件に注意してください．
-
NULL値以外は全てPDO::PARAM_STR扱いになる ．もし間違った型でバインドをしても，MySQL/SQLiteはデータベース側で自動的にキャストし直してくれるが，パフォーマンスの低下やバグの原因になるので，可能な限り避けたほうがいい．PostgreSQLの場合はエラーになる． - また， 既に PDOStatement::bindValue メソッドで値がバインドされていた場合でも，それらは全て無視される．これを用いる場合，全てのバインドをこの引数で行わなければならない．
疑問符プレースホルダ
PDOStatement::bindValue メソッドを用いたときと異なり，? の「番目」が 0 から始まることに注意してください．
$stmt = $pdo->prepare('SELECT * FROM users WHERE city = ? AND gender = ?');
$stmt->execute([$city, $gender]);
$stmt->execute([1 => $gender, 0 => $city]);
名前付きプレースホルダ
$stmt = $pdo->prepare('SELECT * FROM users WHERE city = :city AND gender = :gender');
$stmt->execute([':city' => $city, ':gender' => $gender]);
$stmt->execute(['city' => $city, 'gender' => $gender]);
$stmt->execute(compact('city', 'gender'));
SELECT の結果を取得する
結果の型
MySQL
| データベース | PHP (エミュレーション無しmysqlnd) |
PHP (libmysqlclient) (エミュレーション有りmysqlnd) |
|---|---|---|
| NULL | NULL | NULL |
| 文字列 | String | String |
| 日付 | String | String |
| タイムスタンプ | Integer | String |
| 論理値 | Integer | String |
| PHPで扱える値の整数 | Integer | String |
| PHPで扱えない値の整数 | String | String |
| 浮動小数点数 | Float | String |
エミュレーションに関するオプションは PDO::ATTR_EMULATE_PREPARES という命名ではありますが，プリペアドステートメントを使用しない場合にも影響が及ぶことに注意してください．
PostgreSQL
| データベース | PHP |
|---|---|
| NULL | NULL |
| 文字列 | String |
| 日付 | String |
| タイムスタンプ | Integer |
| 論理値 | Boolean |
| PHPで扱える型の整数 | Integer |
| PHPで扱えない型の整数 | String |
| 浮動小数点数 | String |
SQLite
| データベース | PHP |
|---|---|
| NULL | NULL |
| その他 | String |
PDO::setAttribute で取得する型を変更できるケース
PDO::ATTR_ORACLE_NULLS
| データベース上 | PDO::NULL_EMPTY_STRING |
PDO::NULL_TO_STRING |
|---|---|---|
| NULL | NULL | "" |
| "" | NULL | "" |
オプション名に ORACLE という名前が入っていますが，Oracle以外のデータベースでも利用することができます．
PDO::ATTR_STRINGIFY_FETCHES
true に設定すると，エミュレーションがOFFの場合に数値が文字列化されます．エミュレーションがONの場合は，この設定に関わらず常に文字列化されます．
PDOStatement::fetch
カーソルをずらしながら，指定したフェッチモードで1行ずつ取得していきます．
- 引数を省略した場合はデフォルトフェッチモードが使用される．
- 全ての取得が終わると常に
falseを返す．
デフォルトフェッチモードを PDO::FETCH_ASSOC に設定した際の例を示します．
while ($row = $stmt->fetch()) {
printf("%s lives in %s<br />\n", $row['name'], $row['city']);
}
while ($row = $stmt->fetch()) {
vprintf("%s lives in %s<br />\n", $row);
}
PDOStatementクラスはTraversableインターフェースを実装しているため，デフォルトフェッチモードを使う場合，while文の代わりにforeach文でもっとスマートに書くことができます．但し，HTML表示のために変数を準備する場合には，多くは配列として持っておく方が都合がいいので，後に紹介する PDOStatement::fetchAll メソッドの使用を検討してください．
foreach ($stmt as $row) {
printf("%s lives in %s<br />\n", $row['name'], $row['city']);
}
foreach ($stmt as $i => $row) {
printf("[%d] %s lives in %s<br />\n", $i, $row['name'], $row['city']);
}
PDOStatement::fetchObject
連想配列の代わりにstdClassオブジェクトとして取得します．PDOStatement::fetch で PDO::FETCH_OBJ を指定するケースと等価ですが，こちらの方が短く書くことができます．
while ($row = $stmt->fetchObject()) {
printf("%s lives in %s<br />\n", $row->name, $row->city);
}
PDOStatement::fetchColumn
特定の1カラムのみを文字列として取得します．PDOStatement::fetch で PDO::FETCH_COLUMN を指定するケースと等価ですが，こちらの方が短く書くことができます．
- 先頭から数えてそのカラムが何番目にあるかを第1引数として渡す．「番目」は 0 から始まる．省略した場合は 0 を指定したとみなされる．
- 値に
0が含まれる可能性がある場合は，false !==の条件判定をしなければならない．
while (false !== $value = $stmt->fetchColumn()) {
echo "{$value}<br />\n";
}
PDOStatement::fetchAll
一気に全件取得して2次元配列とします．
- 引数を省略した場合はデフォルトフェッチモードが使用される．
$rows = $stmt->fetchAll();
var_dump($rows);
- 特定のカラムだけ一気に全件取得して1次元配列としたい場合，第1引数に
PDO::FETCH_COLUMNを指定し，第2引数にカラムの「番目」を渡す．「番目」は 0 から始まる．省略した場合は 0 を指定したとみなされる．
$values = $stmt->fetchAll(PDO::FETCH_COLUMN);
var_dump($values);
PDOStatement::setFetchMode
PDOオブジェクト自体にデフォルトフェッチモードを指定する方法を紹介しましたが，このメソッドを利用すれば個別に発行されたPDOStatementオブジェクトに対して後からフェッチモードを指定することができます．引数の渡し方がモードによって異なるので，詳しくはマニュアルを参照してください．
$stmt->setFetchMode(PDO::FETCH_COLUMN, 0);
foreach ($stmt as $i => $name) { ... }
実は… PDO::query を用いる場合にも同じ形式でフェッチモードを指定することができます．
foreach ($pdo->query($sql, PDO::FETCH_COLUMN, 0) as $i => $name) { ... }
UPDATE, INSERT で作用した行数を取得する
こちらの結果に対してフェッチしようとした場合，SQLSTATE[HY000]: General error が発生します．こちらに対して用いることができるのは PDOStatement::rowCount メソッドのみです．
printf("%d行に作用しました<br >\n", $stmt->rowCount());
SELECT で該当した行数を取得する
基本的に，以下の方法に従います．
- 件数だけでなく結果セットも一緒に欲しい場合， PDOStatement::fetchAll メソッドで一気に配列として取得し，それに対してPHPの count 関数を使う．
- 件数だけが欲しい場合は，
SELECT COUNT(*) WHERE ...といったクエリを発行し，その結果を PDOStatement::fetchColumn メソッドで得る．
これらの方法が最も推奨されますが，他の方法が無いわけではありません．以下に補足説明を示します．但しこれらはMySQLやPostgreSQLについてのみ当てはまり，SQLiteには当てはまりません．
バッファクエリ使用時
- SELECT に対しても常に PDOStatement::rowCount メソッドを使うことができる．
非バッファクエリ使用時
- PDOStatement::fetchAll メソッドを実行した後，つまり全てのフェッチが終わった後であれば SELECT に対しても PDOStatement::rowCount メソッドを使うことができる．
- 結果のフェッチを途中で中断したまま次のクエリ実行に移行するときは， PDOStatement::closeCursor メソッドを使ってカーソルを閉じる必要がある．1つ目の結果セットをPDOStatementに保持したまま2つ目のSQLを実行することはできないので，その場合はあらかじめ PDOStatement::fetchAll メソッドでデータを退避させておく必要がある．
データベース接続の切断
データベース処理の最後に
$pdo = null;
と書いているコードが散見されますが，ほとんどの場合ではこの記述は不要です．必要になるのは，非バッファクエリを使うようなバッチ処理のシーンのみです．
エミュレーションに関するまとめ
これまでに何度か登場した，エミュレーションの有無に関する差異を表にまとめます．
| 項目 | エミュレーションON | エミュレーションOFF |
|---|---|---|
| パフォーマンス | ○ | △ |
SET NAMES による安全性 |
△ | ○ |
| NULL値をそのままの型で受け取る | ○ | ○ |
|
数値をそのままの型で受け取る ( mysqlnd のみが対象) |
× | ○ |
| 複数の同名プレースホルダ | ○ | × |
PDO::PARAM_* 定数による正しいキャスト |
× | ○ |
| PDOStatement::bindParam メソッドによる副作用問題 | × | ○ |
| 複文の実行 | ○ | ✕ |
PDOの応用
PDOStatementのデータ取得形式を極める
MySQLの暗黙的な型変換を防止する
既に述べたとおり，MySQLは暗黙的な型変換を行います．
$stmt = $pdo->prepare('SELECT * FROM users WHERE age = ?');
$stmt->execute(['20']);
もしPostgreSQLのように厳密さを与えたいならば，あらかじめ以下のクエリを実行しておきます．
$pdo->exec("SET SESSION sql_mode='TRADITIONAL'");
LIKE検索
部分一致のあいまいな検索を行いたい場合，LIKE演算子を使用します．LIKE検索で使われる特殊文字には
-
%… 任意の0文字以上の文字列 -
_… 任意の1文字
があるため，これらの文字を普通に検索したい場合にはエスケープが必要となります．MySQLの場合はエスケープ用の文字を省略して \ にすることが出来ますが，SQLiteなど他のデータベースとの互換性を考慮する場合，エスケープに使用する文字を明示すべきです．例えば ! を用いる場合は ESCAPE '!' とします．なお，エスケープ用の文字が文字列中に含まれているケースにも対応するために，そのエスケープ用の文字自体のエスケープも必要です．
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ? ESCAPE '!'");
$stmt->bindValue(1, '%' . preg_replace('/(?=[!_%])/', '!', $name) . '%', PDO::PARAM_STR);
$stmt = $pdo->prepare('SELECT * FROM users WHERE name LIKE ?');
$stmt->bindValue(1, '%' . addcslashes($name, '\_%') . '%', PDO::PARAM_STR);
ブール全文検索
MySQLでブール全文検索を行いたい場合，IN BOOLEAN MODE修飾子を使用します．全文検索関数で使われる特殊文字には
-
+… 各行に存在しなければならない -
-… 各行に存在してはならない -
@… 距離 -
<,>… 単語の貢献度 -
(,)… グループ化 -
~… 否定演算子 -
*… 切り捨てまたはワイルドカード -
"… フレーズ検索
があるため，これらの文字を普通に検索したい場合にはエスケープが必要となります．
$stmt = $pdo->prepare('SELECT * FROM users WHERE MATCH (description) AGAINST (? IN BOOLEAN MODE)');
$stmt->bindValue(1, addcslashes($name, '\+-@<>()~*"'), PDO::PARAM_STR);
可変長プレースホルダ
固定長プレースホルダではどうなるか
例えば，検索キーワードを複数受け取って特定のカラムがそれらすべてに部分一致するかどうかの検索を行う場合を考えましょう．キーワードの数が2個の場合，実行の流れは次のようになります．
$keywords = ['foo', 'bar'];
foreach ($keywords as $keyword) {
// LIKE検索のために「%キーワード%」の形式にする
$values[] = '%' . preg_replace('/(?=[!_%])/', '!', $keyword) . '%';
}
$sql = "SELECT * FROM books WHERE ((summary LIKE ? ESCAPE '!') AND (summary LIKE ? ESCAPE '!'))";
$stmt = $pdo->prepare($sql);
$stmt->execute($values);
可変長プレースホルダの導入
ところが，実際は検索キーワードの数が不定個数となるのが一般的です．このような場合にはどうすればいいか？そこで必要になってくるのが，動的にプレースホルダを生成することです．具体例を下に示します．
if ($keywords) {
/* キーワードが1つ以上のときだけ実行 */
foreach ($keywords as $keyword) {
// プレースホルダのLIKE部分を用意
$holders[] = "(summary LIKE ? ESCAPE '!')";
// LIKE検索のために「%キーワード%」の形式にする
$values[] = '%' . preg_replace('/(?=[!_%])/', '!', $keyword) . '%';
}
// AND条件で結合する
$sql = 'SELECT * FROM books WHERE (' . implode(' AND ', $holders) . ')';
// 実行
$stmt = $pdo->prepare($sql);
$stmt->execute($values);
}
検索対象のカラムが2つあり， summary1 と summary2 のどちらかに一致すればOKと見なす場合は次のようにします．OR条件とAND条件を両方用いていることに注意してください．
if ($keywords) {
/* キーワードが1つ以上のときだけ実行 */
foreach ($keywords as $keyword) {
// プレースホルダのLIKE部分を用意
$holders[] = "((summary1 LIKE ? ESCAPE '!') OR (summary2 LIKE ? ESCAPE '!'))";
// LIKE検索のために「%キーワード%」の形式にする
$values[] = $values[] = '%' . preg_replace('/(?=[!_%])/', '!', $keyword) . '%';
}
// AND条件で結合する
$sql = 'SELECT * FROM books WHERE (' . implode(' AND ', $holders) . ')';
// 実行
$stmt = $pdo->prepare($sql);
$stmt->execute($values);
}
検索キーワードを単語単位に分解する
実際に検索システムを実装するときに，どのようにして検索キーワードを $keywords に配列でセットするかどうかが問題となりますが，これは半角スペースで分割させることが一般的であると考えられます．しかし，ここでは半角スペース以外のさまざまな空白文字を正規表現で一括指定しています。
$q = (string)filter_input(INPUT_GET, 'q');
$maxKeywords = 6; // 適当な分割数の上限を設定（無制限にしたい場合は -1）
$keywords = preg_split('/(?:\p{Z}|\p{Cc})++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY);
PREG_SPLIT_NO_EMPTY フラグを用いることで，空文字列の要素を自動的に除外できるのも preg_split 関数の強みです．
トランザクション処理
連続的なSQL実行に一貫性・信頼性を持たせたい場合，「トランザクション」という機能を利用します．
トランザクションの基本的な利用法
使用されるメソッド
処理の実体はデータベースごとに固有の実装が為されていますが，インタフェースはPDOのメソッドとして抽象化されており，SQL文をデータベースごとに書き分けたりする必要はなくなります．
ポイント
- Tryブロックを 2重 に設け，例外発生時にはロールバックを実行し，捕捉した例外を必要に応じて外側のTryブロックに向けてスローする．
- 同じ形式のプリペアドステートメントの生成は1回だけにして，それを使いまわすようにした方がパフォーマンスは向上する．
try {
// データベースに接続
$pdo = new PDO(
'mysql:dbname=testdb;host=localhost;charset=utf8mb4',
'root',
'',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
// パラメータ
$from = 'A';
$to = 'B';
$transfer_amount = 12000;
// プリペアドステートメントを用意
$stmt = $pdo->prepare('UPDATE account SET credit = credit + (?) WHERE id = ?');
// トランザクション処理を開始
$pdo->beginTransaction();
try {
// Aの預金残高を減らす
$stmt->bindValue(1, -1 * $transfer_amount, PDO::PARAM_INT);
$stmt->bindValue(2, $from);
$stmt->execute();
// Bの預金残高を増やす
$stmt->bindValue(1, +1 * $transfer_amount, PDO::PARAM_INT);
$stmt->bindValue(2, $to);
$stmt->execute();
// コミット
$pdo->commit();
} catch (PDOException $e) {
// ロールバック
$pdo->rollBack();
// 外側のTryブロックに対してスロー
throw $e;
}
} catch (PDOException $e) {
// 例外メッセージを表示
header('Content-Type: text/plain; charset=UTF-8', true, 500);
exit($e->getMessage());
}
トランザクションの種類
秀逸な記事は太字にして紹介します．
- tree-tips - MySQLのトランザクション分離レベル
- TECHSCORE - トランザクションの定義
- 主に言語とシステム開発に関して - DBの「トランザクション分離レベル」が必要な理由
- Wikipedia - トランザクション分離レベル
- CUBE SUGAR STORAGE - MySQL を使ってトランザクション分離レベルの違いを試す
- ソフト開発お仕事メモ - [DB]H2, Derby, SQLiteの仕様の調査メモ（ロック周り）
推奨場面まとめ
各データベース製品のデフォルト
-
MySQL のInnoDBのデフォルトは
REPEATABLE READである． -
PostgreSQL のデフォルトは
READ COMMITTEDである． -
SQLite のデフォルトは
DEFERREDであり，これはREAD COMMITTEDに相当する．
分離レベルを設定するSQLの実行
$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$pdo->exec('SET SESSION CHARACTERISTICS AS TRANSACTION SERIALIZABLE');
テーブル名・カラム名のエスケープ処理
設計としてはあまり宜しくありませんが， 「ユーザーに任意のテーブル名やカラム名を指定させたい」 というケースも考えられなくはないです．PDOクラスにこういったものをエスケープする機能は含まれていないので，自前でエスケープを行わなければなりません．
- NULLバイトを除外する．
- バッククオートを2つ連ねてエスケープさせる．
- 最終的にバッククオートでくくる．
$sql = sprintf(
"CREATE TABLE `%s`(id int, name text)",
str_replace(["\0", "`"], ["", "``"], $table_name)
);
PDOクラスの継承
付録
初心者がやりがちなミスの解説
mysql_query などの非推奨関数を利用している
$link = mysql_connect('localhost', 'root', 'password');
mysql_select_db('test');
$result = mysql_query('SELECT * FROM users');
$rows = [];
while ($row = mysql_fetch_assoc($result)) {
$rows[] = $row;
}
あなたが参考にしている情報は古すぎます． mysql_ で始まる関数は，PHP5.5で非推奨になり，PHP7.0で完全に削除されて使えなくなっています．この記事で解説しているように，PDOを使う方法を覚えてください．
SET NAMES あるいは SET CHARACTER SET などで文字コードを指定している
そもそもデータベースで使用する文字コードの指定をしていない
文字コードの指定が無いのは論外として，SET NAMESなどを用いるのは可能な限り避けるべきです．このクエリは，データベース側の文字セットを操作するだけで，PDOドライバの文字セットにはノータッチです．入力側と出力側で文字セットが異なると，脆弱性が発生する原因になります．
- 文字コードの性質上，
SET NAMES sjisやSET CHARACTER SET sjisとした場合，顕著に脆弱性が現れる． -
SET NAMES utf8やSET CHARACTER SET utf8は基本的には安全だが，例外もあるので注意． MySQLにおいては，libmysqlclientのコンパイルオプションに--with-charset=cp932や--with-charset=sjisを指定している場合が該当． mysqlndを利用している場合は問題ない．
PDOクラスでの正しい指定方法は，以下のようになります．
- コンストラクタの DSN(Data Source Name) で指定する．
$pdo = new PDO('mysql:dbname=test;host=localhost;charset=utf8');
- PHP5.3.5以前のPDOではDSNで文字セット指定が出来ないため，
SET NAMESやSET CHARACTER SETに頼らざるを得ない．但しLinux版では，cnfファイルを利用する選択肢がある． - PHP5.3.6Windows版のPDOではDSNで文字セット指定が出来るが，バグがあるため，
SET NAMES utf8やSET CHARACTER SET utf8を使う場合と同じ結果になる． - やむを得ず
SET NAMESを使うにしても，SET CHARACTER SETは避けるべき．
MySQL以外では以下のようになります．
- PostgreSQLでは，DSNに
options='--client_encoding=UTF8'"を含める． - SQLiteでは特に指定する必要が無く，暗黙的にUTF-8が使用される．
"SELECT * FROM users WHERE id = '$id'" のように変数展開を使ってSQL文を組み立てている
Wikipediaを読むのがてっとり早いです．
$id = $_POST['id'];
$sql = "SELECT * FROM users WHERE id = '$id'";
このようなコードは， SQLインジェクション攻撃 の格好の標的になってしまうどころか，それを意図せずとも ' を含めたクエリで検索を行おうとしたときにも正常な処理が行えなくなってしまいます．これを防ぐために，SQL文中で特別な意味を持つ'をエスケープし，\'に変換する必要があります．
mysql_query 関数を使っていた頃は，以下のような手段でSQL文を組み立てるのが主流でした．
- mysql_real_escape_string 関数で文字列をあらかじめエスケープする
- 変数展開して埋め込む
$id = mysql_real_escape_string($_POST['id']);
$sql = "SELECT * FROM users WHERE id = '$id'";
但し，もしこの2行の間にたくさんのコードが入っていたらどうでしょうか？
- あなたは
$idがエスケープ済みかそうでないか，$sqlを書くところまでしっかり覚えていられますか？ - 「エスケープ忘れ」「多重エスケープ」という人為的なミスを生んでしまうリスクはないでしょうか？
また，手動エスケープにはこんな罠もあります．
- htmlspecialchars 関数は 「HTMLの特殊文字」 をエスケープする関数であり， 「SQLの特殊文字」 をエスケープする関数ではない．全く目的が異なる上に，これを通してしまうとHTML特殊文字をデータベースにそのまま格納できないというバグも発生してしまう．
-
addslashes 関数は一応それっぽくSQL文に対してエスケープが行えるが，本当にそれがデータベースに合わせて正しくエスケープされているかどうかの保証は無い．マニュアルにもそういった注意書きがある．例えばSQLiteは
'を\'ではなく''にエスケープしなければならない．
PDOを使う方法では，このようなリスクから開放されます．PDOには，**「プリペアドステートメント」および「プレースホルダ」**という仕組みが取り入れられています．詳細については本文中で紹介しているので，付録から戻って読んでみてください．
$id = $_POST['id'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bindValue(1, $id, PDO::PARAM_STR);
$_POST['id'] などの外部入力からきた変数が定義されているかどうか確認していない
それらが文字列であるかどうかの確認をしていない
それらを出力する際に htmlspecialchars 関数を利用していない
以下の記事をお読みください．
別の場所でも指摘していますが，ただ思考停止して htmlspecialchars 関数を使えばいいというものでもありません．「出力する際に」というのがネックです．例えば以下のコードは間違いです．
$stmt = $pdo->prepare('INSERT INTO users(name) VALUES(?)');
$stmt->bindValue(1, htmlspecialchars($name), PDO::PARAM_STR);
SQLの LIKE 演算子を使っているのに % _ \ のエスケープをしていない
以下のようなコードを書いていたらアウトです．
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ?");
$stmt->bindValue(1, "%{$name}%", PDO::PARAM_STR);
プレースホルダを使っているので脆弱性こそありませんが，LIKE演算子による正しい検索が保証されません．正しい実装方法は本文中に記載しています．
HTMLの <body></body> の中にデータベース接続処理を書いている
echo() や print() をベタ書きしている
Content-Typeを text/plain に変更せずに exit() や die() で強制終了処理を記述している
以下のようなコードを書いていたらアウトです．
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Example</title>
</head>
<body>
<ul>
<?php
try {
$pdo = new PDO('mysql: ... ', 'root', 'password', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->query('SELECT * FROM users');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo '<li>' . htmlspecialchars($row['name']) . '</li>';
}
} catch (PDOException $e) {
exit($e->getMessage());
}
?>
</ul>
</body>
</html>
もし，PDOに接続するところで失敗してしまった場合にどうなるでしょうか？きっと，出力されるHTMLはこんな感じになるはずです．
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Example</title>
</head>
<body>
<ul>No such file or directory
もし，あなたがこんな壊れたHTMLをWebサイト利用者に見られて恥も何も感じないのであれば私は止めませんが，多くの人は気にすると思います．…というよりも，まずこのコードは非常に読みにくいので**「ロジックとテンプレートの分離」**は行いましょう．
- 頭のほうですべてのロジックを書き，そこを過ぎたら用意された変数を使ってHTMLを出力する以外のことは行ってはいけません． （理想を言えば，ファイルも分けるべき）
- 広々と確保した
<?php ... ?>中で echo や print を連呼してHTMLを生成するのは，初心者が陥りやすい典型的な間違ったPHPの使い方です．PHPはこれでもテンプレートエンジン的な役割を持つ言語なので，上記で指摘したロジックとテンプレートの分離を行った後は，PHPの中にHTMLを埋め込むのではなく，HTMLの中にPHPを埋め込む感覚で書きましょう．
上記2点に関して，以下に修正した例を示します．
<?php
try {
$pdo = new PDO('mysql: ... ', 'root', 'password', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$rows = $pdo->query('SELECT * FROM users')->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
exit($e->getMessage());
}
function h($str)
{
return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Example</title>
</head>
<body>
<ul>
<?php foreach ($rows as $row): ?>
<li><?=h($row)?></li>
<?php endforeach; ?>
</ul>
</body>
</html>
echo短縮構文 および 制御構造に関する別の構文 を用いてみました．この2つはHTMLを出力する際に適用するとコードが非常に読みやすくなります．これで，エラー時に出力されるHTMLは以下のようになります．
No such file or directory
ただ，これってもはやHTMLではないですよね？もしエラー時にも丁寧にエラー画面用のHTMLを用意するのであればそれで構わないのですが， exit や die で手抜きしたい場合はWebブラウザに**「これはただのテキストです」**と伝えるようにしましょう． exit の前にこの1行を入れてください．
header('Content-Type: text/plain; charset=UTF-8', true, 500);
500は「500 Internal Server Error」の意味で，サーバ側が原因でエラーになってしまったことを表します．
PDOとmysqliの比較
mysqliクラス (またはmysqli関数)
手続き型(関数)とオブジェクト指向型(クラス)を両方ともサポートしています．
メリット
- 動作が 非常に 速い．
- (手続き型の場合は) mysql関数からの書き換えが容易．
- プリペアドステートメントで型を指定しながら複数のバインドが同時に できる ．
- マルチクエリ を使用できる．1回の関数コールで複数のクエリが実行できるため，普通に2回コールする場合に比べてパフォーマンスは向上する．
- エラーを例外としてスローさせることができる．2種類の設定方法があるが，これに関しては他の記述がオブジェクト指向型でも手続き型を採用すべきであると言える．明らかに手続き型の方がスッキリと書ける．
$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
- 不適切なインデックスを含むクエリに対しても，例外をスローさせることができる．開発段階でデータベースに負荷をかけてしまうクエリを潰すことが出来る利点がある．これを有効化するためには，下記のようにする．
MYSQLI_REPORT_ALLでまとめる手もある．
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT | MYSQLI_REPORT_INDEX);
mysqli_report(MYSQLI_REPORT_ALL);
デメリット
- プリペアドステートメントで名前付きプレースホルダが 使えない ．
- 「プリペアドステートメント ≠ 結果セット」 であり，SQL実行後にプリペアドステートメントから結果セットを取り出す必要がある．この点は次に紹介するPDOクラスと大きく異なる．
- その他の点でも多機能すぎる為，初心者が混乱しやすい．
PDOクラス
オブジェクト指向型のみをサポートしています．
メリット
- 動作が速い．
- エラーを例外としてスローさせることが出来る．但し，一部の特例を除き，コンストラクタは常に例外をスローする．
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- 「プリペアドステートメント ＝ 結果セット」 であり，mysqliクラスに比べると初心者にも分かりやすい．
- プリペアドステートメントで名前付きプレースホルダが 使える ．
- プリペアドステートメントのエミュレーションを行う場合は 同名の名前付きプレースホルダを複数回使える ．但し，エミュレーションを設定していないと
SQLSTATE[HY093]: Invalid parameter numberが発生する． - 他のデータベースへ乗り換える際，書き換えが容易．PDOクラスの使い方だけを覚えておけばドライバが存在するあらゆるデータベースに対応が出来るようになる．
デメリット
- プリペアドステートメントで型を指定しながら複数のバインドが同時に できない ．
-
SET NAMES sjisやSET CHARACTER SET sjisを設定している場合，プリペアドステートメントのエミュレーションを無効にしておかないと脆弱性が発生する． - プリペアドステートメントのエミュレーションを行う場合は PDOStatement::bindValue, PDOStatement::bindParam メソッドで，指定した型と実際の型が一致しない場合，全て文字列としてバインドされる．
PEAR::DB や PEAR::MDB2 に関しては,PDOが安定してきたPHP5.4系以降で使う必要性は皆無でしょう．PHPで実装された抽象化レイヤは，C言語で実装されたPDOよりも明らかにパフォーマンスが悪くなります．