Debian Codegraph @

mysql-test/suite/rpl/t/rpl_mdev382.test

raw
1 --source include/not_windows.inc #unix shell escaping used for mysqlbinlog
2 --source include/have_innodb.inc
3 --source include/have_binlog_format_statement.inc
4 --source include/master-slave.inc
5
6 # MDEV-382: multiple SQL injections in replication code.
7
8 # Test previous SQL injection attack against binlog for SAVEPOINT statement.
9 # The test would cause syntax error on slave due to improper quoting of
10 # the savepoint name.
11 connection master;
12 create table t1 (a int primary key) engine=innodb;
13 create table t2 (a int primary key) engine=myisam;
14
15 begin;
16 insert into t1 values (1);
17 SET sql_mode = 'ANSI_QUOTES';
18 savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`;
19 insert into t1 values (2);
20 insert into t2 values (1);
21 SET sql_mode = '';
22 rollback to savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`;
23 insert into t1 values (3);
24 commit;
25
26 --source include/show_binlog_events2.inc
27
28 # This failed due to syntax error in query when the bug was not fixed.
29 sync_slave_with_master;
30 connection slave;
31
32 # Test some more combinations of ANSI_QUOTES and sql_quote_show_create
33 connection master;
34 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
35 BEGIN;
36 insert into t1 values(10);
37 set sql_mode = 'ANSI_QUOTES';
38 set sql_quote_show_create = 1;
39 savepoint a;
40 insert into t1 values(11);
41 savepoint "a""a";
42 insert into t1 values(12);
43 set sql_quote_show_create = 0;
44 savepoint b;
45 insert into t1 values(13);
46 savepoint "b""b";
47 insert into t1 values(14);
48 set sql_mode = '';
49 set sql_quote_show_create = 1;
50 savepoint c;
51 insert into t1 values(15);
52 savepoint `c``c`;
53 insert into t1 values(16);
54 set sql_quote_show_create = 0;
55 savepoint d;
56 insert into t1 values(17);
57 savepoint `d``d`;
58 insert into t1 values(18);
59 COMMIT;
60 set sql_quote_show_create = 1;
61
62 --source include/show_binlog_events2.inc
63
64 --echo *** Test correct USE statement in SHOW BINLOG EVENTS ***
65 connection master;
66 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
67 set sql_mode = 'ANSI_QUOTES';
68 CREATE DATABASE "db1`; select 'oops!'";
69 use "db1`; select 'oops!'";
70 CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM;
71 INSERT INTO t1 VALUES (1);
72 set sql_mode = '';
73 INSERT INTO t1 VALUES (2);
74 set sql_mode = 'ANSI_QUOTES';
75 --source include/show_binlog_events2.inc
76 set sql_mode = '';
77 set sql_quote_show_create = 0;
78 --source include/show_binlog_events2.inc
79 set sql_quote_show_create = 1;
80 --source include/show_binlog_events2.inc
81 DROP TABLE t1;
82
83 use test;
84
85 --echo ***Test LOAD DATA INFILE with various identifiers that need correct quoting ***
86
87 --let $load_file= $MYSQLTEST_VARDIR/tmp/f'le.txt
88 --write_file $load_file
89 'fo\\o','bar'
90 EOF
91
92 use `db1``; select 'oops!'`;
93 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
94 set timestamp=1000000000;
95 CREATE TABLE `t``1` (`a``1` VARCHAR(4) PRIMARY KEY, `b``2` VARCHAR(3),
96 `c``3` VARCHAR(7));
97 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
98 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/f''le.txt' INTO TABLE `t``1`
99 FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' ENCLOSED BY ''''
100 LINES TERMINATED BY '\\n'
101 (`a``1`, @`b```) SET `b``2` = @`b```, `c``3` = concat('|', "b""a'z", "!");
102
103 SELECT * FROM `t``1`;
104 # Also test when code prefixes table name with database.
105 truncate `t``1`;
106 use test;
107 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
108 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/f''le.txt'
109 INTO TABLE `db1``; select 'oops!'`.`t``1`
110 FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' ENCLOSED BY ''''
111 LINES TERMINATED BY '\\n'
112 (`a``1`, `b``2`) SET `c``3` = concat('|', "b""a'z", "!");
113 SELECT * FROM `db1``; select 'oops!'`.`t``1`;
114 let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1);
115
116 --source include/show_binlog_events2.inc
117 let $MYSQLD_DATADIR= `select @@datadir`;
118 --replace_regex /LOCAL INFILE '.*SQL_LOAD.*' INTO/LOCAL INFILE '<name>' INTO/
119 --exec $MYSQL_BINLOG --short-form --start-position=$binlog_start --stop-position=$pos2 $MYSQLD_DATADIR/master-bin.000001
120
121 sync_slave_with_master;
122 connection slave;
123 SELECT * FROM `db1``; select 'oops!'`.`t``1`;
124 connection master;
125
126 DROP TABLE `db1``; select 'oops!'`.`t``1`;
127 --remove_file $load_file
128
129 connection master;
130 drop table t1,t2;
131
132
133 --echo *** Test truncation of long SET expression in LOAD DATA ***
134 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000));
135 --let $load_file= $MYSQLTEST_VARDIR/tmp/file.txt
136 --write_file $load_file
137 1,X
138 2,A
139 EOF
140
141 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
142 # The bug was that the SET expression was truncated to 256 bytes, so test with
143 # an expression longer than that.
144 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
145 eval LOAD DATA INFILE '$load_file' INTO TABLE t1
146 FIELDS TERMINATED BY ','
147 (a, @b) SET b = CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|', @b);
148
149 SELECT * FROM t1 ORDER BY a;
150 --source include/show_binlog_events2.inc
151
152 sync_slave_with_master;
153 connection slave;
154 SELECT * FROM t1 ORDER BY a;
155
156 connection master;
157 --remove_file $load_file
158 DROP TABLE t1;
159
160
161 --echo *** Test user variables whose names require correct quoting ***
162 use `db1``; select 'oops!'`;
163 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
164 CREATE TABLE t1 (a1 BIGINT PRIMARY KEY, a2 BIGINT, a3 BIGINT, a4 BIGINT UNSIGNED, b DOUBLE, c DECIMAL(65,10), d VARCHAR(100));
165 INSERT INTO t1 VALUES (-9223372036854775808,42,9223372036854775807,18446744073709551615,-1234560123456789e110, -1234501234567890123456789012345678901234567890123456789.0123456789, REPEAT("x", 100));
166 SELECT @`a``1`:=a1, @`a``2`:=a2, @`a``3`:=a3, @`a``4`:=a4, @`b```:=b, @```c`:=c, @```d```:=d FROM t1;
167 INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98));
168 let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1);
169
170 --source include/show_binlog_events2.inc
171
172 --exec $MYSQL_BINLOG --short-form --start-position=$binlog_start --stop-position=$pos2 $MYSQLD_DATADIR/master-bin.000001
173
174 sync_slave_with_master;
175 connection slave;
176 SELECT * FROM `db1``; select 'oops!'`.t1 ORDER BY a1;
177
178 connection master;
179 DROP TABLE t1;
180
181 --echo *** Test correct quoting in foreign key error message ***
182 use `db1``; select 'oops!'`;
183 CREATE TABLE `t``1` ( `a``` INT PRIMARY KEY) ENGINE=innodb;
184 CREATE TABLE `t``2` ( `b``` INT PRIMARY KEY, `c``` INT NOT NULL,
185 FOREIGN KEY fk (`c```) REFERENCES `t``1`(`a```)) ENGINE=innodb;
186 --error ER_TRUNCATE_ILLEGAL_FK
187 TRUNCATE `t``1`;
188 DROP TABLE `t``2`;
189 DROP TABLE `t``1`;
190
191
192 --echo *** Test correct quoting of DELETE FROM statement binlogged for HEAP table that is emptied due to server restart
193
194 # Let's keep the slave stopped during master restart, to avoid any potential
195 # races between slave reconnect and master restart.
196 connection slave;
197 --source include/stop_slave.inc
198
199 connection master;
200 CREATE TABLE `db1``; select 'oops!'`.`t``1` (`a``` INT PRIMARY KEY) ENGINE=heap;
201 INSERT INTO `db1``; select 'oops!'`.`t``1` VALUES (1), (2), (5);
202 SELECT * FROM `db1``; select 'oops!'`.`t``1` ORDER BY 1;
203
204 # Restart the master mysqld.
205 # This will cause an implicit truncation of the memory-based table, which will
206 # cause logging of an explicit DELETE FROM to binlog.
207 --write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
208 wait-rpl_mdev382.test
209 EOF
210
211 --shutdown_server
212
213 --append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
214 restart-rpl_mdev382.test
215 EOF
216
217 connection default;
218 --enable_reconnect
219 --source include/wait_until_connected_again.inc
220 # rpl_end.inc needs to use the connection server_1
221 connection server_1;
222 --enable_reconnect
223 --source include/wait_until_connected_again.inc
224 connection master;
225 --enable_reconnect
226 --source include/wait_until_connected_again.inc
227 set timestamp=1000000000;
228
229 --echo # The table should be empty on the master.
230 let $binlog_file= master-bin.000002;
231 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
232 SELECT * FROM `db1``; select 'oops!'`.`t``1`;
233
234 --echo # The DELETE statement should be correctly quoted
235 --source include/show_binlog_events2.inc
236
237 connection slave;
238 --source include/start_slave.inc
239
240 connection master;
241 sync_slave_with_master;
242 connection slave;
243 --echo # The table should be empty on the slave also.
244 SELECT * FROM `db1``; select 'oops!'`.`t``1`;
245
246 connection master;
247 DROP TABLE `db1``; select 'oops!'`.`t``1`;
248 sync_slave_with_master;
249
250
251 connection master;
252 use test;
253 DROP DATABASE `db1``; select 'oops!'`;
254
255 --echo *** Test correct quoting of mysqlbinlog --rewrite-db option ***
256 CREATE TABLE t1 (a INT PRIMARY KEY);
257 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
258 INSERT INTO t1 VALUES(1);
259 --source include/show_binlog_events2.inc
260 let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1);
261 --exec $MYSQL_BINLOG --short-form --start-position=$binlog_start --stop-position=$pos2 --rewrite-db='test->ts`et' $MYSQLD_DATADIR/master-bin.000002
262 DROP TABLE t1;
263
264 --source include/rpl_end.inc