[CONPY-277] Queries fail when sql_mode=ANSI_QUOTES Created: 2023-12-26  Updated: 2024-02-05  Resolved: 2024-02-05

Status: Closed
Project: MariaDB Connector/Python
Component/s: DBAPI 2.0
Affects Version/s: 1.1.9
Fix Version/s: 1.1.10

Type: Bug Priority: Major
Reporter: Hristo Venev Assignee: Georg Richter
Resolution: Fixed Votes: 0
Labels: None

Python Version: 3.11

 Description   

In Cursor._substitute_parameters, double quotes are used unconditionally to quote strings. Single quotes should be used instead, preferably via mysql_real_escape_string_quote for compatibility with NO_BACKSLASH_ESCAPES.

 Comments   
Comment by Georg Richter [ 2024-01-31 ]

Thanks for your report. Can you please provide a short reproducible example?

Comment by Hristo Venev [ 2024-02-01 ]

On a database with sql-mode=TRADITIONAL,ANSI_QUOTES,ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT:

import mariadb
 
 
 
with mariadb.connect() as conn, conn.cursor(binary=False) as cur:
 
    cur.execute('select ? as x', ('hi',))
 
    print(cur.fetchall())

Setting binary=True works around the issue, but the default binary=False is broken.

Generated at Thu Feb 08 03:31:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.