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で直近〇日、〇ヶ月、〇年以内のデータを検索する方法
他社が作成されたSQL文を見ているとDATE_ADDという使ったことの無い関数が ...
-
-
MySQLでユーザー作成時にエラーが発生
SSHからrootアカウントでMySQLにログインし、ユーザーを作成しようとする ...
-
-
MySQLでテーブル構造とデータをコピーする方法
MySQLで既にあるテーブルとデータに対して実験を行う必要があった。本番テーブル ...
-
-
MySQLで歯抜けになっているint型の数値を取得する
MySQLで歯抜けになっているシーケンス番号を 取得したいケースがあった。 ■ソ ...
-
-
MySQLで文字列を置換する方法
MySQLで文字列の置換を行いたかった。以下に方法をメモ。 目次1 ...