AES256 データ暗号化、複合化
input string |
varbinary長さ |
暗号化HEXのvarchar長さ(日本語基準) |
100 |
310 |
608 |
150 |
470 |
928 |
200 |
620 |
1216 |
250 |
770 |
1504 |
１．暗号化関数
DROP FUNCTION if exists `fnEnVarchar`;
DELIMITER $$
CREATE FUNCTION `fnEnVarchar`(
prmValue varchar(500)
) RETURNS varbinary(1024)
DETERMINISTIC
BEGIN
## select @@session.block_encryption_mode;
SET @@session.block_encryption_mode = 'aes-256-ecb';
return aes_encrypt(prmValue,'Xb6f_9Nx5K!MA_RTnzqf');
END$$
DELIMITER ;
DROP FUNCTION if exists `fnEnVarcharHex`;
DELIMITER $$
CREATE FUNCTION `fnEnVarcharHex`(
prmValue varchar(500)
) RETURNS varchar(1600)
DETERMINISTIC
BEGIN
## select @@session.block_encryption_mode;
SET @@session.block_encryption_mode = 'aes-256-ecb';
return HEX(aes_encrypt(prmValue,'Xb6f_9Nx5K!MA_RTnzqf'));
END$$
DELIMITER ;
２．復号化関数
DROP FUNCTION if exists `fnDeVarchar`;
DELIMITER $$
CREATE FUNCTION `fnDeVarchar`(
prmValue varbinary(1024)
) RETURNS varchar(500)
DETERMINISTIC
BEGIN
## select @@session.block_encryption_mode;
SET @@session.block_encryption_mode = 'aes-256-ecb';
## return cast(aes_decrypt(prmValue ,'Xb6f_9Nx5K!MA_RTnzqf') as char(500));
return convert(aes_decrypt(prmValue, 'Xb6f_9Nx5K!MA_RTnzqf') USING utf8mb4);
END$$
DELIMITER ;
DROP FUNCTION if exists `fnDeVarcharHex`;
DELIMITER $$
CREATE FUNCTION `fnDeVarcharHex`(
prmValue varchar(1600)
) RETURNS varchar(500)
DETERMINISTIC
BEGIN
## select @@session.block_encryption_mode;
SET @@session.block_encryption_mode = 'aes-256-ecb';
## return cast(aes_decrypt(UNHEX(prmValue) ,'Xb6f_9Nx5K!MA_RTnzqf') as char(500));
return convert(aes_decrypt(UNHEX(prmValue), 'Xb6f_9Nx5K!MA_RTnzqf') USING utf8mb4);
END$$
DELIMITER ;
3．テスト実行
DROP TABLE IF EXISTS `tTempData`;
CREATE TABLE `tTempData` (
`Data1` varchar(250) DEFAULT NULL,
`Data2` varbinary(1024) DEFAULT NULL,
`Data2Hex` varchar(1500) DEFAULT NULL
) ;
INSERT INTO tTempData
SELECT 'TEST', fnEnVarchar('test'), fnEnVarcharHex('test');
SELECT Data1
, fnDeVarchar(Data2) AS Data2
, fnDeVarcharHex(Data2Hex) AS Data2Hex
FROM tTempData;