summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/func_crypt.test
blob: cf6a6371e5027fe45f7ead468aab1291d00fedc5 (plain)
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
-- source include/have_crypt.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

select length(encrypt('foo', 'ff')) <> 0;
--replace_result $1$aa$4OSUA5cjdx0RUQ08opV27/ aaqPiZY5xR5l.

create table t1 (name varchar(50), pw varchar(64));
insert into t1 values ('tom', password('my_pass'));
set @pass='my_pass';
select name from t1 where name='tom' and pw=password(@pass);
select name from t1 where name='tom' and pw=password(@undefined);
drop table t1;

# Test new and old password handling functions 

select password('abc');
select password('');
select old_password('abc');
select old_password('');
select password('gabbagabbahey');
select old_password('idkfa');
select length(password('1'));
--replace_result 60 13
select length(encrypt('test'));
--replace_result  \$2a\$04\$aO....................ql.D6ROU4Byvysj72xrV1ZAkrMKS8I6 aaqPiZY5xR5l.
select encrypt('test','aa');
select old_password(NULL);
select password(NULL);
set global old_passwords=on;
select password('');
select old_password('');
select password('idkfa');
select old_password('idkfa');
set old_passwords=on;
select password('idkfa');
select old_password('idkfa');
set global old_passwords=off;
select password('idkfa');
select old_password('idkfa');

# this test shows that new scrambles honor spaces in passwords:
set old_passwords=off;
select password('idkfa ');
select password('idkfa');
select password(' idkfa');
select old_password('idkfa');
select old_password(' i 	 d k f a ');

explain extended select password('idkfa '), old_password('idkfa');

#
# using encrypt & substring_index in view (Bug#7024)
#
CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd");
disable_result_log;
SELECT * FROM v1;
enable_result_log;
drop view v1;
CREATE VIEW v1 AS SELECT SUBSTRING_INDEX("dkjhgd:kjhdjh", ":", 1);
SELECT * FROM v1;
drop view v1;

#
# Bug #13619: Crash on FreeBSD with salt like '_.'
#
--replace_column 1 #
select encrypt('1234','_.');

# End of 4.1 tests

--echo #
--echo # Bug #44767: invalid memory reads in password() and old_password() 
--echo #             functions
--echo #

CREATE TABLE t1(c1 MEDIUMBLOB);
INSERT INTO t1 VALUES (REPEAT('a', 1024));
SELECT OLD_PASSWORD(c1), PASSWORD(c1) FROM t1;
DROP TABLE t1;

#
# Bug#44365 valgrind warnings with encrypt() function
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (f1 smallint(6) default null, f2  mediumtext character set utf8)
engine=myisam default charset=latin1;
insert into t1 values (null,'contraction\'s');
insert into t1 values (-15818,'requirement\'s');
--disable_result_log
select encrypt(f1,f2) as a from t1,(select encrypt(f1,f2) as b from t1) a;
--enable_result_log
drop table t1;

--echo # End of 5.0 tests
--echo #
--echo # Start of 10.0 tests
--echo #

--echo #
--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
--echo #

SET @save_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='derived_merge=on';
# ENCRYPT() is not affected by MDEV-10306
# It already uses tmp_value only for internal purposes and
# returns the result in the String passed to val_str()
CREATE TABLE t1 (t VARCHAR(32) CHARSET latin1);
INSERT INTO t1 VALUES('abcdefghi');
SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT ENCRYPT(t,'aa') t2 FROM t1) sub;
DROP TABLE t1;
SET optimizer_switch=@save_optimizer_switch;

--echo #
--echo # End of 10.0 tests
--echo #

--echo #
--echo # Start of 10.1 tests
--echo #

--let func=password
--source include/func_str_ascii_checksum.inc
--let func=old_password
--source include/func_str_ascii_checksum.inc

--echo #
--echo # MDEV-10864 Wrong result for WHERE .. (f2=COMPRESS('test') OR f2=COMPRESS('TEST'))
--echo #

CREATE TABLE t1 (f1 VARCHAR(4), f2 VARCHAR(64), UNIQUE KEY k1 (f1,f2));
INSERT INTO t1 VALUES ('test',encrypt('test','key')), ('TEST', encrypt('TEST','key'));
SELECT f1 FROM t1 ignore index(k1) WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key'));
SELECT f1 FROM t1                  WHERE f1='test' AND (f2= encrypt('test','key') OR f2= encrypt('TEST','key'));
SELECT f1 FROM t1                  WHERE f1='test' AND (f2= encrypt('TEST','key') OR f2= encrypt('test','key'));
DROP TABLE t1;

--echo # Start of 10.2 tests

CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(30) DEFAULT ENCRYPT(a,123));
SHOW CREATE TABLE t1;
INSERT INTO t1 (a) VALUES ('hello');
SELECT * FROM t1;
DROP TABLE t1;

--echo # End of 10.2 tests

--echo #
--echo # MDEV-25778 Overrun buffer in to_string_native()
--echo #

CREATE TABLE t1 (a DECIMAL(15,11) ZEROFILL);
INSERT INTO t1 (a) VALUES (0.1),(0.2);
SELECT length(ENCRYPT(a)) AS f, COUNT(*) FROM t1 GROUP BY f;
DROP TABLE t1;

--echo # End of 10.6 tests