1. Qiita
  2. Items
  3. PHP

PHPでデータベースに接続するときのまとめ

  • 1568
    Stock
  • 25
    Comment
Stocked
  • mAster_rAdio
  • yucchiy
  • yuku_t
  • nnabeyang
  • petitviolet
  • pogin503
  • WataruGodo
  • ukyo
  • okonomi
  • horitaku1124

初心者がやりがちなミス

以下のどれかに1つでも当てはまるコードは見直す必要があります.付録にリンクを貼っておきましたので,「該当するかも?」という人はクリックして飛んで読んでください.太字にしてあるものは脆弱性に直結する危険度の高いものです.

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=utf8

  • 頭にデータベースの種類を指定して : で区切る.
  • 各項目は 項目名=値 とし, ; で区切る.

dbname

データベース名を指定します.基本的には必須です.但し,データベースを後で USE test のようにSQL文で選択する場合,省略することができます.

host

ホスト名またはIPアドレスを指定します.ローカル環境で動かす場合,省略しても問題ない場合が多いです.localhost は自分自身のホスト名, 127.0.0.1 は自分自身のIPアドレスを指します.どちらを用いても問題が発生する可能性があるので,適宜問題のない方を選択してください.

charset

文字セットを指定します.SET NAMESは避けて,ここで指定するべきです.UTF-8ではなくutf8であることに注意してください.ハイフンは入りません.

$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クラスのエミュレーションを無効にする
$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=utf8',
        'root',
        '',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );

    /* データベースから値を取ってきたり, データを挿入したりする処理 */

} catch (Exception $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 メソッドで直接クエリを実行する

ユーザー入力を伴わないクエリで, INSERTUPDATE 等で作用した件数を直接返り値に欲しい場合は PDO::exec メソッドを代わりに使います.特に結果を必要としない場合においてもこちらを使用すべきです.後に登場する PDOStatement::execute と紛らわしいので注意してください.

全員の年齢を+1し,その対象となった人数を返り値として取得する
$count = $pdo->exec('UPDATE users SET age = age + 1');

PDO::preparePDOStatement::bindValuePDOStatement::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 が暗黙的に使用される.
エミュレーションがONの場合はこうする必要がある
$stmt = $pdo->prepare('SELECT * FROM users WHERE gender = ? AND age = ?');
$stmt->bindValue(1, $gender);
$stmt->bindValue(2, (int)$age, PDO::PARAM_INT);
$stmt->execute();
エミュレーションがOFFの場合はこれでもOK
$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);

名前付きプレースホルダ

  • : を頭につけ,半角英数字とアンダースコアにて構成する.
  • バインド時の頭の : は省略することが出来る.
こちらも同様にエミュレーションがONならば明示的なキャストを忘れない
$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の場合のみ,同名のプレースホルダを複数使うことが出来る.
IDが20で年齢も20歳の人を取得
$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::preparePDOStatement::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]);
compact関数を活用する例
$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']);
}
vprintf()関数を使った応用
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']);
}
0から始まるオフセットを取り出すことも出来る
foreach ($stmt as $i => $row) {
    printf("[%d] %s lives in %s<br />\n", $i, $row['name'], $row['city']);
}

PDOStatement::fetchObject

連想配列の代わりにstdClassオブジェクトとして取得します.PDOStatement::fetchPDO::FETCH_OBJ を指定するケースと等価ですが,こちらの方が短く書くことができます.

while ($row = $stmt->fetchObject()) {
    printf("%s lives in %s<br />\n", $row->name, $row->city);
}

PDOStatement::fetchColumn

特定の1カラムのみを文字列として取得します.PDOStatement::fetchPDO::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オブジェクトに対して後からフェッチモードを指定することができます.引数の渡し方がモードによって異なるので,詳しくはマニュアルを参照してください.

0番目のカラムをforeachで取得していくケース
$stmt->setFetchMode(PDO::FETCH_COLUMN, 0);
foreach ($stmt as $i => $name) { ... }

実は… PDO::query を用いる場合にも同じ形式でフェッチモードを指定することができます.

0番目のカラムをforeachで取得していくケース
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には当てはまりません

バッファクエリ使用時

