Web アプリケーションはデータベースを利用することが多いが、文字コードの問題で悩みたくなければページからデータベースまで、使用する文字コードを統一してしまうのが一番簡単だ。新規に開発するのであれば、すべてを UTF-8に統一すればだいたい問題はないのだが、既存アプリケーションに機能追加を行う場合は文字コードを選べないことも多い。
タイトルは、データベースに PostgreSQL を利用し、データベースの文字コードは EUC_JP、Web アプリケーションは UTF-8 を利用していたときに発生したエラー。調べてみると、EUC_JP の特定の文字コードが UTF-8に変換できないよということらしい。
そういうときは、「?」だったり、「□」が出てきたりするのが普通じゃないの？ということで調べてみる。PostgreSQL のリリースノートに答えがあった。
•Change the server to reject invalidly-encoded multibyte characters in all cases (Tatsuo, Tom)
While PostgreSQL has been moving in this direction for some time, the checks are now applied uniformly to all encodings and all textual input, and are now always errors not merely warnings. This change defends against SQL-injection attacks of the type described in CVE-2006-2313.
SQL インジェクション対策のため、すべてエラーにするということらしい。設定値を調べてみたが、緩和する方法はないようなので、別の方法で対応する必要がある。
Web で検索してみると、PostgreSQL のメーリングリストで、変換マップに必要な文字を追加する方法が見つかった。EUC_JP と UTF-8であれば、以下のような手順になるようだ。
- src/backend/utils/mb/Unicode で、map ファイルを編集(euc_jp_to_utf8.map と utf8_to_euc_jp.map)
- src/backend/utils/mb/conversion_procs/utf8_and_euc_jp で、make clean;make;make install
-
データベースに反映(psql で接続)
DROP CONVERSION pg_catalog.euc_jp_to_utf8;
CREATE DEFAULT CONVERSION pg_catalog.euc_jp_to_utf8 FOR ‘EUC_JP’ TO ‘UTF8’ FROM euc_jp_to_utf8;
DROP CONVERSION pg_catalog.utf8_to_euc_jp;
CREATE DEFAULT CONVERSION pg_catalog.utf8_to_euc_jp FOR ‘UTF8’ TO ‘EUC_JP’ FROM utf8_to_euc_jp;
ソースコードからコンパイル、インストールしているのであれば上記手順がとれるのだが、残念ながらバイナリをインストールして利用しているし、システムの変更もできない。
次に思いついたのが、テーブルをダンプして変換できない文字列を検索、置換してしまうことだが、データが膨大すぎてできそうにない。
最終的には、SELECT 文で正規表現を使って不正な文字を置換して取得することにした。不正な文字と判断するのは、取得した結果の文字列に対してなので、SELECT 文の中で文字を置換してしまえばエラーにはならない。処理が重くなりそうなので、あまりよい方法とは思えないが、0xa9a1 ～ 0xacfe までの文字コードが存在した場合、「・」に変換する SQL を作成してみた。
SELECT regexp_replace(<列名>, '[\\xa9a1-\\xacfe]', '・') as <列名> FROM <テーブル名>
不正とされた文字が、「髙」(はしごだか)のように必要な文字だと単純に置換できないので使い道も限られているけれど・・・
