Bug #121747 C extension: prepared-statement results cut every character string at its first NUL byte

Submitted: 11 Nov 17:51
Reporter: Ke Han Email Updates:
Status: Open Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:26.10.0 OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 17:51] Ke Han
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).

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.