今回の学習
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());
