PHPとデータベース - PDO

提供：MochiuWiki : SUSE, EC, PCB
ナビゲーションに移動 検索に移動

概要

データベースの種類により、使用する関数を区別していた。(例. MySQL : mysqli_connect関数、PostgreSQL : pg_connect関数)
もし、異なるデータベースへ変更・移植する場合は、全て書き換えなくてはならない。

PDO(PHP Data Objects)を使用することで、データベースの種類を問わず同一のクラスやメソッドを使用することができる。

PDOは、PHPからデータベースにアクセスするための軽量で高性能なインターフェイスである。
PDOインターフェイスを実装する各データベースドライバは、正規表現のようなデータベース固有の機能を提供する。

ただし、PDOは、データベースのあらゆる関数を実行できるわけではない。
例えば、データベースにアクセスする場合は、データベース固有のPDOドライバを使用する必要がある。

PDOは、データアクセスの抽象化レイヤを提供する。
つまり、使用しているデータベースの種類に関わらず、同じ関数を使用してクエリの発行やデータの取得が行うことができる。

PDOは、PHP 5.1以降にバンドルされており、PHP 5.0ではPECL拡張モジュールとして使用可能である。
PHP 5の新機能であるオブジェクト指向で設計されているため、それより前のPHPでは動作しない。

ここでは、PDOを使用して、MySQLへ接続して操作する手順を記載する。


PDOのインストール

Linux

PDOおよびPDO_SQLITEドライバは、PHP 5.1.0以降では標準で有効となっている。
必要に応じて、使用するデータベース用のPDOドライバを有効にすることができる。
データベースごとのPDOドライバについての詳細は、こちらのWebサイトを参照すること。

※注意
PDOを共有モジュールとしてビルドする場合(非推奨)、全てのPDOドライバはPDO自体の後にロードしなければならない。

PDOを共有モジュールとしてインストールする場合、php.iniファイルを編集して、PHPの実行時にPDOが自動的に読み込むように設定する。
データベースごとのドライバについても同様、ドライバはpdo.soの後に記述する。
これは、ドライバを読み込む前にPDOの初期化を済ませておく必要があるからである。

もし、PDOおよびデータベースドライバを静的にビルドした場合、この部分は読み飛ばして構わない。

extension=pdo.so


Windows

  1. PDOおよび主要なドライバは、共有モジュールとしてPHPに同梱されているため、php.iniファイルを編集するだけで使用できる。
    extension=php_pdo.dll
    ※注意
    PHP 5.3以降では、上記の設定は不要である。これは、PDOのDLLは必須ではなくなったためである。

  2. 次に、その他のデータベース固有のDLLを読み込む方法は、以下の2つがある。
    • 実行時にdl関数で読み込む。
    • php.iniファイルで、php_pdo.dllに続いて指定する。
      例えば、以下のように追記する。
      以下のDLLは、php.iniファイルにあるextension_dir項目で指定したディレクトリに配置しなければならない。
      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

    ※注意
    php.iniファイルの変更後は、設定を有効にするためにPHPを再起動する必要がある。
    sudo systemctl restart apache2



データベース処理のアンチパターン

