PDO -> クエリの発行

Posted by muchag | PHP | 2010-07-08 (木) 21:05:03

PDO におけるクエリの発行方法は2つ。

  1. //PDO::query
  2. //1回用または直接発行
  3. $dbh->query($sql);
  4.  
  5. //PDOStatement->execute
  6. //複数回用または間接発行(プリペアドステートメント)
  7. $sth = $dbh->prepare($sql);
  8. $sth->execute($params);

 

PDO::query

1回用とは文字通りでよいとして
「直接発行」(あ、私が勝手にこう呼んでいるだけ)とは

  1. $sql = "INSERT INTO REGISTRY (name, value) VALUES ('one', 1)";

のようにSQL文に情報の全てが入り完結している方法。

PHPマニュアル:PDO::query

これだけ見ても分かりづらいので、次を読んでから考える。

 

PDOStatement->execute
間接発行

これも私が勝手に名付けた。
よく目にする プリペアドステートメント っていうやつ。

見た方がはやいので、例を。

  1. $sql = "INSERT INTO REGISTRY (name, value) VALUES (?, ?)";
  2. $param = array('one', 1);

条件の値をSQL文に直接書き込まずに「?」で表しておいて、パラメータ配列にて設定している。
だから間接。
 
 
こうする意味は次を読めば分かる。
 

複数回用

これも論より証拠で。

  1. $sql = "INSERT INTO REGISTRY (name, value) VALUES (?, ?)";
  2. $stmt = $dbh->prepare($sql);
  3. $stmt->bindParam(1, $name);
  4. $stmt->bindParam(2, $value);
  5.  
  6. // 行を挿入します
  7. $name = 'one';
  8. $value = 1;
  9. $stmt->execute();
  10.  
  11. // パラメータを変更し、別の行を挿入します
  12. $name = 'two';
  13. $value = 2;
  14. $stmt->execute();

※PHPマニュアルにあったコードをちょこっとだけ改変

これはすごい、便利だ と思った。
大量のCSVデータの読み込みとかにはとっても便利そう。

PHPマニュアル:PDOStatement->execute
PHPマニュアル:プリペアドステートメントおよびストアドプロシージャ
 

効能

間接発行には、SQLインジェクションを回避できる という効能がある。
 

覚書用に作った適当なコードや引用コードが入り混じって
あちこち不整合になっているので、以下に整理。

 

プリペアドステートメント
パラメータ

設定方法が大きく分けて2つ。
 

配列で一括

前述の例を合体させて

  1. $sql = "INSERT INTO REGISTRY (name, value) VALUES (?, ?)";
  2. $param = array('one', 1);
  3.  
  4. $sth = $dbh->prepare($sql);
  5. $sth->execute($params);

これの問題点は、パラメータのデータ型を指定できず、全て文字列型となる。

ただし、MySQL側ではデータ型が設定されているので
そのままそれぞれのデータ型で解釈してくれ・・・るのかな?
少なくとも現時点までは問題なく動いている。
 

個々に指定

bindValueメソッド

bool PDOStatement::bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] )

  1. $name = 'one';
  2. $value = 1;
  3. $sql = "INSERT INTO REGISTRY (name, value) VALUES (?, ?)";
  4. $stmt = $dbh->prepare($sql);
  5. $stmt->bindValue(1, $name);
  6. $stmt->bindValue(2, $value);
  7. $stmt->execute();

bindValueメソッドが使用された場所で値を設定するので
前もって値を指定しておかなければならない。
ってことは、これは1回用ですな。
 
bindParamメソッド

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )

  1. $sql = "INSERT INTO REGISTRY (name, value) VALUES (?, ?)";
  2. $stmt = $dbh->prepare($sql);
  3. $stmt->bindParam(1, $name);
  4. $stmt->bindParam(2, $value);
  5.  
  6. // 行を挿入します
  7. $name = 'one';
  8. $value = 1;
  9. $stmt->execute();

$stmt->execute(); が呼ばれた時点で値設定が有効になるので
前もって指定しておいてもいいし、後からでも指定できる。
これが正に複数回用。

もちろんどの方法でも複数回できるわけだけど
bindParamメソッドを使う方法が、重複を一番避けられ繰り返しに向いている っていうことかな。

共にパラメータのデータ型を指定できる
 
bindValueメソッドおよびbindParamメソッドで使用できる定数
PDO -> 定義済み定数
 

プレースホルダ
(?)疑問符プレースホルダ

今までの例では、プレースホルダに?を用いてきた。

見た通りだが、この場合は bindValueメソッド、bindParamメソッドの第1引数には
疑問符の順番を入れる。
 

名前付きプレースホルダ

プレースホルダには素直に名前をつけることもできる。

  1. $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
  2. $stmt->bindParam(':name', $name);
  3. $stmt->bindParam(':value', $value);

この場合は、bindParamメソッドの第1引数には、それぞれの名前を入れる。
 

よくある間違い
  1. // 誤
  2. $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
  3. $stmt->execute(array($_GET['name']));
  4.  
  5. // プレースホルダは、値全体に対して使用しなければなりません
  6. // 正
  7. $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
  8. $stmt->execute(array("%$_GET[name]%"));
bindValue, bindParam

今更ながら、名前を見れば当たり前やんね、と思った。

  • bindValue:値をバインド
  • bindParam:変数をバインド

つまり、bindParam は後から変更が効く。
逆に言うと、知らないうちに値が変更されている、という現象が起きる。

参考サイト

PHPマニュアル:PDOStatement->execute
PHPマニュアル:プリペアドステートメントおよびストアドプロシージャ

do_akiの徒然想記:PHP と MySQL と サーバサイド プリペアードステートメント(2010-02-21)
→プリペアドステートメントを利用できるかどうかの設定について書かれている

Qiita:bindParam()とbindValue()の違い(2015-09-13)
→bindValue を利用する方がいいみたい

PHP | 2010-07-08 (木) 21:05:03 |

3 Comments »

  1. ピンバック by PHP+SQLite+PDO 入門 « Blog — 2010/12/5 日曜日 @ 19:23:25

    […] 侵入するとセキュリティホールになります(SQLインジェクション)。外部入力をSQL文に突っ込む場合は、次のような感じでprepareを使う必要がある。このblogがよくまとまって説明している。 […]

  2. Comment by いとら — 2017/1/27 金曜日 @ 12:44:45

    ありがとうございます。モヤモヤが解決しました。

  3. Comment by muchag — 2017/1/28 土曜日 @ 17:01:00

    いとらさん
    はじめまして。

    コメントを残していただき
    ありがとうございます。

    まだまだ勉強中ゆえ
    拙い内容にて冷や汗をかくばかりですが
    このようなコメントいただけると
    とても励みになります。

RSS feed for comments on this post. TrackBack URI

Leave a comment