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で暗号化・復号化したい場合
以下過去記事を参照。
関連記事
-
-
MySQLで文字列を置換する方法
MySQLで文字列の置換を行いたかった。以下に方法をメモ。 目次1 ...
-
-
MySQLでtext型カラムに入っている数値をint型としてソートする
MySQLでtext型として指定されているカラムがあり、その中には文字列であった ...
-
-
MySQLでwhere句を使ってカラム指定でデータをコピーする方法
MySQLで大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且 ...
-
-
SortableJSを使ってデータの並べ替え&保存
あるシステムを拝見した際にtableタグ内に並んでいる項目をドラッグ&ド ...
-
-
MySQLで大文字小文字
半角英数字のみのフォームで入力した値を検索した際、 データに差異が発生。 [Aa ...