| Bug #73415 | mysqldbcompare not escaping values in SQL output | ||
|---|---|---|---|
| Submitted: | 29 Jul 2014 0:31 | Modified: | 9 Oct 2014 0:03 |
| Reporter: | Barrington Haynes | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Utilities | Severity: | S3 (Non-critical) |
| Version: | 1.4.3 / 1.5.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Jul 2014 12:26]
MySQL Verification Team
Hello Barrington, Thank you for the bug report. Verified as described. Thanks, Umesh
[29 Jul 2014 12:27]
MySQL Verification Team
How to repeat:
mysql-advanced-5.6.19]$ bin/mysql -u root -p test
Enter password:
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 28
Server version: 5.6.19-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, 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> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> use db1
Database changed
mysql> create table t1(id int not null auto_increment primary key, name varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(name) values("single quote' bug");
Query OK, 1 row affected (0.00 sec)
mysql> use db2
Database changed
mysql> create table t1(id int not null auto_increment primary key, name varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(name) values("single quote bug");
Query OK, 1 row affected (0.00 sec)
mysql> select * from db1.t1;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | single quote' bug |
+----+-------------------+
1 row in set (0.00 sec)
mysql> select * from db2.t1;
+----+------------------+
| id | name |
+----+------------------+
| 1 | single quote bug |
+----+------------------+
1 row in set (0.00 sec)
########
mysql-advanced-5.6.19]$ mysqldbcompare --server1=root@localhost --server2=root@localhost -a --difftype=sql db2:db1
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases db2 on server1 and db1 on server2
#
# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `db2`
+++ `db1`
@@ -1,1 +1,1 @@
-CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */
# WARNING: Could not generate changes for {0}. No changes required or not supported difference.
# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
--- `db2`
+++ `db1`
@@ -1,1 +1,1 @@
-CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE t1 pass pass -
# - Compare table checksum FAIL
# - Find row differences FAIL
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
UPDATE `db2`.`t1` SET `name` = 'single quote' bug' WHERE `id` = '1';
# Database consistency check failed.
#
# ...done
^^^ single quote is not escaped
[9 Oct 2014 0:03]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Utilities 1.6.1 release, and here's the changelog entry: When comparing databases with the "mysqldbcompare" utility, quote characters within resulting SQL transformation statements were not escaped. As a result, the statements were not valid and could not be executed directly in a client tool, as intended. Thank you for the bug report.
Description: I have been creating scripts to get one database in sync with another using mysqldbcompare. If any of the values contain single quotes, they are not being escaped and the import fails. How to repeat: Use mysqldbcompare on two databases where there is a single quote character in one of the values, not in the other database. Suggested fix: In mysql/utilities/common/sql_transform.py make the following change, escaping the obj before quoting it. def to_sql(obj): """Convert a value to a suitable SQL value placing quotes where needed. obj[in] object (value) to convert Returns (string) converted value """ obj = MySQLConverter().escape(obj) return MySQLConverter().quote(obj)