More than 3 years have passed since last update.

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

Last updated at2021-10-15Posted at 2013-08-26

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

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

Qiita - 【PHP超入門】クラス～例外処理～PDOの基礎 by@7968

初心者がやりがちなミス

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

PDOの基本

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

データベースに接続

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

$pdo=newPDO($dsn,$username,$password,$driver_options);

引数は以下の通りです．

`$dsn`

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

PHP Manual - PDOクラスのデータベース別DSN一覧

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

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

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

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

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

Linux環境はホスト名推奨
- upsilonさん: MySQLではlocalhostと127.0.0.1で接続方法が変わるため注意が必要
Windows環境はIPアドレス推奨
- AH-2 - Windowsでlocalhostへの接続が遅い(解決方法)

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

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

`$username`

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

`$password`

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

`$driver_options`

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

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

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

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

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

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

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

`PDO::ATTR_DEFAULT_FETCH_MODE`

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側で行うかどうかを設定します．

PHP5.1のデフォルトはfalse
PHP5.2以降のデフォルトはtrue

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

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

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

PDOクラスのエミュレーションを無効にする

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

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

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

`PDO::MYSQL_ATTR_USE_BUFFERED_QUERY` (MySQL専用)

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

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

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

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

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

基本コーディング

書き方のテンプレです．

