Perl – igreks開発日記

UTF-8のCSVファイルを文字化けなくエクセルで開く

エクセルの仕様なのか、何も考えずにUTF-8のファイルエクセルで開くと、標準文字コードで開くため、だいたいの確率で文字化けします。

ちなみにShift JISであれば、問題なく開けます。

対策としては、適当なテキストエディタなどで開いて、UTF-8(BOM付き)で一度保存すれば、次からはエクセルでも文字化けせず問題なく開くことが出来ます。

このことから、コンテンツの最初にBOMをつけてやれば、ダウンロード後すぐにエクセルで文字化けなく開くことが出来ます。

動的出力するCSVファイルであれば、対策は簡単です。以下はPerlでの例です。

# このファイルはUTF-8で作成

# CSVのコンテンツ作成
my $csv = "aaa, bbb,ccc, あいうえお, 아이우에오";

# 先頭にBOMを追加
$csv = "\xEF\xBB\xBF" .$csv;

# 出力
print <<EOM;
Content-Type: application/octet-stream
Content-Disposition: attachment; filename=hoge.csv

$csv
EOM

# このファイルはUTF-8で作成

# CSVのコンテンツ作成

my $csv = "aaa, bbb,ccc, あいうえお, 아이우에오";

# 先頭にBOMを追加

$csv = "\xEF\xBB\xBF" .$csv;

# 出力

print <<EOM;

Content-Type: application/octet-stream

Content-Disposition: attachment; filename=hoge.csv

$csv

EOM

たったこれだけです。

なお、他サイトなどで、BOMは”0xFE 0xFF”などと紹介しているところがありますが、”0xFE 0xFF”はUTF-16のBEエイディアンのBOMであり、UTF-8のBOMは”0xEF 0xBB 0xBF”ですのでご注意ください。

テキストエディタだと”0xFE 0xFF”をつけてもちゃんと解釈してくれることがありますが、エクセルの場合は”0xEF 0xBB 0xBF”を付けないとダメ見たいです。

参考サイト）

MySQLで最後にINSERTしたAutoIncrementのidを取得する

生のSQLで取得する場合は、

SELECT LAST_INSERT_ID();

1	SELECT LAST_INSERT_ID();

で取得できます。

DBIを使用している場合は、以下のようにステートメントハンドルから取得できます。

(この辺で接続処理)
   ：
$sth->execute("
  INSERT INTO `table_name` (campany, pref)
  VALUES( 'Igreks Inc.', "東京" )
") || die $sth->errstr();

my $last_insert_id => $sth->{mysql_insertid};

(この辺で接続処理)

：

$sth->execute("

INSERT INTO `table_name` (campany, pref)

VALUES( 'Igreks Inc.', "東京" )

") || die $sth->errstr();

my $last_insert_id => $sth->{mysql_insertid};

参考URL）

WHERE句のINでプレースホルダを使う際の注意点

DBIモジュールでは、プレースホルダを使う場合、基本的な記述は下記のようになる。

　　：
# DB接続、SQL組み立て処理
　　：
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, $value); # 第三引数に値のデータタイプも指定可能
　　：
# 後処理
　　：

　　：

# DB接続、SQL組み立て処理

　　：

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, $value); # 第三引数に値のデータタイプも指定可能

　　：

# 後処理

　　：

んで、SQL分にWHERE句を含んでいる場合で、条件をINで複数指定している場合、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ, い, う");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ, い, う");

でいけると思いきや、値の最初の要素（カンマで区切った一番最初の値＝「あ」）のみプレースホルダに代入されて処理されてしまう。つまり、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ");

と同じ結果となってしまう。

どうやらカンマで連結した文字列を渡しても、配列要素とみなされるようだ。

なので、条件の数分だけ、プレースホルダを作り指定してやる必要がある。

単純に書くと、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?,?,?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ");
$sth->bind_param(2, "い");
$sth->bind_param(3, "う");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?,?,?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ");

$sth->bind_param(2, "い");

$sth->bind_param(3, "う");

てな感じ。

