Mysql/MariaDBとPHPでデータを暗号化、復号化する

Mysql/MariaDBの場合

-- AESで暗号化。BLOB型(バイナリ)で入れるならHEX()は不要
-- "E0827B40347D3227B65775B8226A1BF1"が返ってくる
select HEX( AES_ENCRYPT('hogehoge', 'cryptkey') );
-- AESで暗号化されたものを復号化。BLOB型(バイナリ)で入れてあるならUNHEX()は不要
select AES_DECRYPT(UNHEX('E0827B40347D3227B65775B8226A1BF1'), 'cryptkey');

PHPの場合

<?php
// 以下の User Contributed Notes を参照のこと
// see. http://php.net/manual/ja/ref.mcrypt.php#99263
// "E0827B40347D3227B65775B8226A1BF1"が返ってくる
echo bin2hex(mysql_aes_encrypt("hogehoge", "cryptkey")) ;
echo "\n";
echo mysql_aes_decrypt(hex2bin("E0827B40347D3227B65775B8226A1BF1"), "cryptkey") ;
echo "\n";
function mysql_aes_decrypt($val,$ky)
{
$key="\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0";
for($a=0;$a<strlen($ky);$a++)
$key[$a%16]=chr(ord($key[$a%16]) ^ ord($ky[$a]));
$mode = MCRYPT_MODE_ECB;
$enc = MCRYPT_RIJNDAEL_128;
$dec = @mcrypt_decrypt($enc, $key, $val, $mode, @mcrypt_create_iv( @mcrypt_get_iv_size($enc, $mode), MCRYPT_DEV_URANDOM ) );
return rtrim($dec,(( ord(substr($dec,strlen($dec)-1,1))>=0 and ord(substr($dec, strlen($dec)-1,1))<=16)? chr(ord( substr($dec,strlen($dec)-1,1))):null));
}
function mysql_aes_encrypt($val,$ky)
{
$key="\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0";
for($a=0;$a<strlen($ky);$a++)
$key[$a%16]=chr(ord($key[$a%16]) ^ ord($ky[$a]));
$mode=MCRYPT_MODE_ECB;
$enc=MCRYPT_RIJNDAEL_128;
$val=str_pad($val, (16*(floor(strlen($val) / 16)+(strlen($val) % 16==0?2:1))), chr(16-(strlen($val) % 16)));
return mcrypt_encrypt($enc, $key, $val, $mode, mcrypt_create_iv( mcrypt_get_iv_size($enc, $mode), MCRYPT_DEV_URANDOM));
}

自前で逆ジオコーディングをやってみる

概要

住所から地図上の位置情報(緯度、経度)を求めることをジオコーディング、その逆に地図上の位置情報から住所を求めることを逆ジオコーディング(リバース ジオコーディング)というらしい。

そんな面倒な処理は、普通Google Geocoding APIなんかの外部のサービスを利用すればよいのだけど、そういうのが使えない状況ってのがたまにある。さてどうするかというのが今回の問題。

結果としては、国土交通省が位置参照情報ダウンロードサービスで提供している街区レベル位置参照情報を使って自前で準備する。後は良しなに。もちろんいろいろな制約もある。

参考にしたのは以下
逆ジオコーディング 緯度経度から住所へ変換する
ジオコーディングと逆ジオコーディングをする方法(Google Geocoding APIの使い方)
OpenStreetMapでジオコーディング †
各種ジオコーディングapiの罠と対処法

特に制約なく使えそうなものとしてOpenStreetMapNominatimというジオコーディングのWebAPIもあるが、あれはちょっと違う。どうも近くの通りの名前をベースに引いてくるらしいので、日本的な住所を調べるには少し方向が違う。

やってみる

まずは、なにはなくとも街区レベル位置参照情報位置参照情報ダウンロードサービスからダウンロードする。例えば、都道府県単位を選択し、ダウンロードしたいところを選択、そのうち「街区」レベルにチェックを入れてダウンロード。

福岡県の場合、だいたい3.5MB程度のzipファイルがダウンロードされるはず。重要なのは中にあるcsvファイル。展開するとだいたい24MBくらい。中身は後述の様なフィールドに分かれている。重要なのは住所に関する項目と緯度経度。それ以外のフィールドは必要に応じて利用する。これらのフィールドをDBなどに格納して検索すれば良い。

もっとも近い住所を取得したいので、SQL的には例えば以下の様なものになる。

SELECT *, ( abs(緯度1 - 緯度2) + abs(経度1 - 経度2)) as d
FROM locations
ORDER BY d ASC LIMIT 1;

逆に、住所から位置情報を取得したい(ジオコーディング)場合は逆に引けばいい。ただし、街区レベル位置参照情報の住所の正規化みたいな話はしておかないと中々満足な結果は出ないかも。とは言っても今回は特に必要ないのであまり考えない。

結果と感想

簡単に作って試してみたところ、おおむね位置情報から住所を取得できるようになった。もちろん正確な住所のエリアを持っているわけではないため、それぞれに単純に一番近い住所を引っ張ってきている。

