【注意点】[php] sprintf関数でSQL文を生成する時

今回の学習

sprintf関数を使ってLIKEやDATEFORMATなど%を使うSQL文を生成する時、%は%%としましょう。

参考にしたサイト
http://d.hatena.ne.jp/niconico_licoco+wu/20130117/1358398457

受け取った変数をエスケープするためにsprintf関数を用いてSQL文を作ってクエリに投げるといったやり方をしばしば行います。(私は基本的にこの方法で統一してます)

例えばこんな感じで使ってます(ユーザーの口座情報を取得するクエリ)

$sql = sprintf('SELECT a.name, u.id FROM user_accounts u 
                  JOIN accounts a ON u.account_id=a.id 
                  WHERE u.user_id=%d ORDER BY ID ASC',
    (mysql_real_escape_string($user_id))
);
$result = mysql_query($sql) or die(mysql_error());

ここからが困った時の様子

この時に、SQL文で%を使う場合(LIKEとか、DATEFORMATとか)があります。
例えば自分のアプリケーションではユーザーの今月の収入データを取得します

$sql = sprintf("SELECT SUM(income.amount) AS sum_income
   FROM income 
   WHERE user_id=%d
    AND date >= DATE_FORMAT(NOW(), '%Y-%m-01')
    AND date < ADDDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), interval 2 month)
                     GROUP BY income.user_id ", 
   mysql_real_escape_string($user_id)
);
$result = mysql_query($sql, $link) or die(mysql_error());

です。この時、上のようにsprintfを使って実行すると......

"Query was empty"

クエリがありませんと?! いやいや、んなまさか？
試しにエスケープしてた部分に適当に値を入れてデータベースに直接SQL文を実行すると値が返ってくる。

ん？ん？ん？ 普段ならこの直接SQLを打って間違ってる箇所洗い出して見つかるのだが、今回は分からない。

SQLをコーテーションで囲うだけにして単なる文字列として生成してみると...

$sql = "SELECT SUM(income.amount) AS sum_income
   FROM income 
   WHERE user_id=0
    AND date >= DATE_FORMAT(NOW(), '%Y-%m-01')
    AND date < ADDDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), interval 2 month)
                     GROUP BY income.user_id ";
$result = mysql_query($sql, $link) or die(mysql_error());

これは動く！

sprintf関数が原因とわかりまして、初めてsprintf＋MySQLで検索。
判明しました。sprintfの中で%は%%としなくてはいけないみたいです。%sとか%dで変換指定をするので、そのままじゃダメなのね。納得

ということで

sprintf()内で%を使いたいときは%%とする。

正解は

$sql = sprintf("SELECT SUM(income.amount) AS sum_income
   FROM income 
   WHERE user_id=%d
    AND date >= DATE_FORMAT(NOW(), '%%Y-%%m-01')
    AND date < ADDDATE(DATE_FORMAT(NOW(), '%%Y-%%m-01'), interval 2 month)
                     GROUP BY income.user_id ", 
   mysql_real_escape_string($user_id)
);
$result = mysql_query($sql, $link) or die(mysql_error());