CODEWITHSUNDEEP
pythonsqlparameters
greeneye12
greeneye12

Reputation: 73

MYSQL parameter python issue with table name

I am new in using python API to send a query to mysql.

My issue is very easy to reproduce. I have a table named "ingredient" and I would like to select the rows from python using parameters

If I do cursor.execute("select * from ?",('ingredient',)) I get the error message : Error while connecting to MySQL Not all parameters were used in the SQL statement MySQL connection is closed

I I do cursor.execute("select * from ?",'ingredient') I get the error message : Error while connecting to MySQL 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

Same issues using %s instead of ?. Using the other type of single quote on 'ingredient' instead of 'ingredient' does not give results either.

How is this supposed to work here ?

Upvotes: 1

Views: 589

Answers (2)

jmaloney13
jmaloney13

Reputation: 235

Does your query work if you just write:

cursor.execute("SELECT * FROM ingredient")

?

Upvotes: 0

GMB
GMB

Reputation: 222582

You just can't pass a table name as parameter to a query. The parameterization mechanism is there to pass literal values, not object names. Keep in mind that the database must be able to prepare the query plan from just the parameterized string (without the actual parameter value), which disqualifies using metadata as parameter.

You need string concatenation instead:

cursor.execute("select * from " + yourvar);

Note that, if the variable comes from outside your program, using such contruct exposes your code to SQL injection. You need to manually validate the value of the parameter before execting the query (for example by checking it against a fixed list of allowed values, or by querying the information schema of the database to ensure that the table does exist).

Upvotes: 2

Related Questions