MySQL Bugs: #118025: mysql/connector python version 9.3.0 has a regression which cannot persist binary data with percent signs in it

Bug #118025	mysql/connector python version 9.3.0 has a regression which cannot persist binary data with percent signs in it
Submitted:	21 Apr 13:38	Modified:	3 Jul 21:48
Reporter:	Mike Bayer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S1 (Critical)
Version:	9.3.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
mysql-connector-python as of 9.3.0 cannot correctly persist a binary value that contains double percent signs in it. persisting the string b'binary data with %%s in it' into a BLOB column will show at the MySQL monitor:

MySQL [test]> select * from binary_table;
+---------------------------+
| data                      |
+---------------------------+
| binary data with %s in it |
+---------------------------+
1 row in set (0.002 sec)

whereas with 9.2.0 the two percent signs are maintained:

MySQL [test]> select * from binary_table;
+----------------------------+
| data                       |
+----------------------------+
| binary data with %%s in it |
+----------------------------+
1 row in set (0.002 sec)

a full Python script is provided below.

It would appear that some kind of string escaping is being applied to the binary parameter itself which is inappropriate.   this is a major blocking issue for all users of mysql-connector-python which IMO warrants immediate release.

How to repeat:

from mysql import connector

stream1 = b"binary data with %%s in it"

connection = connector.connect(
    user="scott", password="tiger", host="mysql80", database="test"
)
cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS binary_table")

cursor.execute(
    """CREATE TABLE binary_table (
        data BLOB
)"""
)

cursor.execute(
    """INSERT INTO binary_table (data) VALUES (%(data)s)""",
    {"data": stream1},
)

connection.commit()

cursor.execute("""SELECT binary_table.data FROM binary_table""")

result = cursor.fetchall()
assert stream1 == result[0][0]

Suggested fix:
do not apply string escaping to the bound values of a statement in any way and add unit tests to ensure this does not regress

Hello Mike Bayer,

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

regards,
Umesh

test results

Attachment: 118025.results (application/octet-stream, text), 5.13 KiB.

Posted by developer:
 
Hello Mike,

Thanks for raising this bug and the suggested fix.

Cheers,
Souma Kanti

Posted by developer:
 
Added the following entry to the Connector/Python 9.4.0 changelog: 

"Binary data containing double percent signs (%%) was not correctly persisted in BLOB columns, but had the first percentage sign stripped."