以下にどれか1つでも当てはまるソースコードは見直す必要がある。
太字にしてあるものは、脆弱性に直結する危険度の高いものである。

  • mysql_query等の非推奨関数を使用している。
    mysql_で始まる関数は、PHP5.5で非推奨となっている。

  • MySQLを使用している場合、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を使用している場合は問題無い。

  • SELECT * FROM users WHERE id = '$id'のように変数展開を使用してSQL文を組み立てている。

  • $_POST['id']等の外部入力の変数が定義されているか確認していない。
    外部入力の変数が文字列であるか確認していない。
    また、外部入力の変数を出力する時、htmlspecialchars関数を使用していない。
    以下のようなPHPスクリプトを記述してはならない。
 // レコードを挿入する前にhtmlspecialchars関数を使用している間違った例
 $stmt = $pdo->prepare('INSERT INTO users(name) VALUES(?)');
 $stmt->bindValue(1, htmlspecialchars($name), PDO::PARAM_STR);


  • SQLのLIKE演算子を使用しているにも関わらず、%、_、\にエスケープ文字を付加していない。

  • HTMLのbodyタグの中に、データベースの接続処理を記述している。
    echo関数やprint関数を、そのまま記述している。
    Content-Typeがtext/plainではなく、exit関数やdie関数を使用して強制終了の処理を記述している。
    ロジックとテンプレートの分離を実施する。
    これは、ファイルの先頭で全てのロジックを記述して、その後の処理でHTMLタグを出力する。(ファイルも分割すべきである）
    ロジックとテンプレートの分離を実施した後、HTMLタグの中に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>


もし、exit関数やdie関数を使用する場合は、その直前において、以下のように記述する。
500は500 Internal Server Errorの意味で、サーバ側が原因でエラーになったことを表す。
 header('Content-Type: text/plain; charset=UTF-8', true, 500);



データベースの接続

接続文字列

PDOクラスのインスタンスを作成することにより、接続が確立される。
コンストラクタには、データソース(DSN)、ユーザ名、パスワードを指定する。

データベース接続時になんらかのエラーが発生した場合、PDOExceptionクラスがスローされる。
エラー処理を行う場合はこの例外をキャッチする。
または、エラーを無視して、set_exception_handler関数で設定したグローバル例外ハンドラに処理を任せることもできる。

※注意
PDOコンストラクタからの例外をキャッチしない場合、zendエンジンはPHPスクリプトを終了して、バックトレースを表示する。
このバックトレースは、データベースへの接続の詳細(ユーザ名やパスワード等)を誰でも閲覧することができるため、
必ず、例外処理を記述すること。

データベースへの接続に成功する場合、PDOクラスのインスタンスが返る。このインスタンスが存在する間、接続がアクティブであり続ける。
データベースへの接続を閉じる時、インスタンスにnullを代入して、インスタンスを破棄する。
nullを代入しない場合、PHPスクリプトの終了時に自動的に接続が閉じられる。

※注意
PDOのインスタンスへの参照(PDOStatementクラスのインスタンスからの参照や別のPDOのインスタンスからの参照等)が残っている場合、
それらも併せて削除する必要がある。(PDOStatementクラスのインスタンスにnullを代入する等)

以下に、データベースへの接続を行う時のPDOのインスタンスの生成手順を記載する。

 $<PDOクラスのインスタンス名> = new PDO(<データソース名>, <データベースユーザ名>, <パスワード>, <ドライバオプション>);
 
 PDO PDO::__construct ( string dsn 
                        [, string username 
                        [, string password 
                        [, array driver_options]]] )
 
 指定されたデータベースへの接続を表すPDOインスタンスを生成します。
 
 引数：
   dsn
     データソース名(Data Source Name)またはDSN。データベースに接続するために必要な情報が含まれる。
 
   username
     DSN文字列のユーザ名。このパラメータは、いくつかのPDOドライバではオプションである。
 
   password
     パスワード。
     DSN文字列で必要とされる場合に指定。
 
   driver_options
     ドライバ固有の接続オプションを指定するキー=> 値の配列
 
 戻り値:
   成功時にPDOオブジェクトを返す。


  • データソース名(Data Source Name)
    データベースに接続するために必要な情報。
    PHP Manualに各データベースに応じたDSNの記述方法が掲載されている。
    先頭にデータベースの種類を指定して、:(コロン)で区切る。
    各項目は、項目名=値として、;(セミコロン)で区切る。

    以下の例は、PHP 5.3.6以降のMySQLにおけるデータソース名の記述方法である。(ポート番号は省略可能)
    PHP 5.3.5以前では、charsetは使用できない。
    mysql:dbname=<データベース名>;host=<ホスト名またはIPアドレス>:port=<ポート番号>;charset=<文字コード>
    例. mysql:dbname=SampleDB;host=localhost:port=3306;charset=utf8mb4

    • dbname
      データベース名を指定する。(基本的には必須であるが、後でUSE <データベース名>;とクエリを実行する場合は省略できる)

    • 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

    • charset
      文字コードを指定する。(SET NAMESは使用しないこと)
      UTF-8を指定する場合、utf8であることに注意すること。
      MySQL5.5.3以降を使用する場合、4バイトからなる絵文字等も取り扱えるutf8mb4を使用することを強く推奨する。

  • データベースユーザ名
    データベースのユーザ名を指定する。

  • パスワード
    データベースユーザのパスワードを指定する。

  • ドライバオプション
    接続時のオプションを連想配列で渡す。
    キーは予め用意されている定数を使用する。
    値は予め用意されている定数以外に、論理値や文字列等の一般的な値でもよい。


ドライバオプション

ドライバオプションとその値を以下に示す。

 // ドライバオプションの使用例
 
 $options = [
    // エラー発生時に例外をスロー
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
 
    // フェッチモードをデフォルトで連想配列に設定
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
 
    // プリペアドステートメントを有効化
    PDO::ATTR_EMULATE_PREPARES => false,
 
    // 持続的な接続を無効化
    PDO::ATTR_PERSISTENT => false,
 
    // カラム名を小文字に変換
    PDO::ATTR_CASE => PDO::CASE_LOWER
 ];
 
 $db = new PDO(
    'mysql:host=localhost;dbname=sampledb;charset=utf8mb4',
    'root',
    'root',
    $options
 );


  • PDO::ATTR_ERRMODE
    エラーレポートの方法を設定するための属性である。
    クエリの実行でエラーが起きた場合、どのように処理をするかを指定する。
    初期値は、PDO::ERRMODE_SILENTである。
    • PDO::ERRMODE_EXCEPTION
      エラーが発生した時に例外 (PDOException例外クラス) をスローする。
      エラーを例外としてキャッチできるようになり、デバッグが容易になる。
    • PDO::ERRMODE_WARNING
      クエリの実行Lで発生したエラーをPHPのWarningとして報告する。
      PDOStatement::executeメソッドの戻り値がfalseかどうかを毎回確認する必要がある。
    • PDO::ERRMODE_SILENT
      何も報告しない。
      PDOStatement::executeメソッドの戻り値がfalseかどうかを毎回確認する必要がある。

  • PDO::ATTR_DEFAULT_FETCH_MODE
    SELECT結果を連想配列で取得できる。
    キーがカラム名になるため、データアクセスが直観的になる。

    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側で行うかどうかを設定する。
    PHP 5.2以降の初期値はtrueである。

    falseの場合、ネイティブのプリペアドステートメントを使用する。
    これは、SQLインジェクション対策として有効であり、また、クエリのパフォーマンスが向上する。

    この設定は、いくつかPDOの挙動に違いが現れる。
    エミュレーションを行う場合、プリペアドステートメントのためにデータベースと通信する必要が無くなるため、パフォーマンスは向上する。
    存在しないテーブル名 / カラム名をクエリに持つプリペアドステートメントを発行する場合、
    エミュレーションを行わない場合は即エラーが発生するが、エミュレーションを行う場合はクエリを実行するまでエラーが発生するかどうかわからない。
    • エミュレーションを行う場合のみ、;(セミコロン)区切りで複数のクエリを1つのクエリで実行することができる。

  • PDO::ATTR_PERSISTENT　(コンストラクタでの指定のみ)
    trueの場合、PHPスクリプトが終了してもデータベースへの接続を維持して、次回に再利用する。
    特に、大規模システムでは恩恵が大きい。

    falseの場合は、持続的な接続を無効化する。
    メモリ管理の観点から、通常は無効にすることを推奨する。

  • PDO::MYSQL_ATTR_USE_BUFFERED_QUERY(MySQL専用)
    trueの場合、バッファクエリを使用する。初期値はMySQLの各バージョンによって異なる。
    • バッファクエリ
      全ての情報をデータベースから取得して、PHPスクリプトにより1件ずつ取得する。
    • 非バッファクエリ:
      1件ごとにデータベースサーバと通信を行って，PHPに取り出させる
    取得してくる情報がメモリに収まりきらない莫大なデータ量といった特殊なケースを除けば、バッファクエリを選択した方がよい。
    バッファクエリを設定すると、サーバ負荷も軽減されて、途中までフェッチしたところで突然例外が発生するような事態も避けられる。
    非バッファクエリは、データベースから取得したデータを、コマンドラインからバッチ処理を実行する用途で使用されるが、
    複数同時にクエリを実行できない等の大きな欠点もある。

  • PDO::MYSQL_ATTR_INIT_COMMAND(MySQL専用、コンストラクタでの指定のみ)
    データベースに接続した直後に実行されるクエリをここに記述する。

  • PDO::ATTR_CASE
    PDO::CASE_LOWERを指定する場合、カラム名を小文字に統一する。
    そのため、一貫性のあるコーディングが可能になる。


 <?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,
                         PDO::ATTR_EMULATE_PREPARES => false,
                      ]);
 
       // 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,
       //                  PDO::ATTR_EMULATE_PREPARES => false,
       //               ]);
       //$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>


