Bug #121746 C extension: prepared statements send floats as FLOAT (float32) and read timedelta parameters as datetime.time

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

[11 Nov 17:50] Ke Han
Description:
With the C extension (mysql.connector.connect() returns CMySQLConnection
when it is installed), a prepared cursor (cursor(prepared=True)) stores other
values than the text-protocol cursor of the same connection:

1. Every Python float parameter is narrowed to a C float and sent as
   MYSQL_TYPE_FLOAT, whatever the column type (DOUBLE, DECIMAL, VARCHAR ...):
   3.141592653589793 is stored as 3.1415927410125732, 0.1 as
   0.10000000149011612, 1e15 as 999999986991104, 2.2250738585072014e-308 as 0;
   values above 3.4e38 fail with error 1264.
2. A datetime.timedelta parameter is read with the PyDateTime_TIME_GET_*
   macros, which are for datetime.time objects; on a timedelta they read bytes
   of its days/seconds fields. days, the sign and the microseconds are lost:
   timedelta(seconds=5) is stored as 00:00:00.327680,
   timedelta(days=1, seconds=7) as 00:00:00.458752; most other values fail
   with error 1690 ("TIME value is out of range in 'set_parameter_value'"),
   e.g. timedelta(hours=1).

No error or warning is raised for the stored values. The pure-Python
implementation (use_pure=True) of the same version sends floats as
MYSQL_TYPE_DOUBLE (struct.pack("<d")) and stores every float and positive
timedelta correctly.

How to repeat:
# Python 3.12, pip install mysql-connector-python==26.10.0, server 26.10.0
import datetime
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 cpy202'); txt.execute('CREATE DATABASE cpy202'); txt.execute('USE cpy202')
txt.execute('CREATE TABLE t (id INT PRIMARY KEY, via VARCHAR(10), d DOUBLE, n DECIMAL(40,20), tm TIME(6))')
prep = cnx.cursor(prepared=True)
rows = [(3.141592653589793, datetime.timedelta(seconds=5)),
        (0.1, datetime.timedelta(days=1, seconds=7)),
        (1e15, datetime.timedelta(seconds=3840, microseconds=250000))]
for i, (f, td) in enumerate(rows):
    txt.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (10 * i, 'text', f, f, td))
    prep.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (10 * i + 1, 'prepared', f, f, td))
try:
    prep.execute('INSERT INTO t VALUES (%s, %s, %s, %s, %s)', (99, 'prepared', 1.0, 1.0, datetime.timedelta(hours=1)))
except mysql.connector.Error as e:
    print('timedelta(hours=1):', e)
txt.execute('SELECT id, via, CAST(d AS CHAR), CAST(n AS CHAR), CAST(tm AS CHAR) FROM t ORDER BY id')
for r in txt.fetchall():
    print(r)

Output:
26.10.0 CMySQLConnection
timedelta(hours=1): 1690 (22003): TIME value is out of range in 'set_parameter_value'
(0, 'text', '3.141592653589793', '3.14159265358979300000', '00:00:05.000000')
(1, 'prepared', '3.1415927410125732', '3.14159274101257320000', '00:00:00.327680')
(10, 'text', '0.1', '0.10000000000000000000', '24:00:07.000000')
(11, 'prepared', '0.10000000149011612', '0.10000000149011612000', '00:00:00.458752')
(20, 'text', '1e15', '1000000000000000.00000000000000000000', '01:04:00.250000')
(21, 'prepared', '999999986991104', '999999986991104.00000000000000000000', '00:00:00.003840')

Expected: each "prepared" row equals the "text" row above it (and
timedelta(hours=1) is stored as 01:00:00), as with use_pure=True.

Suggested fix:
MySQLPrepStmt_execute() in src/mysql_capi.c:
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
  pbind->buffer.f = (float)PyFloat_AsDouble(value);
  mbind->buffer_type = MYSQL_TYPE_FLOAT;
should bind a double (pbind->buffer.d / MYSQL_TYPE_DOUBLE), like the
pure-Python implementation.
https://github.com/mysql/mysql-connector-python/blob/a983f1721701f1b122368bf0501a4228e8357...
The PyDelta_Check() branch uses PyDateTime_TIME_GET_HOUR/MINUTE/SECOND/
MICROSECOND on the timedelta; it should use PyDateTime_DELTA_GET_DAYS/
SECONDS/MICROSECONDS, put days * 24 + seconds / 3600 into hour (TIME allows
up to 838 hours) and set MYSQL_TIME.neg for negative values (Python
normalises -1 h to days=-1, seconds=82800, so the magnitude has to be
computed from the whole value).
The query-attribute binding in MySQL_query() has the same two code paths
(lines 2161-2164 and 2228-2244).

Unrelated to the above but in the same function: the DECIMAL branch writes
mbind[i].buffer_type although mbind already points at &mbinds[i]
(line 3438), i.e. it writes into mbinds[2*i], past the end of the array when
a Decimal parameter is in the second half of the parameter list.

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

Page generated in 0.066 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.