1--echo # Tests of the AES functionality 2 3--echo # 4--echo # WL#6781: Support multiple AES encryption modes 5--echo # 6 7--echo #### AES_ENCRYPT return type 8 9--echo # must be aes-128-ecb 10SELECT @@block_encryption_mode; 11 12--echo # must work and return a string 13SELECT TO_BASE64(AES_ENCRYPT('a', 'b')); 14 15--echo # must return 16 16SELECT LENGTH(AES_ENCRYPT('a', 'b')); 17 18--echo # must return binary 19SELECT CHARSET(AES_ENCRYPT('a', 'b')); 20 21--echo # must be equal 22SELECT AES_ENCRYPT('a', 'b') = AES_ENCRYPT('a', 'b'); 23 24 25--echo #### AES_ENCRYPT parameters 26 27--echo # must return NULL 28SELECT AES_ENCRYPT('a', NULL); 29SELECT AES_ENCRYPT(NULL, 'a'); 30SELECT AES_ENCRYPT(NULL, NULL); 31 32--echo # must return value 33SELECT TO_BASE64(AES_ENCRYPT('a', 0)); 34SELECT TO_BASE64(AES_ENCRYPT('a', 12.04)); 35SELECT TO_BASE64(AES_ENCRYPT(0, 'a')); 36SELECT TO_BASE64(AES_ENCRYPT(12.04, 'a')); 37SELECT TO_BASE64(AES_ENCRYPT(0, 0)); 38SELECT TO_BASE64(AES_ENCRYPT(12.04, 12.04)); 39 40 41--echo #### parameter conversion 42 43--echo must be equal 44SELECT AES_ENCRYPT('a', 12.04) = AES_ENCRYPT('a', '12.04'); 45SELECT AES_ENCRYPT('a', 0) = AES_ENCRYPT('a', '0'); 46SELECT AES_ENCRYPT(12.04, 'a') = AES_ENCRYPT('12.04', 'a'); 47SELECT AES_ENCRYPT(0, 'a') = AES_ENCRYPT('0', 'a'); 48SELECT AES_ENCRYPT('\x0Z', 'a') = AES_ENCRYPT('\x0Z', 'a'); 49SELECT AES_ENCRYPT('a', '\x0Z') = AES_ENCRYPT('a', '\x0Z'); 50 51--echo # must not be equal 52SELECT AES_ENCRYPT('a', '\x0Y') = AES_ENCRYPT('a', '\x0Z'); 53SELECT AES_ENCRYPT('\x0Y', 'a') = AES_ENCRYPT('\x0Z', 'a'); 54 55 56--echo #### algorithm 57 58--echo # must not be equal 59SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('b', 'a'); 60SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'b'); 61SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('aa', 'a'); 62SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'aa'); 63SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT(REPEAT('a',1000), 'a'); 64SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', REPEAT('a',1000)); 65 66 67--echo #### persistense 68 69CREATE TABLE t1 (a BINARY(16) PRIMARY KEY); 70 71--echo # must pass without a warning 72INSERT INTO t1 VALUES (AES_ENCRYPT('a','a')); 73 74--echo # must fail 75--error ER_DUP_ENTRY 76INSERT INTO t1 VALUES (AES_ENCRYPT('a','a')); 77 78--echo # must pass 79INSERT INTO t1 VALUES (AES_ENCRYPT('b','a')); 80 81--echo # must return 1 82SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('a', 'a'); 83 84--echo # must return 1 85SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('b', 'a'); 86 87--echo # must return 0 88SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('c', 'a'); 89SELECT COUNT(*) FROM t1 WHERE a = AES_ENCRYPT('a', 'c'); 90 91SELECT TO_BASE64(a) FROM t1 ORDER BY a; 92 93--echo # cleanup 94DROP TABLE t1; 95 96 97--echo #### IV 98 99--echo # must be equal 100SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('a', 16)); 101SELECT AES_ENCRYPT('a', 'a') = AES_ENCRYPT('a', 'a', REPEAT('b', 16)); 102 103--echo # must return a warning 104SELECT TO_BASE64(AES_ENCRYPT('a', 'a', 'a')); 105 106--echo # must pass 107SELECT TO_BASE64(AES_ENCRYPT('a', 'a', NULL)); 108SELECT TO_BASE64(AES_ENCRYPT('a', 'a', REPEAT('a', 1024))); 109SELECT TO_BASE64(AES_ENCRYPT('a', 'a', RANDOM_BYTES(16))); 110 111 112--echo #### RANDOM_BYTES 113 114 115--echo # must be 1 116SELECT LENGTH(RANDOM_BYTES(1)); 117 118--echo # must return binary 119SELECT CHARSET(RANDOM_BYTES(1)); 120 121--echo # must return an error 122--error ER_DATA_OUT_OF_RANGE 123SELECT RANDOM_BYTES(1000000000000); 124 125--error ER_DATA_OUT_OF_RANGE 126SELECT LENGTH(RANDOM_BYTES(0)); 127 128 129--echo #### AES_DECRYPT 130 131--echo # must return binary 132SELECT CHARSET(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a')); 133 134--echo # must return 1 135SELECT LENGTH(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a')); 136 137--echo # must be equal 138SELECT AES_DECRYPT(AES_ENCRYPT('a','a'), 'a') = 'a'; 139 140--echo # must be equal 141SELECT AES_DECRYPT(AES_ENCRYPT(_UTF8'Жоро', 'a'), 'a') = _UTF8'Жоро'; 142SELECT AES_DECRYPT(AES_ENCRYPT('Жоро', 'a'), 'a') = 'Жоро'; 143 144--echo # must be NULL 145SELECT AES_DECRYPT(NULL, 'a'); 146SELECT AES_DECRYPT('a', NULL); 147SELECT AES_DECRYPT(NULL, NULL); 148 149 150--echo #### AES_DECRYPT IV 151 152--echo # must be equal 153SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', NULL); 154SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',16)); 155SELECT 'a' = AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', REPEAT('a',100)); 156 157--echo # must return a warning 158SELECT TO_BASE64(AES_DECRYPT(AES_ENCRYPT('a', 'a'), 'a', 'a')); 159 160 161--echo #### 128, 192 and 256 bit ECB 162 163 164CREATE TABLE aes_ecb(a VARCHAR(16), b128 CHAR(16), b192 CHAR(16), b256 CHAR(16)); 165INSERT INTO aes_ecb (a) VALUES ('a'), ('Жоро'), (REPEAT('a', 10)); 166 167SET SESSION block_encryption_mode='aes-128-ecb'; 168UPDATE aes_ecb SET b128 = AES_ENCRYPT(a, 'a'); 169 170SET SESSION block_encryption_mode='aes-192-ecb'; 171UPDATE aes_ecb SET b192 = AES_ENCRYPT(a, 'a'); 172 173SET SESSION block_encryption_mode='aes-256-ecb'; 174UPDATE aes_ecb SET b256 = AES_ENCRYPT(a, 'a'); 175 176--echo # must return 0 177SELECT COUNT(*) FROM aes_ecb WHERE b128 = b192 OR B192 = b256 OR b128=b256; 178 179SET SESSION block_encryption_mode='aes-256-ecb'; 180 181--echo # must return 3 182SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b256, 'a'); 183 184--echo # must return 0 185SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b256, 'b'); 186 187SET SESSION block_encryption_mode='aes-192-ecb'; 188 189--echo # must return 3 190SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b192, 'a'); 191 192--echo # must return 0 193SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b192, 'b'); 194 195SET SESSION block_encryption_mode='aes-128-ecb'; 196 197--echo # must return 3 198SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b128, 'a'); 199 200--echo # must return 0 201SELECT COUNT(*) FROM aes_ecb WHERE a = AES_DECRYPT(b128, 'b'); 202 203 204SET SESSION block_encryption_mode=DEFAULT; 205DROP TABLE aes_ecb; 206 207 208let $block_mode=cbc; 209source include/func_aes_block.inc; 210 211let $block_mode=default; 212 213 214--echo # 215--echo # Bug #18259229: SERVER CRASHES WITH THE FUNCTION RANDOM_BYTES() 216--echo # 217 218--echo # INT_MAX - 9 (terminating 0 + rounding). Should not crash 219--error ER_DATA_OUT_OF_RANGE 220SELECT RANDOM_BYTES(2147483647 - 9); 221 222 223--echo # 224--echo # End of 5.7 tests 225--echo # 226