PDOクラスのコンストラクタは、PDO::ERRMODE_EXCEPTIONの有無に関わらずPDOExceptionをスローするが、
データベースに接続できない場合等は、Warningが発生する。
もし、データベースを動的に指定する場合、set_error_handler関数を使用してErrorExceptionに変換した後、例外処理を行う。

文字コードの設定

PDOでMySQLに接続する場合は、文字コードの設定 (例: charset=utf8mb4) を指定することを強く推奨する。

もし、明示的に指定しない場合、MySQLのデフォルト設定に依存することになり、環境によって動作が異なる可能性がある。
そのため、確実な文字エンコーディングの制御のために、charsetを明示的に指定することを推奨する。

  • 絵文字や特殊文字のサポート
    utf8mb4は絵文字やその他の4バイト文字を完全にサポートする。
    通常のutf8では絵文字などが正しく保存できない可能性がある。

  • 文字化け防止
    日本語を含む多言語対応において、文字化けを防ぐことができる。
    データベースとアプリケーション間で一貫した文字エンコーディングを保証する。

  • 将来の互換性
    現代のWebアプリケーションでは、utf8mb4が事実上の標準となっている。
    後々データベースに絵文字等を格納する可能性を考慮すると、最初から設定しておくのが無難である。


 // 使用例
 
 $db = new PDO('mysql:host=<IPアドレスまたはホスト名>;dbname=<データベース名>;charset=utf8mb4', '<ユーザ名>', '<パスワード>');



