憂鬱なSQLのためのアレ、またはPDOと仲良くして枕を高くしてねむる #PHP

人間は皆誰しも実行時にSQLを組み立ててデータを取り出すことが大好きだ。そしてその行為の危険さもわからぬまま人類は太陽に向かってゆく。

あとこの記事で紹介してるライブラリは私が書いたコードじゃないですヾ(〃＞＜)ﾉﾞ

はじめに

まあ落ち着いて、去年のPHPカンファレンスの徳丸先生¹のスライドを読んでほしい。ちなみに私は去年のPHPカンファレンスは風邪でダウンしてたので行けなかった。QiitaにはSlideShareの埋め込みができないので、↓の枠はただの画像リンクだ。

Phpcon2015 from Hiroshi Tokumaru

「うちはO/Rマッパーを使ってるよ」ってひとには、この記事はかんけいない。そう、人類はDoctrineのような叡智を手にしてる。素直に素晴らしいMVCモデルやDBレイヤーを持ったフレームワークを選ぶべきだ。私は使ったことがないので無責任な発言はできないが、Laravelのクエリビルダーもなかなか使ひ勝手は良さそうに見える。

PHPとDB/SQL

それでも人類は、自分の手でSQLを書きたがる。PDOを使って。そんなときは必ず以下の記事の内容を頭に叩き込まなければならない。

PHPでデータベースに接続するときのまとめ - Qiita by @mpyw

世の中には「フレームワークとかよくわからないし、シンプルなPHPで…」とおっしゃる初心者の型も居るが、そんなひとには「急がば回れ」との名せりふを贈りたい。たぶん有名なフレームワークに乗った方が安全だよ²。

あ、PDO(PHP Data Objects)を使ってないひとは、これ以上この記事を読む意味はあんまりないです。

PDOのいいところ

文字列とかをいい感じにエスケープしてくれるところ。

$stmt->bindValue(':user', $user, \PDO::PARAM_STR);

PDOのだめなところ

可変個のパラメータに弱すぎる… つまり、IN修飾子。

SELECT * FROM `users` WHERE `id` IN (123, 343, 893, 114, 514)

この形式をPDOでプレースホルダに埋め込む方法は当然あるのだけれど、ここでは紹介しない。

TetoSQL

Packagistに登録済みなので、Composer導入済みの環境でcomposer require zonuexe/tetosqlを実行すれば導入可能。

このライブラリはSQLにバインドする値の実行時型検査を行った上でPDOの機能をラップするあたりが特徴で、SQLインジェクション脆弱性に一定の効果はあるだろう。配列をサポートすることでIN修飾子も問題なく利用できる。

御託はいいので利用例を見せる。

<?php
namespace MyApp;
use Teto\SQL;

const find = '
 SELECT * FROM `users`
 WHERE `status` IN (:statuses@int[]) 
 LIMIT :offset@int, :limit@int
';
$conn = new \PDO('sqlite:/tmp/db.sq3', null, null, [PDO::ATTR_PERSISTENT => true]);
$data = Query::execute($conn, find, [
    ':statuses' => [1, 3],
    ':offset'   => 60,
    ':limit'    => 30,
])->fetch(\PDO::FETCH_ASSOC);

おてがるですね。業務では基本的にMySQLを使ってるが、おそらくSQLiteなどのDBでも動作するだろう。

対象DB

このライブラリはもともとMySQLで利用するために書かれたが、PDOがサポートするDBであればおよそ動作するだろう。ただし、後述するINTの範囲により意図しない動作をすることはできる。

SQL文

現在の仕様では、TetoSQLが受け入れるSQL文はすべて改行コードがLFであることを期待し、これをすべて取り除く。変数が代入される前に処理するため、入力する値から改行コードが取り除かれることはない。この仕様は要望があれば将来的に変更することはありうるが、強い希望がなければ特に変更されることはないだろう。

念のために断っておくが、このライブラリはSQLの構文について干渉しない。実行結果が妥当なSQL文になることは保障しない。文字列のクオーテーションについてはPDO::quote()の仕組みによって各SQLドライバがいい感じにしてくれるだろう。たぶん。

変数

TetoSQLは「SQL」と名はつくが、実際はただのテンプレートシステムである。SQLの構文の妥当性については一切保障しない。変数名は:nameのように、かならず:が前置される。また、SQL文の中では:name@stringのように型が明示される。

変数はSQL文中で複数回展開することができる。この仕様は、たとへばMySQLのINSERT 〜 ON DUPLICATE KEY UPDATEなどに利用できる。

型

TetoSQLには型の概念がある。バインドする値は必ず型を記述する必要がある。

@int: -9223372036854775808から9223372036854775807の範囲の整数値
@string: 文字列
@int[]: 複数の@int値
@string[]: 複数の@string値
@ascdesc: ASCとDESC (ORDER BYの昇順/降順)

intは、いはゆる符号付きBIGINTと一致する。また、64bit環境におけるPHP_INT_MAX, PHP_INT_MINの範囲とも一致する。

[]が付いた型表記は,で区切って展開される。この型表記が期待するのは単純なリストであり、いはゆる連想配列を期待しない。典型的な利用例はWHERE 〜 INである。

PDO

TetoSQLはPDOまたは、PDOと同じインターフェイスを持つクラスを受け取る。PDOクラスを独自実装するためにPDOInterfaceを用意したが、タイプヒントは指定していないので、PDOと同様の動作をする最低限のメソッドがあれば問題ないだろう。

PDOStatementクラスについても同様で、PDOStatementっぽい振舞をするならば動作は期待できる。PDOStatementInterfaceも用意したが、特に利用は必要ない(現在のところ)。

実用例

インスパイヤされて掲示板を作りたくなった(1)シリーズとかで使ってます… が、最近全然更新できてなかったのでそろそろ再開するよヾ(〃＞＜)ﾉﾞ☆

未来

私、実はO/Rマッパーってしっくりこないんです… って感じはあるので、それなりにメンテナンスとか機能追加とかしてく気がします。ｷｶﾞﾑｲﾀﾗﾈ…

脚注

「徳丸本」の作者として有名な型です ↩
私は「既存のWAFってしっくりこないんです…」と言って設計を始めてしまったけど ↩