1# Tests of the AES functionality 2# 3# WL#6781: Support multiple AES encryption modes 4# 5#### AES_ENCRYPT return type 6# must be aes-128-ecb 7SELECT @@block_encryption_mode; 8@@block_encryption_mode 9aes-128-ecb 10# must work and return a string 11SELECT TO_BASE64(AES_ENCRYPT('a', 'b')); 12TO_BASE64(AES_ENCRYPT('a', 'b')) 13VIE8melxXCgTE0xsFy5JTg== 14# must return 16 15SELECT LENGTH(AES_ENCRYPT('a', 'b')); 16LENGTH(AES_ENCRYPT('a', 'b')) 1716 18# must return binary 19SELECT CHARSET(AES_ENCRYPT('a', 'b')); 20CHARSET(AES_ENCRYPT('a', 'b')) 21binary 22# must be equal 23SELECT AES_ENCRYPT('a', 'b') = AES_ENCRYPT('a', 'b'); 24AES_ENCRYPT('a', 'b') = AES_ENCRYPT('a', 'b') 251 26#### AES_ENCRYPT parameters 27# must return NULL 28SELECT AES_ENCRYPT('a', NULL); 29AES_ENCRYPT('a', NULL) 30NULL 31SELECT AES_ENCRYPT(NULL, 'a'); 32AES_ENCRYPT(NULL, 'a') 33NULL 34SELECT AES_ENCRYPT(NULL, NULL); 35AES_ENCRYPT(NULL, NULL) 36NULL 37# must return value 38SELECT TO_BASE64(AES_ENCRYPT('a', 0)); 39TO_BASE64(AES_ENCRYPT('a', 0)) 40aYJapBqdtJb5LdZYNnyvSQ== 41SELECT TO_BASE64(AES_ENCRYPT('a', 12.04)); 42TO_BASE64(AES_ENCRYPT('a', 12.04)) 43zsb8jPtLNXiWI5Kzwf0V0A== 44SELECT TO_BASE64(AES_ENCRYPT(0, 'a')); 45TO_BASE64(AES_ENCRYPT(0, 'a')) 466k2i7KJUMBKiOkGToSMgxg== 47SELECT TO_BASE64(AES_ENCRYPT(12.04, 'a')); 48TO_BASE64(AES_ENCRYPT(12.04, 'a')) 49TXCHis1z3ZT2p2daWZfwLg== 50SELECT TO_BASE64(AES_ENCRYPT(0, 0)); 51TO_BASE64(AES_ENCRYPT(0, 0)) 52Nop3grbtyVAOy+Ycpyx7RA== 53SELECT TO_BASE64(AES_ENCRYPT(12.04, 12.04)); 54TO_BASE64(AES_ENCRYPT(12.04, 12.04)) 55W4FA3x/RuDuacxCfEQY4pQ== 56#### parameter conversion 57must be equal 58SELECT AES_ENCRYPT('a', 12.04) = AES_ENCRYPT('a', '12.04'); 59AES_ENCRYPT('a', 12.04) = AES_ENCRYPT('a', '12.04') 601 61SELECT AES_ENCRYPT('a', 0) = AES_ENCRYPT('a', '0'); 62AES_ENCRYPT('a', 0) = AES_ENCRYPT('a', '0') 631 64SELECT AES_ENCRYPT(12.04, 'a') = AES_ENCRYPT('12.04', 'a'); 65AES_ENCRYPT(12.04, 'a') = AES_ENCRYPT('12.04', 'a') 661 67SELECT AES_ENCRYPT(0, 'a') = AES_ENCRYPT('0', 'a'); 68AES_ENCRYPT(0, 'a') = AES_ENCRYPT('0', 'a') 691 70SELECT AES_ENCRYPT('\x0Z', 'a') = AES_ENCRYPT('\x0Z', 'a'); 71AES_ENCRYPT('\x0Z', 'a') = AES_ENCRYPT('\x0Z', 'a') 721 73SELECT AES_ENCRYPT('a', '\x0Z') = AES_ENCRYPT('a', '\x0Z'); 74AES_ENCRYPT('a', '\x0Z') = AES_ENCRYPT('a', '\x0Z') 751 76# must not be equal 77SELECT AES_ENCRYPT('a', '\x0Y') = AES_ENCRYPT('a', '\x0Z'); 78AES_ENCRYPT('a', '\x0Y') = AES_ENCRYPT('a', '\x0Z') 790 80SELECT AES_ENCRYPT('\x0Y', 'a') = AES_ENCRYPT('\x0Z', 'a'); 81AES_ENCRYPT('\x0Y', 'a') = AES_ENCRYPT('\x0Z', 'a') 820 83#### algorithm 84# must not be equal 85SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('b', 'a'); 86AES_ENCRYPT('a', 'a') = AES_ENCRYPT('b', 'a') 870 88SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'b'); 89AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'b') 900 91SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('aa', 'a'); 92AES_ENCRYPT('a', 'a') = AES_ENCRYPT('aa', 'a') 930 94SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'aa'); 95AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'aa') 960 97SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT(REPEAT('a',1000), 'a'); 98AES_ENCRYPT('a', 'a') = AES_ENCRYPT(REPEAT('a',1000), 'a') 990 100SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', REPEAT('a',1000)); 101AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', REPEAT('a',1000)) 1020 103#### persistense 104CREATE TABLE t1 (a BINARY(16) PRIMARY KEY); 105# must pass without a warning 106INSERT INTO t1 VALUES (AES_ENCRYPT('a','a')); 107# must fail 108INSERT INTO t1 VALUES (AES_ENCRYPT('a','a')); 109ERROR 23000: Duplicate entry '{ W]\xA1\x06u\x9D\xBD\xB1\xA3.\xE2\xD9\xA7t' for key 'PRIMARY' 110# must pass 111INSERT INTO t1 VALUES (AES_ENCRYPT('b','a')); 112# must return 1 113SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('a', 'a'); 114COUNT(*) 1151 116# must return 1 117SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('b', 'a'); 118COUNT(*) 1191 120# must return 0 121SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('c', 'a'); 122COUNT(*) 1230 124SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('a', 'c'); 125COUNT(*) 1260 127SELECT TO_BASE64(a) FROM t1 ORDER BY a; 128TO_BASE64(a) 129eyBXXaEGdZ29saMu4tmndA== 130nZ4GgEfF5ib3dWk0Is8MFw== 131# cleanup 132DROP TABLE t1; 133#### IV 134# must be equal 135SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('a', 16)); 136AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('a', 16)) 1371 138Warnings: 139Warning 1618 <IV> option ignored 140SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('b', 16)); 141AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('b', 16)) 1421 143Warnings: 144Warning 1618 <IV> option ignored 145# must return a warning 146SELECT TO_BASE64(AES_ENCRYPT('a', 'a', 'a')); 147TO_BASE64(AES_ENCRYPT('a', 'a', 'a')) 148eyBXXaEGdZ29saMu4tmndA== 149Warnings: 150Warning 1618 <IV> option ignored 151# must pass 152SELECT TO_BASE64(AES_ENCRYPT('a', 'a', NULL)); 153TO_BASE64(AES_ENCRYPT('a', 'a', NULL)) 154eyBXXaEGdZ29saMu4tmndA== 155Warnings: 156Warning 1618 <IV> option ignored 157SELECT TO_BASE64(AES_ENCRYPT('a', 'a', REPEAT('a', 1024))); 158TO_BASE64(AES_ENCRYPT('a', 'a', REPEAT('a', 1024))) 159eyBXXaEGdZ29saMu4tmndA== 160Warnings: 161Warning 1618 <IV> option ignored 162SELECT TO_BASE64(AES_ENCRYPT('a', 'a', RANDOM_BYTES(16))); 163TO_BASE64(AES_ENCRYPT('a', 'a', RANDOM_BYTES(16))) 164eyBXXaEGdZ29saMu4tmndA== 165Warnings: 166Warning 1618 <IV> option ignored 167#### RANDOM_BYTES 168# must be 1 169SELECT LENGTH(RANDOM_BYTES(1)); 170LENGTH(RANDOM_BYTES(1)) 1711 172# must return binary 173SELECT CHARSET(RANDOM_BYTES(1)); 174CHARSET(RANDOM_BYTES(1)) 175binary 176# must return an error 177SELECT RANDOM_BYTES(1000000000000); 178ERROR 22003: length value is out of range in 'random_bytes' 179SELECT LENGTH(RANDOM_BYTES(0)); 180ERROR 22003: length value is out of range in 'random_bytes' 181#### AES_DECRYPT 182# must return binary 183SELECT CHARSET(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a')); 184CHARSET(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a')) 185binary 186# must return 1 187SELECT LENGTH(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a')); 188LENGTH(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a')) 1891 190# must be equal 191SELECT AES_DECRYPT(AES_ENCRYPT('a','a'), 'a') = 'a'; 192AES_DECRYPT(AES_ENCRYPT('a','a'), 'a') = 'a' 1931 194# must be equal 195SELECT AES_DECRYPT(AES_ENCRYPT(_UTF8'Жоро', 'a'), 'a') = _UTF8'Жоро'; 196AES_DECRYPT(AES_ENCRYPT(_UTF8'Жоро', 'a'), 'a') = _UTF8'Жоро' 1971 198SELECT AES_DECRYPT(AES_ENCRYPT('Жоро', 'a'), 'a') = 'Жоро'; 199AES_DECRYPT(AES_ENCRYPT('Жоро', 'a'), 'a') = 'Жоро' 2001 201# must be NULL 202SELECT AES_DECRYPT(NULL, 'a'); 203AES_DECRYPT(NULL, 'a') 204NULL 205SELECT AES_DECRYPT('a', NULL); 206AES_DECRYPT('a', NULL) 207NULL 208SELECT AES_DECRYPT(NULL, NULL); 209AES_DECRYPT(NULL, NULL) 210NULL 211#### AES_DECRYPT IV 212# must be equal 213SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', NULL); 214'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', NULL) 2151 216Warnings: 217Warning 1618 <IV> option ignored 218SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',16)); 219'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',16)) 2201 221Warnings: 222Warning 1618 <IV> option ignored 223SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',100)); 224'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',100)) 2251 226Warnings: 227Warning 1618 <IV> option ignored 228# must return a warning 229SELECT TO_BASE64(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', 'a')); 230TO_BASE64(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', 'a')) 231YQ== 232Warnings: 233Warning 1618 <IV> option ignored 234#### 128, 192 and 256 bit ECB 235CREATE TABLE aes_ecb(a VARCHAR(16), b128 CHAR(16), b192 CHAR(16), b256 CHAR(16)); 236INSERT INTO aes_ecb (a) VALUES ('a'), ('Жоро'), (REPEAT('a', 10)); 237SET SESSION block_encryption_mode='aes-128-ecb'; 238UPDATE aes_ecb SET b128 = AES_ENCRYPT(a, 'a'); 239SET SESSION block_encryption_mode='aes-192-ecb'; 240UPDATE aes_ecb SET b192 = AES_ENCRYPT(a, 'a'); 241SET SESSION block_encryption_mode='aes-256-ecb'; 242UPDATE aes_ecb SET b256 = AES_ENCRYPT(a, 'a'); 243# must return 0 244SELECT COUNT(*) FROM aes_ecb WHERE b128 = b192 OR B192 = b256 OR b128=b256; 245COUNT(*) 2460 247SET SESSION block_encryption_mode='aes-256-ecb'; 248# must return 3 249SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b256, 'a'); 250COUNT(*) 2513 252# must return 0 253SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b256, 'b'); 254COUNT(*) 2550 256SET SESSION block_encryption_mode='aes-192-ecb'; 257# must return 3 258SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b192, 'a'); 259COUNT(*) 2603 261# must return 0 262SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b192, 'b'); 263COUNT(*) 2640 265SET SESSION block_encryption_mode='aes-128-ecb'; 266# must return 3 267SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b128, 'a'); 268COUNT(*) 2693 270# must return 0 271SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b128, 'b'); 272COUNT(*) 2730 274SET SESSION block_encryption_mode=DEFAULT; 275DROP TABLE aes_ecb; 276#### cbc 277SET @IVA=REPEAT('a', 16); 278SET @IVB=REPEAT('b', 16); 279SET @KEY1=REPEAT('c', 16); 280SET @KEY2=REPEAT('d', 16); 281#### 128-cbc 282SET SESSION block_encryption_mode="aes-128-cbc"; 283# must throw an error without an IV 284SELECT AES_ENCRYPT('a', @KEY1); 285ERROR 42000: Incorrect parameter count in the call to native function 'aes_encrypt' 286block mode dependent. Must be non-0 and non-null 287SELECT LENGTH(AES_ENCRYPT('a', @KEY1, @IVA)); 288LENGTH(AES_ENCRYPT('a', @KEY1, @IVA)) 28916 290block mode dependent 291SELECT TO_BASE64(AES_ENCRYPT('a', @KEY1, @IVA)); 292TO_BASE64(AES_ENCRYPT('a', @KEY1, @IVA)) 293EDJBpPTlIfYc8nytlcwy0Q== 294# must be equal 295SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', @KEY1, @IVA), @KEY1, @IVA); 296'a' = AES_DECRYPT(AES_ENCRYPT('a', @KEY1, @IVA), @KEY1, @IVA) 2971 298# must not be equal 299SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVB); 300'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVB) 3010 302SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY2, @IVA); 303'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY2, @IVA) 304NULL 305SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('b',@KEY1, @IVA), @KEY1, @IVA); 306'a' = AES_DECRYPT(AES_ENCRYPT('b',@KEY1, @IVA), @KEY1, @IVA) 3070 308#### 192-cbc 309SET SESSION block_encryption_mode="aes-192-cbc"; 310# must throw an error without an IV 311SELECT AES_ENCRYPT('a', @KEY1); 312ERROR 42000: Incorrect parameter count in the call to native function 'aes_encrypt' 313# must be equal 314SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVA); 315'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVA) 3161 317# must not be equal 318SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVB); 319'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVB) 3200 321SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY2, @IVA); 322'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY2, @IVA) 323NULL 324SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('b',@KEY1, @IVA), @KEY1, @IVA); 325'a' = AES_DECRYPT(AES_ENCRYPT('b',@KEY1, @IVA), @KEY1, @IVA) 3260 327#### 256-cbc 328SET SESSION block_encryption_mode="aes-256-cbc"; 329# must throw an error without an IV 330SELECT AES_ENCRYPT('a', @KEY1); 331ERROR 42000: Incorrect parameter count in the call to native function 'aes_encrypt' 332# must be equal 333SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVA); 334'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVA) 3351 336# must not be equal 337SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVB); 338'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY1, @IVB) 3390 340SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY2, @IVA); 341'a' = AES_DECRYPT(AES_ENCRYPT('a',@KEY1, @IVA), @KEY2, @IVA) 342NULL 343SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('b',@KEY1, @IVA), @KEY1, @IVA); 344'a' = AES_DECRYPT(AES_ENCRYPT('b',@KEY1, @IVA), @KEY1, @IVA) 3450 346SET SESSION block_encryption_mode=DEFAULT; 347#### 128, 192 and 256 bit cbc 348CREATE TABLE aes_cbc(a VARCHAR(128), b128 VARCHAR(144), 349b192 VARCHAR(144), b256 CHAR(144)); 350INSERT INTO aes_cbc (a) VALUES (REPEAT('a', 128)); 351INSERT INTO aes_cbc (a) VALUES (REPEAT(0x00313233, 32)); 352SET SESSION block_encryption_mode="aes-128-cbc"; 353UPDATE aes_cbc SET b128 = AES_ENCRYPT(a, @KEY1, @IVA); 354SET SESSION block_encryption_mode="aes-192-cbc"; 355UPDATE aes_cbc SET b192 = AES_ENCRYPT(a, @KEY1, @IVA); 356SET SESSION block_encryption_mode="aes-256-cbc"; 357UPDATE aes_cbc SET b256 = AES_ENCRYPT(a, @KEY1, @IVA); 358# must return 0 359SELECT COUNT(*) FROM aes_cbc WHERE b128 = b192 OR B192 = b256 OR b128=b256; 360COUNT(*) 3610 362SET SESSION block_encryption_mode="aes-256-cbc"; 363# must return 2 364SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b256, @KEY1, @IVA); 365COUNT(*) 3662 367# must return 0 368SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b256, 'b', @IVA); 369COUNT(*) 3700 371# must return 0 372SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b256, @KEY1, @IVB); 373COUNT(*) 3740 375SET SESSION block_encryption_mode="aes-192-cbc"; 376# must return 2 377SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b192, @KEY1, @IVA); 378COUNT(*) 3792 380# must return 0 381SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b192, @KEY2, @IVA); 382COUNT(*) 3830 384# must return 0 385SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b256, @KEY1, @IVB); 386COUNT(*) 3870 388SET SESSION block_encryption_mode="aes-128-cbc"; 389# must return 2 390SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b128, @KEY1, @IVA); 391COUNT(*) 3922 393# must return 0 394SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b128, @KEY2, @IVA); 395COUNT(*) 3960 397# must return 0 398SELECT COUNT(*) FROM aes_cbc WHERE a = AES_DECRYPT(b256, @KEY2, @IVB); 399COUNT(*) 4000 401SET SESSION block_encryption_mode=DEFAULT; 402DROP TABLE aes_cbc; 403# 404# Bug #18259229: SERVER CRASHES WITH THE FUNCTION RANDOM_BYTES() 405# 406# INT_MAX - 9 (terminating 0 + rounding). Should not crash 407SELECT RANDOM_BYTES(2147483647 - 9); 408ERROR 22003: length value is out of range in 'random_bytes' 409# 410# End of 5.7 tests 411# 412