Tag Archives: Encrypt

MySQL暗号化 Community EditionとEnterprise

“AES 128　MySQL 4.0.2～”

SET @key_str = SHA2('password',512);
SELECT HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str)) into @AES_ENC;
SELECT @AES_ENC;
SELECT AES_DECRYPT(UNHEX(@AES_ENC),@key_str);

“AES 256　MySQL 5.6.17～ “

SELECT @@session.block_encryption_mode;
SET block_encryption_mode = 'aes-256-cbc';
SELECT @@session.block_encryption_mode;
SET @key_str = SHA2('password',512);
SELECT @key_str;
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str,@init_vector));
SELECT @crypt_str;
SELECT AES_DECRYPT(UNHEX(@crypt_str),@key_str,@init_vector);

“RSAを同じセッションで確認。MySQL 5.6.21～”
こちらはEnterpriseのみで利用可能なPlugin　（鍵をPublicとPrivateに分ける事が可能）

SELECT CREATE_ASYMMETRIC_PRIV_KEY('RSA', 1024) INTO @priv_key;
SELECT CREATE_ASYMMETRIC_PUB_KEY('RSA', @priv_key) INTO @pub_key;
SELECT ASYMMETRIC_ENCRYPT('RSA','プライベート鍵にて暗号化しています', @priv_key) INTO @enc_priv;
SELECT ASYMMETRIC_ENCRYPT('RSA','公開鍵にて暗号化しています', @pub_key) INTO @enc_pub;
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_pub, @priv_key);
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_priv, @pub_key);

―以下メモ―
■暗号化された文字列の長さの確認
The length of crypt_str can be calculated using this formula:
16 * (trunc(string_length / 16) + 1)

root@localhost > SELECT 16 * (TRUNCATE(100/16,1) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,1) + 1) |
+-------------------------------+
|                         115.2 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(100/16,0) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,0) + 1) |
+-------------------------------+
|                           112 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > 


root@localhost > select LENGTH("AES暗号化-個人情報01");
+---------------------------------------+
| LENGTH("AES暗号化-個人情報01")        |
+---------------------------------------+
|                                    27 |
+---------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(27/16,0) + 1);
+------------------------------+
| 16 * (TRUNCATE(27/16,0) + 1) |
+------------------------------+
|                           32 |
+------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT LENGTH(@crypt_str);
+--------------------+
| LENGTH(@crypt_str) |
+--------------------+
|                 64 |
+--------------------+
1 row in set (0.00 sec)

root@localhost > 

■HEXでサイズが2倍になっているので、UNHEXしてあげるとサイズが想定通りであると確認出来る。

root@localhost > SELECT LENGTH(UNHEX(@crypt_str));
+---------------------------+
| LENGTH(UNHEX(@crypt_str)) |
+---------------------------+
|                        32 |
+---------------------------+
1 row in set (0.00 sec)

root@localhost >