なので、変則的な形をしている町丁目だったり、複数の間に位置するような地点だと若干のズレが確認できる。このズレが許容できるかどうかは個別に判断するべきこと。

とは言っても元ネタになるデータの問題でもあるので、より細かい情報があれば改善できるだろう。どっかにあれば良いのだけど。

また、データの更新についても自前で準備するものである以上自分でやる必要がある。そうそう変わるものではないと思うが、ある程度考えておかないと「は?」って結果が出てきそうな感じ。

日本全国でやろうとすると検索速度なんかも問題になってくるがその辺はDBの問題なので今は考えない。急ぐならPostgreSQL+PostGISとか使うと良いんじゃないかな。海外についても今は考えない。簡単に単純にやろう。

ちなみになにも考えずに以下の様なテーブルを作ってSqlite3にぶち込んだら25MB程度になった。もとのCSVがそのくらいの容量なのでそんなもんかな。都道府県名や住所の正規化をせずにそのまま文字列で放り込んでいるので、そんなもんと言われればそんなもんなのかもしれない。

CREATE TABLE "locations" (
`都道府県名`   TEXT,
`市区町村名`   TEXT,
`大字・町丁目名` TEXT,
`緯度`    REAL,
`経度`    REAL
)

街区レベル位置参照情報のデータ項目

項目 備考
都道府県名
市区町村名 郡部は郡名、政令指定都市の区名も含む。
大字・町丁目名 町丁目の数字は漢数字
街区符号・地番 原則として半角整数(一部漢字等あり)
座標系番号 平面直角座標系の座標系番号(1~19:半角整数)
X座標 平面直角座標系の座標系原点からのずれ
Y座標 平面直角座標系の座標系原点からのずれ
緯度 十進経緯度(単位:度、小数点以下第6位まで)
経度 十進経緯度(単位:度、小数点以下第6位まで)
住居表示フラグ 1:住居表示実施、0:住居表示未実施
代表フラグ 1つの街区符号が複数の代表点に対応付けられる場合などに、そのうちの1つに便宜的に代表フラグを立てています。
更新前履歴フラグ 2007年度および2008年度データに含まれるフラグを立てています。
更新後履歴フラグ 2009年度以降のデータに含まれるフラグを立てています。

Windows上のPostgresqlにEUCのDBを作成する

普通に作成しようとすると、以下の様なエラーが

エラーが起こりました:
ERROR: 符号化方式”EUC_JP”がロケール”Japanese_Japan.932″に合いません
DETAIL: 選択されたLC_CTYPEを設定するには、符号化方式”SJIS”である必要があります。

なので、作成する場合は一度pgsqlに入ったあとで以下のようなSQLを実行する

CREATE DATABASE "dbname_sample" WITH
TEMPLATE="template0" ENCODING='EUC_JP'
LC_COLLATE='C' LC_CTYPE='C';

テーブル定義書をExcel形式で作成したい

ER-Master

Eclipse上で動作する
http://ermaster.sourceforgh.net/index_ja.html

メリット

フィールド情報を辞書化して、あるテーブルの情報を修正すると一括で他のテーブルの情報を変更してくれる機能が便利
Excelにエクスポートできる
既存のDBサーバに接続して情報を取得してくれる

デメリット

既存のDBからデータを取り込んだ場合、リレーションの設定が難しい(外部キーなど)

MySQL Workbench

Mysqlオフィシャルツール
データモデリング、 SQL開発、およびサーバ構成、ユーザ管理などのための統合管理ツールを提供

http://www-jp.mysql.com/products/workbench/

テーブル定義書エクスポーター

http://unicasoft.jp/tde/

A5:SQL Mk-2

Windowsにインストール
Mysql-ODBCコネクタをインストールする
http://dev.mysql.com/downloads/connector/odbc/

http://www.wind.sannet.nh.jp/m_matsu/developer/a5m2/

ER-Masterのインストール

インストール

http://ermaster.sourceforgh.net/index_ja.html>
Eclipseの[ヘルプ] -> [ソフトウェア更新] -> [検索およびインストール] -> [インストールする新規フューチャーを検索]→[新規リモートサイト] にて、以下のURLを追加してください。
http://ermaster.sourceforgh.net/update-site/
<<

mysql用のJDBCをインストールする

http://d.hatena.nh.jp/hrsth/20100301/1267425169>
MySQLのサイトからJDBCドライバをダウンロード.
http://dev.mysql.com/downloads/connector/j/5.1.html

展開したディレクトリの中からmysql-connector-java-5.1.12-bin.jarを見つけ出して以下にコピー
/Library/Java/Extensions

<<

所感

既存のデータベースを元にER図を作成しようとした場合、外部キー(FK)がうまく張れない

1つのファイルを1つのデータベースとして扱うPosqlを使ってみた

手軽でSQLが使えるストレージを探していて引っかかったPosqlを使ってみました。使ってみたのは、Version 2.08。