現実的にはINの条件数は動的になると思うので、うちでは下記のような感じで処理している。

・DB処理用ファイル（database.pl）

package database;
　　：
# DB接続処理
　　：
sub db_exec {
  my($sql, $bind) = @_;
  our $sth; my $exec;
  $sth = $db::dbh->prepare($sql) || die $db::dbh->errstr();
  if($bind){
    my $el = 1;
    map {$sth->bind_param($el,$$_{value},$$_{type}); $el++;} @$bind;
  }
  $exec = $sth->execute() || die $sth->errstr();
}

package database;

　　：

# DB接続処理

　　：

sub db_exec {

my($sql, $bind) = @_;

our $sth; my $exec;

$sth = $db::dbh->prepare($sql) || die $db::dbh->errstr();

if($bind){

my $el = 1;

map {$sth->bind_param($el,$$_{value},$$_{type}); $el++;} @$bind;

}

$exec = $sth->execute() || die $sth->errstr();

}

・呼び出し元

requare database.pl;
　　：
my @in = (あ, い, う);
&database::db_exec("
  SELECT * FROM table_name
  WHERE column IN (". join(',', map{'?'} @in ).") 
  ",
  [
    ( map {+{ value => $_ }} @in )
    # さらにプレースホルダがある場合はここに
    ,{ value => 'abc' }
    # のように書く
  ]
  #↑ +{...}はハッシュのリファレンスを示す
);
　　：

requare database.pl;

　　：

my @in = (あ, い, う);

&database::db_exec("

SELECT * FROM table_name

WHERE column IN (". join(',', map{'?'} @in ).")

[

( map {+{ value => $_ }} @in )

# さらにプレースホルダがある場合はここに

,{ value => 'abc' }

# のように書く

]

#↑ +{...}はハッシュのリファレンスを示す

);

　　：

PHPでもフレームワークによってここの処理が違ったりするので注意が必要。

濁点（゛）と半濁点（゜）を含む日本語のメール文字化け対応

JIS（≒ISO-2022-JP）で定義されていない文字コードは、UTF8から変換しても、もちろん正常に表示されません。

しかし日本語のUTF8には、実は、濁点と半濁点をふくむ文字に2パターンの表示方法があります。

たとえば「が」というひらがなですが、普通は、

xE3x81x8C

となります。

しかし、UTF8の決まりでは、

xE3x81x8B + xE3x82x99　（「か」＋「 ○゛」）

でもOKなのです。

うちでは日本語文字コードの変換にUnicode::Japaneseを使ってます。非常に多くの変換パターンが実現できるので便利なのですが、上記の後者の場合（「か」＋「 ○゛」の場合）に、UTF8→JISなどの変換に失敗します。

失敗というか、厳密にはxE3x82x99（ ○゛）とxE3x82x9A（ ○゜）が未定義状態のようで、「が」は「が」のように変換されてしまいます。

エンコーダを変えればいいのかもしれませんが、やはりバグが怖いので、下記のようにJISへの変換前にコードを統一することで対応してます。

$str = "がぎぐげござじずぜぞだぢづでど・・・";