非バッファクエリ使用時

  • 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は暗黙的な型変換を行います.

もしageが整数型でも,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 '!' とします.なお,エスケープ用の文字が文字列中に含まれているケースにも対応するために,そのエスケープ用の文字自体のエスケープも必要です.

PDOクラスでの例 (どんなデータベースにも広く使える方法)
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ? ESCAPE '!'");
$stmt->bindValue(1, '%' . preg_replace('/(?=[!_%])/', '!', $name) . '%', PDO::PARAM_STR);
PDOクラスでの例 (MySQL専用の方法)
$stmt = $pdo->prepare('SELECT * FROM users WHERE name LIKE ?');
$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つあり, summary1summary2どちらかに一致すれば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 に配列でセットするかどうかが問題となりますが,これは半角スペースで分割させることが一般的であると考えられます.しかし,ここでは半角スペースだけでなく…

  • 全てのASCII制御文字
  • 全角スペース
  • ハードスペース(&nbsp;)

正規表現を活用して,これらすべてを対象にしてみます.

$q = (string)filter_input(INPUT_GET, 'q');
$regex = "/[\\x0-\x20\x7f\xc2\xa0\xe3\x80\x80]++/u";
$keywords = preg_split($regex, $q, -1, 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=utf8',
        '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());

}

トランザクションの種類

秀逸な記事は太字にして紹介します.

推奨場面まとめ

ss (2014-03-26 at 01.47.23).png

各データベース製品のデフォルト

  • MySQL のInnoDBのデフォルトは REPEATABLE READ である.
  • PostgreSQL のデフォルトは READ COMMITTED である.
  • SQLite のデフォルトは DEFERRED であり,これは READ COMMITTED に相当する.

分離レベルを設定するSQLの実行

MySQLでの例
$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE');
PostgreSQLでの例
$pdo->exec('SET SESSION CHARACTERISTICS AS TRANSACTION SERIALIZABLE');

テーブル名・カラム名のエスケープ処理

設計としてはあまり宜しくありませんが, 「ユーザーに任意のテーブル名やカラム名を指定させたい」 というケースも考えられなくはないです.PDOクラスにこういったものをエスケープする機能は含まれていないので,自前でエスケープを行わなければなりません.

  • NULLバイトを除外する.
  • バッククオートを2つ連ねてエスケープさせる.
  • 最終的にバッククオートでくくる.
