MySQL の SQL エスケープ

この記事は MySQL Casual Advent Calendar 2013 の15日目の記事です。

今、空前の SQL エスケープブームみたいなので、このビッグウェーブに乗っかってみます。

でも面倒なのでセキュリティについての話はしません。カジュアル！

文字列リテラルとエスケープ

MySQL では SQL 中の文字列リテラルは次のように表現します。

'abc'      -- シングルクォートで括る
"abc"      -- ダブルクォートで括る
0x616263   -- 16進数
x'616263'  -- 16進数
0b011000010110001001100011  -- 2進数
b'011000010110001001100011' -- 2進数

各表記で charset を指定することができます

_utf8 'abc'
_utf8 "abc"
_utf8 0x616263
_utf8 x'616263'
_utf8 0b011000010110001001100011
_utf8 b'011000010110001001100011'

通常は16進数や2進数を使用することはあまりないでしょう。

シングルクォートとダブルクォートの表記では、「\」を使用することでで通常の文字として記述できないコードを指定できます。

\0   -- 0x00 (NUL)
\'   -- 「'」
\"   -- 「"」
\b   -- 0x08 (BS)
\n   -- 0x0A (LF)
\r   -- 0x0D (CR)
\t   -- 0x09 (TAB)
\Z   -- 0x1A (Windows EOF)
\\   -- 「\」
\%   -- 「\%」
\_   -- 「\_」

この「\」をエスケープ文字と言い、「\」を使用して文字を表記することを「文字をエスケープする」と言います。

なお、この他にも「'」中で「''」(シングルクォートを2つ)と記述すれば「'」になります。同様に「"」中で「""」(ダブルクォートを2つ)と記述すれば「"」になります。

変数埋め込み

普通に SQL を記述するときは上記に気をつけて文字列を記述すれば良いのですが、プログラム中で変数の値、特に何が入っているかわからない値を埋め込む時には注意が必要です。

char *value = "ほげ";
sprintf(sql, "SELECT '%s'", value);
//=> SELECT 'ほげ'

上の例の場合は問題ないのですが、value の値に「'」「"」「\」が入っていると正しい SQL が生成されません。

char *value = "ほ'げ";
sprintf(sql, "SELECT '%s'", value);
//=> SELECT 'ほ'げ'

value 中の「'」「"」「\」の前に「\」を置けば問題ないはずです。

char *escape_special_char(char *value)
{
  char *outbuf;
  int i, j;

  outbuf = malloc(strlen(value) * 2 + 1);
  for (i=0, j=0; i < strlen(value); i++) {
    if (value[i] == '\'' || value[i] == '"' || value[i] == '\\')
      outbuf[j++] = '\\';
    outbuf[j++] = value[i];
  }
  outbuf[j] = '\0';
  return outbuf;
}

...

char *value = "ほ'げ";
buf = escape_special_char(value);
sprintf(sql, "SELECT '%s'", buf);
//=> SELECT 'ほ\'げ'
free(buf);

しかしこれはやってはいけないダメな例です。

文字コード

文字コードが UTF-8 等の場合は上の例でも問題ないのですが、シフトJISみたいにマルチバイト文字中に「\」を含む文字があるような文字コードの場合は、もう少し考慮が必要です。

そのような文字コードで、マルチバイト文字を文字単位ではなくバイト単位で処理してしまうと、文字中の「\」まで置き換えてしまいます。

たとえば「表」という文字はシフトJISでは 0x95 0x5c の2バイトで表され、2バイト目の 0x5c が「\」です。「表計算」という文字列を上のプログラムで処理すると「表\計算」となり、元々存在しないはずの「\」が現れてしまいます。

つまり、上のように escape_special_char() のような関数を独自に作って「\」を処理するのではなく、文字列の文字コードにあわせて処理を行う必要があります。

MySQL の C API には特殊文字をエスケープするための mysql_real_escape_string() という関数が用意されているので、それを使用すべきです。

MySQL の API は初期化時にどの文字コードを使用するか指定できます。mysql_real_escape_string() はその文字コードにあわせて処理を行います。

なお、MySQL の API には mysql_escape_string() という関数もありますが、これは使用してはいけません。文字コードを意識しないので適切な処理が行えないためです。

シフトJISで2バイト目に「\」が含まれる文字には以下のようなものがあります。割とよく使われる文字も含まれてますね。

ソ 噂 浬 欺 圭 構 蚕 十 申 曾 箪 貼 能 表 暴 予 禄 兔
喀 媾 彌 拿 杤 歃 濬 畚 秉 綵 臀 藹 觸 軆 鐔 饅 鷭

接続用の文字コード

もうひとつ気をつけないといけないのは、MySQL との接続に使われる文字コードです。

SET NAMES 命令で文字コードを変更すると、接続用文字コードは変更されますが、mysql_real_escape_string() が使用する文字コードは変更されません。

もし、接続中に文字コードを変更した場合は、SET NAMES ではなく mysql_set_character_set() を使用しなければいけません。エスケープ用の文字コードと接続用の文字コードが異なると正しく処理できません。

たとえ文字列がバイナリデータであってもエスケープする際には接続用の文字コードとしてエスケープする必要があります。たとえば、バイナリデータ「0x95 0x5c 0x6e」を latin1 としてエスケープすると、「0x95 0x5c 0x5c 0x6e」になりますが、接続の文字コードがシフトJISだと、MySQL サーバーは「表\n」と解釈して、「0x95 0x5c 0x0a」として扱われてしまいます。

まとめ

文字列中で使用するためにエスケープしないといけない文字があります。
エスケープする場合は文字コードを意識する必要があります。
エスケープする際の文字コードと MySQL の接続用文字コードを合わせる必要があります。

今回は MySQL の C API を例に使用しましたが、どのプログラミング言語であっても同様に注意しなければいけません。面倒な人は、16進数表記リテラルとかプリペアドステートメントを使うのがいいと思います。

明日 12/16 は @Yuryu さんです。