# Hiragana
$str =~ s/\xE3\x81\x8B\xE3\x82\x99/\xE3\x81\x8C/g;	# か+○゛=> が
$str =~ s/\xE3\x81\x8D\xE3\x82\x99/\xE3\x81\x8E/g;	# き+○゛=> ぎ
$str =~ s/\xE3\x81\x8F\xE3\x82\x99/\xE3\x81\x90/g;	# く+○゛=> ぐ
$str =~ s/\xE3\x81\x91\xE3\x82\x99/\xE3\x81\x92/g;	# け+○゛=> げ
$str =~ s/\xE3\x81\x93\xE3\x82\x99/\xE3\x81\x94/g;	# こ+○゛=> ご
$str =~ s/\xE3\x81\x95\xE3\x82\x99/\xE3\x81\x96/g;	# さ+○゛=> ざ
$str =~ s/\xE3\x81\x97\xE3\x82\x99/\xE3\x81\x98/g;	# し+○゛=> じ
$str =~ s/\xE3\x81\x99\xE3\x82\x99/\xE3\x81\x9A/g;	# す+○゛=> ず
$str =~ s/\xE3\x81\x9B\xE3\x82\x99/\xE3\x81\x9C/g;	# せ+○゛=> ぜ
$str =~ s/\xE3\x81\x9D\xE3\x82\x99/\xE3\x81\x9E/g;	# そ+○゛=> ぞ
$str =~ s/\xE3\x81\x9F\xE3\x82\x99/\xE3\x81\xA0/g;	# た+○゛=> だ
$str =~ s/\xE3\x81\xA1\xE3\x82\x99/\xE3\x81\xA2/g;	# ち+○゛=> ぢ
$str =~ s/\xE3\x81\xA4\xE3\x82\x99/\xE3\x81\xA5/g;	# つ+○゛=> づ
$str =~ s/\xE3\x81\xA6\xE3\x82\x99/\xE3\x81\xA7/g;	# て+○゛=> で
$str =~ s/\xE3\x81\xA8\xE3\x82\x99/\xE3\x81\xA9/g;	# と+○゛=> ど
$str =~ s/\xE3\x81\xAF\xE3\x82\x99/\xE3\x81\xB0/g;	# は+○゛=> ば
$str =~ s/\xE3\x81\xAF\xE3\x82\x9A/\xE3\x81\xB1/g;	# は+○゜=> ぱ
$str =~ s/\xE3\x81\xB2\xE3\x82\x99/\xE3\x81\xB3/g;	# ひ+○゛=> び
$str =~ s/\xE3\x81\xB2\xE3\x82\x9A/\xE3\x81\xB4/g;	# ひ+○゜=> ぴ
$str =~ s/\xE3\x81\xB5\xE3\x82\x99/\xE3\x81\xB6/g;	# ふ+○゛=> ぶ
$str =~ s/\xE3\x81\xB5\xE3\x82\x9A/\xE3\x81\xB7/g;	# ふ+○゜=> ぷ
$str =~ s/\xE3\x81\xB8\xE3\x82\x99/\xE3\x81\xB9/g;	# へ+○゛=> べ
$str =~ s/\xE3\x81\xB8\xE3\x82\x9A/\xE3\x81\xBA/g;	# へ+○゜=> ぺ
$str =~ s/\xE3\x81\xBB\xE3\x82\x99/\xE3\x81\xBC/g;	# ほ+○゛=> ぼ
$str =~ s/\xE3\x81\xBB\xE3\x82\x9A/\xE3\x81\xBD/g;	# ほ+○゜=> ぽ

