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