1--source include/have_innodb.inc 2--source include/have_binlog_format_statement.inc 3--source include/master-slave.inc 4--source include/not_windows.inc #unix shell escaping used for mysqlbinlog 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. 11connection master; 12create table t1 (a int primary key) engine=innodb; 13create table t2 (a int primary key) engine=myisam; 14 15begin; 16insert into t1 values (1); 17SET sql_mode = 'ANSI_QUOTES'; 18savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`; 19insert into t1 values (2); 20insert into t2 values (1); 21SET sql_mode = ''; 22rollback to savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`; 23insert into t1 values (3); 24commit; 25 26--source include/show_binlog_events2.inc 27 28# This failed due to syntax error in query when the bug was not fixed. 29sync_slave_with_master; 30connection slave; 31 32# Test some more combinations of ANSI_QUOTES and sql_quote_show_create 33connection master; 34let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 35BEGIN; 36insert into t1 values(10); 37set sql_mode = 'ANSI_QUOTES'; 38set sql_quote_show_create = 1; 39savepoint a; 40insert into t1 values(11); 41savepoint "a""a"; 42insert into t1 values(12); 43set sql_quote_show_create = 0; 44savepoint b; 45insert into t1 values(13); 46savepoint "b""b"; 47insert into t1 values(14); 48set sql_mode = ''; 49set sql_quote_show_create = 1; 50savepoint c; 51insert into t1 values(15); 52savepoint `c``c`; 53insert into t1 values(16); 54set sql_quote_show_create = 0; 55savepoint d; 56insert into t1 values(17); 57savepoint `d``d`; 58insert into t1 values(18); 59COMMIT; 60set sql_quote_show_create = 1; 61 62--source include/show_binlog_events2.inc 63 64--echo *** Test correct USE statement in SHOW BINLOG EVENTS *** 65connection master; 66let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 67set sql_mode = 'ANSI_QUOTES'; 68CREATE DATABASE "db1`; select 'oops!'"; 69use "db1`; select 'oops!'"; 70CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM; 71INSERT INTO t1 VALUES (1); 72set sql_mode = ''; 73INSERT INTO t1 VALUES (2); 74set sql_mode = 'ANSI_QUOTES'; 75--source include/show_binlog_events2.inc 76set sql_mode = ''; 77set sql_quote_show_create = 0; 78--source include/show_binlog_events2.inc 79set sql_quote_show_create = 1; 80--source include/show_binlog_events2.inc 81DROP TABLE t1; 82 83use 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' 90EOF 91 92use `db1``; select 'oops!'`; 93let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 94set timestamp=1000000000; 95CREATE 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 98eval 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 103SELECT * FROM `t``1`; 104# Also test when code prefixes table name with database. 105truncate `t``1`; 106use test; 107--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 108eval 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", "!"); 113SELECT * FROM `db1``; select 'oops!'`.`t``1`; 114let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1); 115 116--source include/show_binlog_events2.inc 117let $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 121sync_slave_with_master; 122connection slave; 123SELECT * FROM `db1``; select 'oops!'`.`t``1`; 124connection master; 125 126DROP TABLE `db1``; select 'oops!'`.`t``1`; 127--remove_file $load_file 128 129connection master; 130drop table t1,t2; 131 132 133--echo *** Test truncation of long SET expression in LOAD DATA *** 134CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000)); 135--let $load_file= $MYSQLTEST_VARDIR/tmp/file.txt 136--write_file $load_file 1371,X 1382,A 139EOF 140 141let $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 145eval LOAD DATA INFILE '$load_file' INTO TABLE t1 146 FIELDS TERMINATED BY ',' 147 (a, @b) SET b = CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|', @b); 148 149SELECT * FROM t1 ORDER BY a; 150--source include/show_binlog_events2.inc 151 152sync_slave_with_master; 153connection slave; 154SELECT * FROM t1 ORDER BY a; 155 156connection master; 157--remove_file $load_file 158DROP TABLE t1; 159 160 161--echo *** Test user variables whose names require correct quoting *** 162use `db1``; select 'oops!'`; 163let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 164CREATE TABLE t1 (a1 BIGINT PRIMARY KEY, a2 BIGINT, a3 BIGINT, a4 BIGINT UNSIGNED, b DOUBLE, c DECIMAL(65,10), d VARCHAR(100)); 165INSERT INTO t1 VALUES (-9223372036854775808,42,9223372036854775807,18446744073709551615,-1234560123456789e110, -1234501234567890123456789012345678901234567890123456789.0123456789, REPEAT("x", 100)); 166SELECT @`a``1`:=a1, @`a``2`:=a2, @`a``3`:=a3, @`a``4`:=a4, @`b```:=b, @```c`:=c, @```d```:=d FROM t1; 167INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98)); 168let $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 174sync_slave_with_master; 175connection slave; 176SELECT * FROM `db1``; select 'oops!'`.t1 ORDER BY a1; 177 178connection master; 179DROP TABLE t1; 180 181--echo *** Test correct quoting in foreign key error message *** 182use `db1``; select 'oops!'`; 183CREATE TABLE `t``1` ( `a``` INT PRIMARY KEY) ENGINE=innodb; 184CREATE 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 187TRUNCATE `t``1`; 188DROP TABLE `t``2`; 189DROP 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. 196connection slave; 197--source include/stop_slave.inc 198 199connection master; 200CREATE TABLE `db1``; select 'oops!'`.`t``1` (`a``` INT PRIMARY KEY) ENGINE=heap; 201INSERT INTO `db1``; select 'oops!'`.`t``1` VALUES (1), (2), (5); 202SELECT * 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 208wait-rpl_mdev382.test 209EOF 210 211--shutdown_server 212 213--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 214restart-rpl_mdev382.test 215EOF 216 217connection default; 218--enable_reconnect 219--source include/wait_until_connected_again.inc 220# rpl_end.inc needs to use the connection server_1 221connection server_1; 222--enable_reconnect 223--source include/wait_until_connected_again.inc 224connection master; 225--enable_reconnect 226--source include/wait_until_connected_again.inc 227set timestamp=1000000000; 228 229--echo # The table should be empty on the master. 230let $binlog_file= master-bin.000002; 231let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 232SELECT * FROM `db1``; select 'oops!'`.`t``1`; 233 234--echo # The DELETE statement should be correctly quoted 235--source include/show_binlog_events2.inc 236 237connection slave; 238--source include/start_slave.inc 239 240connection master; 241sync_slave_with_master; 242connection slave; 243--echo # The table should be empty on the slave also. 244SELECT * FROM `db1``; select 'oops!'`.`t``1`; 245 246connection master; 247DROP TABLE `db1``; select 'oops!'`.`t``1`; 248sync_slave_with_master; 249 250 251connection master; 252use test; 253DROP DATABASE `db1``; select 'oops!'`; 254 255--echo *** Test correct quoting of mysqlbinlog --rewrite-db option *** 256CREATE TABLE t1 (a INT PRIMARY KEY); 257let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 258INSERT INTO t1 VALUES(1); 259--source include/show_binlog_events2.inc 260let $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 262DROP TABLE t1; 263 264--source include/rpl_end.inc 265