mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
1963a87b2e
The fix was introduced, along with re-ordering to do other macros that check test environment capabilities before master/slave is set up.
264 lines
8.8 KiB
Text
264 lines
8.8 KiB
Text
--source include/not_windows.inc #unix shell escaping used for mysqlbinlog
|
|
--source include/have_innodb.inc
|
|
--source include/have_binlog_format_statement.inc
|
|
--source include/master-slave.inc
|
|
|
|
# MDEV-382: multiple SQL injections in replication code.
|
|
|
|
# Test previous SQL injection attack against binlog for SAVEPOINT statement.
|
|
# The test would cause syntax error on slave due to improper quoting of
|
|
# the savepoint name.
|
|
connection master;
|
|
create table t1 (a int primary key) engine=innodb;
|
|
create table t2 (a int primary key) engine=myisam;
|
|
|
|
begin;
|
|
insert into t1 values (1);
|
|
SET sql_mode = 'ANSI_QUOTES';
|
|
savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`;
|
|
insert into t1 values (2);
|
|
insert into t2 values (1);
|
|
SET sql_mode = '';
|
|
rollback to savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`;
|
|
insert into t1 values (3);
|
|
commit;
|
|
|
|
--source include/show_binlog_events2.inc
|
|
|
|
# This failed due to syntax error in query when the bug was not fixed.
|
|
sync_slave_with_master;
|
|
connection slave;
|
|
|
|
# Test some more combinations of ANSI_QUOTES and sql_quote_show_create
|
|
connection master;
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
BEGIN;
|
|
insert into t1 values(10);
|
|
set sql_mode = 'ANSI_QUOTES';
|
|
set sql_quote_show_create = 1;
|
|
savepoint a;
|
|
insert into t1 values(11);
|
|
savepoint "a""a";
|
|
insert into t1 values(12);
|
|
set sql_quote_show_create = 0;
|
|
savepoint b;
|
|
insert into t1 values(13);
|
|
savepoint "b""b";
|
|
insert into t1 values(14);
|
|
set sql_mode = '';
|
|
set sql_quote_show_create = 1;
|
|
savepoint c;
|
|
insert into t1 values(15);
|
|
savepoint `c``c`;
|
|
insert into t1 values(16);
|
|
set sql_quote_show_create = 0;
|
|
savepoint d;
|
|
insert into t1 values(17);
|
|
savepoint `d``d`;
|
|
insert into t1 values(18);
|
|
COMMIT;
|
|
set sql_quote_show_create = 1;
|
|
|
|
--source include/show_binlog_events2.inc
|
|
|
|
--echo *** Test correct USE statement in SHOW BINLOG EVENTS ***
|
|
connection master;
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
set sql_mode = 'ANSI_QUOTES';
|
|
CREATE DATABASE "db1`; select 'oops!'";
|
|
use "db1`; select 'oops!'";
|
|
CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM;
|
|
INSERT INTO t1 VALUES (1);
|
|
set sql_mode = '';
|
|
INSERT INTO t1 VALUES (2);
|
|
set sql_mode = 'ANSI_QUOTES';
|
|
--source include/show_binlog_events2.inc
|
|
set sql_mode = '';
|
|
set sql_quote_show_create = 0;
|
|
--source include/show_binlog_events2.inc
|
|
set sql_quote_show_create = 1;
|
|
--source include/show_binlog_events2.inc
|
|
DROP TABLE t1;
|
|
|
|
use test;
|
|
|
|
--echo ***Test LOAD DATA INFILE with various identifiers that need correct quoting ***
|
|
|
|
--let $load_file= $MYSQLTEST_VARDIR/tmp/f'le.txt
|
|
--write_file $load_file
|
|
'fo\\o','bar'
|
|
EOF
|
|
|
|
use `db1``; select 'oops!'`;
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
set timestamp=1000000000;
|
|
CREATE TABLE `t``1` (`a``1` VARCHAR(4) PRIMARY KEY, `b``2` VARCHAR(3),
|
|
`c``3` VARCHAR(7));
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/f''le.txt' INTO TABLE `t``1`
|
|
FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' ENCLOSED BY ''''
|
|
LINES TERMINATED BY '\\n'
|
|
(`a``1`, @`b```) SET `b``2` = @`b```, `c``3` = concat('|', "b""a'z", "!");
|
|
|
|
SELECT * FROM `t``1`;
|
|
# Also test when code prefixes table name with database.
|
|
truncate `t``1`;
|
|
use test;
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/f''le.txt'
|
|
INTO TABLE `db1``; select 'oops!'`.`t``1`
|
|
FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' ENCLOSED BY ''''
|
|
LINES TERMINATED BY '\\n'
|
|
(`a``1`, `b``2`) SET `c``3` = concat('|', "b""a'z", "!");
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
|
|
--source include/show_binlog_events2.inc
|
|
let $MYSQLD_DATADIR= `select @@datadir`;
|
|
--replace_regex /LOCAL INFILE '.*SQL_LOAD.*' INTO/LOCAL INFILE '<name>' INTO/
|
|
--exec $MYSQL_BINLOG --short-form --start-position=$binlog_start --stop-position=$pos2 $MYSQLD_DATADIR/master-bin.000001
|
|
|
|
sync_slave_with_master;
|
|
connection slave;
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
connection master;
|
|
|
|
DROP TABLE `db1``; select 'oops!'`.`t``1`;
|
|
--remove_file $load_file
|
|
|
|
connection master;
|
|
drop table t1,t2;
|
|
|
|
|
|
--echo *** Test truncation of long SET expression in LOAD DATA ***
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000));
|
|
--let $load_file= $MYSQLTEST_VARDIR/tmp/file.txt
|
|
--write_file $load_file
|
|
1,X
|
|
2,A
|
|
EOF
|
|
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
# The bug was that the SET expression was truncated to 256 bytes, so test with
|
|
# an expression longer than that.
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
eval LOAD DATA INFILE '$load_file' INTO TABLE t1
|
|
FIELDS TERMINATED BY ','
|
|
(a, @b) SET b = CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|', @b);
|
|
|
|
SELECT * FROM t1 ORDER BY a;
|
|
--source include/show_binlog_events2.inc
|
|
|
|
sync_slave_with_master;
|
|
connection slave;
|
|
SELECT * FROM t1 ORDER BY a;
|
|
|
|
connection master;
|
|
--remove_file $load_file
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo *** Test user variables whose names require correct quoting ***
|
|
use `db1``; select 'oops!'`;
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
CREATE TABLE t1 (a1 BIGINT PRIMARY KEY, a2 BIGINT, a3 BIGINT, a4 BIGINT UNSIGNED, b DOUBLE, c DECIMAL(65,10), d VARCHAR(100));
|
|
INSERT INTO t1 VALUES (-9223372036854775808,42,9223372036854775807,18446744073709551615,-1234560123456789e110, -1234501234567890123456789012345678901234567890123456789.0123456789, REPEAT("x", 100));
|
|
SELECT @`a``1`:=a1, @`a``2`:=a2, @`a``3`:=a3, @`a``4`:=a4, @`b```:=b, @```c`:=c, @```d```:=d FROM t1;
|
|
INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98));
|
|
let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
|
|
--source include/show_binlog_events2.inc
|
|
|
|
--exec $MYSQL_BINLOG --short-form --start-position=$binlog_start --stop-position=$pos2 $MYSQLD_DATADIR/master-bin.000001
|
|
|
|
sync_slave_with_master;
|
|
connection slave;
|
|
SELECT * FROM `db1``; select 'oops!'`.t1 ORDER BY a1;
|
|
|
|
connection master;
|
|
DROP TABLE t1;
|
|
|
|
--echo *** Test correct quoting in foreign key error message ***
|
|
use `db1``; select 'oops!'`;
|
|
CREATE TABLE `t``1` ( `a``` INT PRIMARY KEY) ENGINE=innodb;
|
|
CREATE TABLE `t``2` ( `b``` INT PRIMARY KEY, `c``` INT NOT NULL,
|
|
FOREIGN KEY fk (`c```) REFERENCES `t``1`(`a```)) ENGINE=innodb;
|
|
--error ER_TRUNCATE_ILLEGAL_FK
|
|
TRUNCATE `t``1`;
|
|
DROP TABLE `t``2`;
|
|
DROP TABLE `t``1`;
|
|
|
|
|
|
--echo *** Test correct quoting of DELETE FROM statement binlogged for HEAP table that is emptied due to server restart
|
|
|
|
# Let's keep the slave stopped during master restart, to avoid any potential
|
|
# races between slave reconnect and master restart.
|
|
connection slave;
|
|
--source include/stop_slave.inc
|
|
|
|
connection master;
|
|
CREATE TABLE `db1``; select 'oops!'`.`t``1` (`a``` INT PRIMARY KEY) ENGINE=heap;
|
|
INSERT INTO `db1``; select 'oops!'`.`t``1` VALUES (1), (2), (5);
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1` ORDER BY 1;
|
|
|
|
# Restart the master mysqld.
|
|
# This will cause an implicit truncation of the memory-based table, which will
|
|
# cause logging of an explicit DELETE FROM to binlog.
|
|
--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
|
|
wait-rpl_mdev382.test
|
|
EOF
|
|
|
|
--shutdown_server
|
|
|
|
--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
|
|
restart-rpl_mdev382.test
|
|
EOF
|
|
|
|
connection default;
|
|
--enable_reconnect
|
|
--source include/wait_until_connected_again.inc
|
|
# rpl_end.inc needs to use the connection server_1
|
|
connection server_1;
|
|
--enable_reconnect
|
|
--source include/wait_until_connected_again.inc
|
|
connection master;
|
|
--enable_reconnect
|
|
--source include/wait_until_connected_again.inc
|
|
set timestamp=1000000000;
|
|
|
|
--echo # The table should be empty on the master.
|
|
let $binlog_file= master-bin.000002;
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
|
|
--echo # The DELETE statement should be correctly quoted
|
|
--source include/show_binlog_events2.inc
|
|
|
|
connection slave;
|
|
--source include/start_slave.inc
|
|
|
|
connection master;
|
|
sync_slave_with_master;
|
|
connection slave;
|
|
--echo # The table should be empty on the slave also.
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
|
|
connection master;
|
|
DROP TABLE `db1``; select 'oops!'`.`t``1`;
|
|
sync_slave_with_master;
|
|
|
|
|
|
connection master;
|
|
use test;
|
|
DROP DATABASE `db1``; select 'oops!'`;
|
|
|
|
--echo *** Test correct quoting of mysqlbinlog --rewrite-db option ***
|
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
|
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
INSERT INTO t1 VALUES(1);
|
|
--source include/show_binlog_events2.inc
|
|
let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1);
|
|
--exec $MYSQL_BINLOG --short-form --start-position=$binlog_start --stop-position=$pos2 --rewrite-db='test->ts`et' $MYSQLD_DATADIR/master-bin.000002
|
|
DROP TABLE t1;
|
|
|
|
--source include/rpl_end.inc
|