MySQL/Rubyにおける正しいエンコーディング変更方法
MySQLの文字化けを直したい！
発端はSequelを使ってMySQLのデータを操作するRubyスクリプトを書いていたときでした。
UTF-8で保存したはずの文字列が、妙に文字化けしています。
$ mysql -h host -u user -p database ...(略)... Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> \s -------------- mysql Ver 14.14 Distrib 5.1.30, for portbld-freebsd7.1 (i386) using 5.2 ...(略)... Server characterset: ujis Db characterset: utf8 Client characterset: ujis Conn. characterset: ujis ...(略)... mysql>
データベースの文字コードがutf8で、それ以外が全てujisになっています。
自分で管理しているサーバであれば、ここでmy.cnfを編集し、[mysqld]セクションや[client]セクションにdefault-character-setを指定してやれば良いのですが、あいにく今回はさくらのレンタルサーバを使用しているため、編集する権限がありません。
SET NAMESを使ってみたものの
まず最初に試したのは"SET NAMES"ステートメントでした。
- MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.4 接続のキャラクタセットおよび照合順序
- MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.3 SET 構文
$ irb -r sequel irb(main):001:0> mysql = Sequel.connect('mysql://user:password@host/database') => #<Sequel::MySQL::Database: "mysql://user:password@host/database"> irb(main):002:0> mysql << 'SET NAMES utf8' => nil irb(main):003:0> mysql.fetch("SHOW VARIABLES LIKE 'character_set_%'"){ |x| p x } {:Variable_name=>"character_set_client", :Value=>"utf8"} {:Variable_name=>"character_set_connection", :Value=>"utf8"} {:Variable_name=>"character_set_database", :Value=>"utf8"} {:Variable_name=>"character_set_filesystem", :Value=>"binary"} {:Variable_name=>"character_set_results", :Value=>"utf8"} {:Variable_name=>"character_set_server", :Value=>"ujis"} {:Variable_name=>"character_set_system", :Value=>"utf8"} {:Variable_name=>"character_sets_dir", :Value=>"/usr/local/share/mysql/charsets/"} => #<Sequel::MySQL::Dataset: "SHOW VARIABLES LIKE 'character_set_%'"> irb(main):004:0>
実際、この状態でSELECTやINSERTを行うと、きちんとUTF-8のデータが扱えます。
SET NAMESは危ない？
めでたしめでたし…といきたいところなのですが、どうも色々とググってみると、この方法はあまりよろしくないようです。
- SET NAMESは禁止
- へぼへぼCTO日記 - libmysqlclientを使うプログラムはset namesをutf8であっても使ってはいけない
- MySQL5開拓団 - 日本語処理の鉄則 / KLab株式会社
- tmtm日記(2006-06-09) [MySQL][Ruby] セキュアなプログラミング
詳細は上記のサイトなどを参照してもらうとして、簡単に説明すると、「SET NAMESによるエンコーディングの変更はクライアント側の状態に影響を及ぼさないため、mysql_real_escape_string()*1などによるエスケープ処理が正しく行われなくなる」ということのようです。
つまり、前述の例だと、サーバもユーザプログラムもデータをutf8としてやり取りしているのに、クライアントライブラリ内で文字列をエスケープする際はujisとしてエスケープ処理が行われてしまうわけです。
utf8とujisならまぁ特に問題も無さそうですが、上記参考サイトの説明にもある通り、ここにShift-JISが絡んでくると色々まずいですね。\x5cとか。
それに上記サイトには「UTF-8であっても油断してはいけない」とありますので、より「正しい方法」を探してみることにしました。
SET NAMESの代替手段
先程のサイトによると、SET NAMESの代替手段としては、設定ファイルの変更などを除くと、MySQL C APIで言うところの
という2つの方法があるようです。
実際にMySQL C APIのリファレンスマニュアルを見てみても、それ以外に方法はなさそうでした。
Sequelが内部で利用しているMySQL/Rubyは、ほぼそのままMySQL C APIのラッパーとなっていますが、若干古いためか、mysql_set_character_set()に相当する機能が存在しません。
ということは、残る手段はmysql_options()に限られるわけです。
mysql_options()を使ってみる
Sequel及びMySQL/Rubyのソースを軽く読んでみたところ、Sequel.connectのオプション引数として:encodingあるいは:charsetパラメータを渡せば、それがそのままMySQL C APIのmysql_options(MYSQL_SET_CHARSET_NAME)として実行されるようです。
ということで試してみました。
$ irb -r sequel irb(main):001:0> mysql = Sequel.connect('mysql://user:password@host/database', :encoding => 'utf8') => #<Sequel::MySQL::Database: "mysql://user:password@host/database"> irb(main):002:0> mysql.fetch("SHOW VARIABLES LIKE 'character_set_%'"){ |x| p x } {:Variable_name=>"character_set_client", :Value=>"ujis"} {:Variable_name=>"character_set_connection", :Value=>"ujis"} ...(略)... => #<Sequel::MySQL::Dataset: "SHOW VARIABLES LIKE 'character_set_%'"> irb(main):003:0>
…あれ？変わっていないですね。実際にSELECTやINSERTをしてみても、やっぱり文字化けします。
Sequelが悪いのかと思い、直接MySQL/Rubyを呼び出して同じ操作をしてみました。
$ irb -r mysql irb(main):001:0> mysql = Mysql.init => #<Mysql:0x2876711c> irb(main):002:0> mysql.options(Mysql::SET_CHARSET_NAME, 'utf8') => #<Mysql:0x2876711c> irb(main):003:0> mysql.real_connect('host', 'user', 'password', 'database') => #<Mysql:0x2876711c> irb(main):004:0> mysql.query("SHOW VARIABLES LIKE 'character_set_%'").each_hash { |x| p x } {"Variable_name"=>"character_set_client", "Value"=>"ujis"} {"Variable_name"=>"character_set_connection", "Value"=>"ujis"} ...(略)... => #<Mysql::Result:0x2869d424> irb(main):005:0>
やっぱり変わりません。
Cで直接mysql_options()を使ってみる
もうこうなったら、Cで書いて直接MySQL C APIを叩くしかありません。
mysql_encoding.c
#include <stdio.h> #include <mysql.h> int main(int argc, char **argv) { MYSQL mysql; MYSQL_RES *result; MYSQL_ROW row; unsigned int num_fields; unsigned int i; unsigned long *lengths; mysql_init(&mysql); mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8"); mysql_real_connect(&mysql, "host", "user", "password", "database", 0, NULL, 0); //mysql_query(&mysql, "SET NAMES utf8"); // … (A) //mysql_set_character_set(&mysql, "utf8"); // … (B) mysql_query(&mysql, "SHOW VARIABLES LIKE 'character_set%'"); result = mysql_use_result(&mysql); num_fields = mysql_num_fields(result); while ((row = mysql_fetch_row(result))) { lengths = mysql_fetch_lengths(result); for (i = 0; i < num_fields; i++) { printf("[%.*s] ", (int)lengths[i], row[i] ? row[i] : "NULL"); } putchar('\n'); } mysql_free_result(result); mysql_close(&mysql); return 0; }
実際にはエラー処理も入れていますが、長くなるので省いています。
$ gcc -Wall -O2 -lmysqlclient -I/usr/local/mysql/5.1/include/mysql -L/usr/local/mysql/5.1/lib/mysql mysql_encoding.c -o mysql_encoding $ ./mysql_encoding [character_set_client] [ujis] [character_set_connection] [ujis] ...(略)...
やっぱりダメです。
しかし、mysql_encoding.cのソース中にある(A)や(B)のコメントアウトを外すとちゃんとエンコーディングがutf8にセットされることから、サーバもクライアントも正しく動いていないわけではなさそうです。
mysql_options()のソースを読んでみる
どうもmysql_options(MYSQL_SET_CHARSET_NAME)だけではエンコーディングは切り替わらないような気がしてきました。
若干面倒ですが、仕方ないのでMySQLのクライアントライブラリのソースを読んで調べてみます。
下のページからMySQL 5.1.38のソースをダウンロードしてきて、適当な場所に展開します。
問題のコードはlibmysql/client.c内にあるようです。
まずmysql_options()が実際に何をしているのか見てみます。3074行目から。
int STDCALL mysql_options(MYSQL *mysql,enum mysql_option option, const void *arg) { DBUG_ENTER("mysql_option"); DBUG_PRINT("enter",("option: %d",(int) option)); switch (option) { ...(略)... case MYSQL_SET_CHARSET_NAME: my_free(mysql->options.charset_name,MYF(MY_ALLOW_ZERO_PTR)); mysql->options.charset_name=my_strdup(arg,MYF(MY_WME)); break; ...(略)... default: DBUG_RETURN(1); } DBUG_RETURN(0); }
mysql->options.charset_nameに渡された値を格納しているだけのようです。
では、次にこの値が参照されるのはどこでしょうか？軽く検索をかけてみると、1774行目から次のような関数が定義されていました。
C_MODE_START int mysql_init_character_set(MYSQL *mysql) { ...(略)... mysql->charset=get_charset_by_csname(mysql->options.charset_name, MY_CS_PRIMARY, MYF(MY_WME)); ...(略)... return 0; }
get_charset_by_csname()の宣言は以下の通り。
CHARSET_INFO *get_charset_by_csname(const char *cs_name, uint cs_flags, myf my_flags);
ものすごくばっさりと省略しましたが、要は先程のmysql->options.charset_nameを基にCHARSET_INFO構造体を得、それをmysql->charsetに格納しているようです。
このmysql_init_character_set()が呼ばれているのはどこかというと、1845行目から始まるmysql_real_connect()定義の中。あまりに長いので引用しませんが、2213行目です。
つまり、mysql_init() -> mysql_options() -> mysql_real_connect() の順番自体は間違っていないわけです。
その後、このmysql->charsetがどう扱われているかというと…。軽く確認してみた限りでは、特にサーバとの通信の中でこの値が使われている様子はありませんでした。一応2273行目で
buff[8]= (char) mysql->charset->number;
みたいなことをしていますが、前後を見る限りあまり関係なさそうです。
うーん、ここまで読んだ感じ、mysql_options(MYSQL_SET_CHARSET_NAME)はmysql->charsetを書き換えているだけで、サーバに対して何か働きかけをしているわけではないような…。
mysql_set_character_set()も読んでみる
比較のためにmysql_set_character_set()も読んでみます。この関数は、この記事の最初で参照したサイトの中でもエンコーディング切り替えの手段として示されていましたし、また先程のサンプルプログラムでも実際に切り替えに成功していました。
同じファイルの3225行目から始まっています。
int STDCALL mysql_set_character_set(MYSQL *mysql, const char *cs_name) { struct charset_info_st *cs; ...(略)... (cs= get_charset_by_csname(cs_name, MY_CS_PRIMARY, MYF(0)))) ...(略)... sprintf(buff, "SET NAMES %s", cs_name); if (!mysql_real_query(mysql, buff, (uint) strlen(buff))) { mysql->charset= cs; } ...(略)... return mysql->net.last_errno; }
どうやらSET NAMESした後にmysql->charsetにCHARSET_INFO構造体をセットしているだけのようです。
…ん？結局SET NAMESするんですか？
mysql_real_escape_string()も読んでみる
最初にSET NAMESは危ないとした根拠として、mysql_real_escape_string()によるエスケーピングを挙げました。この関数も読んでみます。
こちらはlibmysql/libmysql.cの1623行目から始まっていました。
ulong STDCALL mysql_real_escape_string(MYSQL *mysql, char *to,const char *from, ulong length) { if (mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES) return (uint) escape_quotes_for_mysql(mysql->charset, to, 0, from, length); return (uint) escape_string_for_mysql(mysql->charset, to, 0, from, length); }
escape_quotes_for_mysql()やescape_string_for_mysql()の中までは追いませんが、パッと見てmysql->charsetを参照していることがわかります。
なるほど、要はこのmysql->charsetが「サーバと実際にやりとりしている文字コード」と合致していれば良いわけですね。
整理してみる
なんとなく理解できそうになってきたので、一旦整理してみます。
SET NAMESが危ない理由は？
mysql_real_escape_string()などの内部で参照されているmysql->charsetの情報が実態と異なってしまう場合があるためです。引用はしませんが、mysql_query()でSET NAMESを実行しても、当然ながらmysql->charsetを一緒に書き換えてくれたりはしません。
mysql_set_character_set()が安全な理由は？
SET NAMESした後にmysql->charsetも一緒にセットしてくれるからです。これならサーバとやりとりしている文字コードと、mysql_real_escape_string()などが参照する文字コードが一致するため、間違った文字列操作は発生しません。
mysql_options()って結局何？
MYSQL_SET_CHARSET_NAMEに対する操作に限って言えば、mysql->charsetを指定した文字コードにセットしてくれるだけです。それを特にサーバに知らせたりはしてくれません。この場合、やはりmysql->charsetが示す文字コードが実態と異なってしまいます。SET NAMESだけした場合の丁度逆のパターンですね。
mysql_options()とmysql_set_character_set()の関係って？
つまり、細かいことを無視すれば「mysql_options(MYSQL_SET_CHARSET_NAME) + "SET NAMES" = mysql_set_character_set()」の式が成り立ちます。mysql_set_character_set()は単体で十分な働きをしますが、mysql_options(MYSQL_SET_CHARSET_NAME)とSET NAMESはそれぞれ単体では不足なのです。
じゃあmysql_options()とSET NAMESの関係は？
mysql_options(MYSQL_SET_CHARSET_NAME)はSET NAMESを代替する機能ではなく、お互いを補完しあうものであり、必ずペアで使用しなければなりません。
結局、MySQL/Rubyでの正しいエンコーディング変更方法は？
前述の通り、MySQL/Rubyではmysql_set_character_set()が使えませんので、代わりに同等の働きをするmysql_options(MYSQL_SET_CHARSET_NAME) + "SET NAMES"を実行すればOKです。
mysql = Mysql.init mysql.options(Mysql::SET_CHARSET_NAME, 'utf8') mysql.real_connect('host', 'user', 'password', 'database') mysql.query('SET NAMES utf8')
Sequelなら次のようになります。
mysql = Sequel.connect('mysql://user:password@host/database', :encoding => 'utf8') mysql << 'SET NAMES utf8'
まとめ
ここまで辿り着くのにかなりかかりました…。
手間取った原因は、色々な参考サイトを読んでいく中で、てっきり
- mysql_options(MYSQL_SET_CHARSET_NAME)とmysql_set_character_set()は同等の機能を持ち
- いずれもSET NAMESを代替するものであり
- SET NAMESは絶対に使用してはいけない
のだと勘違いしてしまった点にあります。
実際は
- SET NAMESは単体で使ってはいけないだけ
- mysql_options(MYSQL_SET_CHARSET_NAME)はSET NAMESと組み合わせて使うもの
- あるいはmysql_set_character_set()を用いれば上記2つを代替できる
ということだったようです。
調べ物をするときは思い込みをせず、きちんと正しい意味で捉えましょう、という教訓なのでした…！
あ、でも、ここに書いてあることはあくまでも私の理解レベルに基く記述ですので、間違っている危険性はかなりあると思います。「それは違う」というツッコミがありましたら是非お願い致します！