データベースの持続的な接続

PDO::ATTR_PERSISTENTオプションの値は、string型の値が設定されない限り、bool型(持続的な接続の有無を示す)に変換される。
string型の値を設定する場合、複数の接続プールを使用することができる。

これは、互換性の無い異なる接続を使用する場合に便利である。
例えば、異なるPDO::MYSQL_ATTR_USE_BUFFERED_QUERYの値を設定する場合が挙げられる。

※注意1
持続的な接続をする場合、ドライバのオプションにPDO::ATTR_PERSISTENTを設定して、PDOクラスのコンストラクタに渡す必要がある。
この属性を、インスタンスの生成後にPDO::setAttributeメソッドを使用して設定する場合、ドライバは持続的な接続を使用しない。

※注意2
PDO ODBCドライバを使用しており、ODBCライブラリがODBC接続プーリングをサポートしている場合は、
PDOの持続的な接続を使用せずに、ODBCの接続プーリングに接続キャッシュ処理を任せることを推奨する。

ODBCの接続プールは、プロセス内で他のモジュールと共有されている。
PDOが接続をキャッシュすると、その接続はODBCの接続プールに返されなくなり、他のモジュールによって新たな接続が作成されてしまう。

 <?php
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(PDO::ATTR_PERSISTENT => true));
 ?>



データベースの切断

データベースの接続は、PHPスクリプトが終了しても閉じられずにキャッシュされ、他のPHPスクリプトが同じ内容の接続を要求する時に再利用される。
このキャッシュにより、新しい接続を確立するオーバーヘッドを避けることができるため、Webアプリケーションを高速化できるようになる。

もし、明示的に閉じる場合、作成したPDOクラスのオブジェクトにNULLを代入する。

 <?php
    try
    {
       $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);
       }    
    }
    catch (PDOException $e)
    {
       print('Connection failed:'.$e->getMessage());
       die();
    }
 
    // データベースの切断
    $dbh = null;
 ?>



クエリの実行

PDO::queryメソッド

ユーザ入力を伴わないクエリは、PDO::queryメソッドを実行する。
戻り値は、PDOStatementクラスである。

 $stmt = $pdo->query('SELECT * FROM users');


PDO::execメソッド

ユーザ入力を伴わないクエリにおいて、INSERTやUPDATE等を使用してレコードを直接取得する場合、PDO::execメソッドを使用する。
また、結果を必要としない場合においても、PDO::execメソッドを使用すべきである。

以下の例では、テーブルに存在する全てのレコードを更新して、更新した全てのレコード数を取得している。

 $count = $pdo->exec('UPDATE users SET age = age + 1');


PDO::executeメソッド(3ステップ)

