Connection.escape fails to escape byte strings and raises TypeError: bytes() argument 2 must be str, not dict.

[rsiemens]

Describe the bug

MySQL 8.0.23
Python 3.8.5
mysqlclient 2.0.3

Connection.escape fails to escape byte strings and raises TypeError: bytes() argument 2 must be str, not dict.

At first I assumed this was a Django issue and raised it there (https://code.djangoproject.com/ticket/32595), but was pointed back here. I believe this is the offending code (https://github.com/PyMySQL/mysqlclient/blob/v2.0.1/MySQLdb/connections.py#L189-L194). Looking at the comment I think it can simply be removed as mysqlclient is on 2.x now and Django 1.11 is EOL.

To Reproduce

Simplest reproduction -

• Have a MySQL server running and connect to it

import MySQLdb
conn = MySQLdb.connect(user='root', host='localhost', port=33060)
conn.escape(b'foo', conn.encoders)
>> TypeError: bytes() argument 2 must be str, not dict

Environment

MySQL Server

• Server OS (e.g. Windows 10, Ubuntu 20.04): MacOS 10.15.7
• Server Version (e.g. MariaDB 10.3.16): MySQL 8.0.23

MySQL Client

• OS (e.g. Windows 10, Ubuntu 20.04): MacOS 10.15.7
• Python (e.g. Homebrew Python 3.7.5): 3.8.5
• mysqlclient 2.0.3

[methane]

conn.escape(b'foo', conn.encoders)

This is not intended usage. conn.escape(b'foo') should be used.
And self.encoders[bytes] = bytes was added because Django used it in unexpected way.

It is so frustlating. Why Django use this library so complex way...

[felixxm]

@methane Thanks for an advice. I tried (django/django#14196) calling escape() without encoders but it crashes with unicode chars 🤔 , e.g.

>>> conn.escape('μg/mL')
UnicodeEncodeError: 'ascii' codec can't encode character '\u03bc' in position 0: ordinal not in range(128)

[felixxm]

We can manually encode strings, but should we? 🤔

[methane]

• UnicodeEncodeError is not relating to encoders parameter.　You should use ‘charset＝utf8MB4‘ in ‘connect()‘.
• DB-API doesn't provide API to escape. You should use cur.execute(sql_with_placeholder, params).

[felixxm]

• UnicodeEncodeError is not relating to encoders parameter.　You should use ‘charset＝utf8MB4‘ in ‘connect()‘.

We use charset='utf8' but it also crashes with 'utf8mb4'. Moreover it works with encoders 🤔 :

>>> conn.encoding
'utf8'
>>> conn.escape('μg/mL')
UnicodeEncodeError: 'ascii' codec can't encode character '\u03bc' in position 0: ordinal not in range(128)
>>> conn.escape('μg/mL', conn.encoders)
b"'\xce\xbcg/mL'"

• DB-API doesn't provide API to escape. You should use cur.execute(sql_with_placeholder, params).

Docs are confusing IMO: "escape any special characters in object obj using mapping dict to provide quoting functions for each type." We want to quote values for DDL statements. We would be grateful for any advice. Thanks.

\cc @claudep

[methane]

You are right. Connection.escape() needs conn.encoders to support Unicode.

I am not original author of this library, and Connection.escape() is broken from first. It is implemented in C. But C library don't support Unicode. Connection.encoding is supported by Python.

I added ugly hack to workaround it. That's why Connection.escape(obj) can not support Unicode and you need to pass Connection.encoders.

So there is no simple right way to do it for now.

[felixxm]

@methane Thanks 👍 I've proposed adding a "temporary" workaround to Django, see django/django#14196.

[felixxm]

IMO, this can be closed.

[rsiemens]

Closing - issue #472 captures removing self.encoders[bytes] = bytes in 2.1.

Thanks @methane and @felixxm!