次に、PHPプログラムからSQLite等のデータベースを操作する場合の共通ライブラリ PDO について紹介します
SQLiteは、PDOを使わなくても操作することが出来ますが、PDO ライブラリを使って記述しておけば、基本的には、MySQL や SQLite 等のデータベースの種類に関係なく同じ記述でデータベースを扱うことが出来るので、データベース操作の記述が統一され記述しやすくなり、データベースを移 行する場合等でも最小限の変更で済むようになります
※PDOを使っても、各データベースごとにサポートしているSQL文の違いがあるので全てを共通化出来るわけではありません
PDO
今回作成したプログラムで主に使っているPDOのメソッド（関数）について紹介します
よく使うのは、データベース接続とプリペアードステートメントを使ったSQL実行、結果セットの取得、トランザクションです。
| データベース接続 | __construct | SQLite データベースファイルを絶対パスで指定します
データベースファイルがない場合は、空のデータベースファイル作成を行い、存在する場合はデータベースファイルとの接続を確立します |
| SQL実行 （SQLエスケープなし） |
exec | １回のみでSQLエスケープ不要な信頼できるSQLを実行します
※SELECT文以外の結果取得が不要な場合は、exec を使用し、SELECT文で結果セットを PDOStatement オブジェクトとして取得する場合は query を使用します |
| SQL実行 （プリペアードステートメントによるSQLエスケープ） |
prepare
|
SQL文にプレースホルダ（変数）を使い信頼出来ない値がセットされる可能性がある場合は、SQLインジェクション対策のために prepared, bindValue, execute を使ってSQLエスケープを行い、安全なSQL文にして実行します
※プレースホルダとPHPでの変数値をバインドさせるには bindValue(変数の値をバインド)、bindParam(変数の参照アドレスとしてバインド)があります |
| SELECT結果セット取得 | fetch | SELECTの実行結果（PDOStatement オブジェクト）からデータを取得します
取得データが単一か複数データかによって使用関数が異なります。また、配列として取得するか、オブジェクトとして取得するかによっても異なります |
| トランザクション
|
biginTransaction | リレーショナル・データベースは、多くの場合複数のテーブルからなり、データを追加、更新、削除する場合には、そのテーブル間等でデータの整合性が保たれ ている必要があります。その為にトランザクションというデータ追加、更新、削除に対して関連するデータに対する処理が全て正常に行われた場合に反映させ、何か問題があった場合 は元に戻す機能があります
SQLiteの場合はサーバーレスのファイル型データベースですので、データ追加、更新時 はトランザクションを使ってある程度まとまったデータを処理したほうがディスクI/O操作を減らせるので高速に実行することが出来ます。但し、トランザク ション時間があまり長いとその間は他のクエリーが待たされてロックエラーとなることも考えられますので、一度に処理する最大データ量はバランスよく見極め る必要があります |
| データベースクローズ | デストラクト | PDOのインスタンスに NULL をセットするとクローズされます 明示的にクローズしなくともインスタンスが破棄されるときに自動的にクローズされます |
参照 http://php.net/manual/ja/book.pdo.php
詳細はリンク先サイトの説明を参照して下さい
Ext_PDO
プログラムを作成するにあたり、PDOを直接扱うコードを書くのはちょっと煩わしかったので、少し扱いやすくなるようにラップした Ext_PDO という拡張クラスを書いてみました
WordPress で MySQL を扱う wpdb クラスにちょっと似た感じで使えるように作りました
SQLの実行と結果データの取得をデータ取得無し、変数（カラム）、行（レコード）、複数行取得に分け、各々プリペアードステートメントの有無で分けています。
| データ取得のないSQL実行 | sql_exec($sql) sql_prepare_exec($sql, $ar_param) |
| 変数(カラム)データ取得 | sql_get_var($sql, $col_num=0) sql_prepare_var($sql, $ar_param, $col_num=0) |
| 行（レコード）データ取得 | sql_get_row($sql, $output=PDO::FETCH_OBJ) sql_prepare_row($sql, $ar_param, $output= PDO::FETCH_OBJ) |
| 複数の行データ取得 | sql_get_results($sql, $output = PDO::FETCH_OBJ) sql_prepare_results($sql, $ar_param, $output= PDO::FETCH_OBJ) |
各メソッドの詳細は、短いコードですので直接見て下さい m(_ _)m
<?php
/**
* PDO（SQlite）の拡張クラス
*
*
* @author enomoto celtislab
*/
class Ext_PDO extends PDO {
public function __construct( $dsn, $DB_USER='', $DB_PASS='' ) {
parent::__construct( $dsn, $DB_USER, $DB_PASS);
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
//デバッグ時のエラー表示処理
static function display_error(PDOException $e) {
//if (ini_get( 'display_errors' )){
if ( defined('WP_DEBUG') && WP_DEBUG ){
var_dump($e->getMessage());
}
}
/**
* データ取得のないSQL文の実行
* @param string $sql SQL文
* @return
*/
public function sql_exec($sql) {
try {
$res = $this->exec( $sql );
return $res;
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
/**
* 変数(カラム)データ取得
* ※bool 値のカラムに対しては使用不可（falseの区別が出来ない）
* @param string $sql SQL文
* @return
*/
public function sql_get_var($sql, $col_num=0) {
try {
$stmt = $this->query( $sql );
$var = $stmt->fetchColumn( $col_num );
return $var;
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
/**
* 行（レコード）データを取得
* @param string $sql SQL文
* @param $output
* PDO::FETCH_ASSOC - 結果を連想配列として出力。
* PDO::FETCH_NUM - 結果をインデックス配列として出力。
* 上記以外の指定 - 結果をオブジェクトとして出力。
* @return
*/
public function sql_get_row($sql, $output=PDO::FETCH_OBJ) {
try {
$stmt = $this->query( $sql );
if($output === PDO::FETCH_ASSOC || $output === PDO::FETCH_NUM ){
$row = $stmt->fetch( $output );
}
else {
$row = $stmt->fetchObject();
}
return $row;
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
/**
* 複数の行データを取得
* @param string $sql SQL文
* @param $output
* PDO::FETCH_ASSOC - 結果を連想配列として出力。
* PDO::FETCH_NUM - 結果をインデックス配列として出力。
* 上記以外の指定 - 結果をオブジェクト配列として出力。
* @return
*/
public function sql_get_results($sql, $output = PDO::FETCH_OBJ) {
try {
$stmt = $this->query( $sql );
if($output === PDO::FETCH_ASSOC || $output === PDO::FETCH_NUM ){
$results = $stmt->fetchAll( $output );
}
else {
$stmt->setFetchMode(PDO::FETCH_CLASS, 'stdClass');
$results = $stmt->fetchAll();
}
return $results;
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
/**
* プリペアドステートメント(セキュリティ対策)
* @param string $sql 例. "INSERT INTO table (id, title, author, date) VALUES ( ?, ?, ?, ?)"
* @param array $ar_param 例. array($id, $title, $author, $now->format('Y-m-d H:i:s')
* @param $output
* @return
*/
// データ取得のないSQL文の実行
public function sql_prepare_exec($sql, $ar_param) {
try {
$stmt = $this->prepare( $sql );
$stmt = self::_stmt_bind( $stmt, $ar_param );
return self::_stmt_execute( $stmt );
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
// 変数(カラム)データ取得
public function sql_prepare_var($sql, $ar_param, $col_num=0) {
try {
$stmt = $this->prepare( $sql );
$stmt = self::_stmt_bind( $stmt, $ar_param );
$var = $stmt->execute();
if($var !== false)
$var = $stmt->fetchColumn( $col_num );
return $var;
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
// 行（レコード）データを取得
public function sql_prepare_row($sql, $ar_param, $output= PDO::FETCH_OBJ) {
try {
$stmt = $this->prepare( $sql );
$stmt = self::_stmt_bind( $stmt, $ar_param );
$row = self::_stmt_execute( $stmt, $output, true);
return $row;
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
// 複数の行データを取得
public function sql_prepare_results($sql, $ar_param, $output= PDO::FETCH_OBJ) {
try {
$stmt = $this->prepare( $sql );
$stmt = self::_stmt_bind( $stmt, $ar_param );
return self::_stmt_execute( $stmt, $output);
} catch (PDOException $e) {
if(PDO::inTransaction()){
throw $e;
} else {
self::display_error($e);
return false;
}
}
}
//SQL prepare バインド
static function _stmt_bind($stmt, $ar_param) {
$n = 1;
foreach ($ar_param as $val){
if(is_string($val)){
$stmt->bindValue($n, $val, PDO::PARAM_STR);
}
else if(is_null($val)){
$stmt->bindValue($n, NULL, PDO::PARAM_NULL);
}
else if(is_bool($val)){
$stmt->bindValue($n, $val, PDO::PARAM_BOOL);
}
else if(is_int($val)){
$stmt->bindValue($n, (int)$val, PDO::PARAM_INT);
}
else if(is_float($val)){
//PDOでは実数型指定ができないがSQLiteはREAL型として扱うようキャストしておく
$stmt->bindValue($n, (float)$val);
}
else {
//ラージオブジェクト型（バイナリデータ）
$stmt->bindValue($n, $val, PDO::PARAM_LOB);
}
$n++;
}
return $stmt;
}
//SQL prepare 実行
static function _stmt_execute($stmt, $output = null, $row = false) {
$res = $stmt->execute();
if($res === false)
return false;
if($output === null || $res === false)
return $res;
else {
if($output === PDO::FETCH_ASSOC || $output === PDO::FETCH_NUM ){
$results = ($row)? $stmt->fetch( $output ) : $stmt->fetchAll( $output );
}
else {
$stmt->setFetchMode(PDO::FETCH_CLASS, 'stdClass');
$results = ($row)? $stmt->fetch() : $stmt->fetchAll();
}
return $results;
}
}
/**
* テーブルの存在確認
*
* @return boolean
*/
public function is_table_exist($table) {
//sqlite_master から作成済みのテーブル構造を取得
$res = $this->sql_get_row("SELECT * FROM sqlite_master WHERE type = 'table' AND name = '$table'");
if($res === false)
return false;
return true;
}
}
これだけでもかなりコードが見やすく簡潔に書けるので自分では気に入っています (^^)
興味ある方は、使いやすいように自由に改造して使って下さい
セキュリティ対策
データベースでは、SQLを実行する時に不正なSQLが実行されないように対策 する必要があります。特に Web環境からのアクセスに対しては、外部から入力されたデータを使ってSQL文を生成して実行する場合が多いので、必ず、SQLインジェクション対策を 行い不正なアクセスを防止しなければなりません
PDOでは、プリペアードメソッド prepare でプレースホルダを使い、バインドでデータ型を指定することで最低限のSQLインジェクション対策を行うことが出来ます
WordPress なら、さらに、nonce を併用してより強固な不正アクセス対策を行いましょうhttp://tokkono.cute.coocan.jp/blog/slow/index.php/wordpress/secured-ajax-with-wp-nonce/
以降のPDOのサンプルコードはこの拡張クラス Ext_PDO を使った記述となります
次のページに続きます (^^)
