数値リテラルをシングルクォートで囲むことの是非
高木浩光氏からの批判の一つは、数値リテラルをシングルクォートで囲むことに対する論拠のあいまいさであったように思う。
「性能的にも不利だし」 < 性能の影響は皆無。問題はそこではなく、挙動がSQL仕様で定義されているか。
以下にもう少し掘り下げて考察してみよう。
SQL仕様でどう定義されているか?
以下のようなSQLで、列IDがint型であると仮定しよう
DELETE FROM DOCUMENTS WHERE ID=1
この数値リテラル1をシングルクォートで囲んだ場合の動作はどう規定されているか?
DELETE FROM DOCUMENTS WHERE ID='1'
今手元にJIS SQLなどの規定がないので記憶に頼って書くしかないが、このような場合は、varchar型などの文字型から、int型への「暗黙の型変換」が実施される。つまり、'1'という文字列は、1という数値(整数)に変換されてからSQL実行される。
このケースでは、性能はともかく、両者の実行結果は同じとなる。
しかし、SQLインジェクションを問題にする場合、このような単純ケースではなく、もっと複雑な値が入っている場合について検討する必要がある。我が家のノートPCにインストールされているフリーDBのいくつかを利用して、実験をしてみた。
以下の表は、insert into ... valuesとselect文where句のint型項目について、数値や文字列を種々設定した場合の挙動をまとめたものである。
*1 ORA-01722: 数値が無効です。
*2 1.6と一致する項目がない
*3 varchar の値 '1.0' をデータ型 int に変換できませんでした。
*4 ERROR 1265 (01000): Data truncated for column 'num1' at row 1
*5 数値1と解釈される
*6 invalid input syntax for integer: "1.0"
SQLインジェクションの場合には、'1a'に相当するパターンのバリエーションがとりあえずは問題になるわけだが、多くの実装ではこの場合をエラーとしている。ただし、MySQLのWhere句の場合はエラーとならず、'1a'→1というような変換を実施しているように見受ける。ANSI/JISでこのような細かい規定があるかどうかは不明だが、実際の実装系での挙動は重要である。
もう一つ注目していただきたいことは、一口に暗黙の型変換といっても、実装によりかなりばらつきがあることである。MS SQLServerが保守的な解釈を行うのに対して、Oracleはかなり融通が利いている。開発現場の立場からは、このようなバラツキをアプリケーション実装に持ち込むのは、賢明とはいえないと思う。連載の中で、「プログラミング書法という点でも望ましくない」と書いたのは、このようなことを背景に考えてのことであった。
性能の影響はどうか?
次に、暗黙の型変換の与える性能的な影響について検討しよう。SQLの場合、性能的な問題は主に二つある(下記)。
高木浩光氏のコメントでは、「性能の影響は皆無」と断言しておられるが、そう単純でもないと思う。
私はPascalの言語処理系(Cabezon)を作った経験などから、構文解析にかかるコストは無視できるほど低くはないと考えている。加えて、SQLの場合は、サーバーサイドで、秒間数十から数百ものSQLをさばかなければならない場合も珍しくない。SQLそのものも、例題として紹介したような単純なものではなく、数ページにわたるような複雑なものも多い。
SQLの構文解析コストをOracleのようなデータベースベンダーが重視している傍証としては、構文解析結果をキャッシュとして保存しておく機能があることからもわかるだろう。
しかし、性能劣化の問題として説明しておかなければならないのは、むしろ検索実行に与える影響の方かもしれない。そんな馬鹿なと思われる方もあるかもしれないが、多くのデータベースでは暗黙の型変換を行うと、DBのインデックスが使用されなくなることが知られている(Googleで検索)。インデックスが使用されないことは、とんでもない性能低下を招くわけで、そのような可能性がある選択肢は、現場の開発エンジニアとしてはとりたくない。
本音を言えば
色々書いてきたが、本音を言えば、プログラマの本能として「暗黙の型変換」など使いたくないのである。C言語などで、さんざんこれには泣かされてきたし、ただでさえプログラミングには予期しない出来ことが起こるわけで、普段のプログラミングはできるだけ保守的に書いていくのが私のプログラミングスタイルなのである。ただ、そういうことはITproにはかけないし、これを読んでいる読書の方にも関係ないことだと思う。だから、理屈をつけているわけだが、こはブログなので、私の真情の吐露をお許しいただきたい。
数値リテラルをシングルクォートで囲みたくない理由を書いたところで、ではどう書けばよいのか、これは稿をあらためることにしたい。