テーブル名をエスケープして動的に指定する例
$sql = sprintf(
    "CREATE TABLE `%s`(id int, name text)",
    str_replace(array("\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ドライバの文字セットにはノータッチです.入力側と出力側で文字セットが異なると,脆弱性が発生する原因になります.

PDOクラスでの正しい指定方法は,以下のようになります.

$pdo = new PDO('mysql:dbname=test;host=localhost;charset=utf8');

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文を組み立てるのが主流でした.

  1. mysql_real_escape_string 関数で文字列をあらかじめエスケープする
  2. 変数展開して埋め込む
$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 関数を利用していない

以下の記事をお読みください.

SQLの LIKE 演算子を使っているのに % _ \ のエスケープをしていない

以下のようなコードを書いていたらアウトです.

$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ?");
$stmt->bindValue(1, "%{$name}%", PDO::PARAM_STR);

プレースホルダを使っているので脆弱性こそありませんが,LIKE演算子による正しい検索が保証されません.正しい実装方法は本文中に記載しています.

HTMLの <body></body> の中にデータベース接続処理を書いている
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

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>

これで,エラー時に出力されるHTMLは以下のようになります.

No such file or directory

ただ,これってもはやHTMLではないですよね?もしエラー時にも丁寧にエラー画面用のHTMLを用意するのであればそれで構わないのですが, exitdie で手抜きしたい場合はWebブラウザに「これはただのテキストです」と伝えるようにしましょう. exit の前にこの1行を入れてください.

header('Content-Type: text/plain; charset=UTF-8', true, 500);

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クラスで例外をスローさせる
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  • 「プリペアドステートメント = 結果セット」 であり,mysqliクラスに比べると初心者にも分かりやすい.
  • プリペアドステートメントで名前付きプレースホルダが 使える
  • プリペアドステートメントのエミュレーションを行う場合は 同名の名前付きプレースホルダを複数回使える .但し,エミュレーションを設定していないと SQLSTATE[HY093]: Invalid parameter number が発生する.
  • 他のデータベースへ乗り換える際,書き換えが容易.PDOクラスの使い方だけを覚えておけばドライバが存在するあらゆるデータベースに対応が出来るようになる.

デメリット

  • プリペアドステートメントで型を指定しながら複数のバインドが同時に できない
  • SET NAMES sjisSET CHARACTER SET sjis を設定している場合,プリペアドステートメントのエミュレーションを無効にしておかないと脆弱性が発生する.
  • プリペアドステートメントのエミュレーションを行う場合は PDOStatement::bindValue, PDOStatement::bindParam メソッドで,指定した型と実際の型が一致しない場合,全て文字列としてバインドされる

PEAR::DBPEAR::MDB2 に関しては,PDOが安定してきたPHP5.4系以降で使う必要性は皆無でしょう.PHPで実装された抽象化レイヤは,C言語で実装されたPDOよりも明らかにパフォーマンスが悪くなります.

Send an edit requestYou can propose improvements about the article to the author 💪
3086 contribution

PHP 5.3.5 以前の PDO で charset を指定する方法として次のようなものもありました(MySQL限定)。

11803 contribution

参考になります!追記しておきます。

15336 contribution

awesome :sparkles:

昔PHPを触ってたときにこの記事を読みたかった

11803 contribution

ありがとうございます!

1230 contribution

レベルの違いを感じるわ(^^;
勉強させて頂きます。

11803 contribution

たぼさんお久しぶりです!
いやいや私もまだまだ^^;

1230 contribution

遅れましたm(_ _)m
お久しぶりです(^^)

ちなみに「tabo」って書いてますが、あだ名が「たー坊」なんです。
サンリオではありません★
(笑)

11803 contribution

この記事なんとかならないかなぁ・・・
何が「スパッと解決できて大変助かりました」「めっちゃ助かりました」だよっていう・・・
http://me.beginsprite.com/archives/889

0 contribution

SET NAMES utf8 、 SET CHARACTER SET utf8 が問題になるケース↓もあるようですが、

libmysqlclientのコンパイルオプションに --with-charset=cp932 や --with-charset=sjis を指定している場合

デフォルトでこのような環境があったりするのでしょうか。
なければSET ~ utf8を使う場合は手動コンパイルしていなければ問題ないという言い方もありかなと思いました。

11803 contribution

参考にさせていただいたリンク先を見ると

しかし、これはlatin-1がlibmysqlclientのデフォルトキャラクタセットの場合だけであって (実際コンパイルのデフォルトはそうなっている) libmysqlclientを–with-charset=cp932オプション付きでコンパイルしていた場合なんかだとその限りではない。

と書かれていますね。自分が管理者の場合は無いと断言してもいいぐらいでしょう。サーバー管理者が自分以外の場合はその方に聞いてみないことには分かりません。

311 contribution

はじめまして。
pdoの実際の使い方をコードを交えて解説してあって、とても参考になりました!

ちなみに一点確認なのですが、

<?php
$hoge = isset($_POST['hoge']) : $_POST['hoge'] : '';
<?php
$hoge = isset($_POST['hoge']) && is_string($_POST['hoge']) : $_POST['hoge'] : '';

は両方ともコロンになってますけど三項演算子って事で良いのですよね?

これからも参考にさせていただきます^^

11803 contribution

ご指摘感謝します!(今まで全く気付かなかった)

11803 contribution

誤った記事の犠牲者を発見
http://otukutun.hatenablog.com/entry/2013/02/06/171150

こっちなんかつい最近だし・・・
http://none.3rin.net/php/php-mysql%20pdo%E3%81%AE%E3%80%8Cset%20names%E3%80%8D%E3%81%AE%E4%BB%A3%E6%9B%BF%E7%AD%96

何とかしてくださいよ「beginsprite log」の管理人さん。。。

9 contribution

host にはホストを指定。127.0.0.1 はローカル環境を指す。
localhostとしてもいいが、Windows環境だと遅くなることがあるので注意。

PHPに限りませんが、MySQL では localhost127.0.0.1 で接続方法が変わるため注意が必要です(Unix系のみ?)。前者は Unix ソケットを使用して接続、後者は TCP/IP を使用して接続されます。そのため、my.cnf に skip-networking (TCPポートを無効化) を設定している環境では localhost でないと接続できません。引っ掛かると原因に気付きにくい所なのでご注意を。
とはいえ最近は、skip-networking を指定しない代わりに bind-address = 127.0.0.1 をデフォルトとしてローカルからのみTCP接続を受け付ける設定にするのが主流なようなので、そういった環境では 127.0.0.1 でも特に問題なく動くと思います。

11803 contribution

@hiroblo はい、提示していただければ...(あんまり大量のファイルとかになるとアレですが

11803 contribution

@hiroblo メールでお願いします。

409 contribution

はじめまして。
php初心者なので、すごく参考になります。

質問なのですが、executeでsql実行に失敗した時のエラー処理も書いたほうがいいですか?
(mysql側でのエラー、例えばout of memoryが発生した場合とかを想定してます)
executeは、例外を投げてくれないので戻り値で成否判断しないといけなさそうだったので…

11803 contribution

@re-24 ご質問ありがとうございます。

PDOStatement::executePDO::ERRMODE_EXCEPTION をエラーモードに設定すれば例外投げてくれるはずですが…

記事中で紹介している

"SET SESSION sql_mode='TRADITIONAL'"

を利用すると PDOStatement::execute 実行時にMySQL側でエラーを発生させることが容易に出来ますので、試してみてください。

基本的にPDOは エラー処理 を書かなくても 例外処理 だけで済むように設計されているはずです。

409 contribution

回答ありがとう御座います。

見落としてましたorz
(見落としちゃいけないところ見てなかった気がするけど・・・)

かなり参考になる情報がまとまってるので、定期的にも読ませてもらいますm(_ _)m

41 contribution

参考にさせてもらってます、ありがとうございます
すごい細かい話ですが、「exit や die で強制終了処理を記述している」の中のPDOクラスの場合の10行目のカンマがたぶん余計だと思います

0 contribution

はじめまして。とても良くまとまっていて有用な記事ですね。あちこちのドキュメントを参照する頻度が減ってきて、便利に利用させていただいています。

ところで些細なことなのですが、トランザクション処理のサンプルコード中に $stmt->commit(); という箇所がありますが、これは $pdo->commit(); の typo ですかね? (rollBack() の箇所も同様)

ご確認いただければ幸いです。

952 contribution

PostgreSQL のクライアントエンコーディングは pg_connect と同じく options で指定できます。

connection.php
$dsn = "pgsql:dbname=exampledb host=example.com options='--client_encoding=UTF8'";

PEAR::MDB2 の代わりを挙げるとすれば、MDB2 のコードの一部を取り込んでいる Doctrine DBAL が妥当だと思います。Doctrine DBAL は PDO の薄いラッパーで、プリペアドステートメントがデフォルトで使われるので、うっかり忘れを防ぐことができます。Doctrine は Symfony のプロジェクトに採用されており、コミュニティーの規模と開発者のスキルの点で、開発の継続性が期待できます。

123 contribution

大分昔の特殊な組み合わせのバグに関するトピックですが。
まとめ的に投稿しておきます。

PDO/MySQL(Windows版)の文字エンコーディング指定の不具合原因
http://www.tokumaru.org/d/20110329.html#p01

windows環境のphp5.3.6までは、charset=sjisを指定した場合にのみ
mysqlndドライバのエスケープ処理にバグが有りました。(utf8やcp932は問題なし)

公式配布されていたwindows用php5.3バイナリでは、mysql, mysqli, pdo_mysql拡張モジュールすべてで、mysqlndドライバを使うようにコンパイルされており影響を受けていました。
http://php.net/manual/ja/mysql.installation.php
http://php.net/manual/ja/mysqli.installation.php
http://php.net/manual/ja/ref.pdo-mysql.php

0 contribution

ではみなさん、この歌を聞いてみましょう。
:headphones: https://youtu.be/d2Kj7YybM5o

11803 contribution

記事の内容を大幅に改訂しました

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.