MySQLdb.converters is inconsistent about returning bytes or str in Python 3

[JelleZijlstra]

The MySQLdb.converters module mostly returns strs in Python 3 (which I think is correct), but it ends up calling into _mysql.string_literal in some cases, which returns bytes instead. When escaping tuples this can lead to TypeErrors:

In [35]: sys.version
Out[35]: '3.6.0 (default, Dec 26 2016, 07:53:45) \n[GCC 6.2.0 20160901]'

In [36]: MySQLdb.version_info
Out[36]: (1, 3, 9, 'final', 1)

In [37]: MySQLdb.escape(('a',), MySQLdb.converters.conversions)



Traceback:
  File "/home/jelle/ans/venv64/lib/python3.6/site-packages/IPython/core/interactiveshell.py", line 2881, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-37-d4b2dd1db689>", line 1, in <module>
    MySQLdb.escape(('a',), MySQLdb.converters.conversions)
  File "/home/jelle/ans/venv64/lib/python3.6/site-packages/MySQLdb/converters.py", line 90, in quote_tuple
    return "(%s)" % (','.join(escape_sequence(t, d)))

TypeError: sequence item 0: expected str instance, bytes found

[dillonhicks]

Bump.

Running into the same issue upgrading from 1.3.9 to 1.3.10. However, 1.3.9 works.

In [2]: import sys

In [3]: import MySQLdb

In [4]: sys.version
Out[4]: '3.6.0 (v3.6.0:41df79263a11, Dec 22 2016, 17:23:13) \n[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)]'

In [5]: MySQLdb.version_info
Out[5]: (1, 3, 10, 'final', 0)

---


In [1]: import MySQLdb

In [2]: import MySQLdb.converters

In [3]: MySQLdb.escape(('a',), MySQLdb.converters.conversions)
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-3-d4b2dd1db689> in <module>()
----> 1 MySQLdb.escape(('a',), MySQLdb.converters.conversions)

/workspace/venv/lib/python3.6/site-packages/MySQLdb/converters.py in quote_tuple(t, d)
     88
     89 def quote_tuple(t, d):
---> 90     return "(%s)" % (','.join(escape_sequence(t, d)))
     91
     92 # bytes or str regarding to BINARY_FLAG.

TypeError: sequence item 0: expected str instance, bytes found

Original Traceback:

  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1107, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1396, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/workspace/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/workspace/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 234, in execute
    args = tuple(map(db.literal, args))
  File "/workspace/venv/lib/python3.6/site-packages/MySQLdb/connections.py", line 316, in literal
    s = self.escape(o, self.encoders)
  File "/workspace/venv/lib/python3.6/site-packages/MySQLdb/converters.py", line 90, in quote_tuple
    return "(%s)" % (','.join(escape_sequence(t, d)))
TypeError: sequence item 0: expected str instance, bytes found

[stakemura]

Hi, This issue is serious for me because all parameterized queries using tuple parameter doesn't work on python 3.5 & mysqlclient 1.3.10 & both of Windows and UNIX. (1.3.9 works.)

sample code

import MySQLdb
db = ...
db.query("SELECT * FROM table WHERE value IN %(values)s", {"values": (1,2,3)})

partial traceback

  File "C:\Python35\lib\site-packages\django\db\backends\utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "C:\Python35\lib\site-packages\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "C:\Python35\lib\site-packages\django\db\backends\mysql\base.py", line 112, in execute
    return self.cursor.execute(query, args)
  File "C:\Python35\lib\site-packages\MySQLdb\cursors.py", line 232, in execute
    args = dict((key, db.literal(item)) for key, item in args.items())
  File "C:\Python35\lib\site-packages\MySQLdb\cursors.py", line 232, in <genexpr>
    args = dict((key, db.literal(item)) for key, item in args.items())
  File "C:\Python35\lib\site-packages\MySQLdb\connections.py", line 316, in literal
    s = self.escape(o, self.encoders)
  File "C:\Python35\lib\site-packages\MySQLdb\converters.py", line 90, in quote_tuple
    return "(%s)" % (','.join(escape_sequence(t, d)))