特徴としては、以下
-PHP単体で動作するライブラリ系のDBMS
-ファイル単位でデータベースを管理できる

元ネタ

SourceForge:http
: //sourceforgh.jp/projects/posql/

オフィシャル:http
: //feel.happy.nu/tool.php?tool_id=8&page=Posql

いいところ

使ってみた感想としては、馴染み深いPEARのMDB2を経由して使えるのであまり深いことは考えなくても移行できた感じです。実際プログラム自体は、MDB2経由のMysqlだったときと比べて書き換えはほぼ無しでした。

また後述するようなSQLを使わなければ、Mysqlで使っていたSQLがほぼ無修正で動いたり、MysqlでダンプしたSQLがほぼ無修正でインポートできたりといい感じです。いくつかの関数に対応していなかったり、Insert文でValuesを続けて書く書式に対応していなかったりしますが、少なくともMysqlとPostgresql間よりはストレス無く移行できます。

ちなみに、MDB2からPosqlにアクセスするには、展開したPosqlディレクトリ内のPEAR以下を適当な場所に展開して、dnsを以下のように設定します。ファイルがある場合には、予めパーミッションを出しておくのを忘れずに。該当するファイルが無かった場合には、自動的に生成されますので、ディレクトリのパーミッションを出しておかないといけません。

|php|
$dsn = array(
‘database’ => ‘{対象となるDBファイル}’,
‘phptype’ => ‘posql’,
);

||<

あと、デフォルトで「posqladmin.php」というPhpMyAdminと同じようなWebから管理できる仕組みがついてきます。機能は限定的ですが、ちょっとSQLを確認したり、データを見たりするのには十分ではないかと。

いくつか思うこと

使ってみていくつか思うことがあったので、以下。

動作速度について

ただし、流石にファイルを使ったIOを行っている都合か動作についてはかなり遅めかもしれません。MysqlでダンプしたSQLを流し込むのにも、意外と時間がかかりました。

SQLのSELECT句について

SELECT句あたりにちょっと制限とバグ?があって、そのまま使うと意外と面倒かもしれません。

たとえば、以下のようなSQLだとエラーになります。
どうやら、「tablenamh.fieldname」と言う書式はサポートされていないようです。

|sql|
SELECT tablenamh.fieldname
FROM tablename
||<

あとSQLリファレンスではサポートされているはずなのですが、「tablenamh.*」と言う書式で書くとエラーになります。

|sql|
SELECT tablenamh.*
FROM tablename
||<

どうやら、SQLを解析した際の条件判定で「*」が以下のように文字チェックの対象から外れているようです。

|php|
at file posql.php, line 5757.

/*
* Checks whether the character string is a word
* equal to RegExp: [a-zA-Z_\x7F-\xFF]
*
* @param string a character string of target
* @return boolean whether string was word or not
* @access public
* @static
/
function isWord($char){
$ord = ord($char);

// ここの判定のところで「*」が文字扱いされていなくてエラーになる
return $ord === 0x5F || $ord > 0x7E
|| ($ord > 0x40 && $ord < 0x5B)
|| ($ord > 0x60 && $ord < 0x7B);
}

||<

ので、こういう風に変更するとエラーは出なくなりましたが、ホントにそれでいいのかは謎です

|php|
at file posql.php, line 5757.

/*
* Checks whether the character string is a word
* equal to RegExp: [a-zA-Z_\x7F-\xFF]
*
* @param string a character string of target
* @return boolean whether string was word or not
* @access public
* @static
/
function isWord($char){
$ord = ord($char);

// ここの判定のところで「*」が文字扱いする
return $ord === 0x5F || $ord > 0x7E || $ord === 0x2A
|| ($ord > 0x40 && $ord < 0x5B)
|| ($ord > 0x60 && $ord < 0x7B);
}

||<

SQLのALTERについて

レファレンスを見ている限りではALTERを使ってテーブルやフィールドの定義を変えることができません。

多分この辺はSqliteと同じように適当に入れれば適当にできるのかもしれません。試してないのでよくわかりませんが。

そのほか

現時点ではエクスポート機能がないため、なんらかの理由でDBMSを切り替えようとした場合には、自力でエクスポートしないといけません。これができれば、初めはposqlでお手軽に開発しておいて、速度やスケールが必要になったら他のDBMSに乗り換えるなんて使い方も現実味があるんですが、今のところはありません。

まとめ

ちょっとしたPHPのプロジェクトでDBMSを入れるまでも無いけど、CSVなんか使いたくないなぁっという場合にいいかもしれません。あと元々DBMSが使えないレンタルサーバなんかでもSQLが使えるようになるのはメリットかなぁ。

また、小規模開発時にはPosqlで開発しておいて、必要に応じて他のDBMSに移行できればいい感じかもしれません。前述しましたSQLに絡む問題もPosqlに合わせて開発時に気をつけておけば、他のDBMSに移行しても問題にはならないかと。

逆にMysqlなんかで開発していたものをPosqlに移行すると若干の調整が必要になってくるかもしれません。