PHPにおけるプリペアドステートメントの使用
プリペアドステートメントとは、SQLの可変部分を変数のように扱う方法です。
可変部分に与えられる値を、安全にSQL構文に組み込むことが可能です。
つまり、SQLインジェクションを防げるわけですね。
ただし、いくつか分かりづらい仕様があるため、知識を共有します。
それは、属性と型の指定です。
まずはコードを見てみましょう。
MITライセンスね。
<?php function sql($dbname, $hostname, $user, $passwd, $bind_array, $sql_command, $get_return = false){ $db = 'mysql:dbname='.$dbname.';host='.$hostname.''; try{ $pdo = new PDO($db ,$user, $passwd, [PDO::ATTR_PERSISTENT => false]); $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); if(! $pdo->beginTransaction()){ throw new ErrorException('Transaction failed.'); } $stmt = $pdo->prepare($sql_command); foreach ($bind_array as $key => $value){ $type = null; $typelist = [ ':bool_' => PDO::PARAM_BOOL, ':int_' => PDO::PARAM_INT, ':float_' => PDO::PARAM_STR, ':str_' => PDO::PARAM_STR, ':blob_' => PDO::PARAM_LOB, ':null_' => PDO::PARAM_NULL, ]; foreach ($typelist as $header => $typevalue){ if(strcmp(substr($key, 0, strlen($header)), $header) === 0){ $type = $typevalue; break; } } if(is_null($type)){ throw new ErrorException('Type cannot be identified.'); } if(! $stmt->bindValue($key, $value, $type)){ throw new ErrorException('Binding failed.'); } } $resp = $stmt->execute(); if($resp){ $pdo->commit(); }else{ throw new ErrorException('Execute failed.'); } $result = $resp; if($get_return){ $result = $stmt->fetch(); } }catch(\Throwable $e){ $result = false; } $pdo = null; return $result; }
上記のコードはPDOを用いてMySQLのDBエンジンと通信します。
ポイントは、$stmt->fetch();を戻り値取得時にしか実行しないこと、catch時に$pdo->rollBack();を記述しないことです。
エミュレーションモードが有効である場合、戻り値のないコマンドにfetchを試みると、一般エラーになります。
この後、失敗しているにも関わらずロールバックを試みてしまうと、さらなるエラーを誘発します。
一方、エミュレーションモードが無効である場合、戻り値のないコマンドにfetchを試みるとfalseが戻ります。
呼び出し方は以下のとおりです。
<?php error_reporting(E_ALL); //error_reporting(0); $result = sql('dbt', 'localhost', 'username', 'password', [ ':int_num'=>1, ':str_text'=>'文字列', ':str_date_time'=>'2021-01-01 10:10:10', ':str_num_float'=>0.1, ':bool_bool'=>true, ':blob_large'=>'テスト' ], 'insert into table1 (num, text, date_time, num_float, bool, large) values (:int_num, :str_text, :date_date_time, :float_num_float, :bool_bool, :blob_larg' ); if($result === false){ echo 'Failure'; }
それでは、属性と型の指定を読み解いていきましょう。
属性
データベース接続の持続: しない
<?php $pdo = new PDO($db ,$user, $passwd, [PDO::ATTR_PERSISTENT => false]);
データベース接続をインスタンス化し持続的（永続的）に接続します。
規模が大きなサービスでは有効ですが、レンタルサーバーでの運用ではfalseが良いでしょう。
上記の[PDO::ATTR_PERSISTENT => false]の部分が該当の属性です。
時折setAttributeでPDO::ATTR_PERSISTENTを記述した例を見ることがありますが、PDO初期化の段階で指定しないと、この機能は働きませんので注意しましょう。
nullと空白文字の変換: しない
<?php $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);
ここでは、nullや空白文字の変換は行わないようにしています。
| PDO::NULL_NATURAL | 変換なし |
| PDO::NULL_EMPTY_STRING | 空文字をnullにする |
| PDO::NULL_TO_STRING | nullを空文字にする |
エラーレポート: 例外を発生
<?php $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
開発中は例外をthrowするようにしましょう。
運用中はエラーレポートの出力は脆弱性につながりますので、エラーコードのみ（サイレント）にします。
<?php $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
| PDO::ERRMODE_SILENT | エラーコードのみ |
| PDO::ERRMODE_WARNING | E_WARNING（警告）を発生 |
| PDO::ERRMODE_EXCEPTION | 例外を発生 |
Fetchモード: 連想配列として取得
<?php $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
取得するデータは連想配列にすると便利です。
モードは以下があります。
| モード | 概要 |
|---|---|
| PDO::FETCH_BOTH | カラム名の連想配列と配列の混合（デフォルト） |
| PDO::FETCH_NUM | 配列 |
| PDO::FETCH_ASSOC | カラム名の連想配列 |
| PDO::FETCH_NAMED | PDO::FETCH_ASSOCと同じ。ただし、同一名カラムを結合 |
| PDO::FETCH_BOUND | bindColumn()でバインドされた変数に代入 |
| PDO::FETCH_INTO | 既存インスタンスに代入 |
| PDO::FETCH_CLASS | 新規クラスに代入 |
| PDO::FETCH_PROPS_LATE | PDO::FETCH_CLASSと共に呼び出すと、コンストラクターを呼び出してからプロパティーを代入 |
| PDO::FETCH_COLUMN | カラム名のみ、0ですべて、それ以外で指定カラム |
| PDO::FETCH_OBJ | カラム名をプロパティーとするstdClassオブジェクト |
| PDO::FETCH_LAZY | カラム名をプロパティーとするPDORowオブジェクト |
プリペアドステートメントのエミュレーション: 無効
<?php $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
エミュレーションモードが無効の場合、静的プレースホルダーと呼ばれる「DBエンジンがネイティブにサポートするプリペアドステートメント」を用います。
エミュレーションモードが有効の場合、動的プレースホルダーと呼ばれる、「ライブラリーが独自に実装したプリペアドステートメント」を用います。
プリペアドステートメントのエミュレーションは過去にはライブラリーの実装に不具合があり、SQLインジェクション脆弱性が生じていました。
エミュレーションの脆弱性はここのところ報告がありませんが、この歴史的背景をどう捉えるかがポイントです。
個人的には無効にしますが、サーバー資源や環境、または思想によって選択はそれぞれです。
よって、以下のどちらを選択するか、論点はここに帰結します。
同一名のプレースホルダー、複文の実行が可能です。
- falseにして、DBエンジンが提供するプリペアドステートメントを使う
レスポンスはやや低下しますが、ライブラリー実装の不具合による脆弱性を回避できます。
型
まずはコードを見てみましょう。
<?php $typelist = [ ':bool_' => PDO::PARAM_BOOL, ':int_' => PDO::PARAM_INT, ':float_' => PDO::PARAM_STR, ':str_' => PDO::PARAM_STR, ':blob_' => PDO::PARAM_LOB, ':null_' => PDO::PARAM_NULL, ];
PDOで指定可能な型はBOOLEAN、INT、STR、BLOB、NULLのみです。
FLOATをはじめ、DATEやDATETIME、VCHARやCHARなどの型はありません。
その他の型に対して、PDOではSTR型を指定します。
では、fetchの結果を見てみましょう。
以下のようにデータを取得します。
<?php $result = sql( 'dbt', 'localhost', 'username', 'password', [], 'SELECT * FROM `table1` WHERE id=0', true ); var_dump($result);
エミュレーション無効
<?php array(7) { ["id"]=> int(0) ["num"]=> int(100) ["text"]=> string(9) "文字列" ["date_time"]=> string(19) "2021-01-01 10:10:10" ["num_float"]=> float(0.1) ["bool"]=> int(1) ["large"]=> string(9) "テスト" }
PDOはテーブル側の型指定に従い、可能な限りデータを取得しようとしているのが分かりますね。
ただし、PHPではバイナリはString型、日時はDateTimeクラスによって実装しています。
このため、どうしようもないものは、String型として取得しています。
なお、MySQLでは内部的にBOOLEAN型をTINYINTとして扱っています。
PHPもこれに従ってint型に変換しているわけです。
エミュレーション有効
<?php array(7) { ["id"]=> string(2) "0" ["num"]=> string(3) "100" ["text"]=> string(9) "文字列" ["date_time"]=> string(19) "2021-01-01 10:10:10" ["num_float"]=> string(3) "0.1" ["bool"]=> string(1) "1" ["large"]=> string(9) "テスト" }
エミュレーションが有効の場合、取得する値はすべてString型に変換されています。
型はすべてString型であれ、内容はエミュレーション無効と同じですね。
余談ですが、BLOB型は「大きいサイズ」と表現していますが、これは4KB～32KB以上と定義はデータベースによって異なります。
そして、「大きいサイズ」は運用環境、設計思想によっても異なります。
個人的には、MySQLでは、VARBINARY型のサイズ指定をしないものをBLOBと捉えています。
用途は画像などのバイナリーが想定されます。
MySQLのTEXT型もBLOB型と似たことが言えるかも知れません。
つまり、私はMySQLでは、VARCHAR型のサイズ指定をしないものをTEXT型と捉えています。
これはテキストドキュメントや長文の投稿などが想定されます。
