Bug #107434 mysql-connector-python doesn't work with NO_BACKSLASH_ESCAPES SQL Mode

• Submitted: 31 May 2022 12:32
• Modified: 6 Jun 2022 4:19
• Reporter: Paweł P
• Status: Closed
• Category: Connector / Python
• Severity: S2 (Serious)
• Version: mysql-connector-python: 8.0.29, mysql: 5.7.37-40, libmysqlclient: 8.0.29
• OS: Ubuntu
• CPU Architecture: Any

[31 May 2022 12:32] Paweł P

Description:
mysql-connector-python doesn't work with NO_BACKSLASH_ESCAPES SQL Mode.

The following traceback is logged:

Traceback (most recent call last):
  File "/home/pawel/test/python/fun3.py", line 10, in <module>
    cursor.execute(query, ["test"])
  File "/home/pawel/.local/lib/python3.9/site-packages/mysql/connector/cursor_cext.py", line 257, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/pawel/.local/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 684, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Failed converting Python 'str'

How to repeat:
import mysql.connector
from mysql.connector.constants import SQLMode

cnx = mysql.connector.connect(user='root', db='test', passwd='admin', host='127.0.0.1', ssl_disabled=True)
cnx.sql_mode = [SQLMode.NO_BACKSLASH_ESCAPES]
cursor = cnx.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS test (`id` int NOT NULL AUTO_INCREMENT, `text` VARCHAR(256), PRIMARY KEY(`id`) )")
cursor.execute("INSERT INTO test (`text`) VALUES ('test')")
query = """SELECT * FROM test WHERE text = %s"""
cursor.execute(query, ["test"])

for (id,text) in cursor:
  print("{} {}".format(id, text))

cursor.close()
cnx.commit()
cnx.close()

The code works fine without:
cnx.sql_mode = [SQLMode.NO_BACKSLASH_ESCAPES]

The code works fine with 8.0.23 version.

[31 May 2022 12:58] Paweł P

mysql version added

[31 May 2022 13:33] MySQL Verification Team

Hi Mr. P,

Thank you for your bug report.

Please, let us know the version of C API (libmysqlclient) that C/Python is linked to. Also, let us know the version of the server that you are using.

[31 May 2022 14:26] Paweł P

libmysqlclient version added

[31 May 2022 17:10] MySQL Verification Team

Thank you for the feedback.

[6 Jun 2022 4:19] Philip Olson

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Python 8.0.30 release, and here's the proposed changelog entry from the documentation team:

The C Extension implementation did not function with the
NO_BACKSLASH_ESCAPES SQL mode enabled. The fix is to internally call
mysql_real_escape_string_quote() instead of
mysql_real_escape_string_quote(). The workaround was to use the pure
Python implementation (use_pure=True).

Thank you for the bug report.