# Katakana
$str =~ s/\xE3\x82\xAB\xE3\x82\x99/\xE3\x82\xAC/g;	# カ+○゛=> ガ
$str =~ s/\xE3\x82\xAD\xE3\x82\x99/\xE3\x82\xAE/g;	# キ+○゛=> ギ
$str =~ s/\xE3\x82\xAF\xE3\x82\x99/\xE3\x82\xB0/g;	# ク+○゛=> グ
$str =~ s/\xE3\x82\xB1\xE3\x82\x99/\xE3\x82\xB2/g;	# ケ+○゛=> ゲ
$str =~ s/\xE3\x82\xB3\xE3\x82\x99/\xE3\x82\xB4/g;	# コ+○゛=> ゴ
$str =~ s/\xE3\x82\xB5\xE3\x82\x99/\xE3\x82\xB6/g;	# サ+○゛=> ザ
$str =~ s/\xE3\x82\xB7\xE3\x82\x99/\xE3\x82\xB8/g;	# シ+○゛=> ジ
$str =~ s/\xE3\x82\xB9\xE3\x82\x99/\xE3\x82\xBA/g;	# ス+○゛=> ズ
$str =~ s/\xE3\x82\xBB\xE3\x82\x99/\xE3\x82\xBC/g;	# セ+○゛=> ゼ
$str =~ s/\xE3\x82\xBD\xE3\x82\x99/\xE3\x82\xBE/g;	# ソ+○゛=> ゾ
$str =~ s/\xE3\x82\xBF\xE3\x82\x99/\xE3\x83\x80/g;	# タ+○゛=> ダ
$str =~ s/\xE3\x83\x81\xE3\x82\x99/\xE3\x83\x82/g;	# チ+○゛=> ヂ
$str =~ s/\xE3\x83\x84\xE3\x82\x99/\xE3\x83\x85/g;	# ツ+○゛=> ヅ
$str =~ s/\xE3\x83\x86\xE3\x82\x99/\xE3\x83\x87/g;	# テ+○゛=> デ
$str =~ s/\xE3\x83\x88\xE3\x82\x99/\xE3\x83\x89/g;	# ト+○゛=> ド
$str =~ s/\xE3\x83\x8F\xE3\x82\x99/\xE3\x83\x90/g;	# ハ+○゛=> バ
$str =~ s/\xE3\x83\x8F\xE3\x82\x9A/\xE3\x83\x91/g;	# ハ+○゜=> パ
$str =~ s/\xE3\x83\x92\xE3\x82\x99/\xE3\x83\x93/g;	# ヒ+○゛=> ビ
$str =~ s/\xE3\x83\x92\xE3\x82\x9A/\xE3\x83\x94/g;	# ヒ+○゜=> ピ
$str =~ s/\xE3\x83\x95\xE3\x82\x99/\xE3\x83\x96/g;	# フ+○゛=> ブ
$str =~ s/\xE3\x83\x95\xE3\x82\x9A/\xE3\x83\x97/g;	# フ+○゜=> プ
$str =~ s/\xE3\x83\x98\xE3\x82\x99/\xE3\x83\x99/g;	# ヘ+○゛=> ベ
$str =~ s/\xE3\x83\x98\xE3\x82\x9A/\xE3\x83\x9A/g;	# ヘ+○゜=> ペ
$str =~ s/\xE3\x83\x9B\xE3\x82\x99/\xE3\x83\x9C/g;	# ホ+○゛=> ボ
$str =~ s/\xE3\x83\x9B\xE3\x82\x9A/\xE3\x83\x9D/g;	# ホ+○゜=> ポ

$str = "がぎぐげござじずぜぞだぢづでど・・・";

# Hiragana

$str =~ s/\xE3\x81\x8B\xE3\x82\x99/\xE3\x81\x8C/g; # か+○゛=> が

$str =~ s/\xE3\x81\x8D\xE3\x82\x99/\xE3\x81\x8E/g; # き+○゛=> ぎ

$str =~ s/\xE3\x81\x8F\xE3\x82\x99/\xE3\x81\x90/g; # く+○゛=> ぐ

$str =~ s/\xE3\x81\x91\xE3\x82\x99/\xE3\x81\x92/g; # け+○゛=> げ

$str =~ s/\xE3\x81\x93\xE3\x82\x99/\xE3\x81\x94/g; # こ+○゛=> ご

$str =~ s/\xE3\x81\x95\xE3\x82\x99/\xE3\x81\x96/g; # さ+○゛=> ざ

$str =~ s/\xE3\x81\x97\xE3\x82\x99/\xE3\x81\x98/g; # し+○゛=> じ

$str =~ s/\xE3\x81\x99\xE3\x82\x99/\xE3\x81\x9A/g; # す+○゛=> ず

$str =~ s/\xE3\x81\x9B\xE3\x82\x99/\xE3\x81\x9C/g; # せ+○゛=> ぜ

$str =~ s/\xE3\x81\x9D\xE3\x82\x99/\xE3\x81\x9E/g; # そ+○゛=> ぞ

$str =~ s/\xE3\x81\x9F\xE3\x82\x99/\xE3\x81\xA0/g; # た+○゛=> だ

$str =~ s/\xE3\x81\xA1\xE3\x82\x99/\xE3\x81\xA2/g; # ち+○゛=> ぢ