TypeError: sequence item 0: expected str instance, bytes found

[Fkawala]

@stakemura ditto !

[methane]

I'm very confused. First report said it doesn't work 1.3.9. But another report says it works on 1.3.9, but not 1.3.10????

I can't find any relating changes between 1.3.9 and 1.3.10.

[Fkawala]

@methane I had the problem described by @stakemura with version 1.3.10, downgrading to 1.3.9 fixes the problem.

[methane]

Please stop using tuple argument. It uses underlying broken API.
MySQLdb has long history. There are many old and dangerous APIs alive, and tuple
interpolation uses them. I don't know I can fix it without breaking backward compatibility.

Use ORM instead. Or build string like "(%s, %s, %s, %s)" by yourself.

[methane]

please send pull request to add failing tests which describe your cases.

[methane]

MySQLdb's string functions and methods are full of chaos.
I had managed to work. It's huge pain. I burned out.

I find #140 broke it again. I can't find any way in backward compatible way.
I'll fix it with small backward incompatibility.

But I don't have enough love and energy to manage this feature.
I won't write test for this. It may or may not work.

[stakemura]

I'm so sorry. I mentioned tuple parameter not list.
So I modified previous post.

By the way, Tuple adaption is defined as for psycopg2 . I guess this function is quite useful. But I understood this is not defined on Python DB's API specification unfortunately. As you said, I 'll rewrite queries using tuple adaption on MySQL.

[saaj]

As #174 turned out to be a duplicate, I'd like to chime in with several observation:

1. There's a regression in the patch release, 1.3.10. If it's semver practised, it's wrong to break existing functionality for whatever reason.
2. The behaviour in demonstratively inconsistent as it fails only in py3 and only in latest mysqlclient, where prior versions and pymysql are okay.
3. It is irrelevant to suggest to use an ORM!
4. It is a useful feature and at least some code depends on it. Is there a security concern with tuple repr?
5. Use _binary prefix for bytes/bytearray parameters (now optional) #140 points to 1.3.8: unicode_literal always adding the _binary prefix #123 but it's confusing and not clear what is the purpose of the binary prefixes and how they justify breakage of existing functionality.

[methane]

There's a regression in the patch release, 1.3.10. If it's semver practised, it's wrong to break existing functionality for whatever reason.
The behaviour in demonstratively inconsistent as it fails only in py3 and only in latest mysqlclient, where prior versions and pymysql are okay.

sure.

It is irrelevant to suggest to use an ORM!

ORM is one workaroud.
Another workaround is write (%s, %s, %s) by hand.

It is a useful feature and at least some code depends on it. Is there a security concern with tuple repr?

It's not security issue.

#140 points to #123 but it's confusing and not clear what is the purpose of the binary prefixes and how they justify breakage of existing functionality.

Both feature using very very very complicated part what I don't want to maintain...
It doesn't "justify" the breakage. It caused the breakage.
And I don't have enough motivation and time to fix it for now.

[stebunovd]

Downgrade to 1.3.9 worked for me too

[Dareen]

ditto
downgraded to 1.3.9 and it worked

[methane]

FWI, encoding tuple is totally broken from long ago.
Even in 1.3.9, it will use wrong escaping rule and encoding.
It worked for simple case, but it will cause UnicodeError or even SQL injection.

cur.execute("SELECT * FROM t WHERE id IN %s", (tuple(ids),)) is not safer than
cur.execute("SELECT * FROM t WHERE id IN %s" % (tuple(ids),)), but very slower than it.

So I strongly recommend you to don't use it.

If you really need it, try #155.

[methane]

I upload 1.3.11c1 including #155 to PyPI.

[Djidiouf]

I've found myself looking for some help on how to do a correct query and landing here. I'm quite concerned about the security issues mentioned.

However, I've found later this in the MySQL documentation:
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

It looks like it's a safer option as the data are separated from the statement.