勉強したことのメモ

Webエンジニア / プログラマが勉強したことのメモ。

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

  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アルゴリズムを用いた暗号化と復号化方法

 - MySQL データベース

  関連記事

MySQLでINSERT時にオートインクリメントではないカラムに連番を登録する方法

MySQLでINSERT時にオートインクリメントではないカラムに連番を登録したい ...

MySQLでROW_NUMBER関数を使用してグループ毎に連番を設定する方法

以前にMySQLでオートインクリメントを使用せずにグループ毎に連番を設定する方法 ...

PHPでmysqli関数使用時のプリペアドステートメントの利用方法

PHPでMySQLを扱う際はmysqli関数を、エスケープの際はreal_esc ...

MySQLで文字列を置換する方法

MySQLで文字列の置換を行いたかった。以下に方法をメモ。   目次1 ...

MySQLでデータがあれば上書き、無ければ挿入する

既存のソースを編集時に「REPLACE INTO~~」 という見たことの無いSQ ...