PHPとデータベース - PDO

概要

PDO(PHP Data Objects)拡張モジュールは、PHPからデータベースにアクセスするための軽量で高性能なインターフェイスである。
PDOインターフェイスを実装する各データベースドライバは、正規表現のようなデータベース固有の機能を提供する。
ただし、PDO拡張モジュールは、データベースのあらゆる関数を実行できるわけではない。

データベースサーバにアクセスするには、データベース固有のPDOドライバを使用する必要がある。

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

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

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

データベースの接続と管理

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

MySQLへの接続

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

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

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

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

 <?php
    try
    {
       $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
       foreach($dbh->query('SELECT * from FOO') as $row)
       {
          print_r($row);
       }
       $dbh = null;
    }
    catch(PDOException $e)
    {
       print "エラー!: " . $e->getMessage() . "<br/>";
       die();
    }
 ?>

データベースの接続を閉じる

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

 <?php
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    
    $sth = $dbh->query('SELECT * FROM foo');
 
    // 使用を終了したので、閉じます
    $sth = null;
    $dbh = null;
 ?>

持続的な接続

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

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

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

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

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

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

プリペアドステートメントおよびストアドプロシージャ

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

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

• 一般的に、クエリを実行する時、クエリの解析、コンパイル、実行プランの最適化が行われる。
  プリペアドステートメントを使用する場合、これらの解析、コンパイル、最適化の繰り返しを避けることができる。
  端的に言うと、プリペアドステートメントは使用するリソースが少ないため、高速に動作するということである。
  
  
• プリペアドステートメントに渡すパラメータは、引用符で括る必要は無く、ドライバが自動的に行う。
  プリペアドステートメントでは、SQLインジェクションは発生しない。

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

Insert (1)

以下の例では、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();
 ?>

Insert (2)

以下の例では、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();
 ?>

SELECT

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

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

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

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

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

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