<?phptry{/* リクエストから得たスーパーグローバル変数をチェックするなどの処理 */// データベースに接続$pdo=newPDO('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><metacharset="utf-8"><title>Example</title></head><body><!-- ここではHTMLを書く以外のことは一切しない --></body></html>

参考: コンストラクタでのエラーモードってどうなるの？

PDOのコンストラクタはPDO::ERRMODE_EXCEPTION の有無に関わらずPDOException をスローしますが，ホストに接続できなかった場合などにWarning を発生します．普通は気に留める必要はありませんが，ホストを動的に指定させるようなプログラムを書いているならば，set_error_handler 関数を使ってErrorException に変換しなければ，例外処理を行うことができません．

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

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

全員を取得する

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

PDO::exec メソッドで直接クエリを実行する

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

全員の年齢を+1し，その対象となった人数を返り値として取得する

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

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

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

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

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

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

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

疑問符プレースホルダ

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

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

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

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

良い例

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

悪い例

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

名前付きプレースホルダ

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

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

頭のコロンを省略したケース

$stmt->bindValue('gender',$gender);

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

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

Qiita - 最近PDOを使っていてハマったこと2つ．

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

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

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

`SELECT` の結果を取得する

結果の型

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

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

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

データベース	PHP
NULL	NULL
その他	String

PDO::setAttribute で取得する型を変更できるケース

`PDO::ATTR_ORACLE_NULLS`

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

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

`PDO::ATTR_STRINGIFY_FETCHES`

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

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

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

0から始まるオフセットを取り出すことも出来る

foreach($stmtas$i=>$row){printf("[%d] %s lives in %s<br />\n",$i,$row['name'],$row['city']);}

PDOStatement::fetchObject

連想配列の代わりにstdClassオブジェクトとして取得します．PDOStatement::fetch でPDO::FETCH_OBJ を指定するケースと等価ですが，こちらの方が短く書くことができます．

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

PDOStatement::fetchColumn

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

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

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

PDOStatement::fetchAll

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

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

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

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

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

PDOStatement::setFetchMode

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

0番目のカラムをforeachで取得していくケース

$stmt->setFetchMode(PDO::FETCH_COLUMN,0);foreach($stmtas$i=>$name){...}

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

0番目のカラムをforeachで取得していくケース

foreach($pdo->query($sql,PDO::FETCH_COLUMN,0)as$i=>$name){...}

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

こちらの結果に対してフェッチしようとした場合，SQLSTATE[HY000]: General error が発生します．こちらに対して用いることができるのはPDOStatement::rowCount メソッドのみです．

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

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

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

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

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

バッファクエリ使用時

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

非バッファクエリ使用時

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

データベース接続の切断

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

$pdo=null;

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

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

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

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

PDOの応用

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

Qiita - PDOフェッチパターン大全

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

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

もしageが整数型でも，MySQLはエラーを発生しない

$stmt=$pdo->prepare('SELECT * FROM users WHERE age = ?');$stmt->execute(['20']);

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

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

Qiita - MySQLの自動変換が厄介な時の設定

LIKE検索

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

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

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

どんなデータベースにも広く使える方法

$stmt=$pdo->prepare("SELECT * FROM users WHERE name LIKE ? ESCAPE '!'");$stmt->bindValue(1,'%'.preg_replace('/(?=[!_%])/','!',$name).'%',PDO::PARAM_STR);

MySQL専用の方法

$stmt=$pdo->prepare('SELECT * FROM users WHERE name LIKE ?');$stmt->bindValue(1,'%'.addcslashes($name,'\_%').'%',PDO::PARAM_STR);

ブール全文検索

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

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

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

MySQL専用の方法

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

可変長プレースホルダ

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

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

$keywords=['foo','bar'];foreach($keywordsas$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);

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

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

if($keywords){/* キーワードが1つ以上のときだけ実行 */foreach($keywordsas$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 のどちらかに一致すればOKと見なす場合は次のようにします．OR条件とAND条件を両方用いていることに注意してください．

if($keywords){/* キーワードが1つ以上のときだけ実行 */foreach($keywordsas$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 に配列でセットするかどうかが問題となりますが，これは半角スペースで分割させることが一般的であると考えられます．しかし，ここでは半角スペース以外のさまざまな空白文字を正規表現で一括指定しています。

$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 関数の強みです．

トランザクション処理

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

Wikipedia - トランザクション処理

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

使用されるメソッド

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

ポイント

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

例

try{// データベースに接続$pdo=newPDO('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());}

トランザクションの種類

秀逸な記事は太字にして紹介します．

推奨場面まとめ

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

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

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

MySQLでの例

$pdo->exec('SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE');

PostgreSQLでの例

$pdo->exec('SET SESSION CHARACTERISTICS AS TRANSACTION SERIALIZABLE');

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

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

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

テーブル名をエスケープして動的に指定する例

$sql=sprintf("CREATE TABLE `%s`(id int, name text)",str_replace(["\0","`"],["","``"],$table_name));

PDOクラスの継承

付録

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

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

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

文字コードの指定が無いのは論外として，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であっても使ってはいけない

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

コンストラクタのDSN(Data Source Name) で指定する．

$pdo=newPDO('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 を指定する
PHP5.3.6Windows版のPDOではDSNで文字セット指定が出来るが，バグがあるため，SET NAMES utf8 やSET CHARACTER SET utf8 を使う場合と同じ結果になる．
やむを得ずSET NAMES を使うにしても，SET CHARACTER SET は避けるべき．
- MySQLマニュアル - SET NAMES と SET CHARACTER SET の違い

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

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

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

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

Wikipedia - SQLインジェクション

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

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

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

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

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

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

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

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

Qiita - $_GET, $_POSTなどを受け取る際の処理

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

データベースに挿入する前にhtmlspecialchars関数を適用している間違った例

$stmt=$pdo->prepare('INSERT INTO users(name) VALUES(?)');$stmt->bindValue(1,htmlspecialchars($name),PDO::PARAM_STR);

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

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

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

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

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

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

<!DOCTYPE html><html><head><metacharset="utf-8"><title>Example</title></head><body><ul><?phptry{$pdo=newPDO('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はこんな感じになるはずです．

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

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

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

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

<?phptry{$pdo=newPDO('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());}functionh($str){returnhtmlspecialchars($str,ENT_QUOTES,'UTF-8');}?><!DOCTYPE html><html><head><metacharset="utf-8"><title>Example</title></head><body><ul><?phpforeach($rowsas$row):?><li><?=h($row)?></li><?phpendforeach;?></ul></body></html>

echo短縮構文 および制御構造に関する別の構文 を用いてみました．この2つはHTMLを出力する際に適用するとコードが非常に読みやすくなります．これで，エラー時に出力されるHTMLは以下のようになります．

No such file or directory

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

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

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

PDOとmysqliの比較

mysqliクラス (またはmysqli関数)

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

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

オブジェクト指向型

$driver=newmysqli_driver();$driver->report_mode=MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT;

手続き型

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

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

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT|MYSQLI_REPORT_INDEX);

mysqli_report(MYSQLI_REPORT_ALL);

久保清隆の成長ノート - MySQLパフォーマンスチューニングのためのインデックスの基礎知識

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

PDOクラス

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

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

PDOクラスで例外をスローさせる

$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

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

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

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

[Z]ZAPAブロ～グ2.0 - PDO，PEAR::DB，Mysql関数の速度比較

3320

3475

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Movatterモバイル変換

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

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

初心者がやりがちなミス

PDOの基本

データベースに接続

$dsn

$username

$password

$driver_options

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

よく使われるドライバオプションとその値

PDO::ATTR_ERRMODE

PDO::ATTR_DEFAULT_FETCH_MODE

PDO::ATTR_EMULATE_PREPARES

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

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (MySQL専用)

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

基本コーディング

参考: コンストラクタでのエラーモードってどうなるの？

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

PDO::exec メソッドで直接クエリを実行する

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

疑問符プレースホルダ

名前付きプレースホルダ

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

疑問符プレースホルダ

名前付きプレースホルダ

SELECT の結果を取得する

結果の型

PDO::setAttribute で取得する型を変更できるケース

PDO::ATTR_ORACLE_NULLS

PDO::ATTR_STRINGIFY_FETCHES

PDOStatement::fetch

PDOStatement::fetchObject

PDOStatement::fetchColumn

PDOStatement::fetchAll

PDOStatement::setFetchMode

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

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

バッファクエリ使用時

非バッファクエリ使用時

データベース接続の切断

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

PDOの応用

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

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

LIKE検索

ブール全文検索

可変長プレースホルダ

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

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

検索キーワードを単語単位に分解する

トランザクション処理

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

使用されるメソッド

ポイント

トランザクションの種類

推奨場面まとめ

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

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

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

PDOクラスの継承

付録

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

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

SET NAMES あるいはSET CHARACTER SET などで文字コードを指定しているそもそもデータベースで使用する文字コードの指定をしていない

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

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

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

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

PDOとmysqliの比較

mysqliクラス (またはmysqli関数)

PDOクラス

`$dsn`

`$username`

`$password`

`$driver_options`

`PDO::ATTR_ERRMODE`

`PDO::ATTR_DEFAULT_FETCH_MODE`

`PDO::ATTR_EMULATE_PREPARES`

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

`PDO::MYSQL_ATTR_USE_BUFFERED_QUERY` (MySQL専用)

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

`SELECT` の結果を取得する

`PDO::ATTR_ORACLE_NULLS`

`PDO::ATTR_STRINGIFY_FETCHES`

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

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

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

`SET NAMES` あるいは`SET CHARACTER SET` などで文字コードを指定している
そもそもデータベースで使用する文字コードの指定をしていない

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

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

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

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