PHPでZendDBを使用している際のプレースホルダの注意点まとめ

世間はメリークリスマスと言って浮かれているはずの日なのですが、
不具合が起きたので夜中まで調査しておりました。
ググッても解決できなかったので誰かの為になるようにブログに書いておくことにします。

なお、この注意点は厳密にはZendDBの仕様と言うよりも、PDOの仕様の注意点なのですが、
フレームワークを使っていたために原因の特定が難しかったということもあり、
ZendDBの使用者向けに書いています。


まず、プレースホルダとはクエリの以下のような「?」で記載することができる
動的変数のことです。
++++++++++++++++++++++++++++++++++++++++++
SELECT
    *
FROM
    table
WHERE
    id = ? -- ＜これ
++++++++++++++++++++++++++++++++++++++++++
通常は以下のように名前付きプレースホルダを使用します。
++++++++++++++++++++++++++++++++++++++++++
SELECT
    *
FROM
    table
WHERE
    id = :id -- ＜これ
++++++++++++++++++++++++++++++++++++++++++
で、問題なのがプレースホルダを記載できる箇所の制限が実はかなり多いということ。
例えば、MERGE テーブルのように同じレイアウトのテーブルがたくさんある場合に、
以下のように書きたくなるのですが、こういう書き方はできません。
++++++++++++++++++++++++++++++++++++++++++
SELECT
    *
FROM
    :table
++++++++++++++++++++++++++++++++++++++++++
理由は簡単でプレースホルダを展開すると以下のように
クォーテーション付きで展開されるからです。
++++++++++++++++++++++++++++++++++++++++++
SELECT
    *
FROM
    'table'

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use nea・・・
++++++++++++++++++++++++++++++++++++++++++
同じ理由からカラム名にも使用できません。
++++++++++++++++++++++++++++++++++++++++++
SELECT
    :id 
FROM
    table

↓以下のように展開される

SELECT
    'id'
FROM
    table
++++++++++++++++++++++++++++++++++++++++++
上記例の場合には以下のような結果が出力されます。

+----+
| id |
+----+
| id |
・
・
・
| id |
+----+
15 rows in set (0.00 sec)

よって、クエリとして使用可能な箇所が限られます。
ではクォーテーションが付いている状態で、実行できないようなSQL文は実行できないのかというと
そう言うことでもないらしく・・・

++++++++++++++++++++++++++++++++++++++++++
SELECT
    id
FROM
    table
LIMIT :start, :end

↓以下のように展開される

SELECT
    id
FROM
    table
LIMIT '0', '1'
++++++++++++++++++++++++++++++++++++++++++
上記クエリは、クエリとしてプロンプトから実行すると以下のエラーがでます。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '1'' at line 5

しかし、プログラム上から動かすと正しい結果が得られます。

++++++++++++++++++++++++++++++++++++++++++
-- 以下は正常に実行可能です
$sql = "
    SELECT
        id
    FROM
        table
    LIMIT :start, :end
";
$bind = array(
    'start' => 0, 'end' => 1
);
ZendDBのInstance()->fetchRow($sql, $bind);
++++++++++++++++++++++++++++++++++++++++++

他にもサブクエリの中のサブクエリで動かしたら、動かなかったりと
地雷的な要素が多い。
う～む。単純な機能の割には制約が多いなぁ。
制約が多いのは構わないのですが、その制約が情報としてまとまっていないことも
不信感が強まります。

数値でクエリを吐きたい時も文字列になってしまうのもパフォーマンス的に宜しくないですし、
なまじ中途半端に動いてしまう場合があるから、何が原因なのかがよけいわかりにくかったです。
これだけ不明瞭な制約が多いと自作したほうが良いと思える・・・

ちなみにZendだとプレースホルダしか使っていないのに「?」がどうとか表示されて
悩みました

PHP Zend_Db_Statement_Exception:  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?\n


暫定対処
一日寝て思いついたのですが、
そもそも名前付きプレースホルダを使用する理由は以下になります。
・動的にクエリを書きたい
・SQLインジェクションの心配をしたくない
・$select . ' id FROM' . $table のようなクォーテーションだらけの
可読性の低いプログラムを書きたくない。

->ヒアドキュメントがいいのではないだろうか。

ヒアドキュメントならばクォーテーションを意識しなくてよいし、
プレースホルダと変数を違和感無く書き分けることができるわけです。
(もちろんルールは厳密にする必要がありますが)

例
++++++++++++++++++++++++++++++++++++++++++
//ヒアドキュメント使用パターン
const $tableName = 'tableName';
$sql = ＜＜＜EOI
    SELECT
        *
    FROM
        {$tableName}
    WHERE
        id = :id
EOI;

$bind = array(
    'id' => $id
);
ZendDBのInstance()->fetchRow($sql, $bind);
++++++++++++++++++++++++++++++++++++++++++

上記のように、ユーザーが画面で入力した値は名前付きプレースホルダ。
それ以外の動的に変更したい箇所はヒアドキュメント内の変数に入れる形にすれば
可読性を損なうこと無く動的にクエリを書けます。

え？{$tableName}の所が、危険に見える？
確かにそうなのですが、そこはConstの値のみを入れるなどの
ルールを徹底させることにより回避しています。

とりあえず私は上記の方法でプログラムを行なっておりますが、
他に何かいい案が御座いましたら教えて頂ければ幸いです。