[CONPY-141] Can't execute prepared statements with parameters Created: 2021-02-05  Updated: 2021-02-08  Resolved: 2021-02-06

Status: Closed
Project: MariaDB Connector/Python
Component/s: Other
Affects Version/s: 1.0.5
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Luciano Barcaro Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Execute prepared statements with dynamic parameters returns a SQL syntax error.

import mariadb
 
 
 
db = mariadb.connect(host='localhost', user='test', password='test', database='test')
 
qr = db.cursor()
 
 
 
qr.execute('prepare TEST from "select ?, ?"')
 
 
 
# Works as expected
 
qr.execute('execute TEST using 100, 200')
 
records = qr.fetchall()
 
 
 
# Returns a syntax error
 
qr.execute('execute TEST using ?, ?', (50, 100))
 
# Traceback (most recent call last):
 
#   File "<stdin>", line 1, in <module>
 
# mariadb.ProgrammingError: You have an error in your SQL syntax; check the manual that # corresponds to your MariaDB server version for the right syntax to use near '?, ?' at line 1
 
 
 
# Same error with %s
 
 
 
qr.execute('deallocate prepare TEST')
 
db.close()



 Comments   
Comment by Georg Richter [ 2021-02-06 ]

Why do you think this is a bug? Isn't the error message clear?

# mariadb.ProgrammingError: You have an error in your SQL syntax; check the manual that # corresponds to your MariaDB server version for the right syntax to use near '?, ?' at line 1

According to the manual

"After preparing a statement with PREPARE, you execute it with an EXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter markers, you must supply a USING clause that lists user variables containing the values to be bound to the parameters. Parameter values can be supplied only by user variables, and the USING clause must name exactly as many variables as the number of parameter markers in the statement."

Comment by Luciano Barcaro [ 2021-02-08 ]

I asked this because mysql.connector works, and I'm migrating from it.

Should be nice if mariadb connector could escape and quote for itself (should be a lot faster) the arguments.

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