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

**【2021/10/15 追記】**
**この記事は更新が停止されています。現在では筆者の思想が変化している面もありますので，過去の記事として参考程度にご覧ください。PDO に関しては大きく変わっていない部分が多いとは思いますが， PHP 8.x 以降での動作保証はありません。**

# あらかじめ読んでおきたい記事

- [Qiita - 【PHP超入門】クラス～例外処理～PDOの基礎](http://qiita.com/7968/items/6f089fec8dde676abb5b) by @7968 

# 初心者がやりがちなミス

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

- [`mysql_query` などの非推奨関数を利用している](#mysql_query-%E3%81%AA%E3%81%A9%E3%81%AE%E9%9D%9E%E6%8E%A8%E5%A5%A8%E9%96%A2%E6%95%B0%E3%82%92%E5%88%A9%E7%94%A8%E3%81%97%E3%81%A6%E3%81%84%E3%82%8B)
- [`SET NAMES` あるいは `SET CHARACTER SET` などで文字コードを指定している<br>そもそもデータベースで使用する文字コードの指定をしていない](#set-names-%E3%81%82%E3%82%8B%E3%81%84%E3%81%AF-set-character-set-%E3%81%AA%E3%81%A9%E3%81%A7%E6%96%87%E5%AD%97%E3%82%B3%E3%83%BC%E3%83%89%E3%82%92%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%A6%E3%81%84%E3%82%8B%E3%81%9D%E3%82%82%E3%81%9D%E3%82%82%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%A7%E4%BD%BF%E7%94%A8%E3%81%99%E3%82%8B%E6%96%87%E5%AD%97%E3%82%B3%E3%83%BC%E3%83%89%E3%81%AE%E6%8C%87%E5%AE%9A%E3%82%92%E3%81%97%E3%81%A6%E3%81%84%E3%81%AA%E3%81%84)
- **[`"SELECT * FROM users WHERE id = '$id'"` のように変数展開を使ってSQL文を組み立てている](#select--from-users-where-id--id-%E3%81%AE%E3%82%88%E3%81%86%E3%81%AB%E5%A4%89%E6%95%B0%E5%B1%95%E9%96%8B%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6sql%E6%96%87%E3%82%92%E7%B5%84%E3%81%BF%E7%AB%8B%E3%81%A6%E3%81%A6%E3%81%84%E3%82%8B)**
- [`$_POST['id']` などの外部入力からきた変数が定義されているかどうか確認していない<br>それらが文字列であるかどうかの確認をしていない<br>**それらを出力する際に `htmlspecialchars` 関数を利用していない**](#_postid-%E3%81%AA%E3%81%A9%E3%81%AE%E5%A4%96%E9%83%A8%E5%85%A5%E5%8A%9B%E3%81%8B%E3%82%89%E3%81%8D%E3%81%9F%E5%A4%89%E6%95%B0%E3%81%8C%E5%AE%9A%E7%BE%A9%E3%81%95%E3%82%8C%E3%81%A6%E3%81%84%E3%82%8B%E3%81%8B%E3%81%A9%E3%81%86%E3%81%8B%E7%A2%BA%E8%AA%8D%E3%81%97%E3%81%A6%E3%81%84%E3%81%AA%E3%81%84%E3%81%9D%E3%82%8C%E3%82%89%E3%81%8C%E6%96%87%E5%AD%97%E5%88%97%E3%81%A7%E3%81%82%E3%82%8B%E3%81%8B%E3%81%A9%E3%81%86%E3%81%8B%E3%81%AE%E7%A2%BA%E8%AA%8D%E3%82%92%E3%81%97%E3%81%A6%E3%81%84%E3%81%AA%E3%81%84%E3%81%9D%E3%82%8C%E3%82%89%E3%82%92%E5%87%BA%E5%8A%9B%E3%81%99%E3%82%8B%E9%9A%9B%E3%81%AB-htmlspecialchars-%E9%96%A2%E6%95%B0%E3%82%92%E5%88%A9%E7%94%A8%E3%81%97%E3%81%A6%E3%81%84%E3%81%AA%E3%81%84)
- [SQLの `LIKE` 演算子を使っているのに `%` `_` `\` のエスケープをしていない](#sql%E3%81%AE-like-%E6%BC%94%E7%AE%97%E5%AD%90%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A6%E3%81%84%E3%82%8B%E3%81%AE%E3%81%AB--_--%E3%81%AE%E3%82%A8%E3%82%B9%E3%82%B1%E3%83%BC%E3%83%97%E3%82%92%E3%81%97%E3%81%A6%E3%81%84%E3%81%AA%E3%81%84)
- [HTMLの `<body></body>` の中にデータベース接続処理を書いている<br>`echo()` や `print()` をベタ書きしている<br>Content-Typeを `text/plain` に変更せずに `exit()` や `die()` で強制終了処理を記述している](#html%E3%81%AE-bodybody-%E3%81%AE%E4%B8%AD%E3%81%AB%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%8E%A5%E7%B6%9A%E5%87%A6%E7%90%86%E3%82%92%E6%9B%B8%E3%81%84%E3%81%A6%E3%81%84%E3%82%8Becho-%E3%82%84-print-%E3%82%92%E3%83%99%E3%82%BF%E6%9B%B8%E3%81%8D%E3%81%97%E3%81%A6%E3%81%84%E3%82%8Bcontent-type%E3%82%92-textplain-%E3%81%AB%E5%A4%89%E6%9B%B4%E3%81%9B%E3%81%9A%E3%81%AB-exit-%E3%82%84-die-%E3%81%A7%E5%BC%B7%E5%88%B6%E7%B5%82%E4%BA%86%E5%87%A6%E7%90%86%E3%82%92%E8%A8%98%E8%BF%B0%E3%81%97%E3%81%A6%E3%81%84%E3%82%8B)

# PDOの基本

PHPでデータベースに接続するためには，現在はPDOを使う方法が主流であると言えるでしょう．PDOの基本的な使い方を確認していきます．内容によって，「前でさらっと書いてあることの定義が後で出てくる」など，どうしても多少前後してしまう部分はありますが，特に気にせずに読み流してください．

## データベースに接続

[PDO::__construct] メソッドを使用してインスタンスを生成します．コンストラクタなので，実際には直接これを呼び出すコードは書かずに， **new演算子** を用います．

```php
$pdo = new PDO($dsn, $username, $password, $driver_options);
```

引数は以下の通りです．

### `$dsn` 

データベースに接続するために必要な情報です． (Data Source Name)
以下に各データベース製品に応じたDSNの書き方が掲載されています．

- [PHP Manual - PDOクラスのデータベース別DSN一覧](http://www.php.net/manual/ja/pdo.drivers.php)

MySQLの基本的な書き方を例に挙げます．

**`mysql:dbname=test;host=localhost;charset=utf8mb4`**

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

#### <font color="silver">dbname</font>

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

#### <font color="silver">host</font>

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

- Linux環境はホスト名推奨
  - [upsilonさん: MySQLではlocalhostと127.0.0.1で接続方法が変わるため注意が必要](http://qiita.com/mpyw/items/b00b72c5c95aac573b71#comment-e9db50fff9bffa1dd6f8)
- Windows環境はIPアドレス推奨
  - [AH-2 - Windowsでlocalhostへの接続が遅い(解決方法)](http://www.ah-2.com/2012/01/28/win_localhost_slow.html)

#### <font color="silver">charset</font>

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

**【2016/12/17 追記】**
**MySQL5.5.3以降の場合は，4バイトからなる絵文字なども正常に取り扱える`utf8mb4`を使用することを強く推奨します。**

### `$username`

ユーザー名．ルート権限を使う場合，デフォルトでは `root` です．

### `$password`

パスワード．ルート権限を使う場合，デフォルトでは空白です．

### `$driver_options`

接続時のオプションを連想配列で渡します．

- キーはあらかじめ用意されている定数を取る．
- 値はあらかじめ用意されている定数以外に，論理値・文字列などの一般的な値も取り得る．

```php
[
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]
```

オプションの内容については後で触れます．

## 接続後にオプションを指定

[PDO::setAttribute] メソッドを使用します．多くのオプションはコンストラクタの `$driver_options` で指定してもこちらで指定しても大差はありませんが， `PDO::MYSQL_ATTR_INIT_COMMAND` `PDO::ATTR_PERSISTENT` など，一部コンストラクタ専用のものがあります．

```php
$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として報告する．<br><ins>[PDOStatement::execute] メソッドの返り値が `false` でないかを毎回のように確認する必要がある</ins>．
- `PDO::ERRMODE_SILENT` は何も報告しない．<br><ins>[PDOStatement::execute] メソッドの返り値が `false` でないかを毎回のように確認する必要がある</ins>．

### `PDO::ATTR_DEFAULT_FETCH_MODE`

[PDOStatement::fetch] メソッドや [PDOStatement::fetchAll] メソッドで引数が省略された場合や，ステートメントがforeach文に直接かけられた場合のフェッチスタイルを設定します．デフォルトは`PDO::FETCH_BOTH`です．

- `PDO::FETCH_BOTH`<br>
カラム番号とカラム名の両方をキーとする連想配列で取得する．
- `PDO::FETCH_NUM`<br>
カラム番号をキーとする配列で取得する．
- **`PDO::FETCH_ASSOC`**<br>
カラム名をキーとする連想配列で取得する．これが一番ポピュラーな設定．
- `PDO::FETCH_OBJ`<br>
カラム名をプロパティとする基本オブジェクトで取得する．

### `PDO::ATTR_EMULATE_PREPARES`

データベース側が持つ「プリペアドステートメント」という機能のエミュレーションをPDO側で行うかどうかを設定します．

- PHP5.1のデフォルトは `false`
- <ins>PHP5.2以降のデフォルトは `true`</ins>

この設定で，いくつかPDOの挙動に違いが表れます．

- プリペアドステートメントのためにデータベースサーバと通信する必要が無くなるため，エミュレーションを行ったほうがパフォーマンスは向上する．
- 存在しないテーブル名やカラム名をSQL文に持つプリペアドステートメントを発行したとき，エミュレーションOFFの場合はすぐにエラーが発生するが，エミュレーションONの場合は実際にクエリを実行するまでエラーが発生するかどうかわからない．
- エミュレーションがONの場合のみ， `;` 区切りで複数のSQL文を1つのクエリで実行することができる．

その他，どちらにも利点と欠点があるので，違いは追って見ていき，最後に表にまとめることにします．

```php:PDOクラスのエミュレーションを無効にする
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
```

### `PDO::ATTR_PERSISTENT` (コンストラクタ専用)

`true` を設定すると，スクリプトが終了してもデータベースへの接続を維持し，次回に再利用します．規模が大きくなってくると設定する恩恵が大きくなりますが，ほそぼそと練習用サイトを作っているうちは役に立たないでしょう．

### `PDO::MYSQL_ATTR_USE_BUFFERED_QUERY` (MySQL専用)

`true` のとき， バッファクエリを使用します．デフォルト値はバージョンによって異なるようで，不明です．PHP5.3の時代には既にデフォルトが `true` で今も変わっていない…？少なくとも，私が確認できる限りの最近バージョンではデフォルトは `true` です．

- **バッファクエリ**:<br>全ての情報をデータベースサーバから取得してきておいて，PHPに1件ずつ取り出させる
- **非バッファクエリ**:<br>1件ごとにデータベースサーバと通信を行って，PHPに取り出させる

取得してくる情報がメモリに収まりきらない莫大な量である，といった非常に特殊なケースを除けば，バッファクエリを選択しておく方が無難です．サーバ負荷も軽減され，途中までフェッチしたところで突然例外が発生するような事態も避けられます．また，非バッファクエリには複数同時にクエリを実行できないなどの大きな欠点もあります．基本的にこれは，データベースから取得したデータでHTMLを表示する用途ではなく，<ins>コマンドラインからバッチ処理を実行する</ins>用途で使われます．

### `PDO::MYSQL_ATTR_INIT_COMMAND` (MySQL専用) (コンストラクタ専用)

接続した直後に実行されるクエリをここに書きます．

## 基本コーディング

書き方のテンプレです．

```php
<?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** をスローしますが， <ins>ホストに接続できなかった</ins>場合などに **Warning** を発生します．普通は気に留める必要はありませんが，ホストを動的に指定させるようなプログラムを書いているならば， [set_error_handler](http://www.php.net/manual/ja/function.set-error-handler.php) 関数を使って **ErrorException** に変換しなければ，例外処理を行うことができません．

## [PDO::query] メソッドで直接クエリを実行する


ユーザー入力を伴わないクエリに関しては単に [PDO::query] メソッドを実行すればいいだけです．返り値は **PDOStatement** となります．

```php:全員を取得する
$stmt = $pdo->query('SELECT * FROM users');
```

## [PDO::exec](http://www.php.net/manual/ja/pdo.exec.php) メソッドで直接クエリを実行する

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

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

## [PDO::prepare] → [PDOStatement::bindValue] → [PDOStatement::execute] の3ステップでクエリを実行する

ユーザー入力を受け取ってSQL文を動的に生成する場合は **プリペアドステートメント** と **プレースホルダ** を使わなければなりません．

- **プレースホルダ**:<br>直訳すると「場所取り」．何かユーザ入力を当てはめる場所としてあらかじめ確保しておくもの．
- **プリペアドステートメント**:<br>直訳すると「予約文」．文を予約したもの．通常，「予約文」は「場所取り」を使うために作られる．もし「場所取り」が無ければ普通に [PDO::query] などで実行するだけで十分なためである．

プレースホルダには2種類あり，**疑問符プレースホルダ** を使う方法と， **名前付きプレースホルダ** を使う方法があります．もしこれらが混ざってしまうと

```
SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
```

が発生するので，どちらか一方のみを選択してください．

### 疑問符プレースホルダ

- `?` の「番目」は **1** から始まる．
- `PDO::PARAM_STR` は省略することが出来る．
- エミュレーションがONの場合には正しくキャストしてくれないバグのようなものが仕様として存在するため，<ins>文字列以外のものを扱う際に明示的なキャストが必要</ins>．
- `NULL` 値に関しては `PDO::PARAM_NULL` が暗黙的に使用される．

```php:エミュレーションが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();
```

```php:エミュレーションが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();
```

- 番目で整数以外を指定した場合，適当な変換が行われる． (但し，こんなコードは書くべきではない)

```php:良い例
$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);
```

```php:悪い例
// 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);
```

### 名前付きプレースホルダ

- `:` を頭につけ，半角英数字とアンダースコアにて構成する．
- バインド時の頭の `:` は省略することが出来る．

```php:こちらも同様にエミュレーションが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);
```

```php:頭のコロンを省略したケース
$stmt->bindValue('gender', $gender);
```

- <ins>エミュレーションがONの場合のみ</ins>，同名のプレースホルダを複数使うことが出来る．

```php: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] メソッドもありますが，原則的にこちらを使う必要はありません．<ins>エミュレーションがONの場合</ins>，実行後にバインドした変数が文字列型にされる仕様もあるので注意してください．

- [Qiita - 最近PDOを使っていてハマったこと2つ．](http://qiita.com/_dozen_/items/e3c00a0a581378a4cc70)

## [PDO::prepare] → [PDOStatement::execute] の2ステップでクエリを実行する

[PDOStatement::execute] メソッドの引数に配列を渡すと，それらを全てバインドしたあとそのままSQLを実行してくれます．但し，以下の条件に注意してください．

- <ins>`NULL` 値以外は全て `PDO::PARAM_STR` 扱いになる</ins> ．もし間違った型でバインドをしても，MySQL/SQLiteはデータベース側で自動的にキャストし直してくれるが，パフォーマンスの低下やバグの原因になるので，可能な限り避けたほうがいい．<ins>PostgreSQLの場合はエラーになる</ins>．
- また， <ins>既に [PDOStatement::bindValue] メソッドで値がバインドされていた場合でも，それらは全て無視される</ins>．これを用いる場合，全てのバインドをこの引数で行わなければならない．

### 疑問符プレースホルダ

[PDOStatement::bindValue] メソッドを用いたときと異なり，`?` の「番目」が **0** から始まることに注意してください．

```php
$stmt = $pdo->prepare('SELECT * FROM users WHERE city = ? AND gender = ?');
$stmt->execute([$city, $gender]);
```

```php:キーを適切に設定すれば順番を変えて指定することも可能
$stmt->execute([1 => $gender, 0 => $city]);
```

### 名前付きプレースホルダ

```php
$stmt = $pdo->prepare('SELECT * FROM users WHERE city = :city AND gender = :gender');
$stmt->execute([':city' => $city, ':gender' => $gender]);
```

```php:頭のコロンは省略することが出来る
$stmt->execute(['city' => $city, 'gender' => $gender]);
```

```php:compact関数を活用する例
$stmt->execute(compact('city', 'gender'));
```

## `SELECT` の結果を取得する

### 結果の型

#### <font color="silver">MySQL</font>

| データベース | PHP<br />(エミュレーション無しmysqlnd) | PHP<br />(libmysqlclient)<br />(エミュレーション有りmysqlnd)<br /> |
|:--------:|:--------:|:--------:|
| NULL | NULL | NULL |
| 文字列 | String | String |
| 日付 | String | String |
| タイムスタンプ | Integer | String |
| 論理値 | **Integer** | String |
| PHPで扱える<ins>値の</ins>整数 | Integer | String | 
| PHPで扱えない<ins>値の</ins>整数 | String | String | 
| 浮動小数点数 | Float | String |

エミュレーションに関するオプションは `PDO::ATTR_EMULATE_PREPARES` という命名ではありますが，<ins>プリペアドステートメントを使用しない場合にも影響が及ぶ</ins>ことに注意してください．

#### <font color="silver">PostgreSQL</font>

| データベース | PHP |
|:--------:|:--------:|
| NULL | NULL |
| 文字列 | String |
| 日付 | String |
| タイムスタンプ | Integer |
| 論理値 | Boolean |
| PHPで扱える<ins>型の</ins>整数 | Integer |
| PHPで扱えない<ins>型の</ins>整数 | String |
| 浮動小数点数 | **String** |

#### <font color="silver">SQLite</font>

| データベース | PHP | 
|:--------:|:--------:|
| NULL | NULL |
| その他 | String |

### [PDO::setAttribute](http://www.php.net/manual/ja/pdo.setattribute.php) で取得する型を変更できるケース

#### `PDO::ATTR_ORACLE_NULLS`

| データベース上 |**`PDO::NULL_EMPTY_STRING`**|**`PDO::NULL_TO_STRING`**|
|:--------:|:--------:|:--------:|
| NULL | NULL | "" |
| "" | NULL | "" |

オプション名に `ORACLE` という名前が入っていますが，Oracle以外のデータベースでも利用することができます．

#### `PDO::ATTR_STRINGIFY_FETCHES`

`true` に設定すると，エミュレーションがOFFの場合に数値が文字列化されます．<ins>エミュレーションがONの場合は，この設定に関わらず常に文字列化されます</ins>．

### [PDOStatement::fetch]

カーソルをずらしながら，指定したフェッチモードで1行ずつ取得していきます．

- 引数を省略した場合はデフォルトフェッチモードが使用される．
- 全ての取得が終わると常に `false` を返す．

デフォルトフェッチモードを `PDO::FETCH_ASSOC` に設定した際の例を示します．

```php:一番基本的な方法
while ($row = $stmt->fetch()) {
    printf("%s lives in %s<br />\n", $row['name'], $row['city']);
}
```

```php:vprintf()関数を使った応用
while ($row = $stmt->fetch()) {
    vprintf("%s lives in %s<br />\n", $row);
}
```

PDOStatementクラスはTraversableインターフェースを実装しているため，デフォルトフェッチモードを使う場合，while文の代わりにforeach文でもっとスマートに書くことができます．但し，<ins>HTML表示のために変数を準備する場合には，多くは配列として持っておく方が都合がいいので，後に紹介する [PDOStatement::fetchAll] メソッドの使用を検討してください</ins>．

```php
foreach ($stmt as $row) {
    printf("%s lives in %s<br />\n", $row['name'], $row['city']);
}
```

```php:0から始まるオフセットを取り出すことも出来る
foreach ($stmt as $i => $row) {
    printf("[%d] %s lives in %s<br />\n", $i, $row['name'], $row['city']);
}
```

### [PDOStatement::fetchObject](http://www.php.net/manual/ja/pdostatement.fetchobject.php)

連想配列の代わりにstdClassオブジェクトとして取得します．[PDOStatement::fetch](http://www.php.net/manual/ja/pdostatement.fetch.php) で `PDO::FETCH_OBJ` を指定するケースと等価ですが，こちらの方が短く書くことができます．

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

### [PDOStatement::fetchColumn](http://www.php.net/manual/ja/pdostatement.fetchcolumn.php)

特定の1カラムのみを文字列として取得します．[PDOStatement::fetch] で `PDO::FETCH_COLUMN` を指定するケースと等価ですが，こちらの方が短く書くことができます．

- 先頭から数えてそのカラムが何番目にあるかを第1引数として渡す．「番目」は **0** から始まる．省略した場合は 0 を指定したとみなされる．
- 値に `0` が含まれる可能性がある場合は， **`false !==`** の条件判定をしなければならない．

```php
while (false !== $value = $stmt->fetchColumn()) {
    echo "{$value}<br />\n";
}
```

### [PDOStatement::fetchAll]

一気に全件取得して2次元配列とします．

- 引数を省略した場合はデフォルトフェッチモードが使用される．

```php
$rows = $stmt->fetchAll();
var_dump($rows);
```

- 特定のカラムだけ一気に全件取得して1次元配列としたい場合，第1引数に **`PDO::FETCH_COLUMN`** を指定し，第2引数にカラムの「番目」を渡す．「番目」は **0** から始まる．省略した場合は 0 を指定したとみなされる．

```php
$values = $stmt->fetchAll(PDO::FETCH_COLUMN);
var_dump($values);
```

### [PDOStatement::setFetchMode](http://www.php.net/manual/ja/pdostatement.setfetchmode.php)

PDOオブジェクト自体にデフォルトフェッチモードを指定する方法を紹介しましたが，このメソッドを利用すれば個別に発行されたPDOStatementオブジェクトに対して後からフェッチモードを指定することができます．引数の渡し方がモードによって異なるので，詳しくはマニュアルを参照してください．

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

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

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

## `UPDATE`, `INSERT` で作用した行数を取得する

こちらの結果に対してフェッチしようとした場合，`SQLSTATE[HY000]: General error` が発生します．こちらに対して用いることができるのは [PDOStatement::rowCount](http://www.php.net/manual/ja/pdostatement.rowcount.php) メソッドのみです．

```php
printf("%d行に作用しました<br >\n", $stmt->rowCount());
```

## `SELECT` で該当した行数を取得する

基本的に，以下の方法に従います．

- 件数だけでなく結果セットも一緒に欲しい場合， [PDOStatement::fetchAll] メソッドで一気に配列として取得し，それに対してPHPの [count] 関数を使う．
- 件数だけが欲しい場合は， `SELECT COUNT(*) WHERE ...` といったクエリを発行し，その結果を [PDOStatement::fetchColumn] メソッドで得る．

これらの方法が最も推奨されますが，他の方法が無いわけではありません．以下に補足説明を示します．但しこれらはMySQLやPostgreSQLについてのみ当てはまり，<ins>SQLiteには当てはまりません</ins>．

### バッファクエリ使用時

- **SELECT** に対しても常に [PDOStatement::rowCount] メソッドを使うことができる．

### 非バッファクエリ使用時

- [PDOStatement::fetchAll] メソッドを実行した後，つまり<ins>全てのフェッチが終わった後</ins>であれば **SELECT** に対しても [PDOStatement::rowCount] メソッドを使うことができる．
- 結果のフェッチを途中で中断したまま次のクエリ実行に移行するときは， [PDOStatement::closeCursor] メソッドを使ってカーソルを閉じる必要がある．1つ目の結果セットをPDOStatementに保持したまま2つ目のSQLを実行することはできないので，その場合はあらかじめ [PDOStatement::fetchAll] メソッドでデータを退避させておく必要がある．

## データベース接続の切断

データベース処理の最後に

```php
$pdo = null;
```

と書いているコードが散見されますが，ほとんどの場合ではこの記述は不要です．必要になるのは，非バッファクエリを使うようなバッチ処理のシーンのみです．

# エミュレーションに関するまとめ

これまでに何度か登場した，エミュレーションの有無に関する差異を表にまとめます．

| 項目 | エミュレーションON | エミュレーションOFF |
|:----|:---------:|:----:|
| パフォーマンス | ○ | △ |
| `SET NAMES` による安全性 | △ | ○ |
| NULL値をそのままの型で受け取る | ○ | ○ |
| **数値をそのままの型で受け取る**<br />( **mysqlnd** のみが対象) | × | ○ |
| **複数の同名プレースホルダ** | ○ | × |
| **`PDO::PARAM_*` 定数による正しいキャスト** | × | ○ |
| **[PDOStatement::bindParam]** メソッドによる副作用問題 | × | ○ |
| **複文の実行** | ○ | ✕ |

# PDOの応用

## PDOStatementのデータ取得形式を極める

- [Qiita - PDOフェッチパターン大全](http://qiita.com/mpyw/items/d52351bd1a8068344cc2)

## MySQLの暗黙的な型変換を防止する

既に述べたとおり，MySQLは暗黙的な型変換を行います．

```php:もしageが整数型でも，MySQLはエラーを発生しない
$stmt = $pdo->prepare('SELECT * FROM users WHERE age = ?');
$stmt->execute(['20']);
```

もしPostgreSQLのように厳密さを与えたいならば，あらかじめ以下のクエリを実行しておきます．

```php
$pdo->exec("SET SESSION sql_mode='TRADITIONAL'");
```

- [Qiita - MySQLの自動変換が厄介な時の設定](http://qiita.com/yukix/items/c9ad1e38bb98b7c924f3)

## LIKE検索

部分一致のあいまいな検索を行いたい場合，LIKE演算子を使用します．LIKE検索で使われる特殊文字には

- `%` … 任意の0文字以上の文字列
- `_` … 任意の1文字

があるため，これらの文字を普通に検索したい場合にはエスケープが必要となります．MySQLの場合はエスケープ用の文字を省略して `\` にすることが出来ますが，SQLiteなど他のデータベースとの互換性を考慮する場合，エスケープに使用する文字を明示すべきです．例えば `!` を用いる場合は `ESCAPE '!'` とします．なお，エスケープ用の文字が文字列中に含まれているケースにも対応するために，そのエスケープ用の文字自体のエスケープも必要です．

```php:どんなデータベースにも広く使える方法
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ? ESCAPE '!'");
$stmt->bindValue(1, '%' . preg_replace('/(?=[!_%])/', '!', $name) . '%', PDO::PARAM_STR);
```

```php:MySQL専用の方法
$stmt = $pdo->prepare('SELECT * FROM users WHERE name LIKE ?');
$stmt->bindValue(1, '%' . addcslashes($name, '\_%') . '%', PDO::PARAM_STR);
```

## ブール全文検索

MySQLでブール全文検索を行いたい場合，IN BOOLEAN MODE修飾子を使用します．全文検索関数で使われる特殊文字には

- `+` … 各行に存在しなければならない
- `-` … 各行に存在してはならない
- `@` … 距離
- `<`, `>` … 単語の貢献度
- `(`, `)` … グループ化
- `~` … 否定演算子
- `*` … 切り捨てまたはワイルドカード
- `"` … フレーズ検索

があるため，これらの文字を普通に検索したい場合にはエスケープが必要となります．

```php:MySQL専用の方法
$stmt = $pdo->prepare('SELECT * FROM users WHERE MATCH (description) AGAINST (? IN BOOLEAN MODE)');
$stmt->bindValue(1, addcslashes($name, '\+-@<>()~*"'), PDO::PARAM_STR);
```

## 可変長プレースホルダ

### 固定長プレースホルダではどうなるか

例えば，検索キーワードを複数受け取って特定のカラムがそれらすべてに部分一致するかどうかの検索を行う場合を考えましょう．キーワードの数が2個の場合，実行の流れは次のようになります．

```php
$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);
```

### 可変長プレースホルダの導入

ところが，実際は検索キーワードの数が不定個数となるのが一般的です．このような場合にはどうすればいいか？そこで必要になってくるのが，動的にプレースホルダを生成することです．具体例を下に示します．

```php
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* の<ins>どちらかに</ins>一致すればOKと見なす場合は次のようにします．OR条件とAND条件を両方用いていることに注意してください．

```php
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` に配列でセットするかどうかが問題となりますが，これは半角スペースで分割させることが一般的であると考えられます．しかし，ここでは半角スペース以外のさまざまな空白文字を正規表現で一括指定しています。

```php
$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](http://www.php.net/manual/ja/function.preg-split.php) 関数の強みです．

## トランザクション処理

連続的なSQL実行に一貫性・信頼性を持たせたい場合，「トランザクション」という機能を利用します．

- [Wikipedia - トランザクション処理](http://ja.wikipedia.org/wiki/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%87%A6%E7%90%86)

### トランザクションの基本的な利用法

#### 使用されるメソッド

処理の実体はデータベースごとに固有の実装が為されていますが，インタフェースはPDOのメソッドとして抽象化されており，SQL文をデータベースごとに書き分けたりする必要はなくなります．

- [PDO::beginTransaction](http://www.php.net/manual/ja/pdo.begintransaction.php)
- [PDO::commit](http://www.php.net/manual/ja/pdo.commit.php)
- [PDO::rollBack](http://www.php.net/manual/ja/pdo.rollback.php)
- [PDO::lastInsertId](http://www.php.net/manual/ja/pdo.lastinsertid.php) (必要に応じて)

#### ポイント

- Tryブロックを **2重** に設け，例外発生時にはロールバックを実行し，捕捉した例外を必要に応じて<ins>外側のTryブロックに向けてスロー</ins>する．
- 同じ形式のプリペアドステートメントの生成は1回だけにして，それを使いまわすようにした方がパフォーマンスは向上する．

```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,
        ]
    );
    
    // パラメータ
    $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のトランザクション分離レベル](http://tree-tips.appspot.com/mysql/transaction_isoration/)**
- **[TECHSCORE - トランザクションの定義](http://www.techscore.com/tech/sql/SQL11/11_03.html)**
- **[主に言語とシステム開発に関して - DBの「トランザクション分離レベル」が必要な理由](http://d.hatena.ne.jp/language_and_engineering/20110104/p1)**
- [Wikipedia - トランザクション分離レベル](http://ja.wikipedia.org/wiki/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB)
- [CUBE SUGAR STORAGE - MySQL を使ってトランザクション分離レベルの違いを試す](http://momijiame.tumblr.com/post/41512778756/mysql)
- [ソフト開発お仕事メモ - \[DB\]H2, Derby, SQLiteの仕様の調査メモ（ロック周り）](http://d.hatena.ne.jp/sekom/20081115/p1)

#### 推奨場面まとめ

![ss (2014-03-26 at 01.47.23).png](https://qiita-image-store.s3.amazonaws.com/0/25060/ff3dadc2-2819-235f-eb2a-8f12ba5b9405.png)

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

- **MySQL** のInnoDBのデフォルトは **`REPEATABLE READ`** である．
- **PostgreSQL** のデフォルトは **`READ COMMITTED`** である．
- **SQLite** のデフォルトは **`DEFERRED`** であり，これは `READ COMMITTED` に相当する．

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

```php:MySQLでの例
$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE');
```

```php:PostgreSQLでの例
$pdo->exec('SET SESSION CHARACTERISTICS AS TRANSACTION SERIALIZABLE');
```

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

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

- NULLバイトを除外する．
- バッククオートを<ins>2つ連ねて</ins>エスケープさせる．
- 最終的にバッククオートでくくる．

```php:テーブル名をエスケープして動的に指定する例
$sql = sprintf(
    "CREATE TABLE `%s`(id int, name text)",
    str_replace(["\0", "`"], ["", "``"], $table_name)
);
```

## PDOクラスの継承

- [Qiita - PDOでオブジェクトをフェッチ＆JSONとCSVファイル出力](http://qiita.com/k-holy/items/ce829a60cafe48bd1f81)
- [Architect Note - PDOの真の力を開放する - PHPでデータベースを扱う(3)](http://blog.tojiru.net/article/277021312.html)

-----
-----

# 付録

## 初心者がやりがちなミスの解説

### `mysql_query` などの非推奨関数を利用している

```php
$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` などで文字コードを指定している<br>そもそもデータベースで使用する文字コードの指定をしていない

文字コードの指定が無いのは論外として，`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を利用している場合は問題ない．
  - [へぼい日記 - libmysqlclientを使うプログラムはset namesをutf8であっても使ってはいけない](http://blog.everqueue.com/chiba/2009/02/05/129/)

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

- コンストラクタの [DSN(Data Source Name)](http://www.php.net/manual/ja/ref.pdo-mysql.connection.php) で指定する．

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

- PHP5.3.5以前のPDOではDSNで文字セット指定が出来ないため， `SET NAMES` や `SET CHARACTER SET` に頼らざるを得ない．但しLinux版では，cnfファイルを利用する選択肢がある．
  - [Qiita - PHP 5.3.6より前のバージョンの PDO MySQL で charset を指定する](http://qiita.com/ngyuki/items/d88a4df860abb51eb714)
- PHP5.3.6Windows版のPDOではDSNで文字セット指定が出来るが，バグがあるため， `SET NAMES utf8` や `SET CHARACTER SET utf8` を使う場合と同じ結果になる．
- やむを得ず `SET NAMES` を使うにしても， `SET CHARACTER SET` は避けるべき．
  - [MySQLマニュアル - SET NAMES と SET CHARACTER SET の違い](http://dev.mysql.com/doc/refman/4.1/ja/charset-connection.html)

MySQL以外では以下のようになります．

- PostgreSQLでは，DSNに `options='--client_encoding=UTF8'"` を含める．
- SQLiteでは特に指定する必要が無く，暗黙的にUTF-8が使用される．

### **`"SELECT * FROM users WHERE id = '$id'"` のように変数展開を使ってSQL文を組み立てている**

Wikipediaを読むのがてっとり早いです．

- [Wikipedia - SQLインジェクション](http://ja.wikipedia.org/wiki/SQL%E3%82%A4%E3%83%B3%E3%82%B8%E3%82%A7%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3)

```php
$id = $_POST['id'];
$sql = "SELECT * FROM users WHERE id = '$id'";
```

このようなコードは， **SQLインジェクション攻撃** の格好の標的になってしまうどころか，それを意図せずとも `'` を含めたクエリで検索を行おうとしたときにも正常な処理が行えなくなってしまいます．これを防ぐために，SQL文中で特別な意味を持つ`'`をエスケープし，`\'`に変換する必要があります．

[mysql_query] 関数を使っていた頃は，以下のような手段でSQL文を組み立てるのが主流でした．

1. [mysql_real_escape_string] 関数で文字列をあらかじめエスケープする
2. 変数展開して埋め込む

```php
$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には，**「プリペアドステートメント」**および**「プレースホルダ」**という仕組みが取り入れられています．詳細については本文中で紹介しているので，付録から戻って読んでみてください．

```php
$id = $_POST['id'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bindValue(1, $id, PDO::PARAM_STR);
```

### `$_POST['id']` などの外部入力からきた変数が定義されているかどうか確認していない<br>それらが文字列であるかどうかの確認をしていない<br>**それらを出力する際に `htmlspecialchars` 関数を利用していない**

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

- [Qiita - $_GET, $_POSTなどを受け取る際の処理](http://qiita.com/mpyw/items/2f9955db1c02eeef43ea)

別の場所でも指摘していますが，ただ思考停止して [htmlspecialchars] 関数を使えばいいというものでもありません．「<ins>出力する際に</ins>」というのがネックです．例えば以下のコードは間違いです．

```php:データベースに挿入する前にhtmlspecialchars関数を適用している間違った例
$stmt = $pdo->prepare('INSERT INTO users(name) VALUES(?)');
$stmt->bindValue(1, htmlspecialchars($name), PDO::PARAM_STR);
```

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

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

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

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

### HTMLの `<body></body>` の中にデータベース接続処理を書いている<br>`echo()` や `print()` をベタ書きしている<br>Content-Typeを `text/plain` に変更せずに `exit()` や `die()` で強制終了処理を記述している

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

```php
<!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はこんな感じになるはずです．

```html
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>Example</title>
    </head>
    <body>
        <ul>No such file or directory
```

もし，あなたがこんな壊れたHTMLをWebサイト利用者に見られて恥も何も感じないのであれば私は止めませんが，多くの人は気にすると思います．…というよりも，まずこのコードは非常に読みにくいので**「ロジックとテンプレートの分離」**は行いましょう．

- 頭のほうですべてのロジックを書き，そこを過ぎたら<ins>用意された変数を使ってHTMLを出力する以外のことは行ってはいけません</ins>． （理想を言えば，ファイルも分けるべき） 
- 広々と確保した `<?php ... ?>` 中で [echo] や [print] を連呼してHTMLを生成するのは，初心者が陥りやすい典型的な間違ったPHPの使い方です．PHPはこれでも**テンプレートエンジン**的な役割を持つ言語なので，上記で指摘したロジックとテンプレートの分離を行った後は，<ins>PHPの中にHTMLを埋め込むのではなく，**HTMLの中にPHPを埋め込む**感覚で書きましょう</ins>．

上記2点に関して，以下に修正した例を示します．

```php
<?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]短縮構文** および **[制御構造に関する別の構文](http://php.net/manual/ja/control-structures.alternative-syntax.php)** を用いてみました．この2つはHTMLを出力する際に適用するとコードが非常に読みやすくなります．これで，エラー時に出力されるHTMLは以下のようになります．

```html
No such file or directory
```

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

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

500は「500 Internal Server Error」の意味で，サーバ側が原因でエラーになってしまったことを表します．

## PDOとmysqliの比較

### [mysqliクラス (またはmysqli関数)](http://www.php.net/manual/ja/book.mysqli.php)

手続き型(関数)とオブジェクト指向型(クラス)を両方ともサポートしています．

#### <font color="silver">メリット</font>

- 動作が **非常に** 速い．
- (手続き型の場合は) mysql関数からの書き換えが容易．
- プリペアドステートメントで型を指定しながら複数のバインドが同時に **できる** ．
- **マルチクエリ** を使用できる．1回の関数コールで複数のクエリが実行できるため，普通に2回コールする場合に比べてパフォーマンスは向上する．
- エラーを例外としてスローさせることができる．2種類の設定方法があるが，これに関しては<ins>他の記述がオブジェクト指向型でも手続き型を採用すべき</ins>であると言える．明らかに手続き型の方がスッキリと書ける．

```php:オブジェクト指向型
$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
```

```php:手続き型
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
```

- 不適切なインデックスを含むクエリに対しても，例外をスローさせることができる．開発段階でデータベースに負荷をかけてしまうクエリを潰すことが出来る利点がある．これを有効化するためには，下記のようにする． `MYSQLI_REPORT_ALL` でまとめる手もある．

```php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT | MYSQLI_REPORT_INDEX);
```

```php
mysqli_report(MYSQLI_REPORT_ALL);
```

- [久保清隆の成長ノート - MySQLパフォーマンスチューニングのためのインデックスの基礎知識](http://d.hatena.ne.jp/kiyo560808/20101117/1289952549)

#### <font color="silver">デメリット</font>

- プリペアドステートメントで名前付きプレースホルダが **使えない** ．
- **「プリペアドステートメント ≠ 結果セット」** であり，SQL実行後にプリペアドステートメントから結果セットを取り出す必要がある．この点は次に紹介するPDOクラスと大きく異なる．
- その他の点でも多機能すぎる為，初心者が混乱しやすい．

### [PDOクラス](http://php.net/manual/ja/book.pdo.php)

オブジェクト指向型のみをサポートしています．

#### <font color="silver">メリット</font>

- 動作が速い．
- エラーを例外としてスローさせることが出来る．但し，一部の特例を除き，<ins>コンストラクタは常に例外をスローする</ins>．

```php:PDOクラスで例外をスローさせる
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
```

- **「プリペアドステートメント ＝ 結果セット」** であり，mysqliクラスに比べると初心者にも分かりやすい．
- プリペアドステートメントで名前付きプレースホルダが **使える** ．
- プリペアドステートメントのエミュレーションを行う場合は **同名の名前付きプレースホルダを複数回使える** ．但し，エミュレーションを設定していないと `SQLSTATE[HY093]: Invalid parameter number` が発生する．
- 他のデータベースへ乗り換える際，書き換えが容易．PDOクラスの使い方だけを覚えておけばドライバが存在するあらゆるデータベースに対応が出来るようになる．

#### <font color="silver">デメリット</font>

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

[PEAR::DB](http://pear.php.net/manual/ja/package.database.db.php) や [PEAR::MDB2](http://pear.php.net/manual/ja/package.database.mdb2.php) に関しては,PDOが安定してきたPHP5.4系以降で使う必要性は皆無でしょう．PHPで実装された抽象化レイヤは，C言語で実装されたPDOよりも明らかにパフォーマンスが悪くなります．

- [\[Z\]ZAPAブロ～グ2.0 - PDO，PEAR::DB，Mysql関数の速度比較](http://zapanet.info/blog/item/972)

[mysql_query]: http://php.net/manual/ja/function.mysql-query.php
[mysql_real_escape_string]: http://php.net/manual/ja/function.mysql-real-escape-string.php
[htmlspecialchars]: http://www.php.net/manual/ja/function.htmlspecialchars.php
[addslashes]: http://www.php.net/manual/ja/function.addslashes.php
[exit]: http://www.php.net/manual/ja/function.exit.php
[die]: http://www.php.net/manual/ja/function.die.php
[PDO::__construct]: http://php.net/manual/ja/pdo.construct.php
[PDO::setAttribute]: http://www.php.net/manual/ja/pdo.setattribute.php
[PDOStatement::execute]: http://www.php.net/manual/ja/pdostatement.execute.php
[PDOStatement::fetch]: http://www.php.net/manual/ja/pdostatement.fetch.php
[PDOStatement::fetchAll]: http://www.php.net/manual/ja/pdostatement.fetchall.php
[PDOStatement::bindValue]: http://www.php.net/manual/ja/pdostatement.bindvalue.php
[PDOStatement::bindParam]: http://www.php.net/manual/ja/pdostatement.bindparam.php
[PDO::query]: http://www.php.net/manual/ja/pdo.query.php
[PDOStatement::rowCount]: http://www.php.net/manual/ja/pdostatement.rowcount.php
[PDO::exec]: http://www.php.net/manual/ja/pdo.exec.php
[PDO::prepare]: http://www.php.net/manual/ja/pdo.prepare.php
[PDOStatement::bindParam]: http://www.php.net/manual/ja/pdostatement.bindparam.php
[count]: http://www.php.net/manual/ja/function.count.php
[PDOStatement::fetchColumn]: http://www.php.net/manual/ja/pdostatement.fetchcolumn.php
[PDOStatement::closeCursor]: http://www.php.net/manual/ja/pdostatement.closecursor.php
[echo]: http://www.php.net/manual/ja/function.echo.php
[print]: http://www.php.net/manual/ja/function.print.php