PDO::prepareメソッド → PDOStatement::bindValueメソッド → PDOStatement::executeメソッドの3ステップでクエリを実行する。
ユーザ入力からクエリを動的に作成する場合、プリペアドステートメントとプレースホルダを使用する。

  • プレースホルダ
    ユーザ入力を代入する場所として、予め確保するためのもの。
    プレースホルダには2種類あり、疑問符プレースホルダを使用する方法と名前付きプレースホルダ使用する方法がある。
    これらを混ぜて使用する場合はエラーとなる。
  • プリペアドステートメント
    プレースホルダを使用するために作成する。


  • 疑問符プレースホルダ
    ?の要素は、1から始まる。
    PDO::PARAM_STRは省略することができる。
    エミュレーションが有効の場合、正しくキャストできない場合があるため、文字列以外を扱う時は明示的なキャストが必要である。
    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();
 
 // エミュレーションが無効の場合、上記または下記のいずれの記述も可能
 <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();


  • 名前付きプレースホルダ
    :(コロン)を先頭に付加して、半角英数字とアンダースコアにて構成する。
    バインド時の先頭は、:(コロン)は省略できる。
    エミュレーションが有効の場合、明示的なキャストが必要である。
 // エミュレーションが有効の場合
 $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);



 // 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メソッドも存在するが、
PDOStatement::bindParamメソッドを使用する必要はない。
エミュレーションが有効の場合、実行後にバインドした変数が文字列型に変換する仕様もあるので、注意すること。

PDO::executeメソッド(2ステップ)

PDO::prepareメソッド → PDOStatement::executeメソッドの2ステップでクエリを実行する。
PDOStatement::executeメソッドの引数に配列を渡す場合、それらを全てバインドした後そのままクエリが実行される。

ただし、以下の条件に注意すること。
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'));



トランザクションおよび自動コミット

トランザクション内で実行された作業は、データベースに安全に反映されることが保証されている。
トランザクションのコミット時は、他の接続の干渉を受けることはない。
また、未コミット時では、トランザクション内での作業はいつでも取り消すことができる。

PDOクラスを使用してデータベースへ接続する場合、自動コミットモードで動作する。
自動コミットモードとは、データベースがトランザクションをサポートしている場合、クエリが暗黙的にトランザクションのもとで実行される。
データベースがトランザクションをサポートしていない場合、トランザクションを使用せずに実行される。

トランザクションを使用する場合は、PDO::beginTransactionメソッドを使用して、トランザクションを初期化する必要がある。
使用しているドライバがトランザクションをサポートしていない場合、必ず、PDOExceptionクラスがスローされる。

トランザクションを終了する場合は、PDO::commitメソッドあるいはPDO::rollBackメソッドを使用する。

以下の例では、データベースのテーブルに対して、新しい2つのレコードを追加している。
トランザクションがアクティブな間は、作業中のデータについては、他から一切変更が加えられないことが保証されている。
もし何か問題が発生すれば、catchブロック内でトランザクション開始以降の全ての変更がロールバックされる。

 <?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();
 
       // SQLクエリの実行
       $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)
    {
       // エラーが発生した場合はロールバック
       if ($dbh->inTransaction()) {
          $dbh->rollBack();
       }
       echo "失敗しました。" . $e->getMessage();
    }
 ?>


データベースの接続を閉じる場合またはPHPスクリプトが終了する場合、
コミットが完了していないトランザクションがあるならば、自動的にロールバックされる。
これは、明示的にコミットしていない場合、予期せぬ状態で終了したと推測されるため、データの不整合が発生するのを避けるための機能である。

※注意
自動的にロールバックが行われる時は、PDO::beginTransactionメソッドを実行した場合のみである。
トランザクションを開始するクエリを手動で発行した場合、PDOはそれを知ることができないため、問題が発生してもロールバックできない。


プリペアドステートメント

プリペアドステートメントとは、実行するSQLをコンパイルしたテンプレートのようなものである。
パラメータ変数を使用することで、SQLをカスタマイズすることができる。

プリペアドステートメントには、以下に示す2つの大きな利点がある。

  • 一般的に、クエリを実行する時、クエリの解析、コンパイル、実行プランの最適化が行われる。
    プリペアドステートメントを使用する場合、これらの解析、コンパイル、最適化の繰り返しを避けることができる。
    端的に言うと、プリペアドステートメントは使用するリソースが少ないため、高速に動作するということである。

  • プリペアドステートメントに渡すパラメータは、引用符で括る必要は無く、ドライバが自動的に行う。
    プリペアドステートメントでは、SQLインジェクションは発生しない。


