MySQLでテキスト(日本語、半角英数字)を暗号化・復号化する方法

MySQLでデータを登録する際に暗号化したいというケースがあった。対象のデータは氏名等の日本語とメールアドレス等の半角英数字になる。また、データを取り出す際は当然復号化もしたい。PHP側で色々処理する必要があるのかと思いきやMySQL側で対応できた。以下に実装方法をメモ。

データベース構造

mysql> SHOW COLUMNS FROM test_table;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| mail  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

ソースコード

登録

INSERT INTO `test_table` 
    (`name`, `mail`) 
VALUES 
    ((HEX(AES_ENCRYPT('鈴木', 'pass'))), (HEX(AES_ENCRYPT('suzuki@test.com', 'pass')))),
    ((HEX(AES_ENCRYPT('山田', 'pass'))), (HEX(AES_ENCRYPT('yamada@test.com', 'pass'))));

取り出し(読み込み)

SELECT 
    `id`,
    CONVERT(
        AES_DECRYPT(UNHEX(name), 'passs')
        USING utf8
    ) AS name,
    CONVERT(
        AES_DECRYPT(UNHEX(mail), 'passs')
        USING utf8
    ) AS mail
FROM `test_table`;

検索

SELECT 
    `id`,
    CONVERT(
        AES_DECRYPT(UNHEX(name), 'pass')
        USING utf8
    ) AS name,
    CONVERT(
        AES_DECRYPT(UNHEX(mail), 'pass')
        USING utf8
    ) AS mail
FROM `test_table`
WHERE
    CONVERT(
        AES_DECRYPT(UNHEX(name), 'pass')
        USING utf8
    ) = '鈴木';

解説等

AES_ENCRYPT / AES_DECRYPT関数の第二引数にあるpass部分は適宜変更すること。尚、暗号化・復号化の際はpass部分が一致していないといけない点に注意。そのためSQL文を発行するPHP側で定数等で定義しておくと良さそう。

リファレンス

AES_ENCRYPT

https://dev.mysql.com/doc/refman/8.0/ja/encryption-functions.html#function_aes-encrypt

AES_DECRYPT

https://dev.mysql.com/doc/refman/8.0/ja/encryption-functions.html#function_aes-decrypt

PHPで暗号化・復号化したい場合

以下過去記事を参照。

PHPでBlowfishアルゴリズムを用いた暗号化と復号化方法