MySQL のデータを暗号化する

はじめに

業務でDBのデータの暗号化、複合化に関して少し触れたので忘れないように覚え書き。
MySQL のみで暗号化、複合化を行います。

準備

下記コマンド実行で test テーブルを作成

sql

CREATE TABLE IF NOT EXISTS `tests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

データを暗号化して複合化してみる

暗号化には AES_ENCRYPT() を使用します。

AES_ENCRYPT(str,key_str[,init_vector])
AES_ENCRYPT() は、鍵文字列 key_str を使用して文字列 str を暗号化し、暗号化された出力を含むバイナリ文字列を返します。

リファレンスに記載がある通り、str には暗号化するデータ、
key_str には暗号化、複合化する為に使用する鍵文字列を指定します。

では実際に暗号化していきます。
複合化までを通して進めていきたいので、所々変数を使用しています。
バイナリ文字列などをコピペしても正しく同一の文字列と認識されないようなので。

鍵作成

使用できる鍵の長さは 196 または 256 ビットです

とあるので、今回は256ビットの鍵を作成したいと思います。
(実際には128ビット以上であれば問題なく暗号化できているみたいなのですが、詳しくは分りませんでした。)

鍵を作成する為に、RANDOM_BYTES()を使用します。

この関数は、SSL ライブラリ (OpenSSL または yaSSL) の乱数ジェネレータを使用して生成されたランダムな len バイトのバイナリ文字列を返します。len で許可されている値は、1 から 1024 までの範囲内です。値がこの範囲外の場合、RANDOM_BYTES() は警告を生成し、NULL を返します。

sql

--32バイト(256ビット)のバイナリ文字列を生成
SELECT RANDOM_BYTES(32);
+----------------------------------+
| RANDOM_BYTES(32)                 |
+----------------------------------+
| ?�H'�f�TEnB��#(�)xّC����z         |
+----------------------------------+

このままだと扱いにくいので、

sql

-- 32バイト(256ビット)のバイナリ文字列を生成して、 HEX()で16進数に変換して変数 @key に代入 
SELECT @key := HEX(RANDOM_BYTES(32));
+------------------------------------------------------------------+
| @key := HEX(RANDOM_BYTES(32))                                    |
+------------------------------------------------------------------+
| 3999B97FF714860E4C38790621ADD729286597DBAFE69CE681005EFF0A8D84F1 |
+------------------------------------------------------------------+

-- 代入されているかチェック
SELECT @key;
+------------------------------------------------------------------+
| @key                                                             |
+------------------------------------------------------------------+
| 3999B97FF714860E4C38790621ADD729286597DBAFE69CE681005EFF0A8D84F1 |
+------------------------------------------------------------------+

暗号化

sql

-- test という文字列を、256ビット文字列を鍵として暗号化して変数 @encrypted に代入

SELECT @encrypted := AES_ENCRYPT('test',UNHEX(@key));
+-----------------------------------------------+
| @encrypted := AES_ENCRYPT('test',UNHEX(@key)) |
+-----------------------------------------------+
| ���|@�|eW�pL                                  |
+-----------------------------------------------+

-- 代入されているかチェック
 SELECT @encrypted;
+------------------+
| @encrypted       |
+------------------+
| ���|@�|eW�pL     |
+------------------+

@encryptedに入っているのが暗号化されたデータになります。

複合化

複合化には、 AES_DECRYPT() を使用します。

AES_DECRYPT(crypt_str,key_str[,init_vector])
AES_DECRYPT() は、鍵文字列 key_str を使用して暗号化された文字列 crypt_str を復号化し、元のプレーンテキスト文字列を返します。関数引数のいずれかが NULL の場合は、関数で NULL が返されます。

では複合化していきたいと思います。

sql

SELECT AES_DECRYPT(@encrypted,UNHEX(@key));
+-------------------------------------+
| AES_DECRYPT(@encrypted,UNHEX(@key)) |
+-------------------------------------+
| test                                |
+-------------------------------------+

testが表示されているので正しく複合化されました。

実際に暗号化してデータを登録してみる

sql

-- まずは普通にデータ登録して、データの確認をする
INSERT INTO tests (text) values ('test1');
Query OK, 1 row affected (0.005 sec)

SELECT * FROM tests;
+----+-------+
| id | text  |
+----+-------+
|  1 | test1 |
+----+-------+

-- 暗号化してデータを登録、確認してみます
-- バイナリ文字列をそのまま保存するわけにはいかないので、HEX()で16進数に変換します

INSERT INTO tests (text) values (HEX(AES_ENCRYPT('test2',UNHEX(@key))));
Query OK, 1 row affected (0.003 sec)

SELECT * FROM tests;
+----+----------------------------------+
| id | text                             |
+----+----------------------------------+
|  1 | test1                            |
|  2 | 85FEFFCF71B26BAEB67E8A5E6E365DD4 |
+----+----------------------------------+

text が暗号化されたデータが保存されました。
暗号化されたデータが正しいか確認したいと思います。

sql

SELECT id, AES_DECRYPT(UNHEX(text),UNHEX(@key)) FROM tests;
+----+--------------------------------------+
| id | AES_DECRYPT(UNHEX(text),UNHEX(@key)) |
+----+--------------------------------------+
|  1 | NULL                                 |
|  2 | test2                                |
+----+--------------------------------------+

あっていました。
ちなみに id=1 のレコードが NULL になっているのは、UNHEX(text)がNULLを返す為です。

検索してみる

最後にwhere文で、textカラムに対して条件を指定して検索してみたいと思います。

sql

-- 検索用にもう１つレコードを追加
INSERT INTO tests (text) values(HEX(AES_ENCRYPT('test3',UNHEX(@key))));
Query OK, 1 row affected (0.004 sec)

SELECT * FROM tests;
+----+----------------------------------+
| id | text                             |
+----+----------------------------------+
|  1 | test1                            |
|  2 | 85FEFFCF71B26BAEB67E8A5E6E365DD4 |
|  3 | C09048B3174421114619BE40A627DCCF |
+----+----------------------------------+

-- text=test3 の条件で検索
SELECT * FROM tests WHERE AES_DECRYPT(UNHEX(text),UNHEX(@key)) = 'test3';
+----+----------------------------------+
| id | text                             |
+----+----------------------------------+
|  3 | C09048B3174421114619BE40A627DCCF |
+----+----------------------------------+

-- 結果を複合化したければ
SELECT id, AES_DECRYPT(UNHEX(text),UNHEX(@key)) AS text FROM tests WHERE AES_DECRYPT(UNHEX(text),UNHEX(@key)) = 'test3';
+----+-------+
| id | text  |
+----+-------+
|  3 | test3 |
+----+-------+

のような感じで検索も出来ました。

参考にさせていただいた記事など