プリペアドステートメントは、データベースの種類や機能に関わらず同じ仕組みで データベースへのアクセスができる。


プリペアドステートメントの種類

bindValueメソッド (値渡し)

  • メリット
    バインドした後に元の変数が変更されても影響を受けない。
    直接リテラル値をバインドできる。
    メモリ効率が良い。 (参照を保持しない)
  • デメリット
    ループ内で変数を変更して再実行する場合は、その都度、bindValueメソッドが必要となる。


 $value = 100;
 $stmt->bindValue(1, $value, PDO::PARAM_INT);
 $value = 200;      // この変更はSQLに影響しない
 $stmt->execute();  // 100 が使用される


bindParamメソッド (参照渡し)

  • メリット
    変数の参照を保持するので、値を変更して再実行が容易
    ストアドプロシージャのOUTパラメータで使用可能
  • デメリット
    リテラル値を直接バインドできない。
    メモリを若干多く使用する。 (参照を保持するため)


 $value = 100;
 $stmt->bindParam(1, $value, PDO::PARAM_INT);
 $value = 200;      // この変更がSQLに反映される
 $stmt->execute();  // 200 が使用される


executeメソッドで直接記述

  • メリット
    コードが簡潔
    1回のみ使用する場合は効率的

  • デメリット
    変数の値を後から変更して再実行する場合は、その都度配列を作る必要がある。
    参照渡しができない。


 $stmt = $pdo->prepare("UPDATE student SET id = ?, name = ?, grade = ? WHERE id = ?");
 $stmt->execute([$id, $name, $grade, $old_id]);  // シンプル！
 
 ## 1回限りのクエリの場合、executeメソッド
 $stmt->execute([$id, $name, $grade]);


使い分け

 # 使い分け
 
 ## 一般的なケースでは、bindValueメソッドを使用
 $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
 $stmt->bindValue(1, $id, PDO::PARAM_INT);
 $stmt->execute();
 
 ## 以下に示す場合は、bindParamメソッドを使用
 ## ループ内で値を変更しながら実行する場合
 ## ループ内で変数を変更しながら再実行する場合
 ## ストアドプロシージャのOUTパラメータを使用する場合
 ## 特別な理由で参照渡しが必要な場合
 $stmt = $pdo->prepare("INSERT INTO logs (user_id, action) VALUES (?, ?)");
 $stmt->bindParam(1, $user_id, PDO::PARAM_INT);
 $stmt->bindParam(2, $action, PDO::PARAM_STR);
 
 foreach($actions as $action) {
    $stmt->execute();  // $actionの値が自動的に使用される
 }


セキュリティが重要な場合は、型を明示的に指定できるbindValueメソッド、または、bindParamメソッドを使用する。

※注意
ただし、実務では一貫性を保つため、特別な理由がない限りどちらか一方に統一することが推奨される。


レコードの取得(プリペアドステートメント)

MySQL PHPのデータ型
(エミュレーション無しmysqlnd) 		PHPのデータ型(libmysqlclient)
(エミュレーション有りmysqlnd)
NULL NULL NULL
文字列 String String
日付 String String
タイムスタンプ Integer String
論理値 Integer String
PHPで扱える値の整数 Integer String
PHPで扱えない値の整数 String String
浮動小数点 String String

※注意
エミュレーションに関するオプションは、PDO::ATTR_EMULATE_PREPARESという命名ではあるが、
プリペアドステートメントを使用しない場合にも影響が及ぶことに注意すること。

下表に、PDO::setAttributeで取得するデータ型を変更できるものを示す。
PDO::ATTR_ORACLE_NULLSオプションは、Oracle以外のデータベースでも使用できる。

データベース PDO::NULL_EMPTY_STRING PDO::NULL_TO_STRING
NULL NULL ""
"" NULL ""


また、PDO::ATTR_STRINGIFY_FETCHESオプションをtrueに指定する時、エミュレーションが無効の場合は数値が文字列に変換される。
エミュレーションが有効の場合、設定に関わらず常に数値が文字列に変換される。

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インターフェースを実装しているため、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メソッド

連想配列の代わりにクラスオブジェクトを取得する。
これは、PDO::FETCH_OBJを指定してPDOStatement::fetchメソッドを使用する場合と同じであるが、こちらの方が簡潔に記述できる。

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


PDOStatement::fetchColumnメソッド

