BUG#34228442: Fix NO_BACKSLASH_ESCAPES SQL mode support in c-ext

nmariz · nmariz · commit dbb4345c79cc · 2022-06-03T11:16:20.000+01:00
In MySQL 5.7.6, the C API function mysql_real_escape_string_quote(),
has been implemented as a replacement for mysql_real_escape_string()
because the latter function can fail to properly encode characters
when the NO_BACKSLASH_ESCAPES SQL mode is enabled.

This patch fixes the NO_BACKSLASH_ESCAPES SQL mode when using the c-ext
by using mysql_real_escape_string_quote() function for servers &gt;= 5.7.6.

Change-Id: Ief95be49f33b80d7e9d27fafa4b727e33c2e4045
diff --git a/CHANGES.txt b/CHANGES.txt
@@ -17,6 +17,7 @@ v8.0.30
 - WL#15035: Enforce PEP 7 and PEP 8 coding style
 - WL#14822: Refactor the authentication plugin mechanism
 - WL#14815: Support OpenSSL 3.0
+- BUG#34228442: Fix NO_BACKSLASH_ESCAPES SQL mode support in c-ext
 - BUG#34127959: Add isolation level support in Django backend
 - BUG#33923516: Allow tuple of dictionaries as "failover" argument
 - BUG#28821983: Fix rounding errors for decimal values
diff --git a/src/mysql_capi.c b/src/mysql_capi.c
@@ -1455,8 +1455,13 @@ MySQL_escape_string(MySQL *self, PyObject *value)
     to = PyBytes_FromStringAndSize(NULL, from_size * 2 + 1);
     to_str = PyBytes_AsString(to);
 
+#if MYSQL_VERSION_ID >= 50706
+    escaped_size = (Py_ssize_t)mysql_real_escape_string_quote(&self->session, to_str, from_str,
+                                                              (unsigned long)from_size, '\'');
+#else
     escaped_size = (Py_ssize_t)mysql_real_escape_string(&self->session, to_str, from_str,
                                                         (unsigned long)from_size);
+#endif
 
     _PyBytes_Resize(&to, escaped_size);
     Py_XDECREF(from);
diff --git a/tests/test_bugs.py b/tests/test_bugs.py
@@ -6915,3 +6915,28 @@ def test_decimal_update(self):
             self.assertEqual(res, Decimal("100000000000.00000203"))
 
             cur.execute(f"DROP TABLE IF EXISTS {table}")
+
+
+class BugOra34228442(tests.MySQLConnectorTests):
+    """BUG#34228442: Fix NO_BACKSLASH_ESCAPES SQL mode support in c-ext."""
+
+    @foreach_cnx()
+    def test_no_backslash_escapes(self):
+        table = "BugOra34228442"
+        self.cnx.sql_mode = [constants.SQLMode.NO_BACKSLASH_ESCAPES]
+        with self.cnx.cursor() as cur:
+            cur.execute(f"DROP TABLE IF EXISTS {table}")
+            cur.execute(
+                f"""
+                CREATE TABLE {table} (
+                    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                    `text` VARCHAR(255)
+                )
+                """
+            )
+            cur.execute(f"INSERT INTO {table} (`text`) VALUES ('test')")
+            cur.execute(f"SELECT text FROM {table} WHERE text = %s", ["test"])
+            res = cur.fetchall()
+            self.assertEqual(res[0][0], "test")
+            self.assertEqual(self.cnx.sql_mode, "NO_BACKSLASH_ESCAPES")
+            cur.execute(f"DROP TABLE IF EXISTS {table}")
