変数バインディングを使ってもSQLインジェクション対策にならない
気がつけば4半期に一度更新するかどうかという状況になっています。
書きたい事があっても、いざ書こうと思っていろいろ調べていると新事実が次々と浮かび上がり、結局書き終えるのに丸一日かかってしまったりして、挙げ句でき上がった文章があれなのでその出来映えに泣きたくなり、結局更新から遠ざかってしまうのです。
そうこうしているうちに、書こうと思っていた事と近い内容の記事をid:t_komuraさんの所で見つけたので思わずコメントしてしまいました。
すでにid:hoshikuzuさんによるまとめもありますが、コメントだけなのもあんまりなので敢えて書こうと思います。びっくりするほど時期逃し過ぎ。
MySQL 4.1以降での文字の扱い
MySQLはバージョン4.1以降で文字の扱いが大きく変わりました。
それまでのMySQLは、クライアント側で使っている文字(バイトの並び)がそのままDBに格納され、取得するとそのまま返ってくるという非常に単純な挙動*1でした。従って、クライアント側で使用している文字エンコーディング(符号化方式)がDBで使用する文字エンコーディングと異なる場合は、クライアント側でDBに合わせて変換を行う必要がありました。
しかし、MySQL 4.1以降ではサーバ側とクライアント側にそれぞれ文字エンコーディングが指定できるようになり、ちゃんと設定すればサーバが透過的に変換してくれるので、クライアント側で事前に変換をする必要が無くなったのです。
ここまでなら便利な機能が増えて良かった良かったとなるのですが、現実はそうも行かないのでした。
MySQLはサーバもクライアントもデフォルトでlatin1という文字コード(?)を使用します。latin1というのは名前の通りの文字コードで、漢字とかはからっきしダメです。MySQLサーバで何も指定せずにDBを作るとその中のテーブルでは基本的にlatin1を使う事になりますので、日本向けのサービスでMySQLを使用するなら、大抵はujis(EUC-JP),sjis(Shift_JIS),utf8(UTF-8)のどれかを指定してDBを作ります。
DBの文字エンコーディングをUTF-8にして、DBサーバに対してクライアントとなるアプリケーションからUTF-8のINSERT SQLを発行した場合、問題なく動きそうですが、MySQLのクライアントは何も設定していなければSQLの文字エンコーディングがlatin1だとサーバに通知するので、サーバではlatin1からDBの文字エンコーディングであるUTF-8へ変換するルールを送られてきたUTF-8に適用してしまい、大抵の場合文字化けしてグチャグチャになります。
4.1より前のバージョンではこういう変換は行われなかったため、その時代に書かれたアプリケーションの中には動かなくなるものもあり対策が必要になりました。
その対策としては
- SET NAMESを使う
- MySQL 4.1.15以降のskip-character-set-client-handshakeオプションを使う
- クライアントライブラリを再コンパイル
- MySQL 3.xクライアントライブラリを使う
などがあります。
SET NAMESはサーバへの接続後に実行できるステートメントです。接続後にSET NAMES utf8;等と実行すると、クライアント側の文字エンコーディングとしてUTF-8が使えるようになります。これが一番の正攻法になると思いますが、アプリケーションないしライブラリの変更が必要です。
skip-character-set-client-handshakeはこの問題を解決すべく追加されたオプションです。サーバをこのオプション付きで起動すると、クライアントから送られてきた文字エンコーディングの指定を無視し、DBに設定されている文字エンコーディングを使うようになります。サーバを起動し直すだけで良く、アプリケーションやライブラリの変更は必要ありません。
クライアントライブラリを文字エンコーディング指定付きで再コンパイルすると、デフォルトをlatin1から変更できるそうです。アプリケーションの書き換えは必要ありませんが、そこかしこを再コンパイルしなきゃならなくなったりする諸刃の剣。素人にはお勧めできない(ので試していません)。
MySQL 3.xのクライアントライブラリを使用すると、変換を行わないような、skip-character-set-client-handshakeに似ているような微妙な効果が得られます。しかしこういう使い方が想定されているかどうかはよくわからないので玄人にもお勧めできません。
古いアプリケーション向けには、これらの対策を行う必要があります。また、新しくアプリケーションを作る場合でもこの辺の事を意識しなければなりません。
例えばODBCを使っていた場合、対策は簡単で「接続後に実行するSQL」の部分にSET NAMESを書いておけば良いのです。基本的にアプリケーションの変更は不要です。
例えばDBを抽象化するライブラリを使っていた場合、接続後にSET NAMESを実行するように変更すれば良いのです。文字エンコーディングとして渡す値をどこかで指定する必要がありますが、アプリケーションの変更は少なくて済みます。
そして我らがPHPの場合ですが、PHPでは言語自体に用意されている関数を直に使うのが一般的になっています。「PHP MySQL」で検索するとmysql_connect()を使っているサンプルばかりが出てきて、恐らく標準的なライブラリであるはずのPEARの事はなかなか出てきません。
アプリケーション開発者が独自にDB関連の処理を抽象化していたり、せめて接続部分だけでも関数化してあればSET NAMESを発行する処理の追加は楽なのですが、あくまで個人的な感覚として、PHPで書かれたスクリプトでそういった事に気を配っているものは少ないような気がします。文化的なものでしょうか。
そうなると接続直後にSET NAMESを発行するには全部のmysql_connect()を探すことになり、ソースファイルが多ければずいぶんと大変な作業になります。
そして思うのです。これからはPEAR::DBを使おうと。
mysql_real_escape_string()
SQLインジェクションを防ぐには
のどちらかを行うようにという話はそこかしこで見られるわけですが、MySQL+PHP4を使う立場から言わせてもらうと、変数バインディングは使いたくても使えないんです。
MySQLはどのバージョンからかは知りませんが変数バインディングを使えるようになりました。しかしPHPから使うにはmysqliをサポートしたPHP5以降が必要なのです。しかし色々な事情があり、簡単にはバージョンアップに踏み切れません。
そこで登場するのがPEAR::DBです。PEAR::DBにはprepareメソッドが用意されていて、PHPが対応している各種DBについて変数バインディングがサポートされていればその機能を使い、サポートされていなければ擬似的に変数バインディングが行えるようになっています。
これは便利だというわけで喜んで使っていたのですが、開発も終盤に差し掛かったある日、いやな事に気づいたのです。特定の文字を使うとSQLが構文エラーになる事に。
PEAR::DBでは変数バインディングが使えないmysqlでは「?」などの置き換え文字に、値をmysql_real_escape_string()でエスケープした上で割り当てています。
そして、ここで開発していたのは諸般の事情によってShift_JISのSQLを発行するアプリケーションでした。
さて、id:t_komuraさんの所に書いたコメントですが、
mysql_real_escape_string()でもSET NAMES sjis;等を実行した後に使うと2バイト目の0x5C文字がエスケープされないようです。PEAR::DBのDB_common::prepare() ではMySQL使用時は内部でmysql_real_escape_string()が使われるので、エスケープされない場合がありました。確か環境は PHP 4.4.1/MySQL 4.1.12です。SET NAMES binary;でなんとか回避してます。
自分で書いておいてなんですが、一部間違いがありました。
恥ずかしながら今まで知らなかったのですが、Shift_JISに設定されたMySQLサーバに対してShift_JISの
INSERT INTO goods (name) VALUES('勤務表\');
というクエリを発行しても構文エラーになるんですね。
Shift_JISに設定されたサーバを使う事が無かったので気にしていなかったのですが、「表」という0x5Cを含む文字であってもShift_JISとして認識される文字であれば2バイトを1文字として扱う事を今朝知りました。(*´д｀*)ハズカシイ。
ですので、さっきのSQLは正しくは
INSERT INTO goods (name) VALUES('勤務表');
になるはずです。
知らなかったおかげで、PHPとMySQLのエスケープ関数部分のソースを見ながら、マルチバイト文字がエスケープされずに通過してしまう事に「おかしい…」と三日三晩悩むはめになりました。
実際、Shift_JISで書かれた
SET NAMES sjis;
INSERT INTO goods (name) VALUES('勤務表');
このSQLはMySQL4.1.11-Debian_4sarge2に対して問題なくINSERTが行えます。
PHPのmysql_real_escape_string()関数は、libmysqlclientに用意された関数を呼んでいて*2、引数に取ったMySQLへのリンクに応じて適切にエスケープを行う関数だと説明されています。
mysql_escape_string()という関数も存在するのですが、古いサーバに対してSQLを発行するのでなければmysql_real_escape_string()を使うようにとFAQに書かれています。
確かに、Shift_JISの「表」はmysql_escape_string()では「表\」となってしまいますので、mysql_real_escape_string()を使わないとエラーになるばかりか、場合によってはSQLインジェクションが可能になってしまいます。
Shift_JISに設定されたリンクと「表」をmysql_real_escape_string()に渡せば「表」と返ってくるはずです。
検証コードを書いてみました。エラー処理とかは適当です。
$link = mysql_connect("server", "user") or die("can not connect.");
mysql_select_db("escape_test", $link);
mysql_query("SET NAMES sjis", $link);
// kinmuhyo
$source_value = urldecode("%8B%CE%96%B1%95%5C");
$value = mysql_real_escape_string($source_value, $link);
$result = mysql_query("INSERT INTO goods (name) VALUES(" . $value . ");", $link);
if(!$result) {
print mysql_error($link);
}
else {
print "OK";
}
mysql_close($link);
動かしてみたところ、MySQLにsyntax errorと言われました。
$valueの値を表示させてみたら、「表\」と表示されました。
えーと…。
PHPのソースを見ると、mysql_real_escape_string()から呼ばれている処理の実態となるmysql_sub_escape_string()のマルチバイト文字処理の部分は
#ifdef USE_MB
ほげ
endif
と書いてあります。
Cは詳しくないんですが、#ifdefからendifまではUSE_MBが定義されている場合にのみ評価されるって事くらいなら知ってます。
USE_MBっていうのはmbstringの事でしょうか？
そういえば、検証したPHPは事情があってmbstringを有効にしてなかったのでした。
うわー！また恥ずかしい！「Hello world!」からやり直せ自分！
と思いながらPHPをenable-mbstringを付けてmakeし直して先の検証コードを実行したら、またsyntax errorで「表\」でした。orz
USE_MBを追いかけたら、定義しているファイルは
php-4.4.2/ext/mysql/libmysql/config-win.h
だけでした。で、そのconfig-win.hはphp-4.4.2/ext/mysql/libmysql/global.hで_WIN32,_WIN64,__WIN32__,WIN32のどれかが定義されている時のみ読まれるみたいなんですが、これで良いんでしょうか。
結局、現時点ではPEAR::DBのmysqlで使える擬似的な変数バインディングは、中身を良く理解して使わないと危険という事で、そういう事で良いんでしょうか。
苦手なCで、直接libmysqlclientのmysql_real_escape_string()を呼ぶPHPの検証コードとほぼ同じ内容のプログラム*3を書いて実行してみたところ、こちらも「表\」でした。
だれかたすけてください
