MySQL5.6.21の商用版でよりセキュアな暗号化が提供されたようなので後程確認するとして、
まずは現状のAESの暗号化を復習してみる。

以下抜粋
—————————————————-
http://dev.mysql.com/doc/refman/5.6/en/enterprise-encryption.html

As of MySQL 5.6.21, MySQL Enterprise Edition includes a set of encryption functions
based on the OpenSSL library that expose OpenSSL capabilities at the SQL level.
These functions enable Enterprise applications to perform the following operations:

Implement added data protection using public-key asymmetric cryptography
Create public and private keys and digital signatures
Perform asymmetric encryption and decryption
Use cryptographic hashing for digital signing and data verification and validation
Enterprise Encryption supports the RSA, DSA, and DH cryptographic algorithms.
Enterprise Encryption is supplied as a user-defined function (UDF) library, from which individual functions can be installed individually.

■これまでのAESを利用しての暗号化の復習
name列は暗号化された時の桁数を計算するのが面倒だったので大目で1024で設定しています。

CREATE TABLE `Personal_Info` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(1024) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

root@localhost > CREATE TABLE `Personal_Info` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(1024) NOT NULL DEFAULT '',
    ->    PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

root@localhost > INSERT INTO Personal_Info(name) values (HEX(AES_ENCRYPT("AES暗号化-個人情報01", 'password')));
Query OK, 1 row affected (0.04 sec)

root@localhost > select * from Personal_Info;
+----+------------------------------------------------------------------+
| id | name                                                             |
+----+------------------------------------------------------------------+
|  1 | D8F991170C3468696E4D963AE4A8E2A1D6404D7066F30A5D0419C0EC80D0602B |
+----+------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT CONVERT(AES_DECRYPT(UNHEX(name),"password") USING utf8) name FROM Personal_Info;
+-----------------------------+
| name                        |
+-----------------------------+
| AES暗号化-個人情報01        |
+-----------------------------+
1 row in set (0.00 sec)

root@localhost > 

Comments are closed.

Post Navigation