$str =~ s/\xE3\x81\xA4\xE3\x82\x99/\xE3\x81\xA5/g; # つ+○゛=> づ

$str =~ s/\xE3\x81\xA6\xE3\x82\x99/\xE3\x81\xA7/g; # て+○゛=> で

$str =~ s/\xE3\x81\xA8\xE3\x82\x99/\xE3\x81\xA9/g; # と+○゛=> ど

$str =~ s/\xE3\x81\xAF\xE3\x82\x99/\xE3\x81\xB0/g; # は+○゛=> ば

$str =~ s/\xE3\x81\xAF\xE3\x82\x9A/\xE3\x81\xB1/g; # は+○゜=> ぱ

$str =~ s/\xE3\x81\xB2\xE3\x82\x99/\xE3\x81\xB3/g; # ひ+○゛=> び

$str =~ s/\xE3\x81\xB2\xE3\x82\x9A/\xE3\x81\xB4/g; # ひ+○゜=> ぴ

$str =~ s/\xE3\x81\xB5\xE3\x82\x99/\xE3\x81\xB6/g; # ふ+○゛=> ぶ

$str =~ s/\xE3\x81\xB5\xE3\x82\x9A/\xE3\x81\xB7/g; # ふ+○゜=> ぷ

$str =~ s/\xE3\x81\xB8\xE3\x82\x99/\xE3\x81\xB9/g; # へ+○゛=> べ

$str =~ s/\xE3\x81\xB8\xE3\x82\x9A/\xE3\x81\xBA/g; # へ+○゜=> ぺ

$str =~ s/\xE3\x81\xBB\xE3\x82\x99/\xE3\x81\xBC/g; # ほ+○゛=> ぼ

$str =~ s/\xE3\x81\xBB\xE3\x82\x9A/\xE3\x81\xBD/g; # ほ+○゜=> ぽ

# Katakana

$str =~ s/\xE3\x82\xAB\xE3\x82\x99/\xE3\x82\xAC/g; # カ+○゛=> ガ

$str =~ s/\xE3\x82\xAD\xE3\x82\x99/\xE3\x82\xAE/g; # キ+○゛=> ギ

$str =~ s/\xE3\x82\xAF\xE3\x82\x99/\xE3\x82\xB0/g; # ク+○゛=> グ

$str =~ s/\xE3\x82\xB1\xE3\x82\x99/\xE3\x82\xB2/g; # ケ+○゛=> ゲ

$str =~ s/\xE3\x82\xB3\xE3\x82\x99/\xE3\x82\xB4/g; # コ+○゛=> ゴ

$str =~ s/\xE3\x82\xB5\xE3\x82\x99/\xE3\x82\xB6/g; # サ+○゛=> ザ

$str =~ s/\xE3\x82\xB7\xE3\x82\x99/\xE3\x82\xB8/g; # シ+○゛=> ジ

$str =~ s/\xE3\x82\xB9\xE3\x82\x99/\xE3\x82\xBA/g; # ス+○゛=> ズ

$str =~ s/\xE3\x82\xBB\xE3\x82\x99/\xE3\x82\xBC/g; # セ+○゛=> ゼ

$str =~ s/\xE3\x82\xBD\xE3\x82\x99/\xE3\x82\xBE/g; # ソ+○゛=> ゾ

$str =~ s/\xE3\x82\xBF\xE3\x82\x99/\xE3\x83\x80/g; # タ+○゛=> ダ

$str =~ s/\xE3\x83\x81\xE3\x82\x99/\xE3\x83\x82/g; # チ+○゛=> ヂ

$str =~ s/\xE3\x83\x84\xE3\x82\x99/\xE3\x83\x85/g; # ツ+○゛=> ヅ

$str =~ s/\xE3\x83\x86\xE3\x82\x99/\xE3\x83\x87/g; # テ+○゛=> デ

$str =~ s/\xE3\x83\x88\xE3\x82\x99/\xE3\x83\x89/g; # ト+○゛=> ド

