PHPとデータベース - PDOのソースを表示

== 概要 ==
データベースの種類により、使用する関数を区別していた。(例. MySQL : mysqli_connect関数、PostgreSQL : pg_connect関数)<br>
もし、異なるデータベースへ変更・移植する場合は、全て書き換えなくてはならない。<br>
<br>
PDO(PHP Data Objects)を使用することで、データベースの種類を問わず同一のクラスやメソッドを使用することができる。<br>
<br>
PDOは、PHPからデータベースにアクセスするための軽量で高性能なインターフェイスである。<br>
PDOインターフェイスを実装する各データベースドライバは、正規表現のようなデータベース固有の機能を提供する。<br>
<br>
ただし、PDOは、データベースのあらゆる関数を実行できるわけではない。<br>
例えば、データベースにアクセスする場合は、データベース固有のPDOドライバを使用する必要がある。<br>
<br>
PDOは、データアクセスの抽象化レイヤを提供する。<br>
つまり、使用しているデータベースの種類に関わらず、同じ関数を使用してクエリの発行やデータの取得が行うことができる。<br>
<br>
<u>PDOは、PHP 5.1以降にバンドルされており、PHP 5.0ではPECL拡張モジュールとして使用可能である。</u><br>
<u>PHP 5の新機能であるオブジェクト指向で設計されているため、それより前のPHPでは動作しない。</u><br>
<br>
ここでは、PDOを使用して、MySQLへ接続して操作する手順を記載する。<br>
<br><br>

