MySQL Bugs: #85100: Commits related to "BUG22529828" broke binary data handling for python 2.7

Bug #85100	Commits related to "BUG22529828" broke binary data handling for python 2.7
Submitted:	21 Feb 2017 11:18	Modified:	22 Mar 2017 18:51
Reporter:	Marcin Lulek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S2 (Serious)
Version:	2.1.4+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello,

In commits

https://github.com/mysql/mysql-connector-python/commit/def354c559086dfc4cf49e26f945696f27f...

and 

https://github.com/mysql/mysql-connector-python/commit/80b343048e0ae3b4b8c7e6ede1ff42dd2fe...

There was a security fix related to SQL injection. Unfortunately the fix breaks python 2.7 ability to insert binary data.

The driver tries to do value.decode("utf-8") on binary data for python 2.7 and fails here: 
https://github.com/mysql/mysql-connector-python/commit/80b343048e0ae3b4b8c7e6ede1ff42dd2fe...

When the binary data contains non-ascii characters.
I've attached example python script that illustrates the problem.

This bug affects all mysql-connector-python versions higher than 2.1.3 under python 2.7.

Python 3 will not attempt to decode the value so the insert is performed correctly.

How to repeat:
from __future__ import print_function
import pickle

import mysql.connector

# please create this table
"""
CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `section` varchar(50) NOT NULL,
  `pickled` longblob NOT NULL,
  `json_data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
"""

pickled = pickle.dumps({'a': 'b'}, pickle.HIGHEST_PROTOCOL)
pickled2 = pickle.dumps({'c': 'd'}, pickle.HIGHEST_PROTOCOL)

## PURE EXAMPLE

user = 'test'
password = 'test'
database = 'test'
host = '127.0.0.1'
cnx = mysql.connector.connect(
    user=user, password=password, host=host, database=database)

cursor = cnx.cursor()
add_row_q = 'INSERT INTO test_table (section, pickled) ' \
            'VALUES (%(section)s, %(pickled)s)'

new_row = cursor.execute(add_row_q, {'section': 'foo', 'pickled': pickled})
cnx.commit()
new_row = cursor.lastrowid
print(new_row)

cursor.execute('select * from test_table')
for row in cursor:
    print(row[2], pickle.loads(row[3]))

cnx.close()

Suggested fix:
Do not decode the value for binary data/columns.

Hello Marcin Lulek,

Thank you for the bug report and test case.
Verified this behavior on MySQL Connector/Python 2.1.4.

Thanks,
Chiranjeevi.

Traceback (most recent call last):
  File "D:\Python\85100.py", line 35, in <module>
    new_row = cursor.execute(add_row_q, {'section': 'foo', 'pickled': pickled})
  File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 537, in execute
    stmt, self._process_params_dict(params))
  File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 112, in _bytestr_format_dict
    if PY2 else bytestr)
  File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 110, in replace
    return value.decode("utf-8") if PY2 else value
  File "C:\Python27\lib\encodings\utf_8.py", line 16, in decode
    return codecs.utf_8_decode(input, errors, True)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x80 in position 1: invalid start byte

Posted by developer:
 
Noted in 2.1.6 changelog.

The fix for Bug #22529828 caused Python 2.7 to be unable to insert
binary data.

Posted by developer:
 
Noted in 2.1.6 changelog.

The fix for Bug #22529828 caused Python 2.7 to be unable to insert
binary data.