特定の1カラムのみを文字列として取得する。
これは、PDO::FETCH_COLUMNを指定してPDOStatement::fetchメソッドを使用する場合と同じでるが、こちらの方が簡潔に記述できる。

先頭から数えてそのカラムが何番目(0オリジン)にあるかを第1引数として渡す。(省略する場合、0を指定したとみなされる)
カラムの値に0が含まれる可能性がある場合は、false !==の判定をしなければならない。

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


PDOStatement::fetchAllメソッド

全てのレコードを取得して2次元配列とする。

  • 引数を省略する場合、デフォルトフェッチモードが使用される。
  • 特定のカラムのみ全てのレコードを取得して1次元配列とする場合、
    第1引数にPDO::FETCH_COLUMNを指定して、第2引数に先頭から数えてそのカラムが何番目(0オリジン)にあるかを渡す。(省略する場合、0を指定したとみなされる)
 $rows = $stmt->fetchAll();
 var_dump($rows);
 
 $values = $stmt->fetchAll(PDO::FETCH_COLUMN);
 var_dump($values);


PDOStatement::setFetchModeメソッド

上記のセクションでは、PDOクラス自身にデフォルトフェッチモードを指定する方法を記載したが、
PDOStatement::setFetchModeメソッドを使用する場合、個別に発行されたPDOStatementクラスに対して、後からフェッチモードを指定できる。
また、モードにより引数の渡し方が異なるため、詳細はPHPのマニュアルを参照すること。

以下の例では、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)
 {
    // ...処理
 }


SQLインジェクション攻撃の対策

以下の例では、フォームで入力したキーの値に応じたデータを取得している。
ユーザの入力内容は自動的に引用符で括られるため、SQLインジェクション攻撃の恐れは無い。

 <?php
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
    if($stmt->execute(array($_GET['name'])))
    {
       while($row = $stmt->fetch())
       {
          print_r($row);
       }
    }
 ?>



レコードの追加(プリペアドステートメント)

以下の例では、nameおよびvalueを名前付きプレースホルダで置き換えて、INSERT文を実行している。

 <?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();
 ?>


以下の例では、nameおよびvalueをプレースホルダ?で置き換えて、INSERT文を実行している。

 <?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();
 ?>



レコードの更新(プリペアドステートメント)

以下の例では、name、value、idを名前付きプレースホルダで置き換えて、UPDATE文を実行している。

 <?php
    $stmt = $dbh->prepare("UPDATE REGISTRY SET name = :name value = :value WHERE id = :id");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':value', $value);
    $stmt->bindParam(':id', $id);
 
    // レコード目の更新
    $name = 'one';
    $value = 1;
    $stmt->execute();
 ?>


以下の例では、name、value、idをプレースホルダ?で置き換えて、UPDATE文を実行している。

 <?php
    $stmt = $dbh->prepare("UPDATE REGISTRY SET name = ? value = ? WHERE id = ?");
    $stmt->bindParam(1, $name);
    $stmt->bindParam(2, $value);
    $stmt->bindParam(3, $id);
 
    // レコードの更新
    $name = 'one';
    $value = 1;
    $stmt->execute();
 ?>



ストアドプロシージャ

ストアドプロシージャの呼び出し : 出力パラメータの指定

データベースドライバがサポートしている時、入力パラメータだけでなく、出力パラメータもバインドすることが可能である。

出力パラメータは、ストアドプロシージャから値を取得するために使用する。
この場合、返される値の大きさがどの程度になるのかをバインド時に知る必要がある。
指定した大きさよりも大きな値が返される場合は、エラーが発生する。

 <?php
    $stmt = $dbh->prepare("CALL sp_returns_string(?)");
    $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 
 
    // ストアドプロシージャを呼び出す
    $stmt->execute();
 
    print "プロシージャが返した値は $return_value です\n";
 ?>


ストアドプロシージャの呼び出し : 入出力パラメータの指定

入出力の両方に使用するパラメータを指定することもできる。
入出力パラメータの書式は、出力パラメータと同じである。

以下の例では、ストアドプロシージャに文字列'hello'を渡している。
プロシージャの結果が返ってくると、文字列'hello'はプロシージャの返す値に置き換えられる。

 <?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";
 ?>



プレースホルダの間違った使用例

 <?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]%"));
 ?>



「http://mochiuwiki.e2.valueserver.jp/index.php?title=PHPとデータベース_-_PDO&oldid=12412」から取得