BUG#34655520: Wrong cursor.statement values in the results of cursor.execute(..., multi=True)

oscpache · oscpache · commit 8f17fbf190b5 · 2022-10-28T13:28:09.000-05:00
Wrong MySQLCursor.statement values are returned in the results of
cursor.execute(query_text, multi=True) when the following
conditions stand:

a) query_text is a string containing two queries or more,
separated by semicolons (;). For example,
query_text = "SELECT 1; SELECT 2".

b) The second query (or third, fourth, ...) uses a literal
or an identifier containing an odd number of backticks (`),
an odd number of quotes (') or an odd number of double quotes (").
For example, query_text = 'SELECT 1; SELECT "`"'

The expected answer is that each cursor from the result returns
one of the queries embedded in query_text when the corresponding
statement property is consulted. However, when the above conditions
stand, the before mentioned property may return more than one query.

With this patch, the behaviour is corrected by updating the regex
used to split out the executed operation (query_text).

Change-Id: I894509a0545947559818e0dc3ad588841b88eae1
diff --git a/CHANGES.txt b/CHANGES.txt
@@ -18,6 +18,7 @@ v8.0.32
 - BUG#34695103: Remove debug messages that shows authentication data
 - BUG#34689812: Fix datetime conversion when using prepared cursors
 - BUG#34675508: Character set 'utf8' unsupported in python mysql connector when using MariaDB
+- BUG#34655520: Wrong MySQLCursor.statement values in the results of cursor.execute(..., multi=True)
 - BUG#34556157: Kerberos authorization fails when using SSPI as security interface
 - BUG#34499578: MySQLCursor.executemany() fails to correctly identify BULK data loading ops
 - BUG#34467201: Add init_command connection option
diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py
@@ -99,7 +99,7 @@
     """,
     re.X,
 )
-RE_SQL_SPLIT_STMTS = re.compile(b""";(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)""")
+RE_SQL_SPLIT_STMTS = re.compile(b""";(?=(?:[^"'`]*["'`].*["'`])*[^"'`]*$)""")
 RE_SQL_FIND_PARAM = re.compile(b"""%s(?=(?:[^"'`]*["'`][^"'`]*["'`])*[^"'`]*$)""")
 
 ERR_NO_RESULT_TO_FETCH = "No result set to fetch from"
diff --git a/tests/cext/test_cext_cursor.py b/tests/cext/test_cext_cursor.py
@@ -578,7 +578,7 @@ def tests_nextset(self):
         cur.close()
         self.cnx.rollback()
 
-    def tests_execute_multi(self):
+    def test_execute_multi(self):
         tbl = "myconnpy_execute_multi"
         stmt = (
             "SELECT 'result', 1; INSERT INTO {0} () VALUES (); "
@@ -623,6 +623,23 @@ def tests_execute_multi(self):
 
         cur.close()
 
+        # test statement property
+        operations = [
+            'select 1; SELECT "`";',
+            "SELECT '\"'; SELECT 2; select '```';",
+            "select 1; select '`'; select 3; select \"'''''\";",
+        ]
+        control = [
+            ["select 1", 'SELECT "`"'],
+            ["SELECT '\"'", "SELECT 2", "select '```'"],
+            ["select 1", "select '`'", "select 3", "select \"'''''\""],
+        ]
+        with self._get_cursor(self.cnx) as cur:
+            for operation, exps in zip(operations, control):
+                for res_cur, exp in zip(cur.execute(operation, multi=True), exps):
+                    self.assertEqual(exp, res_cur.statement)
+                    _ = res_cur.fetchall()
+
 
 class CExtMySQLCursorBufferedTests(tests.CMySQLCursorTests):
     def _get_cursor(self, cnx=None):
diff --git a/tests/test_cursor.py b/tests/test_cursor.py
@@ -628,6 +628,25 @@ def test_execute(self):
         self.assertEqual(exp_result, results)
         self.cur.execute("DROP PROCEDURE multi_results")
 
+    def test_execute_multi(self):
+        # Tests when execute(..., multi=True)
+        operations = [
+            'select 1; SELECT "`";',
+            "SELECT '\"'; SELECT 2; select '```';",
+            "select 1; select '`'; select 3; select \"'''''\";",
+        ]
+        control = [
+            ["select 1", 'SELECT "`"'],
+            ["SELECT '\"'", "SELECT 2", "select '```'"],
+            ["select 1", "select '`'", "select 3", "select \"'''''\""],
+        ]
+        self.cnx = connection.MySQLConnection(**tests.get_mysql_config())
+        with self.cnx.cursor() as cur:
+            for operation, exps in zip(operations, control):
+                for res_cur, exp in zip(cur.execute(operation, multi=True), exps):
+                    self.assertEqual(exp, res_cur.statement)
+                    _ = res_cur.fetchall()
+
     def test_executemany(self):
         """MySQLCursor object executemany()-method"""
         self.check_method(self.cur, "executemany")

Original file line number	Diff line number	Diff line change
`@@ -99,7 +99,7 @@`
`99`	`99`	`""",`
`100`	`100`	`re.X,`
`101`	`101`	`)`
`102`		-RE_SQL_SPLIT_STMTS = re.compile(b""";(?=(?:[^"'`]["'`][^"'`]["'`])[^"'`]$)""")
	`102`	+RE_SQL_SPLIT_STMTS = re.compile(b""";(?=(?:[^"'`]["'`].["'`])[^"'`]$)""")
`103`	`103`	RE_SQL_FIND_PARAM = re.compile(b"""%s(?=(?:[^"'`]["'`][^"'`]["'`])[^"'`]$)""")
`104`	`104`
`105`	`105`	`ERR_NO_RESULT_TO_FETCH = "No result set to fetch from"`