MySQL Bugs: #109651: mysql-connector-python binary conversion problem with NO_BACKSLASH

Bug #109651	mysql-connector-python binary conversion problem with NO_BACKSLASH_ESCAPES mode
Submitted:	16 Jan 2023 13:49	Modified:	17 Feb 2023 13:13
Reporter:	Paweł P	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S2 (Serious)
Version:	8.0.31	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
mysql-connector-python truncates binary data on '0' byte when NO_BACKSLASH_ESCAPES SQL Mode is enabled.

Reproduced with:
Python 3.8, mysql-connector-python 8.0.31, mysql 5.7.39-42

The similar bug was reported in the past https://bugs.mysql.com/bug.php?id=107434.

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 test (`id` int NOT NULL AUTO_INCREMENT, `data` longblob, PRIMARY KEY(`id`) )")
val = b'\x01\x02\x00\x01'
cursor.execute("INSERT INTO test (`data`) VALUES (%s)", (val, ))
query = """SELECT data FROM test LIMIT 1"""
cursor.execute(query)

for (data) in cursor:
  print(f'{data}')

cursor.close()
cnx.close()

=======
b'\x01\x02\x00\x01' is truncated at 0 byte and only '\x01\x02' in inserted.

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

Hello Paweł P,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Posted by developer:
 
Thanks for your bug report.
While this bug is being fixed, you can use `converter_class=MySQLConverter` in the connection option as a workaround.
It can be imported as:

    from mysql.connector.conversion import MySQLConverter

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

The C extension implementation's converter truncated bytes that contained
a \x00 byte when using NO_BACKSLASH_ESCAPES mode in MySQL. 

As a workaround, use converter_class=MySQLConverter as a connection
option.

Thank you for the bug report.

Unfortunately, the suggested workaround doesn't work. It fails with the test that's below. Could you please also test the upcoming 8.0.33 with it?

##################################################################

cnx = mysql.connector.connect(user='root', db='test, host='127.0.0.1', ssl_disabled=True, converter_class=MySQLConverter)
cnx.sql_mode = [SQLMode.NO_BACKSLASH_ESCAPES]

cursor = cnx.cursor()
data = "'"
cursor.execute("CREATE TABLE test (`id` int NOT NULL AUTO_INCREMENT, `data` text, PRIMARY KEY(`id`) )")
cursor.execute("INSERT INTO test (`data`) VALUES (%s)", (data, ))

Traceback (most recent call last):
  File "a.py", line 12, in <module>
    cursor.execute("INSERT INTO test (`data`) VALUES (%s)", (data, ))
  File "/home/pawel/.local/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 279, in execute
    result = self._cnx.cmd_query(
  File "/home/pawel/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 573, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\'')' at line 1

Posted by developer:
 
Thanks for the feedback.
Indeed, while the workaround `converter_class=MySQLConverter` works for the `\x00`, it fails for single quotes while using the default converter.
This issue will be fixed in the upcoming 8.0.33 release.