$str =~ s/\xE3\x83\x8F\xE3\x82\x99/\xE3\x83\x90/g; # ハ+○゛=> バ

$str =~ s/\xE3\x83\x8F\xE3\x82\x9A/\xE3\x83\x91/g; # ハ+○゜=> パ

$str =~ s/\xE3\x83\x92\xE3\x82\x99/\xE3\x83\x93/g; # ヒ+○゛=> ビ

$str =~ s/\xE3\x83\x92\xE3\x82\x9A/\xE3\x83\x94/g; # ヒ+○゜=> ピ

$str =~ s/\xE3\x83\x95\xE3\x82\x99/\xE3\x83\x96/g; # フ+○゛=> ブ

$str =~ s/\xE3\x83\x95\xE3\x82\x9A/\xE3\x83\x97/g; # フ+○゜=> プ

$str =~ s/\xE3\x83\x98\xE3\x82\x99/\xE3\x83\x99/g; # ヘ+○゛=> ベ

$str =~ s/\xE3\x83\x98\xE3\x82\x9A/\xE3\x83\x9A/g; # ヘ+○゜=> ペ

$str =~ s/\xE3\x83\x9B\xE3\x82\x99/\xE3\x83\x9C/g; # ホ+○゛=> ボ

$str =~ s/\xE3\x83\x9B\xE3\x82\x9A/\xE3\x83\x9D/g; # ホ+○゜=> ポ

ご自由にコピペどうぞ。

参考サイト）

http://d.hatena.ne.jp/kamosawa/20151015

http://www.seiai.ed.jp/sys/text/java/utf8table.html

http://webmastertool.jp/other/utf.html

http://www.asahi-net.or.jp/~ax2s-kmtn/ref/unicode/u3040.html

MySQLのクエリログの出力方法

/etc/my.cnfを開く。

[mysqld]ディレクティブにログの種類と出力場所を指定。

代表的なものは下記。

（エラーログ）※これはたぶん最初から書いてある
log_error=”/var/log/mysqld.log”
log_warnings=1

（一般クエリログ）
log=”/var/log/sql.log”

（バイナリログ）
log_bin=”/var/log/mysqlbin.log”
log_bin_index=”/var/log/mysqlbin.list”
max_binlog_size=1M
expire_logs_days=1

（スロークエリログ）
slow_query_log=1
slow_query_log_file=”/var/log/slowquery.log”
log_queries_not_using_indexes
log_slow_admin_statements

設定を書いて保存したら、出力ファイルを作成し、所有者をmysqlに変更しておく。

（一般クエリログの場合）
# touch /var/log/sql.log
# chown mysql:mysql /var/log/sql.log

最後にmysqldを再起動。
# service mysqld restart

一般クエリログの出力例
——————————-
　　：
18 Connect ****@localhost on dbname
18 Query set autocommit=1
18 Query SET NAMES utf8
18 Query SELECT * FROM tbl1
18 Query SELECT * FROM tbl2
WHERE id = ‘*****’
18 Query UPDATE tbl2
SET clm1 = UNIX_TIMESTAMP()
WHERE id = ‘*****’
18 Query SELECT * FROM tbl3 WHERE id=’****’
17 Quit
　　：
——————————-

ちなみにSQLインジェクション攻撃を受けている場合のログの例
——————————-
　　：
1019 Connect ****@localhost on dbname
1019 Query set autocommit=1
1019 Query SET NAMES utf8
1019 Query SELECT * FROM tbl1 WHERE id=’-2353′ OR ORD(MID((SELECT IFNULL(CAST(clm1 AS CHAR),0x20) FROM tbl2 LIMIT 1328,1),12,1))>112#’ AND clm2=’abcdefg’
1019 Query SELECT * FROM tbl3
1019 Quit
　　：
——————————-

ちなみに、
# tail -f /var/log/sql.log
とすれば発行クエリがリアルタイムでチェック可能。

DBI使って開発段階で生のSQLをチェックしたい時かあると思いますが、DBIx::QueryLogとか使うよりこっちのほうがアナログで簡単そう。