【MySQL】 MySQL で、データを暗号化 / 復号化 する

MySQL

■ MySQL で、データを暗号化 / 復号化 する

方法

 * AES_ENCRYPT() / AES_DECRYPT() などの暗号化関数を使用する
https://dev.mysql.com/doc/refman/5.6/ja/encryption-functions.html

■ 例１

テーブル

CREATE TABLE IF NOT EXISTS `user` (
	`user_id` VARCHAR(100) NOT NULL,
	`password` VARBINARY(200) NOT NULL, -- VARBINARYを使用する
	PRIMARY KEY (`user_id`)
)
COMMENT='ユーザ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

暗号化 / INSERT文

「ENCRYPT-KEY」で暗号化 
INSERT INTO `user`(`user_id`, `password`) VALUES ('user1', AES_ENCRYPT('password1','ENCRYPT-KEY'));
INSERT INTO `user`(`user_id`, `password`) VALUES ('user2', AES_ENCRYPT('password2','ENCRYPT-KEY'));
INSERT INTO `user`(`user_id`, `password`) VALUES ('user3', AES_ENCRYPT('password3','ENCRYPT-KEY'));
暗号後のデータ 
user_id| password
-------+-----------------------------------
user1    0x33078F9392F9294E8145AF9182655542
user2    0x185ADB100E0AFE6E461C4CDE327F7F65
user3    0x3D027700B5B2357281450D20F1BB8145

復号化 / SELECT文

「ENCRYPT-KEY」で復号化 
SELECT `user_id`, AES_DECRYPT(`password`,'ENCRYPT-KEY') FROM `user`;
復号後のデータ 
user_id| AES_DECRYPT(`password`,'ENCRYPT-KEY')
-------+-----------------------------------
user1    password1
user2    password2
user3    password3

復号化 / SELECT文・WHERE句

SELECT `user_id`, AES_DECRYPT(`password`,'ENCRYPT-KEY') FROM `user`
WHERE AES_DECRYPT(`password`,'ENCRYPT-KEY') = 'password2';
復号後のデータ 
user_id| AES_DECRYPT(`password`,'ENCRYPT-KEY')
-------+-----------------------------------
user2    password2

# ただ、これだと、インデックス効かないな・・・

例２

 * 「例１」だと、データ型「VARBINARY」でないとダメなので、
   VARCHAR()で行える方法を考える
  => HEX() / UNHEX() を使用する

# でも、暗号化するなら、データ長も増えるから、いずれにせよデータ型の変更は伴うが。。。

テーブル

CREATE TABLE IF NOT EXISTS `user` (
	`user_id` VARCHAR(100) NOT NULL,
	`password` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`user_id`)
)
COMMENT='ユーザ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

暗号化 / INSERT文

「ENCRYPT-KEY」で暗号化 
INSERT INTO `user`(`user_id`, `password`) VALUES ('user1', HEX(AES_ENCRYPT('password1','ENCRYPT-KEY')));
INSERT INTO `user`(`user_id`, `password`) VALUES ('user2', HEX(AES_ENCRYPT('password2','ENCRYPT-KEY')));
INSERT INTO `user`(`user_id`, `password`) VALUES ('user3', HEX(AES_ENCRYPT('password3','ENCRYPT-KEY')));

復号化 / SELECT文

「ENCRYPT-KEY」で復号化 
SELECT `user_id`, AES_DECRYPT(UNHEX(`password`),'ENCRYPT-KEY') FROM `user`;

参考文献

* 助かりました
http://d.hatena.ne.jp/yamaback/20110819/1313735667
http://www.infinity-blue.jp/archives/23

http://plaza.rakuten.co.jp/leaklab/diary/201104270000/
https://www.gimmickgeek.com/2013/02/23/mysql%E3%81%A7%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E6%9A%97%E5%8F%B7%E5%8C%96%E3%82%92%E8%A1%8C%E3%81%86-aes_encrypt%E3%81%A8aes_decrypt/
http://blog.goo.ne.jp/kent-12_2008/e/500290b319d11dea4d43bbc310116a9d
http://blog.livedoor.jp/trisys/archives/747600.html
http://www.detelu.com/blog/2008/09/mysql%E3%81%A7%E5%8F%AF%E9%80%86%E6%9A%97%E5%8F%B7%E5%8C%96%E3%81%99%E3%82%8B/