set tidb_cost_model_version=1;
# Tests of the AES functionality
#
# WL#6781: Support multiple AES encryption modes
#
#### AES_ENCRYPT return type
# must be aes-128-ecb
SELECT @@block_encryption_mode;
@@block_encryption_mode
aes-128-ecb
# must work and return a string
SELECT TO_BASE64(AES_ENCRYPT('a', 'b'));
TO_BASE64(AES_ENCRYPT('a', 'b'))
VIE8melxXCgTE0xsFy5JTg==
# must return 16
SELECT LENGTH(AES_ENCRYPT('a', 'b'));
LENGTH(AES_ENCRYPT('a', 'b'))
16
# must be equal
SELECT AES_ENCRYPT('a', 'b') = AES_ENCRYPT('a', 'b');
AES_ENCRYPT('a', 'b') = AES_ENCRYPT('a', 'b')
1
#### AES_ENCRYPT parameters
# must return NULL
SELECT AES_ENCRYPT('a', NULL);
AES_ENCRYPT('a', NULL)
NULL
SELECT AES_ENCRYPT(NULL, 'a');
AES_ENCRYPT(NULL, 'a')
NULL
SELECT AES_ENCRYPT(NULL, NULL);
AES_ENCRYPT(NULL, NULL)
NULL
# must return value
SELECT TO_BASE64(AES_ENCRYPT('a', 0));
TO_BASE64(AES_ENCRYPT('a', 0))
aYJapBqdtJb5LdZYNnyvSQ==
SELECT TO_BASE64(AES_ENCRYPT('a', 12.04));
TO_BASE64(AES_ENCRYPT('a', 12.04))
zsb8jPtLNXiWI5Kzwf0V0A==
SELECT TO_BASE64(AES_ENCRYPT(0, 'a'));
TO_BASE64(AES_ENCRYPT(0, 'a'))
6k2i7KJUMBKiOkGToSMgxg==
SELECT TO_BASE64(AES_ENCRYPT(12.04, 'a'));
TO_BASE64(AES_ENCRYPT(12.04, 'a'))
TXCHis1z3ZT2p2daWZfwLg==
SELECT TO_BASE64(AES_ENCRYPT(0, 0));
TO_BASE64(AES_ENCRYPT(0, 0))
Nop3grbtyVAOy+Ycpyx7RA==
SELECT TO_BASE64(AES_ENCRYPT(12.04, 12.04));
TO_BASE64(AES_ENCRYPT(12.04, 12.04))
W4FA3x/RuDuacxCfEQY4pQ==
#### parameter conversion
must be equal
SELECT AES_ENCRYPT('a', 12.04) = AES_ENCRYPT('a', '12.04');
AES_ENCRYPT('a', 12.04) = AES_ENCRYPT('a', '12.04')
1
SELECT AES_ENCRYPT('a', 0) = AES_ENCRYPT('a', '0');
AES_ENCRYPT('a', 0) = AES_ENCRYPT('a', '0')
1
SELECT AES_ENCRYPT(12.04, 'a') = AES_ENCRYPT('12.04', 'a');
AES_ENCRYPT(12.04, 'a') = AES_ENCRYPT('12.04', 'a')
1
SELECT AES_ENCRYPT(0, 'a') = AES_ENCRYPT('0', 'a');
AES_ENCRYPT(0, 'a') = AES_ENCRYPT('0', 'a')
1
SELECT AES_ENCRYPT('\x0Z', 'a') = AES_ENCRYPT('\x0Z', 'a');
AES_ENCRYPT('\x0Z', 'a') = AES_ENCRYPT('\x0Z', 'a')
1
SELECT AES_ENCRYPT('a', '\x0Z') = AES_ENCRYPT('a', '\x0Z');
AES_ENCRYPT('a', '\x0Z') = AES_ENCRYPT('a', '\x0Z')
1
# must not be equal
SELECT AES_ENCRYPT('a', '\x0Y') = AES_ENCRYPT('a', '\x0Z');
AES_ENCRYPT('a', '\x0Y') = AES_ENCRYPT('a', '\x0Z')
0
SELECT AES_ENCRYPT('\x0Y', 'a') = AES_ENCRYPT('\x0Z', 'a');
AES_ENCRYPT('\x0Y', 'a') = AES_ENCRYPT('\x0Z', 'a')
0
#### algorithm
# must not be equal
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('b', 'a');
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('b', 'a')
0
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'b');
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'b')
0
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('aa', 'a');
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('aa', 'a')
0
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'aa');
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'aa')
0
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT(REPEAT('a',1000), 'a');
AES_ENCRYPT('a', 'a') = AES_ENCRYPT(REPEAT('a',1000), 'a')
0
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', REPEAT('a',1000));
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', REPEAT('a',1000))
0
#### persistense
CREATE TABLE t1 (a BINARY(16) PRIMARY KEY);
# must pass without a warning
INSERT INTO t1 VALUES (AES_ENCRYPT('a','a'));
# must pass
INSERT INTO t1 VALUES (AES_ENCRYPT('b','a'));
# must return 1
SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('a', 'a');
COUNT(*)
1
# must return 1
SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('b', 'a');
COUNT(*)
1
# must return 0
SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('c', 'a');
COUNT(*)
0
SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('a', 'c');
COUNT(*)
0
SELECT TO_BASE64(a) FROM t1 ORDER BY a;
TO_BASE64(a)
eyBXXaEGdZ29saMu4tmndA==
nZ4GgEfF5ib3dWk0Is8MFw==
# cleanup
DROP TABLE t1;
#### IV
# must be equal
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('a', 16));
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('a', 16))
1
SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('b', 16));
AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('b', 16))
1
# must return a warning
SELECT TO_BASE64(AES_ENCRYPT('a', 'a', 'a'));
TO_BASE64(AES_ENCRYPT('a', 'a', 'a'))
eyBXXaEGdZ29saMu4tmndA==
# must pass
SELECT TO_BASE64(AES_ENCRYPT('a', 'a', NULL));
TO_BASE64(AES_ENCRYPT('a', 'a', NULL))
eyBXXaEGdZ29saMu4tmndA==
SELECT TO_BASE64(AES_ENCRYPT('a', 'a', REPEAT('a', 1024)));
TO_BASE64(AES_ENCRYPT('a', 'a', REPEAT('a', 1024)))
eyBXXaEGdZ29saMu4tmndA==
SELECT TO_BASE64(AES_ENCRYPT('a', 'a', RANDOM_BYTES(16)));
TO_BASE64(AES_ENCRYPT('a', 'a', RANDOM_BYTES(16)))
eyBXXaEGdZ29saMu4tmndA==
#### RANDOM_BYTES
# must be 1
SELECT LENGTH(RANDOM_BYTES(1));
LENGTH(RANDOM_BYTES(1))
1
# must return an error
SELECT RANDOM_BYTES(1000000000000);
Error 1690: length value is out of range in 'random_bytes'
SELECT LENGTH(RANDOM_BYTES(0));
Error 1690: length value is out of range in 'random_bytes'
#### AES_DECRYPT
# must return 1
SELECT LENGTH(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a'));
LENGTH(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a'))
1
# must be equal
SELECT AES_DECRYPT(AES_ENCRYPT('a','a'), 'a') = 'a';
AES_DECRYPT(AES_ENCRYPT('a','a'), 'a') = 'a'
1
# must be equal
SELECT AES_DECRYPT(AES_ENCRYPT(_UTF8'Жоро', 'a'), 'a') = _UTF8'Жоро';
AES_DECRYPT(AES_ENCRYPT(_UTF8'Жоро', 'a'), 'a') = _UTF8'Жоро'
1
SELECT AES_DECRYPT(AES_ENCRYPT('Жоро', 'a'), 'a') = 'Жоро';
AES_DECRYPT(AES_ENCRYPT('Жоро', 'a'), 'a') = 'Жоро'
1
# must be NULL
SELECT AES_DECRYPT(NULL, 'a');
AES_DECRYPT(NULL, 'a')
NULL
SELECT AES_DECRYPT('a', NULL);
AES_DECRYPT('a', NULL)
NULL
SELECT AES_DECRYPT(NULL, NULL);
AES_DECRYPT(NULL, NULL)
NULL
#### AES_DECRYPT IV
# must be equal
SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', NULL);
'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', NULL)
1
SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',16));
'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',16))
1
SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',100));
'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',100))
1
# must return a warning
SELECT TO_BASE64(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', 'a'));
TO_BASE64(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', 'a'))
YQ==
#### 128, 192 and 256 bit ECB
CREATE TABLE aes_ecb(a VARBINARY(16), b128 CHAR(16), b192 CHAR(16), b256 CHAR(16)) charset latin1;
INSERT INTO aes_ecb (a) VALUES ('a'), ('Жоро'), (REPEAT('a', 10));
SET SESSION block_encryption_mode='aes-128-ecb';
UPDATE aes_ecb SET b128 = AES_ENCRYPT(a, 'a');
SET SESSION block_encryption_mode='aes-192-ecb';
UPDATE aes_ecb SET b192 = AES_ENCRYPT(a, 'a');
SET SESSION block_encryption_mode='aes-256-ecb';
UPDATE aes_ecb SET b256 = AES_ENCRYPT(a, 'a');
# must return 0
SELECT COUNT(*) FROM aes_ecb WHERE b128 = b192 OR B192 = b256 OR b128=b256;
COUNT(*)
0
SET SESSION block_encryption_mode='aes-256-ecb';
# must return 3
SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b256, 'a');
COUNT(*)
3
# must return 0
SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b256, 'b');
COUNT(*)
0
SET SESSION block_encryption_mode='aes-192-ecb';
# must return 3
SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b192, 'a');
COUNT(*)
3
# must return 0
SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b192, 'b');
COUNT(*)
0
SET SESSION block_encryption_mode='aes-128-ecb';
# must return 3
SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b128, 'a');
COUNT(*)
3
# must return 0
SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b128, 'b');
COUNT(*)
0
SET SESSION block_encryption_mode=DEFAULT;
DROP TABLE aes_ecb;
#
# Bug #18259229: SERVER CRASHES WITH THE FUNCTION RANDOM_BYTES()
#
# INT_MAX - 9 (terminating 0 + rounding). Should not crash
SELECT RANDOM_BYTES(2147483647 - 9);
Error 1690: length value is out of range in 'random_bytes'
#
# End of 5.7 tests
#