Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7494

single quote and backslash cannot be inserted using CONNECT

    XMLWordPrintable

    Details

      Description

      Data including ' (single quote) errors when trying to insert to a CONNECT table.

      ERROR 1296 (HY000): Got error 122 '(1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'def',250.00000000000000000000)' at line 1 [INSERT INTO `' from CONNECT

      Also data including \ (backslash) can be inserted but deletes the backslash automatically.

      Below is the log of a test case.

      server1

      --------------
      SHOW VARIABLES LIKE "%CHAR%"
      --------------
       
      +--------------------------+----------------------------+
      | Variable_name            | Value                      |
      +--------------------------+----------------------------+
      | character_set_client     | utf8                       |
      | character_set_connection | utf8                       |
      | character_set_database   | utf8                       |
      | character_set_filesystem | binary                     |
      | character_set_results    | utf8                       |
      | character_set_server     | utf8                       |
      | character_set_system     | utf8                       |
      | character_sets_dir       | /usr/share/mysql/charsets/ |
      +--------------------------+----------------------------+
      8 rows in set (0.00 sec)
       
      --------------
      CREATE DATABASE IF NOT EXISTS test
      --------------
       
      Query OK, 1 row affected, 1 warning (0.00 sec)
       
      Note (Code 1007): Can't create database 'test'; database exists
      --------------
      CREATE OR REPLACE TABLE table_coupon_varchar (
        coupon_no varchar(10) NOT NULL DEFAULT '',
        memo varchar(30) DEFAULT NULL,
        discount double DEFAULT NULL,
        PRIMARY KEY (coupon_no)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
      --------------
       
      Query OK, 0 rows affected (0.05 sec)
       
      --------------
      INSERT INTO table_coupon_varchar VALUES ("1","abc",250),("2","def",200),("3","ghi",300)
      --------------
       
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM table_coupon_varchar
      --------------
       
      +-----------+------+----------+
      | coupon_no | memo | discount |
      +-----------+------+----------+
      | 1         | abc  |      250 |
      | 2         | def  |      200 |
      | 3         | ghi  |      300 |
      +-----------+------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      GRANT ALL ON test.* TO 'root'@'10.0.1.33'
      --------------
       
      Query OK, 0 rows affected (0.00 sec)
       
      Bye

      server2

      --------------
      SHOW VARIABLES LIKE "%CHAR%"
      --------------
       
      +--------------------------+----------------------------+
      | Variable_name            | Value                      |
      +--------------------------+----------------------------+
      | character_set_client     | utf8                       |
      | character_set_connection | utf8                       |
      | character_set_database   | utf8                       |
      | character_set_filesystem | binary                     |
      | character_set_results    | utf8                       |
      | character_set_server     | utf8                       |
      | character_set_system     | utf8                       |
      | character_sets_dir       | /usr/share/mysql/charsets/ |
      +--------------------------+----------------------------+
      8 rows in set (0.00 sec)
       
      --------------
      CREATE DATABASE IF NOT EXISTS test
      --------------
       
      Query OK, 1 row affected, 1 warning (0.00 sec)
       
      Note (Code 1007): Can't create database 'test'; database exists
      --------------
      CREATE OR REPLACE TABLE table_coupon_varchar (
        coupon_no varchar(10) NOT NULL DEFAULT '',
        memo varchar(30) DEFAULT NULL,
        discount double DEFAULT NULL,
        PRIMARY KEY (coupon_no)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
      --------------
       
      Query OK, 0 rows affected (0.04 sec)
       
      --------------
      INSERT INTO table_coupon_varchar VALUES ("4","abc'def",250),("5","abc'def",250),("6","gh\\ij",200),("7","gh\\ij",200)
      --------------
       
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM table_coupon_varchar
      --------------
       
      +-----------+---------+----------+
      | coupon_no | memo    | discount |
      +-----------+---------+----------+
      | 4         | abc'def |      250 |
      | 5         | abc'def |      250 |
      | 6         | gh\ij   |      200 |
      | 7         | gh\ij   |      200 |
      +-----------+---------+----------+
      4 rows in set (0.00 sec)
       
      --------------
      CREATE OR REPLACE TABLE maria_table_coupon_varchar
      ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon_varchar' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
      --------------
       
      Query OK, 0 rows affected (0.04 sec)
       
      --------------
      SELECT * FROM maria_table_coupon_varchar
      --------------
       
      +-----------+------+----------+
      | coupon_no | memo | discount |
      +-----------+------+----------+
      | 1         | abc  |      250 |
      | 2         | def  |      200 |
      | 3         | ghi  |      300 |
      +-----------+------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      INSERT INTO maria_table_coupon_varchar SELECT coupon_no, memo, discount FROM table_coupon_varchar WHERE coupon_no = "4"
      --------------
       
      --------------
      SELECT * FROM maria_table_coupon_varchar
      --------------
       
      +-----------+------+----------+
      | coupon_no | memo | discount |
      +-----------+------+----------+
      | 1         | abc  |      250 |
      | 2         | def  |      200 |
      | 3         | ghi  |      300 |
      +-----------+------+----------+
      3 rows in set (0.00 sec)
       
      --------------
      INSERT INTO maria_table_coupon_varchar SELECT coupon_no,  REPLACE( memo, "'", "''"), discount FROM table_coupon_varchar WHERE coupon_no = "5"
      --------------
       
      Query OK, 1 row affected (0.00 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM maria_table_coupon_varchar
      --------------
       
      +-----------+---------+----------+
      | coupon_no | memo    | discount |
      +-----------+---------+----------+
      | 1         | abc     |      250 |
      | 2         | def     |      200 |
      | 3         | ghi     |      300 |
      | 5         | abc'def |      250 |
      +-----------+---------+----------+
      4 rows in set (0.00 sec)
       
      --------------
      INSERT INTO maria_table_coupon_varchar SELECT coupon_no, memo, discount FROM table_coupon_varchar WHERE coupon_no = "6"
      --------------
       
      Query OK, 1 row affected (0.01 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM maria_table_coupon_varchar
      --------------
       
      +-----------+---------+----------+
      | coupon_no | memo    | discount |
      +-----------+---------+----------+
      | 1         | abc     |      250 |
      | 2         | def     |      200 |
      | 3         | ghi     |      300 |
      | 5         | abc'def |      250 |
      | 6         | ghij    |      200 |
      +-----------+---------+----------+
      5 rows in set (0.00 sec)
       
      --------------
      INSERT INTO maria_table_coupon_varchar SELECT coupon_no, REPLACE( memo, "\\", "\\\\"), discount FROM table_coupon_varchar WHERE coupon_no = "7"
      --------------
       
      Query OK, 1 row affected (0.00 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      --------------
      SELECT * FROM maria_table_coupon_varchar
      --------------
       
      +-----------+---------+----------+
      | coupon_no | memo    | discount |
      +-----------+---------+----------+
      | 1         | abc     |      250 |
      | 2         | def     |      200 |
      | 3         | ghi     |      300 |
      | 5         | abc'def |      250 |
      | 6         | ghij    |      200 |
      | 7         | gh\ij   |      200 |
      +-----------+---------+----------+
      6 rows in set (0.00 sec)
       
      Bye

      I made a test case, it can be used with the below command.

      server1

      mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test4_script_server1.sql;" > /root/test3_script_server1.log

      server2

      mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test4_script_server2.sql;" > /root/test3_script_server2.log

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            takuya Takuya Aoki
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: