そうしたところ、以下のご指摘をいただきました。
39番目のスライドは下記ですね。intへのキャストは下から3行目の (int) $id を指します。@ockeghem スライド拝見しました。39番目のスライドですが、バインドのタイミングでintにキャストするのはちょっと例として良くない気がします。意図的にオーバーフローを起こすことで想定外のレコードの取得を許してしまいそうです。キャストしない方がまだ安全だと思うのですが。— ちんぺい (@chinpei215) 2016年4月16日
SQLデータベースは、int型よりも大きな桁数を扱える場合があるので、intへのキャストを避けた方がよいという指摘は一般論としてはもっともなものだと考えます。PHPの場合、9223372036854775807を越える数字文字列をint型にキャストすると、9223372036854775807が返ります(64ビット環境の場合)。これを考慮していない場合、悪用される可能性はあります。
それにも関わらず、MySQLとPDOの組み合わせの場合、int型へのキャストが望ましい状況があります。その理由を説明します。$ php -r "var_dump((int)'999999999999999999999999999999');" int(9223372036854775807)
PoC
PDOのプレースホルダの挙動について、以下のサンプル(PoC)で紹介します。テーブル定義とデータ
CREATE TABLE xdecimal (id DECIMAL(20)); -- DECIMAL(20)は10進20桁の数値型 INSERT INTO xdecimal VALUES (18015376320243459); INSERT INTO xdecimal VALUES (18015376320243460); INSERT INTO xdecimal VALUES (18015376320243461);
PHPサンプル
<?php
$db = new PDO("mysql:host=127.0.0.1;dbname=test;charset=utf8", DBUSER, DBPASSWD);
$ps = $db->prepare("SELECT id FROM xdecimal WHERE id=:id");
$id = '18015376320243461';
$ps->bindValue(':id', $id, PDO::PARAM_INT); // intへのキャストはしない
$ps->execute();
$row = $ps->fetch();
echo "$id -> ${row[0]}\n";
$db = null;
このスクリプトはテーブル xdecimal からid=18015376320243461を検索して表示します。生成されるSQL文
上記スクリプトのプレースホルダにより生成されるSQL文は下記のとおりです。
SELECT id FROM xdecimal WHERE id='18015376320243461'
ポイントは、PDO::PARAM_INTと整数型を指定しているのに、文字列リテラル（赤字部分）として値が生成されているところです。MySQLと暗黙の型変換の問題
ここで問題は、列idの型がDECIMAL(20)という数値型なのに、文字列型の値と比較しているところです。MySQLは、この場合、両者を浮動小数点型に変換してから比較します。以下は、MySQL 5.6のリファレンスマニュアルから該当部分の引用です。次のルールでは、比較演算の際にどのように変換が発生するのかについて説明します。すなわち、列 id と、文字列リテラル'18015376320243461' の双方を浮動小数点数に変換してから比較することになります。
12.2 式評価での型変換（MySQL 5.6 リファレンスマニュアル）より引用
- NULL-safe <=> 等価比較演算子の場合を除いて、一方または両方の引数が NULL の場合は、比較の結果も NULL になります。NULL <=> NULL の場合は、結果が true になります。変換は必要ありません。
- 比較演算の両方の引数が文字列の場合は、文字列として比較されます。
- 両方の引数が整数の場合は、整数として比較されます。
- 16 進値が数字と比較されない場合は、バイナリ文字列として処理されます。
- 引数の一方が TIMESTAMP または DATETIME カラムで他方が定数の場合は、比較が実行される前に定数がタイムスタンプに変換されます。これは、ODBC により適合させるために実行されます。これは、IN() への引数には実行されません。念のため、比較を行う際は、常に完全な日付時間、日付、または時間文字列を使用してください。たとえば、日付または時間の値とともに BETWEEN を使用したときの結果を最適にするには、CAST() を使用して、明示的に値を目的のデータ型に変換します。
- テーブル (複数可) からの単一行のサブクエリーは、定数とみなされません。たとえば、サブクエリーで DATETIME 値と比較される整数が返される場合は、比較が 2 つの整数として実行されます。整数は時間値には変換されません。オペランドを DATETIME 値として比較するには、CAST() を使用して、明示的にサブクエリーの値を DATETIME に変換します。
- 引数のいずれかが 10 進値の場合、比較はその他の引数に依存します。その他の引数が 10 進値または整数値の場合、引数は 10 進値として比較され、その他の引数が浮動小数点値の場合、引数は浮動小数点値として比較されます。
- ほかのすべてのケースでは、引数は浮動小数点 (実) 数として比較されます。
PoCの実行結果
前記PHPスクリプトの実行結果は以下の通りです。$ php xdecimal.php
18015376320243461 -> 18015376320243459
18015376320243461を検索したのに、18015376320243459が返るという不思議な結果となっています。SQL文単体の実行では下記となります。
このような奇妙な結果となる原因は、MySQLの「引数は浮動小数点 (実) 数として比較されます」という仕様に起因します。浮動小数点数(倍精度)の仮数部の桁数は52ビット（暗黙のビットを足して53ビット、10進16桁弱）しかなく、18015376320243461という10進17桁の整数を正確に表現できないことが原因です。mysql> SELECT id FROM xdecimal WHERE id='18015376320243461'; +-------------------+ | id | +-------------------+ | 18015376320243459 | | 18015376320243460 | | 18015376320243461 | +-------------------+ 3 rows in set (0.00 sec)
バインド値を整数にキャストした場合
一方、bindValueでバインドする値をint型にキャストすると、生成されるSQL文と実行結果は下記となります。今度は浮動小数点数を経由しないため、正確な結果が返ります。先のスライドでint型へのキャストをいれていた理由は、このためです。mysql> SELECT id FROM xdecimal WHERE id=18015376320243461; +-------------------+ | id | +-------------------+ | 18015376320243461 | +-------------------+ 1 row in set (0.00 sec)
もっとよい方法はないか?
64ビット版のPHPを使った場合でも、int型の最大値は9223372036854775807なので、これを超えると冒頭の指摘のように不具合がおきます。この場合はどうしたらよいでしょうか?そもそも数値型を使わずに文字列型を使う方法もありますが、その場合は数値計算が出来ません。せっかくDECIMAL型は65桁までの十進数が使えるのにもったいないですね。
ちょっと面倒ですが、以下のように型変換を明示すれば、暗黙の型変換およびそれに伴う浮動小数点数への変換を防ぐことが出来ます。
SELECT id FROM xdecimal WHERE id=CAST(:id AS DECIMAL(20))実行結果は以下の通りです。大丈夫ですね。
mysql> SELECT id FROM xdecimal WHERE id=CAST('18015376320243461' AS DECIMAL(20));
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.01 sec)
まとめ
PDOのサンプルスクリプトで、バインド時に整数型の値をintにキャストしていた理由を説明しました。intへのキャストは桁あふれの危険性はあるものの、浮動小数点数への暗黙の型変換よりはマシという意味で、一種のバッドノウハウだと思います。MySQLの暗黙の型変換は本当にやっかいで、詳しくは下記の参考文献をお読み下さい。本当に望ましい書き方は、PHPスクリプトではなくSQL文側にキャストを書くことでしょうが、もっと良い方法があれば、ご教授下さい。
参考文献
- SQLの暗黙の型変換はワナがいっぱい
- PDOでの数値列の扱いにはワナがいっぱい
- PDOでの数値列の扱いにはワナがいっぱい（2）
- SQLインジェクションゴルフ - なんと3文字で認証回避が可能に
- とある診断員とSQLインジェクション
蛇足
実は、先のid型にインデックスをつけた場合は、先ほどとは挙動が変わります。
mysql> ALTER TABLE xdecimal ADD INDEX(id);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT id FROM xdecimal WHERE id='18015376320243461';
+-------------------+
| id |
+-------------------+
| 18015376320243461 |
+-------------------+
1 row in set (0.00 sec)
インデックスの有無で挙動が変わるのは、MySQLのバグではないかと思いますが、この「バグ」を修正するのは中々やっかいだなと思います。【HASHコンサルティング広告】
HASHコンサルティング株式会社は、ウェブアプリケーションのセキュリティに関心のあるセキュリティエンジニアを募集しています。
興味のある方は、twitterやfacebookのメッセージ、あるいは問い合わせページからお問い合わせください。
