Description:
With the C extension (the default implementation when installed), a
prepared cursor (cursor(prepared=True), binary protocol) returns every
CHAR/VARCHAR/TEXT value (any non-binary character set) only up to its
first NUL byte: 'abc\0def' is returned as 'abc', '\0tail' as '' and 'k\0z'
as 'k'. There is no error or warning, so a read-modify-write or a copy made
with prepared statements stores the truncated values (shown below).
Binary columns (charset 63) are returned in full. The text-protocol cursor
of the same connection and the pure-Python implementation (use_pure=True)
return the full values. A NUL byte is a valid character in these column types
and is stored and returned by the server; values like this come from binary
protocols, serialized data or user input.
How to repeat:
# Python 3.12, pip install mysql-connector-python==26.10.0, server 26.10.0
import mysql.connector
cnx = mysql.connector.connect(host='127.0.0.1', port=3306, user='root', autocommit=True) # C extension
print(mysql.connector.__version__, type(cnx).__name__)
txt = cnx.cursor()
txt.execute('DROP DATABASE IF EXISTS cpy203'); txt.execute('CREATE DATABASE cpy203'); txt.execute('USE cpy203')
ddl = '(id INT PRIMARY KEY, v VARCHAR(40), t TEXT, c CHAR(10) CHARACTER SET latin1, b VARBINARY(40))'
txt.execute('CREATE TABLE src ' + ddl); txt.execute('CREATE TABLE dst ' + ddl)
txt.execute("INSERT INTO src VALUES (1, CONCAT('abc', X'00', 'def'), CONCAT(X'00', 'tail'), CONCAT('k', X'00', 'z'), CONCAT('abc', X'00', 'def'))")
prep = cnx.cursor(prepared=True)
prep.execute('SELECT id, v, t, c, b FROM src WHERE id = %s', (1,))
row = prep.fetchone()
print('prepared cursor:', row)
txt.execute('SELECT id, v, t, c, b FROM src WHERE id = 1')
print('text cursor: ', txt.fetchone())
# copy the row with the prepared cursor
prep.execute('INSERT INTO dst VALUES (%s, %s, %s, %s, %s)', tuple(bytes(x) if isinstance(x, bytearray) else x for x in row))
txt.execute('SELECT HEX(v), HEX(t), HEX(c), HEX(b) FROM src UNION ALL SELECT HEX(v), HEX(t), HEX(c), HEX(b) FROM dst')
print('src:', txt.fetchone()); print('dst:', txt.fetchone())
Output:
26.10.0 CMySQLConnection
prepared cursor: (1, 'abc', '', 'k', bytearray(b'abc\x00def'))
text cursor: (1, 'abc\x00def', '\x00tail', 'k\x00z', b'abc\x00def')
src: ('61626300646566', '007461696C', '6B007A', '61626300646566')
dst: ('616263', '', '6B', '61626300646566')
Expected: the prepared cursor returns 'abc\x00def', '\x00tail', 'k\x00z' like
the text cursor (and like use_pure=True), and dst equals src.
Suggested fix:
MySQLPrepStmt_fetch_row() in src/mysql_capi.c fetches each column into a
bytes object of the right length, but converts non-binary character columns
with PyUnicode_FromString(PyBytes_AsString(obj)), which stops at the first
NUL byte:
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
Decoding with the length, e.g.
PyUnicode_DecodeUTF8(PyBytes_AsString(obj), PyBytes_Size(obj), NULL) (or the
connection's character set), keeps the whole value. The DECIMAL branch uses
the same PyUnicode_FromString pattern (line 3765; harmless there), and the
SET branch splits with strtok_r, which is also NUL-terminated.
Related but on another path: Bug #107502 (text-protocol C-extension result of
a binary SUBSTRING() cut at a NUL byte, fixed in 8.0.30).
Description: With the C extension (the default implementation when installed), a prepared cursor (cursor(prepared=True), binary protocol) returns every CHAR/VARCHAR/TEXT value (any non-binary character set) only up to its first NUL byte: 'abc\0def' is returned as 'abc', '\0tail' as '' and 'k\0z' as 'k'. There is no error or warning, so a read-modify-write or a copy made with prepared statements stores the truncated values (shown below). Binary columns (charset 63) are returned in full. The text-protocol cursor of the same connection and the pure-Python implementation (use_pure=True) return the full values. A NUL byte is a valid character in these column types and is stored and returned by the server; values like this come from binary protocols, serialized data or user input. How to repeat: # Python 3.12, pip install mysql-connector-python==26.10.0, server 26.10.0 import mysql.connector cnx = mysql.connector.connect(host='127.0.0.1', port=3306, user='root', autocommit=True) # C extension print(mysql.connector.__version__, type(cnx).__name__) txt = cnx.cursor() txt.execute('DROP DATABASE IF EXISTS cpy203'); txt.execute('CREATE DATABASE cpy203'); txt.execute('USE cpy203') ddl = '(id INT PRIMARY KEY, v VARCHAR(40), t TEXT, c CHAR(10) CHARACTER SET latin1, b VARBINARY(40))' txt.execute('CREATE TABLE src ' + ddl); txt.execute('CREATE TABLE dst ' + ddl) txt.execute("INSERT INTO src VALUES (1, CONCAT('abc', X'00', 'def'), CONCAT(X'00', 'tail'), CONCAT('k', X'00', 'z'), CONCAT('abc', X'00', 'def'))") prep = cnx.cursor(prepared=True) prep.execute('SELECT id, v, t, c, b FROM src WHERE id = %s', (1,)) row = prep.fetchone() print('prepared cursor:', row) txt.execute('SELECT id, v, t, c, b FROM src WHERE id = 1') print('text cursor: ', txt.fetchone()) # copy the row with the prepared cursor prep.execute('INSERT INTO dst VALUES (%s, %s, %s, %s, %s)', tuple(bytes(x) if isinstance(x, bytearray) else x for x in row)) txt.execute('SELECT HEX(v), HEX(t), HEX(c), HEX(b) FROM src UNION ALL SELECT HEX(v), HEX(t), HEX(c), HEX(b) FROM dst') print('src:', txt.fetchone()); print('dst:', txt.fetchone()) Output: 26.10.0 CMySQLConnection prepared cursor: (1, 'abc', '', 'k', bytearray(b'abc\x00def')) text cursor: (1, 'abc\x00def', '\x00tail', 'k\x00z', b'abc\x00def') src: ('61626300646566', '007461696C', '6B007A', '61626300646566') dst: ('616263', '', '6B', '61626300646566') Expected: the prepared cursor returns 'abc\x00def', '\x00tail', 'k\x00z' like the text cursor (and like use_pure=True), and dst equals src. Suggested fix: MySQLPrepStmt_fetch_row() in src/mysql_capi.c fetches each column into a bytes object of the right length, but converts non-binary character columns with PyUnicode_FromString(PyBytes_AsString(obj)), which stops at the first NUL byte: https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357... Decoding with the length, e.g. PyUnicode_DecodeUTF8(PyBytes_AsString(obj), PyBytes_Size(obj), NULL) (or the connection's character set), keeps the whole value. The DECIMAL branch uses the same PyUnicode_FromString pattern (line 3765; harmless there), and the SET branch splits with strtok_r, which is also NUL-terminated. Related but on another path: Bug #107502 (text-protocol C-extension result of a binary SUBSTRING() cut at a NUL byte, fixed in 8.0.30).