== PDOのインストール ==
==== Linux ====
PDOおよびPDO_SQLITEドライバは、PHP 5.1.0以降では標準で有効となっている。<br>
必要に応じて、使用するデータベース用のPDOドライバを有効にすることができる。<br>
データベースごとのPDOドライバについての詳細は、[https://www.php.net/manual/ja/pdo.drivers.php こちらのWebサイト]を参照すること。<br>
<br>
<u>※注意</u><br>
<u>PDOを共有モジュールとしてビルドする場合(非推奨)、全てのPDOドライバはPDO自体の後にロードしなければならない。</u><br>
<br>
PDOを共有モジュールとしてインストールする場合、php.iniファイルを編集して、PHPの実行時にPDOが自動的に読み込むように設定する。<br>
データベースごとのドライバについても同様、ドライバはpdo.soの後に記述する。<br>
これは、ドライバを読み込む前にPDOの初期化を済ませておく必要があるからである。<br>
<br>
もし、PDOおよびデータベースドライバを静的にビルドした場合、この部分は読み飛ばして構わない。<br>
 extension=pdo.so
<br>
==== Windows ====
# PDOおよび主要なドライバは、共有モジュールとしてPHPに同梱されているため、php.iniファイルを編集するだけで使用できる。
#: extension=php_pdo.dll
#: <u>※注意</u>
#: <u>PHP 5.3以降では、上記の設定は不要である。これは、PDOのDLLは必須ではなくなったためである。</u>
#: <br>
# 次に、その他のデータベース固有のDLLを読み込む方法は、以下の2つがある。
#* 実行時に<code>dl</code>関数で読み込む。
#* php.iniファイルで、php_pdo.dllに続いて指定する。<br>例えば、以下のように追記する。<br>以下のDLLは、php.iniファイルにある<code>extension_dir</code>項目で指定したディレクトリに配置しなければならない。
#*: extension=php_pdo.dll
#*: extension=php_pdo_firebird.dll
#*: extension=php_pdo_informix.dll
#*: extension=php_pdo_mssql.dll
#*: extension=php_pdo_mysql.dll
#*: extension=php_pdo_oci.dll
#*: extension=php_pdo_oci8.dll
#*: extension=php_pdo_odbc.dll
#*: extension=php_pdo_pgsql.dll
#*: extension=php_pdo_sqlite.dll  
#: <br>
#: <u>※注意</u>
#: <u>php.iniファイルの変更後は、設定を有効にするためにPHPを再起動する必要がある。</u>
#: <code>sudo systemctl restart apache2</code>
<br><br>

== データベース処理のアンチパターン ==
以下にどれか1つでも当てはまるソースコードは見直す必要がある。<br>
太字にしてあるものは、脆弱性に直結する危険度の高いものである。<br>
* <code>mysql_query</code>等の非推奨関数を使用している。
*: <code>mysql_</code>で始まる関数は、PHP5.5で非推奨となっている。
*: <br>
* MySQLを使用している場合、<code>SET NAMES</code>あるいは<code>SET CHARACTER SET</code>等で文字コードを指定している。<br>または、データベースで使用する文字コードが未指定である。
*: <code>SET NAMES</code>等の使用は避けるべきである。
*: これは、データベース側の文字セットを変更するだけであり、PDOドライバの文字セットは無関係なため、
*: 入力側と出力側で文字セットが異なる場合は脆弱性が発生する原因になる。
** 文字コードの性質上、<code>SET NAMES sjis</code>や<code>SET CHARACTER SET sjis</code>と設定する場合、顕著に脆弱性が現れる。
** <code>SET NAMES utf8</code>や<code>SET CHARACTER SET utf8</code>は基本的には安全だが、例外もあるため注意すること。<br>MySQLにおいては、libmysqlclientのコンパイルオプションに<code>--with-charset=cp932</code>や<code>--with-charset=sjis</code>を指定している場合が該当する。<br>mysqlndを使用している場合は問題無い。
*: <br>
* '''<code>SELECT * FROM users WHERE id = '$id'</code>のように変数展開を使用してSQL文を組み立てている。'''
*: <br>
* <code>$_POST['id']</code>等の外部入力の変数が定義されているか確認していない。<br>外部入力の変数が文字列であるか確認していない。<br>'''また、外部入力の変数を出力する時、<code>htmlspecialchars</code>関数を使用していない。
*: 以下のようなPHPスクリプトを記述してはならない。
 <syntaxhighlight lang="php">
 // レコードを挿入する前にhtmlspecialchars関数を使用している間違った例
 $stmt = $pdo->prepare('INSERT INTO users(name) VALUES(?)');
 $stmt->bindValue(1, htmlspecialchars($name), PDO::PARAM_STR);
 </syntaxhighlight>
<br>
* SQLの<code>LIKE</code>演算子を使用しているにも関わらず、<code>%</code>、<code>_</code>、<code>\</code>にエスケープ文字を付加していない。
*: <br>
* HTMLの<code>body</code>タグの中に、データベースの接続処理を記述している。<br><code>echo</code>関数や<code>print</code>関数を、そのまま記述している。<br>Content-Typeが<code>text/plain</code>ではなく、<code>exit</code>関数や<code>die</code>関数を使用して強制終了の処理を記述している。
*: ロジックとテンプレートの分離を実施する。
*: これは、ファイルの先頭で全てのロジックを記述して、その後の処理でHTMLタグを出力する。(ファイルも分割すべきである）
*: ロジックとテンプレートの分離を実施した後、<u>HTMLタグの中にPHPスクリプトを記述する手法</u>を用いる。
*: 以下の例では、ロジックとテンプレートを分離して、データベースにアクセスしてレコードを取得している。
 <syntaxhighlight lang="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>
 </syntaxhighlight>
<br>
: もし、exit関数やdie関数を使用する場合は、その直前において、以下のように記述する。
: 500は<code>500 Internal Server Error</code>の意味で、サーバ側が原因でエラーになったことを表す。
 <syntaxhighlight lang="php">
 header('Content-Type: text/plain; charset=UTF-8', true, 500);
 </syntaxhighlight>
<br><br>

== データベースの接続 ==
PDOクラスのインスタンスを作成することにより、接続が確立される。<br>
コンストラクタには、データソース(DSN)、ユーザ名、パスワードを指定する。<br>
<br>
データベース接続時になんらかのエラーが発生した場合、<code>PDOException</code>クラスがスローされる。<br>
エラー処理を行う場合はこの例外をキャッチする。<br>
または、エラーを無視して、<code>set_exception_handler</code>関数で設定したグローバル例外ハンドラに処理を任せることもできる。<br>
<br>
<u>※注意</u><br>
<u>PDOコンストラクタからの例外をキャッチしない場合、zendエンジンはPHPスクリプトを終了して、バックトレースを表示する。</u><br>
<u>このバックトレースは、データベースへの接続の詳細(ユーザ名やパスワード等)を誰でも閲覧することができるため、</u><br>
<u>必ず、例外処理を記述すること。</u><br>
<br>
データベースへの接続に成功する場合、PDOクラスのインスタンスが返る。このインスタンスが存在する間、接続がアクティブであり続ける。<br>
データベースへの接続を閉じる時、インスタンスに<code>null</code>を代入して、インスタンスを破棄する。<br>
<code>null</code>を代入しない場合、PHPスクリプトの終了時に自動的に接続が閉じられる。<br>
<br>
<u>※注意</u><br>
<u>PDOのインスタンスへの参照(<code>PDOStatement</code>クラスのインスタンスからの参照や別のPDOのインスタンスからの参照等)が残っている場合、</u><br>
<u>それらも併せて削除する必要がある。(<code>PDOStatement</code>クラスのインスタンスに<code>null</code>を代入する等)</u><br>
<br>
以下に、データベースへの接続を行う時のPDOのインスタンスの生成手順を記載する。<br>
 <syntaxhighlight lang="php">
 $<PDOクラスのインスタンス名> = new PDO(<データソース名>, <データベースユーザ名>, <パスワード>, <ドライバオプション>);
 </syntaxhighlight>
<br>
* データソース名(Data Source Name)
*: データベースに接続するために必要な情報。
*: PHP Manualに各データベースに応じたDSNの記述方法が掲載されている。
*: 先頭にデータベースの種類を指定して、<code>:</code>(コロン)で区切る。
*: 各項目は、<code>項目名=値</code>として、<code>;</code>(セミコロン)で区切る。
*: <br>
*: 以下の例は、PHP 5.3.6以降のMySQLにおけるデータソース名の記述方法である。(ポート番号は省略可能)
*: PHP 5.3.5以前では、charsetは使用できない。
*: <code>mysql:dbname=<データベース名>;host=<ホスト名またはIPアドレス>:port=<ポート番号>;charset=<文字コード></code>
*: 例. <code>mysql:dbname=SampleDB;host=localhost:port=3306;charset=utf8mb4</code>
*: <br>
** dbname
**: データベース名を指定する。(基本的には必須であるが、後で<code>USE <データベース名>;</code>とクエリを実行する場合は省略できる)
**: <br>
** host
**: ホスト名またはIPアドレスを指定する。
**: (ローカル環境のみで実行する場合、省略しても問題ない場合がある)
**: Linuxでは、ホスト名を指定することが推奨される。
**: http://qiita.com/mpyw/items/b00b72c5c95aac573b71#comment-e9db50fff9bffa1dd6f8
**: Windowsでは、IPアドレスを指定することが推奨される。
**: http://www.ah-2.com/2012/01/28/win_localhost_slow.html
**: <br>
** charset
**: 文字コードを指定する。(<code>SET NAMES</code>は使用しないこと)
**: <u>UTF-8を指定する場合、<code>utf8</code>であることに注意すること。</u>
**: <u>MySQL5.5.3以降を使用する場合、4バイトからなる絵文字等も取り扱える<code>utf8mb4</code>を使用することを強く推奨する。</u>
**: <br>
* データベースユーザ名
*: データベースのユーザ名を指定する。
*: <br>
* パスワード
*: データベースユーザのパスワードを指定する。
*: <br>
* ドライバオプション
*: 接続時のオプションを連想配列で渡す。
*: キーは予め用意されている定数を使用する。
*: 値は予め用意されている定数以外に、論理値や文字列等の一般的な値でもよい。
<br>
以下に、よく使用されるドライバオプションとその値を示す。<br>
* PDO::ATTR_ERRMODE
*: クエリの実行でエラーが起こった場合、どのように処理をするかを指定する。
*: 初期値は、<code>PDO::ERRMODE_SILENT</code>である。
** PDO::ERRMODE_EXCEPTION
**: 例外をスローする。
** PDO::ERRMODE_WARNING
**: クエリの実行Lで発生したエラーをPHPのWarningとして報告する。
**: <code>PDOStatement::execute</code>メソッドの戻り値が<code>false</code>かどうかを毎回確認する必要がある。
** PDO::ERRMODE_SILENT
**: 何も報告しない。
**: <code>PDOStatement::execute</code>メソッドの戻り値が<code>false</code>かどうかを毎回確認する必要がある。
*: <br>
* PDO::ATTR_DEFAULT_FETCH_MODE
*: <code>PDOStatement::fetch</code>メソッドや<code>PDOStatement::fetchAll</code>メソッドで引数が省略された場合、または、
*: ステートメントが<code>foreach</code>文に直接かけられた場合のフェッチスタイルを設定する。
*: 初期値は、<code>PDO::FETCH_BOTH</code>である。
** PDO::FETCH_BOTH
**: カラム番号とカラム名の両方をキーとする連想配列で取得する。
** PDO::FETCH_NUM
**: カラム番号をキーとする配列で取得する。
** PDO::FETCH_ASSOC
**: カラム名をキーとする連想配列で取得する。この設定が最も使用される。
** PDO::FETCH_OBJ
**: カラム名をプロパティとする基本オブジェクトで取得する。
*: <br>
* PDO::ATTR_EMULATE_PREPARES
*: データベース側が持つプリペアドステートメント機能のエミュレーションをPDO側で行うかどうかを設定する。
*: PHP 5.2以降の初期値は<code>true</code>である。
*: この設定は、いくつかPDOの挙動に違いが現れる。
** プリペアドステートメントのためにデータベースと通信する必要が無くなるため、エミュレーションを行う方がパフォーマンスは向上する。
** 存在しないテーブル名やカラム名をクエリに持つプリペアドステートメントを発行する場合、<br>エミュレーションを行わない場合はすぐにエラーが発生するが、エミュレーションを行う場合はクエリを実行するまでエラーが発生するかどうかわからない。
** エミュレーションを行う場合のみ、<code>;</code>(セミコロン)区切りで複数のクエリを1つのクエリで実行することができる。
*: <br>
* PDO::ATTR_PERSISTENT(コンストラクタでの指定のみ)
*: <code>true</code>の場合、PHPスクリプトが終了してもデータベースへの接続を維持して、次回に再利用する。
*: 特に、大規模システムでは恩恵が大きい。
*: <br>
* PDO::MYSQL_ATTR_USE_BUFFERED_QUERY(MySQL専用)
*: <code>true</code>の場合、バッファクエリを使用する。初期値はMySQLの各バージョンによって異なる。
** バッファクエリ
**: 全ての情報をデータベースから取得して、PHPスクリプトにより1件ずつ取得する。
** 非バッファクエリ:
**: 1件ごとにデータベースサーバと通信を行って，PHPに取り出させる
*: 取得してくる情報がメモリに収まりきらない莫大なデータ量といった特殊なケースを除けば、バッファクエリを選択した方がよい。
*: バッファクエリを設定すると、サーバ負荷も軽減されて、途中までフェッチしたところで突然例外が発生するような事態も避けられる。
*: 非バッファクエリは、データベースから取得したデータを、コマンドラインからバッチ処理を実行する用途で使用されるが、
*: 複数同時にクエリを実行できない等の大きな欠点もある。
*: <br>
* PDO::MYSQL_ATTR_INIT_COMMAND(MySQL専用、コンストラクタでの指定のみ)
*: データベースに接続した直後に実行されるクエリをここに記述する。
<br>
 <syntaxhighlight lang="php">
 <?php
    try
    {
       // PHP 5.3.6以降
       $dbh = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', $user, $pass, 
                      [
                         PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                      ]);
 
       // PHP 5.3.5以前
       //$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, 
       //               [
       //                  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
       //                  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
       //               ]);
       //$dbh->exec("SET NAMES utf8mb4");
 
       foreach($dbh->query('SELECT * from FOO') as $row)
       {
          print_r($row);
       }
       $dbh = null;
    }
    catch(PDOException $e)
    {
       // エラーが発生した場合、"500 Internal Server Error"を表示して終了する
       // もし、エラー画面を表示する場合、HTMLの表示を継続する
       // ここではエラー内容を表示しているが、実際の商用環境ではログファイルに記録してWebブラウザには表示しない方が望ましい
       header('Content-Type: text/plain; charset=UTF-8', true, 500);
       print "エラー!: " . $e->getMessage() . "<br/>";
       die();
    }
 ?>
 
 // Webブラウザにheader関数以降に表示するHTMLがUTF-8で記述されていることを認識させる
 // または、header関数の代わりに<meta charset="utf-8">を記述する
 // 両方記述してもよい
 header('Content-Type: text/html; charset=utf-8');
 
 <!DOCTYPE html>
 <html>
    <head>
       <meta charset="utf-8">
       <title>Example</title>
    </head>
    <body>
       <!-- ここではHTMLタグ以外を記述しない -->
    </body>
 </html>
 </syntaxhighlight>
<br>
<u>PDOクラスのコンストラクタは、<code>PDO::ERRMODE_EXCEPTION</code>の有無に関わらず<code>PDOException</code>をスローするが、</u><br>
<u>データベースに接続できない場合等は、Warningが発生する。</u><br>
<u>もし、データベースを動的に指定する場合、<code>set_error_handler</code>関数を使用して<code>ErrorException</code>に変換した後、例外処理を行う。</u><br>
<br><br>

== データベースの持続的な接続 ==
<code>PDO::ATTR_PERSISTENT</code>オプションの値は、string型の値が設定されない限り、bool型(持続的な接続の有無を示す)に変換される。<br>
string型の値を設定する場合、複数の接続プールを使用することができる。<br>
<br>
これは、互換性の無い異なる接続を使用する場合に便利である。<br>
例えば、異なる<code>PDO::MYSQL_ATTR_USE_BUFFERED_QUERY</code>の値を設定する場合が挙げられる。<br>
<br>
<u>※注意1</u><br>
<u>持続的な接続をする場合、ドライバのオプションに<code>PDO::ATTR_PERSISTENT</code>を設定して、PDOクラスのコンストラクタに渡す必要がある。</u><br>
<u>この属性を、インスタンスの生成後に<code>PDO::setAttribute</code>メソッドを使用して設定する場合、ドライバは持続的な接続を使用しない。</u><br>
<br>
<u>※注意2</u><br>
<u>PDO ODBCドライバを使用しており、ODBCライブラリがODBC接続プーリングをサポートしている場合は、</u><br>
<u>PDOの持続的な接続を使用せずに、ODBCの接続プーリングに接続キャッシュ処理を任せることを推奨する。</u><br>
<br>
<u>ODBCの接続プールは、プロセス内で他のモジュールと共有されている。</u><br>
<u>PDOが接続をキャッシュすると、その接続はODBCの接続プールに返されなくなり、他のモジュールによって新たな接続が作成されてしまう。</u><br>
 <syntaxhighlight lang="php">
 <?php
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(PDO::ATTR_PERSISTENT => true));
 ?>
 </syntaxhighlight>
<br><br>

== データベースの切断 ==
データベースの接続は、PHPスクリプトが終了しても閉じられずにキャッシュされ、他のPHPスクリプトが同じ内容の接続を要求する時に再利用される。<br>
このキャッシュにより、新しい接続を確立するオーバーヘッドを避けることができるため、Webアプリケーションを高速化できるようになる。<br>
 <syntaxhighlight lang="php">
 <?php
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass,
                   [
                      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                   ]);
    
    foreach($dbh->query('SELECT * from FOO') as $row)
    {
       print_r($row);
    }    
 
    // データベースの切断
    $sth = null;
    $dbh = null;
 ?>
 </syntaxhighlight>
<br><br>

== クエリの実行 ==
==== PDO::queryメソッド ====
ユーザ入力を伴わないクエリは、PDO::queryメソッドを実行する。<br>
戻り値は、PDOStatementクラスである。<br>
 <syntaxhighlight lang="php">
 $stmt = $pdo->query('SELECT * FROM users');
 </syntaxhighlight>
<br>
==== PDO::execメソッド ====
ユーザ入力を伴わないクエリにおいて、INSERTやUPDATE等を使用してレコードを直接取得する場合、PDO::execメソッドを使用する。<br>
また、結果を必要としない場合においても、PDO::execメソッドを使用すべきである。<br>
<br>
以下の例では、テーブルに存在する全てのレコードを更新して、更新した全てのレコード数を取得している。<br>
 <syntaxhighlight lang="php">
 $count = $pdo->exec('UPDATE users SET age = age + 1');
 </syntaxhighlight>
<br>
==== PDO::executeメソッド(3ステップ) ====
<code>PDO::prepare</code>メソッド → <code>PDOStatement::bindValue</code>メソッド → <code>PDOStatement::execute</code>メソッドの3ステップでクエリを実行する。<br>
ユーザ入力からクエリを動的に作成する場合、プリペアドステートメントとプレースホルダを使用する。<br>
* プレースホルダ
*: ユーザ入力を代入する場所として、予め確保するためのもの。
*: プレースホルダには2種類あり、<u>疑問符プレースホルダを使用する方法</u>と<u>名前付きプレースホルダ使用する方法</u>がある。
*: これらを混ぜて使用する場合はエラーとなる。
* プリペアドステートメント
*: プレースホルダを使用するために作成する。
<br>
* 疑問符プレースホルダ<br>
*: ?の要素は、1から始まる。<br>
*: <code>PDO::PARAM_STR</code>は省略することができる。<br>
*: エミュレーションが有効の場合、正しくキャストできない場合があるため、文字列以外を扱う時は明示的なキャストが必要である。<br>
*: NULL値に関しては、<code>PDO::PARAM_NULL</code>が暗黙的に使用される。<br>
 <syntaxhighlight lang="php">
 // エミュレーションが有効の場合
 $stmt = $pdo->prepare('SELECT * FROM users WHERE gender = ? AND age = ?');
 $stmt->bindValue(1, $gender);
 $stmt->bindValue(2, (int)$age, PDO::PARAM_INT);
 $stmt->execute();
 
 // エミュレーションが無効の場合、上記または下記のいずれの記述も可能
 <syntaxhighlight lang="php">
 $stmt = $pdo->prepare('SELECT * FROM users WHERE gender = ? AND age = ?');
 $stmt->bindValue(1, $gender);
 $stmt->bindValue(2, $age, PDO::PARAM_INT);
 $stmt->execute();
 </syntaxhighlight>
<br>
* 名前付きプレースホルダ
*: <code>:</code>(コロン)を先頭に付加して、半角英数字とアンダースコアにて構成する。
*: バインド時の先頭は、<code>:</code>(コロン)は省略できる。
*: エミュレーションが有効の場合、明示的なキャストが必要である。
 <syntaxhighlight lang="php">
 // エミュレーションが有効の場合
 $stmt = $pdo->prepare('SELECT * FROM users WHERE age = :age AND gender = :gender');
 $stmt->bindValue(':age', (int)$age, PDO::PARAM_INT);
 $stmt->bindValue(':gender', $gender);
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="php">
 // バインド時において、先頭の:(コロン)を省略する場合
 $stmt->bindValue('gender', $gender);
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="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();
 </syntaxhighlight>
<br>
なお、 値を即時にバインドせずに、変数を参照的にバインドしておき、実行時に値をバインドする<code>PDOStatement::bindParam</code>メソッドも存在するが、<br>
<code>PDOStatement::bindParam</code>メソッドを使用する必要はない。<br>
エミュレーションが有効の場合、実行後にバインドした変数が文字列型に変換する仕様もあるので、注意すること。<br>
<br>
==== PDO::executeメソッド(2ステップ) ====
<code>PDO::prepare</code>メソッド → <code>PDOStatement::execute</code>メソッドの2ステップでクエリを実行する。<br>
<code>PDOStatement::execute</code>メソッドの引数に配列を渡す場合、それらを全てバインドした後そのままクエリが実行される。<br>
<br>
ただし、以下の条件に注意すること。<br>
<u>NULL値以外は全て<code>PDO::PARAM_STR</code>扱いになる。</u><br>
もし、間違った型でバインドする場合はMySQL / SQLiteはデータベース側で自動的にキャストし直すが、<br>
パフォーマンスの低下やバグの原因になるため、可能な限り避けること。(PostgreSQLの場合はエラーになる)<br>
<br>
また、既に<code>PDOStatement::bindValue</code>メソッドで値がバインドされている場合でも、それらは全て無視される。<br>
これを用いる場合、全てのバインドをこの引数で行わなければならない。<br>
<br>
* 疑問符プレースホルダ
*: <code>PDOStatement::bindValue</code>メソッドとは異なり、<code>?</code>のインデックスは0から始まる。
 <syntaxhighlight lang="php">
 $stmt = $pdo->prepare('SELECT * FROM users WHERE city = ? AND gender = ?');
 
 $stmt->execute([$city, $gender]);
 
 // キーを設定して順番を変えて指定することもできる
 $stmt->execute([1 => $gender, 0 => $city]);
 </syntaxhighlight>
<br>
* 名前付きプレースホルダ
 <syntaxhighlight lang="php">
 $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'));
 </syntaxhighlight>
<br><br>

== トランザクションおよび自動コミット ==
トランザクション内で実行された作業は、データベースに安全に反映されることが保証されている。<br>
トランザクションのコミット時は、他の接続の干渉を受けることはない。<br>
また、未コミット時では、トランザクション内での作業はいつでも取り消すことができる。<br>
<br>
PDOクラスを使用してデータベースへ接続する場合、自動コミットモードで動作する。<br>
自動コミットモードとは、データベースがトランザクションをサポートしている場合、クエリが暗黙的にトランザクションのもとで実行される。<br>
データベースがトランザクションをサポートしていない場合、トランザクションを使用せずに実行される。<br>
<br>
トランザクションを使用する場合は、<code>PDO::beginTransaction</code>メソッドを使用して、トランザクションを初期化する必要がある。<br>
使用しているドライバがトランザクションをサポートしていない場合、必ず、<code>PDOException</code>クラスがスローされる。<br>
<br>
トランザクションを終了する場合は、<code>PDO::commit</code>メソッドあるいは<code>PDO::rollBack</code>メソッドを使用する。<br>
<br>
以下の例では、データベースのテーブルに対して、新しい2つのレコードを追加している。<br>
トランザクションがアクティブな間は、作業中のデータについては、他から一切変更が加えられないことが保証されている。<br>
もし何か問題が発生すれば、catchブロック内でトランザクション開始以降の全ての変更がロールバックされる。<br>
 <syntaxhighlight lang="php">
 <?php
    try
    {
       $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', array(PDO::ATTR_PERSISTENT => true));
       echo "接続しました\n";
    }
    catch (Exception $e)
    {
       die("接続できません: " . $e->getMessage());
    }
 
    try
    {  
       $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
       $dbh->beginTransaction();
       $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
       $dbh->exec("insert into salarychange (id, amount, changedate) values (23, 50000, NOW())");
       $dbh->commit();
    }
    catch(Exception $e)
    {
       $dbh->rollBack();
       echo "失敗しました。" . $e->getMessage();
    }
 ?>
 </syntaxhighlight>
<br>
データベースの接続を閉じる場合またはPHPスクリプトが終了する場合、<br>
コミットが完了していないトランザクションがあるならば、自動的にロールバックされる。<br>
これは、明示的にコミットしていない場合、予期せぬ状態で終了したと推測されるため、データの不整合が発生するのを避けるための機能である。<br>
<br>
<u>※注意</u><br>
<u>自動的にロールバックが行われる時は、<code>PDO::beginTransaction</code>メソッドを実行した場合のみである。</u><br>
<u>トランザクションを開始するクエリを手動で発行した場合、PDOはそれを知ることができないため、問題が発生してもロールバックできない。</u><br>
<br><br>

== プリペアドステートメント ==
プリペアドステートメントとは、実行するSQLをコンパイルしたテンプレートのようなものである。<br>
パラメータ変数を使用することで、SQLをカスタマイズすることができる。<br>
<br>
プリペアドステートメントには、以下に示す2つの大きな利点がある。<br>
* 一般的に、クエリを実行する時、クエリの解析、コンパイル、実行プランの最適化が行われる。<br>プリペアドステートメントを使用する場合、これらの解析、コンパイル、最適化の繰り返しを避けることができる。<br>端的に言うと、プリペアドステートメントは使用するリソースが少ないため、高速に動作するということである。<br><br>
* プリペアドステートメントに渡すパラメータは、引用符で括る必要は無く、ドライバが自動的に行う。<br>プリペアドステートメントでは、SQLインジェクションは発生しない。
<br>
プリペアドステートメントは、データベースの種類や機能に関わらず同じ仕組みで データベースへのアクセスができる。<br>
<br><br>

== レコードの取得(プリペアドステートメント) ==
==== PDOStatement::fetchメソッド ====
カーソルを移動して、指定したフェッチモードで1行ずつ取得する。<br>
* 引数を省略する場合、デフォルトフェッチモードが使用される。
* 全てのレコードを取得した場合、falseを返す。
<br>
以下の例では、フェッチモードを<code>PDO::FETCH_ASSOC</code>に設定している。<br>
 <syntaxhighlight lang="php">
 // 基本的な構文
 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);
 }
 </syntaxhighlight>
<br>
デフォルトフェッチモードの場合、<code>PDOStatement</code>クラスは<code>Traversable</code>インターフェースを実装しているため、foreach文で記述することができる。<br>
ただし、HTMLのための変数を用意する場合、配列として持つ方が都合が良いため、<code>PDOStatement::fetchAll</code>メソッドの使用することを推奨する。<br>
 <syntaxhighlight lang="php">
 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']);
 }
 </syntaxhighlight>
<br>
==== PDOStatement::fetchObjectメソッド ====
連想配列の代わりにクラスオブジェクトを取得する。<br>
これは、<code>PDO::FETCH_OBJ</code>を指定して<code>PDOStatement::fetch</code>メソッドを使用する場合と同じであるが、こちらの方が簡潔に記述できる。<br>
 <syntaxhighlight lang="php">
 while($row = $stmt->fetchObject())
 {
    printf("%s lives in %s<br />\n", $row->name, $row->city);
 }
 </syntaxhighlight>
<br>
==== PDOStatement::fetchColumnメソッド ====
特定の1カラムのみを文字列として取得する。<br>
これは、<code>PDO::FETCH_COLUMN</code>を指定して<code>PDOStatement::fetch</code>メソッドを使用する場合と同じでるが、こちらの方が簡潔に記述できる。<br>
<br>
先頭から数えてそのカラムが何番目(0オリジン)にあるかを第1引数として渡す。(省略する場合、0を指定したとみなされる)<br>
カラムの値に0が含まれる可能性がある場合は、<code>false !==</code>の判定をしなければならない。<br>
 <syntaxhighlight lang="php">
 while(false !== $value = $stmt->fetchColumn())
 {
    echo "{$value}<br />\n";
 }
 </syntaxhighlight>
<br>
==== PDOStatement::fetchAllメソッド ====
全てのレコードを取得して2次元配列とする。<br>
* 引数を省略する場合、デフォルトフェッチモードが使用される。
* 特定のカラムのみ全てのレコードを取得して1次元配列とする場合、<br>第1引数に<code>PDO::FETCH_COLUMN</code>を指定して、第2引数に先頭から数えてそのカラムが何番目(0オリジン)にあるかを渡す。(省略する場合、0を指定したとみなされる)
 <syntaxhighlight lang="php">
 $rows = $stmt->fetchAll();
 var_dump($rows);
 
 $values = $stmt->fetchAll(PDO::FETCH_COLUMN);
 var_dump($values);
 </syntaxhighlight>
<br>
以下の例では、フォームで入力したキーの値に応じたデータを取得している。<br>
ユーザの入力内容は自動的に引用符で括られるため、SQLインジェクション攻撃の恐れは無い。<br>
 <syntaxhighlight lang="php">
 <?php
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
    if($stmt->execute(array($_GET['name'])))
    {
       while($row = $stmt->fetch())
       {
          print_r($row);
       }
    }
 ?>
 </syntaxhighlight>
<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|-
! style="background-color:#00ffff;" | MySQL 
! style="background-color:#00ffff;" | PHPのデータ型<br>(エミュレーション無しmysqlnd) 
! style="background-color:#00ffff;" | PHPのデータ型(libmysqlclient)<br>(エミュレーション有りmysqlnd)
|-
| NULL || NULL || NULL 
|-
| 文字列 || String || String
|-
| 日付 || String || String
|-
| タイムスタンプ || Integer || String 
|-
| 論理値 || Integer || String 
|-
| PHPで扱える値の整数 || Integer || String 
|-
| PHPで扱えない値の整数 || String || String 
|-
| 浮動小数点 || String || String 
|}
</center>
<u>※注意</u><br>
<u>エミュレーションに関するオプションは、PDO::ATTR_EMULATE_PREPARESという命名ではあるが、</u><br>
<u>プリペアドステートメントを使用しない場合にも影響が及ぶことに注意すること。</u><br>
<br>
下表に、<code>PDO::setAttribute</code>で取得するデータ型を変更できるものを示す。<br>
<code>PDO::ATTR_ORACLE_NULLS</code>オプションは、Oracle以外のデータベースでも使用できる。<br>
<center>
{| class="wikitable" style="background-color:#fefefe;"
|-
! style="background-color:#00ffff;" | データベース 
! style="background-color:#00ffff;" | PDO::NULL_EMPTY_STRING 
! style="background-color:#00ffff;" | PDO::NULL_TO_STRING
|-
| NULL || NULL || ""
|-
| "" || NULL || ""
|}
</center>
<br>
また、PDO::ATTR_STRINGIFY_FETCHESオプションをtrueに指定する時、エミュレーションが無効の場合は数値が文字列に変換される。<br>
エミュレーションが有効の場合、設定に関わらず常に数値が文字列に変換される。<br>
<br><br>

== レコードの追加(プリペアドステートメント) ==
以下の例では、nameおよびvalueを<u>名前付きプレースホルダ</u>で置き換えて、INSERT文を実行している。<br>
 <syntaxhighlight lang="php">
 <?php
    $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':value', $value);
 
    // 1レコード目の挿入
    $name = 'one';
    $value = 1;
    $stmt->execute();
 
    // パラメータを変更して、2レコード目の挿入
    $name = 'two';
    $value = 2;
    $stmt->execute();
 ?>
 </syntaxhighlight>
<br>
以下の例では、nameおよびvalueを<u>プレースホルダ<code>?</code></u>で置き換えて、INSERT文を実行している。<br>
 <syntaxhighlight lang="php">
 <?php
    $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
    $stmt->bindParam(1, $name);
    $stmt->bindParam(2, $value);
 
    // 1レコード目の挿入
    $name = 'one';
    $value = 1;
    $stmt->execute();
 
    // パラメータを変更して、2レコード目の挿入
    $name = 'two';
    $value = 2;
    $stmt->execute();
 ?>
 </syntaxhighlight>
<br><br>

== ストアドプロシージャ ==
==== ストアドプロシージャの呼び出し : 出力パラメータの指定 ====
データベースドライバがサポートしている時、入力パラメータだけでなく、出力パラメータもバインドすることが可能である。<br>
<br>
出力パラメータは、ストアドプロシージャから値を取得するために使用する。<br>
この場合、返される値の大きさがどの程度になるのかをバインド時に知る必要がある。<br>
指定した大きさよりも大きな値が返される場合は、エラーが発生する。<br>
 <syntaxhighlight lang="php">
 <?php
    $stmt = $dbh->prepare("CALL sp_returns_string(?)");
    $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 
 
    // ストアドプロシージャを呼び出す
    $stmt->execute();
 
    print "プロシージャが返した値は $return_value です\n";
 ?>
 </syntaxhighlight>
<br>
==== ストアドプロシージャの呼び出し : 入出力パラメータの指定 ====
入出力の両方に使用するパラメータを指定することもできる。<br>
入出力パラメータの書式は、出力パラメータと同じである。<br>
<br>
以下の例では、ストアドプロシージャに文字列'hello'を渡している。<br>
プロシージャの結果が返ってくると、文字列'hello'はプロシージャの返す値に置き換えられる。<br>
 <syntaxhighlight lang="php">
 <?php
    $stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
    $value = 'hello';
    $stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 
 
    // ストアドプロシージャの呼び出し
    $stmt->execute();
 
    print "プロシージャが返した値は $value です\n";
 ?>
 </syntaxhighlight>
<br><br>

== プレースホルダの間違った使用例 ==
 <syntaxhighlight lang="php">
 <?php
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
    $stmt->execute(array($_GET['name']));
 
    // プレースホルダは、値全体に対して使用しなければなりません
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
    $stmt->execute(array("%$_GET[name]%"));
 ?>
 </syntaxhighlight>
<br><br>

__FORCETOC__
[[カテゴリ:Web]]