1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
|
drop table if exists t1;
create table t1 (x blob);
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('a','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','a'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('ab','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','ab'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abc','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abc'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcd','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcd'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcde','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcde'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcdef','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdef'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcdefg','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefg'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcdefgh','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefgh'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcdefghi','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghi'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcdefghij','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghij'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('abcdefghijk','The quick red fox jumped over the lazy brown dog'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','abcdefghijk'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('The quick red fox jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('quick red fox jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('red fox jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('fox jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('dog!','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('dog!!','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('dog!!!','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('dog!!!!','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('dog!!!!!','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
insert into t1 values (des_encrypt('jumped over the lazy brown dog','sabakala'));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
select hex(x), hex(des_decrypt(x,'sabakala')) from t1;
hex(x) hex(des_decrypt(x,'sabakala'))
FFA185A4656D113445E31D7A5B31BB57671A4CA9E21E63FE5D9C801E0CC7AA6190C13E269C2AE8D8060D3FB3FEA94FEC7FB006B9DCAC3E3E41 NULL
FFC620C3B84E926A54 NULL
FFA4F77D4220A16C980AF7CB576F8DC0A864F357825C16F329F24F66EBA775765B7C1538B90970740F853B556AEBD35AC31B962EA9B12B5AD4 NULL
FFACFF5921BB876A90 NULL
FFA6F18760E7CC5A4C325244B8486F692AAA11D229AF9ED4E4C9D56D7C0278C0DDA58F73E15E2B5F6C1DDD19B22B8071C454C930585449AAEB NULL
FF3736DFEDC4B765F4 NULL
FF598681EA5F03CD6D6AEA2B118DF4885DD78BECDFB012BBB05386E436DC403D3CB9DE3BE8D5D3BB7FD90A1F9F9A3E055BB3B4FD3F6A869473 NULL
FF15B8B5952D630CAE NULL
FF11EE3A400685226B76D5EC567681FA90247CE3C9DBE43341311C22F74562B1199957D80E300737791F6345BBC61AE03F28F52E5A6DCC78B2 NULL
FF0A832AE10DC85483 NULL
FF6F5C0BF4C5F899B4E7C091D9B1F1E92A7623B651B150CA3E7F420B4DD316D2C1BF76FCF9F9A046C000A9E21C106591E8C1930201B1750269 NULL
FFA08D5FB849A9FC90 NULL
FFF7331312FE153A39B1EC0D65BC1D3A0B6FCD49DA8C95D6161F53B11D297BAE142BCA6B9492DAE9A02AF455F16CA2C1CF4E1AD17297E947E2 NULL
FFDEE60A938478E059 NULL
FF95A729E73D5D87416A53055029E8CAA95B4F7B49F3D2D821A95D1FCE70F4B7A3226077176723F3DCC0A44D3B2EE9EFBC4D31AA87C948916C NULL
FF1C78557F542A1FDC91943761B2EED14F NULL
FF1E35B0775EEE512544A75BDAF58EA1655F5C899D3C5191A47263E2D11C3E688F662AB79F66D3B1DF9C75BD869EB8E04FDAE85719CB573A43 NULL
FF1C78557F542A1FDCDC4182B5314185E5 NULL
FF783123DCB36F98A51C39A560C92E129F1DDEEAB170825406A61260FBFBBFB0F2E48DB3282588A975C9C71E0EACA71A2B642A8C9C2E921A9F NULL
FF1C78557F542A1FDCAC4B1B6B47206306 NULL
FF6D9B450837017D06CA1F1C9A0E700D03DEF06A4F954527A961CA805F70320E9F3F0007636B80768A253A5F7ADABC18B78F1A2FA560CC0B21 NULL
FF1C78557F542A1FDCE9038BD99DD43F2E NULL
FF23FD03BA7548DD0957EBA7A8FBF7A18589762F3913E9A935BDA72F6F28202DC64572E0D633A54EA55BFD2C749E408C8632CCE36A7AE00619 NULL
FFD8DD3C4ABCB02FCDFE1383ECC0F61E7D02CD3BA72BBAEA26384D14835796501B3DC9A2F7EC2FC1633BDA6D56464536FE12010049C53A1991 54686520717569636B2072656420666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FFACC5C5479575CBCA518B05778139B1BFC10F07299C98D04F580BC2F816828722D65A89C1831BD29DA626D319813BD374 717569636B2072656420666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FF128D5517241DEEC631ABD2A47FA66E57930001417F18204328B0B2CB13F7AD2F50B8336EFAE7DE21 72656420666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FF7CF971283B4DC2D050B3DB22684737B74B5B1CF12CF2FAC5A5995A298505F56D82BBFB9FC3E70059 666F78206A756D706564206F76657220746865206C617A792062726F776E20646F67
FF8333F3DD21E4488F967E03DD12394813A49F72848BB49473D3CB1C8A1AACF220 6A756D706564206F76657220746865206C617A792062726F776E20646F67
FFE8CB7FD80E6262C5FEB042A2DCC73B699CEEDCA6DC4458A0 6F76657220746865206C617A792062726F776E20646F67
FFA29334D7CDB1B403DF3EB992067DD524C7D568E8D98EBFE5 746865206C617A792062726F776E20646F67
FF4F0C5858FE2358D400E38831D5577C85 6C617A792062726F776E20646F67
FFB370CD6BAFD1CB95974D21DCCA2DD9D7 62726F776E20646F67
FF8F7777B28C7A459A 646F67
FF75213A4D7D01D715 646F6721
FF2DCAF574B173FB4D 646F672121
FFFA775787BE776B15 646F67212121
FF3FC2E42D7C840905 646F6721212121
FF9723312D26D9E6DA01D01A784A64DB9D 646F672121212121
FF8333F3DD21E4488F967E03DD12394813A49F72848BB49473D3CB1C8A1AACF220 6A756D706564206F76657220746865206C617A792062726F776E20646F67
FF8333F3DD21E4488F967E03DD12394813A49F72848BB49473D3CB1C8A1AACF220 6A756D706564206F76657220746865206C617A792062726F776E20646F67
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(x,'sabakala') as s from t1 having s like '%dog%';
s
The quick red fox jumped over the lazy brown dog
quick red fox jumped over the lazy brown dog
red fox jumped over the lazy brown dog
fox jumped over the lazy brown dog
jumped over the lazy brown dog
over the lazy brown dog
the lazy brown dog
lazy brown dog
brown dog
dog
dog!
dog!!
dog!!!
dog!!!!
dog!!!!!
jumped over the lazy brown dog
jumped over the lazy brown dog
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
drop table t1;
select hex(des_encrypt("hello")),des_decrypt(des_encrypt("hello"));
hex(des_encrypt("hello")) des_decrypt(des_encrypt("hello"))
85D6DC8859F9759BBB hello
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(des_encrypt("hello",4));
des_decrypt(des_encrypt("hello",4))
hello
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(des_encrypt("hello",'test'),'test');
des_decrypt(des_encrypt("hello",'test'),'test')
hello
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select hex(des_encrypt("hello")),hex(des_encrypt("hello",5)),hex(des_encrypt("hello",'default_password'));
hex(des_encrypt("hello")) hex(des_encrypt("hello",5)) hex(des_encrypt("hello",'default_password'))
85D6DC8859F9759BBB 85D6DC8859F9759BBB FFD6DC8859F9759BBB
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
select des_decrypt(des_encrypt("hello"),'default_password');
des_decrypt(des_encrypt("hello"),'default_password')
hello
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(des_encrypt("hello",4),'password4');
des_decrypt(des_encrypt("hello",4),'password4')
hello
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_encrypt("hello",10);
des_encrypt("hello",10)
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Warning 1108 Incorrect parameters to procedure 'des_encrypt'
select des_encrypt(NULL);
des_encrypt(NULL)
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
select des_encrypt(NULL, 10);
des_encrypt(NULL, 10)
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
select des_encrypt(NULL, NULL);
des_encrypt(NULL, NULL)
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
select des_encrypt(10, NULL);
des_encrypt(10, NULL)
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Warning 1108 Incorrect parameters to procedure 'des_encrypt'
select des_encrypt("hello", NULL);
des_encrypt("hello", NULL)
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Warning 1108 Incorrect parameters to procedure 'des_encrypt'
select des_decrypt("hello",10);
des_decrypt("hello",10)
hello
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(NULL);
des_decrypt(NULL)
NULL
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(NULL, 10);
des_decrypt(NULL, 10)
NULL
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(NULL, NULL);
des_decrypt(NULL, NULL)
NULL
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt(10, NULL);
des_decrypt(10, NULL)
10
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select des_decrypt("hello", NULL);
des_decrypt("hello", NULL)
hello
Warnings:
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
SET @a=des_decrypt(des_encrypt("hello"));
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
flush des_key_file;
select @a = des_decrypt(des_encrypt("hello"));
@a = des_decrypt(des_encrypt("hello"))
1
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select hex("hello");
hex("hello")
68656C6C6F
select hex(des_decrypt(des_encrypt("hello",4),'password2'));
hex(des_decrypt(des_encrypt("hello",4),'password2'))
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
select hex(des_decrypt(des_encrypt("hello","hidden")));
hex(des_decrypt(des_encrypt("hello","hidden")))
NULL
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
Warning 1108 Incorrect parameters to procedure 'des_decrypt'
explain extended select des_decrypt(des_encrypt("hello",4),'password2'), des_decrypt(des_encrypt("hello","hidden"));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
Note 1003 select des_decrypt(des_encrypt('hello',4),'password2') AS `des_decrypt(des_encrypt("hello",4),'password2')`,des_decrypt(des_encrypt('hello','hidden')) AS `des_decrypt(des_encrypt("hello","hidden"))`
#
# Start of 10.1 tests
#
#
# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
#
CREATE TABLE t1 (a INT(6) ZEROFILL);
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=1 AND DES_ENCRYPT('test',a)=_latin1 'abc' COLLATE latin1_bin;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and des_encrypt('test',`test`.`t1`.`a`) = 'abc'
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-10134 Add full support for DEFAULT
#
CREATE TABLE t1 (
a VARCHAR(30),
b BLOB DEFAULT DES_ENCRYPT(a, 'passwd'),
c TEXT DEFAULT DES_DECRYPT(b, 'passwd')
);
Warnings:
Note 1287 'des_encrypt' is deprecated and will be removed in a future release
Note 1287 'des_decrypt' is deprecated and will be removed in a future release
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` varchar(30) DEFAULT NULL,
`b` blob DEFAULT des_encrypt(`a`,'passwd'),
`c` text DEFAULT des_decrypt(`b`,'passwd')
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
INSERT INTO t1 (a) VALUES ('test');
SELECT c FROM t1;
c
test
DROP TABLE t1;
#
# End of 10.2 tests
#
|
