補足
この記事は旧徳丸浩の日記からの転載です。
元URL、
アーカイブ、
はてなブックマーク1、
はてなブックマーク2。
備忘のため転載いたしますが、この記事は2009年9月24日に公開されたもので、当時の徳丸の考えを示すものを、基本的に内容を変更せずにそのまま転載するものです。
補足終わり
このエントリでは、SQLにおいて「暗黙の型変換」を使うべきでない理由として、具体的な「ワナ」をいくつか紹介します。
数値項目に対するSQLインジェクション対策のまとめにて説明したように、RDBの数値型の列に対してSQLインジェクション対策をする方法として、以下の三種類が知られています。
- バインド機構を用いる
- パラメータの数値としての妥当性確認を行う
- パラメータを文字列リテラルとしてエスケープする
このうち、方法3を使うべきでない説明の補足です。具体的には、方法3には、「暗黙の型変換」が発生しますが、それが思わぬ事故を招く可能性が高いことを説明したいと思います。
方法3に従うと、最終的には以下のようなSQLを発行することになります。age列は年齢を示す列であり、整数型を想定しています。
SELECT * FROM sample1 WHERE age = '27'
挿入の場合は以下のようになります。
INSERT INTO sample1 (age) VALUES ('27')
これらに出てくる '27' という文字列リテラルは、SQL実行時に「暗黙に」型変換されます。
変換されるのは文字列型にか、数値型にか
暗黙の型変換では、プログラマは型の変換内容を明記しないため、RDB毎に持つ型変換ルールに従って型が決定されます。ISO/JISのSQLでは文字列型と数値型の「暗黙の型変換」は規定されていないため、データベースソフトウェア毎に処理系依存のルールがあります。
一番ありがちな以下のようなケースで考えてみましょう。数値型の列と文字列リテラルの比較です(ageは数値型の列)。
… WHERE age > '27'
左辺が数値型、右辺は文字列型です。この場合はどのように型変換されるのでしょうか。二種類の可能性があります。
- ageを文字列型に変換する
- '27'を数値型に変換する
どっちでも似たようなものと思われるかもしれませんが、この違いは重要です。処理結果とパフォーマンスに大きく影響するからです。結論としては、現在広く利用されているDBMS(MySQL、PostgreSQL、Oracle、MS SQL Server)では数値型に合わせられます。しかし、仮に文字列型にあわせるようなRDBMSがあったとすると、以下のような結果になります。
'9' > '27'
文字列の大小比較は辞書式順序に従うためです。
現実には、数値型にそろえる形で変換されるので、先のSQLは以下のように変形され、実行されます。
… WHERE age > 27
これはこれで大丈夫なのですが、こんどは列が文字列型、リテラルが数値型の場合で考えてみます。日本オラクル社にはかつて社員番号0を持つ社員犬「ウェンディ」がいたそうですので、彼女を検索してみましょう。
…WHERE employeeid = 0
社員番号を持つ列employeeidは、実際には文字列型であると想定します。この場合、列employeeidの方が数値型に変換されながら検索が実行されます。これはパフォーマンスの低下をもらたらします。インデックスが使用できないからです。実際問題として、数値型に変換後に 0 に一致する文字列は、「0」の他、「00」、「00000」など無数にあるため、そのようなインデックスは作成できないのです。結果として、文字列型の列と数値リテラルを比較すると、インデックスが使用されないためにパフォーマンスが低下します。
文字列型からどの数値型に変換されるのか
次に、文字列リテラルから数値型に「暗黙に」変換された結果は、どのような数値型になるのでしょうか。私が調べた範囲では、以下のような可能性があります。
- 浮動小数点数型になる
- 文脈に応じて柔軟に型が決定される
私が調べた範囲では、MySQLは常に 1.の浮動小数点数になり、PostgreSQL、MS SQL、Oracleは 2.の文脈に応じた型になるようです。あぁ、浮動小数点数と聞いただけでイヤな感じがしたあなた、あなたのイヤな感じは現実のものになります。以下、MySQL 5.1での実験結果を紹介します。まず、次のようなテーブルを用意します。
mysql> create table dtest0 (d0 decimal(20, 0));
Query OK, 0 rows affected (0.01 sec)
ご覧のように、十進20桁の列を一つ持つテーブルです。これに「文字列」を挿入してみます。
mysql> insert into dtest0 values('12345678901234567890');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dtest0;
+----------------------+
| d0 |
+----------------------+
| 12345678901234567890 |
+----------------------+
1 row in set (0.00 sec)
挿入においては、期待通りに動いています。念のため、以下のSELECTでも確認してみましょう。
mysql> select * from dtest0 where d0 = 12345678901234567890;
+----------------------+
| d0 |
+----------------------+
| 12345678901234567890 |
+----------------------+
1 row in set (0.00 sec)
問題ないですね。それでは、問題の「暗黙の型変換」を伴うパターンです。
mysql> select * from dtest0 where d0 = '12345678901234567890';
Empty set (0.00 sec)
あれ、見つからないですね。それでは、この列を挿入したらどうでしょうか。
mysql> insert into dtest0 values (12345678901234570000);
Query OK, 1 row affected (0.05 sec)
mysql> select * from dtest0 where d0 = '12345678901234567890';
+-----------------------+
| d0 |
+-----------------------+
| 123456789012345670000 |
+-----------------------+
今度は見つかりましたが、期待に反して、「12345678901234570000」という値がヒットしています。なぜでしょうか。
実は、WHERE句の実行に先立ち、'12345678901234567890'が浮動小数点数に変換されているからです。その様子を実験してみましょう。
mysql> select '12345678901234567890'+0;
+--------------------------+
| '12345678901234567890'+0 |
+--------------------------+
| 1.23456789012346e+019 |
+--------------------------+
1 row in set (0.00 sec)
0を加算することによって、数値への「暗黙の型変換」結果を表示させました。ご覧のように、浮動小数点数になっています。先の表示「12345678901234570000」とは末尾が少し異なりますが、これは丸めによるものです。このあたりが浮動小数点数のイヤラシイところですね。しかし、ここで示した挙動はMySQLのリファレンスマニュアルにちゃんと書いてあります。
次のルールは、比較の演算に対してどのように変換が行われるかを示しています :
- 一方か両方の引数が NULL の場合、比較の結果は、NULL-safe <=> 等値比較演算子以外は、NULL になります。NULL <=> NULL の場合、結果は true です。
- 【中略】
- 他のすべてのケースでは、引数は浮動少数点 ( 実 ) 数として比較されます。
[11.1.2. 式評価でのタイプ変換より引用]
ふつー、こんなとこまで読まないよと言いたくなりますが、「暗黙の型変換」を利用する場合は、このあたりのことも知った上で、SQLの実行結果を予測しなければならないということです。佐名木智貴氏が、「
セキュアWebプログラミングTips集(ソフト・リサーチ・センター)」の中で述べられた名文句を思い出します。
ぜひ読者諸氏には今一度、自分の使っているデータベース・ソフトウェアのSQLリファレンスを通読することを推奨する(同書P213)。
私はとても「リファレンスを通読」する根性はないので、できるだけ「安全第一」のプログラミングにより、予期せぬ挙動が入らないようにしています。「暗黙の型変換を避ける」というのもその一つで、セキュリティ上の安全にもつながると信じます。
他のDBMSはどうか
ここで、他のDBMSにも少し触れます。PostgreSQL、MS SQL Server、Oracleは、調べた範囲ではMySQLほどイヤラシイことにはならなかったのですが、油断は禁物です。先に、MySQLで実行した「'12345678901234567890'+0」をPostgreSQL(8.4)で実行すると、以下のようになります。
test=# select '12345678901234567890' + 0;
ERROR: 値"12345678901234567890"は型integerの範囲外です
行 1: select '12345678901234567890' + 0;
^
以下は、MS SQL Server(SQL Server 2008)の実行結果
varchar の値 '12345678901234567890' の変換が int 型の列でオーバーフローしました。
これらPostgreSQLとMS SQL Serverでは、文字列リテラルと数値型の演算に際して、演算対象の数値型に型をあわせているようです。PostgreSQLのマニュアルには以下の記述があります。
文字列リテラルに型が指定されていない場合、後述するように、後の段階で解決されるようにとりあえず場所を確保するための型であるunknownが割り当てられます
[10.1. 概要より引用]
a. 二項演算子の1つの引数がunknown型であった場合、この検査のもう片方の引数と同一の型であると仮定します。
[10.2. 演算子より引用]
これを確かめるため、以下の実験をしてみました。PostgreSQL8.4の結果を示しますが、MS SQL Server 2008でも同様の結果です。
test=# select '12345678901234567890' + cast(0 as decimal(20,0));
?column?
---------------------
12345678901234567890
(1 行)
数値0を decimal(20,0) という型にキャストしたので、文字列リテラルの方も同じ型にあわせてくれました。便利ですね…なんてことはないのであって、素直に以下のように書けばよいのです。
tokumaru=# select 12345678901234567890 + 0;
?column?
---------------------
12345678901234567890
(1 行)
DB2はどうか
Oracleは割合融通の利くRDBMSであり、暗黙の型変換についても期待したとおりに動いてくれます。一方、IBM DB2は、SQL仕様を厳格に実装しているという印象があり、従来「暗黙の型変換」を許していませんでした。これは、寺田氏(
id:teracc)が「
DB2の文字列→数値変換」として検証結果をまとめておられます。そこでは、DB2のV9.5とV8.2にて、文字列から数値への暗黙の型変換がエラーになることが示されています。私も、DB2 Express-C 9.5にて追試をして、同様の結果を確認しました。
ところが、最近DB2 V9.7（試用版）にて同様の確認をしたところ、V9.7に至って暗黙の型変換が認められるようになっていました。以下は、V9.5での結果。
db2 => select '1'+1 from dual
SQL0402N 算術関数または演算 "+"
のオペランドのデータ・タイプが、数値ではありません。 SQLSTATE=42819
続いて、V9.7での結果です。
db2 => select '1'+1 from dual
1
------------------------------------------
2
1 レコードが選択されました。
db2 => select '12345678901234567890'+0 from dual
1
------------------------------------------
12345678901234567890
1 レコードが選択されました。
まだ十分確認ができていないのですが、DB2 V9.7は、Oracleとの互換性をウリにしているため、暗黙の型変換もOracleの挙動に合わせたものではないかと想像しています。DB2のこの仕様変更は、暗黙の型変換の仕様がDBMS毎に異なるだけではなく、同一DBMSのバージョンによっても挙動が異なることを示す良い例だと考えています。
まとめ
SQLの「暗黙の型変換」による予期しない動作について紹介しました。暗黙の型変換の詳細仕様は実装依存であり、時として、利用者の想定以外の動作をする場合があり、危険です。暗黙の型変換を避け、型変換が必要な場合は、CASTによる明示的な変換を行うようにするべきです。予期しない動作の例として以下のようなものを説明しました。
- 検索時にインデックスが使用できずにパフォーマンスが低下する
- 暗黙の型変換により精度が損なわれる
- 暗黙の型変換により、型の範囲外になるというエラーが発生する
- DBのバージョンアップにより暗黙の型変換の仕様が変わる
というわけで、このエントリの結論は以下のようになります。
- 例えば、暗黙の型変換を避ける
- どうしても暗黙の型変換を使いたかったら、SQLリファレンスを通読してからにする（冗談です）
参考:
追記（2009/09/25）
IBM DB2の上記仕様変更について、
id:umqさんから
指摘を頂き、IBM DB2のリファレンスに以下の記述が追記されていることがわかりました。
ストリング・データ・タイプを使用するオペランドは、算術演算を実行する前に、CAST 指定の規則を使用して DECFLOAT(34) に変換されます。詳しくは、『データ・タイプ間のキャスト』を参照してください。このストリングには、数値の文字ストリング表記が含まれていなければなりません。
[DB2 Version 9.7 for Linux, UNIX, and Windowsより引用]
この記述は、
Version9.5のリファレンスには存在せず、Version9.7で追加されたことが分かります。また、DECFLOAT(34)という型は、十進浮動小数点数で34桁の精度を持つという意味ですので、DECIMAL型の31桁、BIGINTの19桁をカバーする数値型として選ばれているようです。なんだかIBMらしい、富豪的な仕様ですね。
ともあれ、DB2 Version9.7から「暗黙の型変換」が正式にサポートされたことがはっきりしました。やはり、「暗黙の型変換」などというマイナーな仕様は、いつの間にか仕様が変更されることは十分あり得るわけで、そのようなあやふやなものに依存するプログラミングは危険だということだと思います。
*1 社員番号を文字列型で保持することは業務システムでは広く行われます