MySQL でデータを暗号復号化する方法

• 概要
• 環境
• テーブル作成
• 暗号化データ登録
• データを復号化する
• 最後に
• 参考サイト

概要

今回は AES_ENCRYPT を使った方法を紹介します

環境

• macOS 11.6
• MySQL 8.0.26

テーブル作成

暗号化するカラムは VARBINARY 型で作成するのがポイントです

create table user (id int primary key auto_increment, name varchar(20), password varbinary(255));

暗号化データ登録

encrypt_secret_key は好きな文字列で OK です 暗号化および復号化する際に必要なシークレット情報になります
今回は直接指定していますが事前にハッシュ化してそれを指定するほうがよりセキュアです

insert into user values (null, 'hawksnowlog', AES_ENCRYPT('password12345', SHA2('encrypt_secret_key',512)));

or

select SHA2('encrypt_secret_key',512));
insert into user values (null, 'hawksnowlog', AES_ENCRYPT('password12345', '07f4fcde88f57126755e1f58c78ef8c97cd24b210cb198ae18c19c5010322b23b510198a996cdc25039f99ff9eaf7e34fbda81b99c83621bcddeec225f1ecf5d'));

データを確認すると以下のような暗号化された データが登録されているのが確認できます

mysql> select * from user;
+----+-------------+------------------------------------+
| id | name        | password                           |
+----+-------------+------------------------------------+
|  2 | hawksnowlog | 0x7119DC241B2B245C4CBA5CBE9FC5E90B |
+----+-------------+------------------------------------+
1 row in set (0.00 sec)

データを復号化する

AES_DECRYPT の1つ目の引数はカラム名です 2つ目の引数で先程暗号化にしようしたシークレット情報を使います

またカラムは varbinary なのでこれを char にコンバートすることで人間にもわかる文字列に変換できます

select convert(AES_DECRYPT(`password`, SHA2('encrypt_secret_key',512)), char(255)) from user;

or

select convert(AES_DECRYPT(`password`, '07f4fcde88f57126755e1f58c78ef8c97cd24b210cb198ae18c19c5010322b23b510198a996cdc25039f99ff9eaf7e34fbda81b99c83621bcddeec225f1ecf5d'), char(255)) from user;

最後に

暗号化するカラムは varbinary で定義するほうが良いみたいです

varchart + hex を使って保存するケースも見ますが公式では推奨ではないようです

参考サイト

• https://dev.mysql.com/doc/refman/5.6/ja/encryption-functions.html