compose mysql query in python

Question

I want to fetch all rows from MySQL table with

query = "SELECT * FROM %s WHERE last_name=%s"
cursor.execute(query, ("employees","Smith"))

but I'm getting You have an error in your SQL syntax. When I try

query = "SELECT * FROM employees WHERE last_name=%s"
cursor.execute(query, ("Smith",))

all is fine. Documentation says cursor.execute(operation, params=None, multi=False) The parameters found in the tuple or dictionary params are bound to the variables in the operation.link on docs

Thank you all for your comments. I think I will follow another answer https://stackoverflow.com/a/13448443/1403984 about whitelisting — flebas, Aug 23 '17 at 10:16
Glenfant's answer is correct and safe assuming the column name isn't untrusted input. — Cees Timmerman, Oct 13 '17 at 12:34

glenfant · Answer 1 · 2017-08-22T12:43:47.610

1

The first will generate an SQL like this:

SELECT * FROM 'employees' WHERE last_name='smith'

The parameters are SQL quoted.

If you really need to have a table name as param, you must proceed in 2 steps:

table_name = 'employees'
query_tpl = "SELECT * FROM {} WHERE last_name=%s"
query = query_tpl.format(table_name)
cursor.execute(query, ("Smith",))

edited Aug 22 '17 at 12:43

answered Aug 22 '17 at 12:38

glenfant

1,298
8
9

score -1 · Answer 2 · answered Aug 22 '17 at 12:38

-1

you need to add the quote symbol. So the query will be like

SELECT * FROM employees WHERE last_name='Smith'

Change both your query to

query = "SELECT * FROM %s WHERE last_name='%s'"
query = "SELECT * FROM employees WHERE last_name='%s'"

answered Aug 22 '17 at 12:38

Arijit Panda

1,581
2
17
36

score -1 · Answer 3 · answered Aug 22 '17 at 12:40

-1

You can't use a parameter for the table name in the execute call. But you can use Python string interpolation for that:

query = "SELECT * FROM %s WHERE last_name=%s" %("employees","Smith")
cursor.execute(query)

answered Aug 22 '17 at 12:40

Harsha Biyani

7,049
9
37
61

1

Params are not quoted. You should care of security. – glenfant Aug 22 '17 at 12:46

score -1 · Answer 4 · answered Aug 22 '17 at 12:40

-1

You can't use a table name as a parameter. you are generating invalid sql with your code that is putting quotes around each string. the table name cannot have quotes around it.

sql you are generating

select * from 'employees' where last_name = 'Smith'

What sql you want

select * from employees where last_name = 'Smith'

you would have to format the string first like the example below.

query = "SELECT * from {} wherre last_name ='{}'"
cursor.execute(query.format("employees","Smith"))

using code like this does open up the possibility of SQL injection. so please bear that in mind.

answered Aug 22 '17 at 12:40

ttallierchio

460
7
17

1

Bad idea. You don't quote params and may open your app to SQL injections. – glenfant Aug 22 '17 at 12:45
1

totally agree and should not do this, hence why i said opens up the possibility of sql injection. – ttallierchio Aug 22 '17 at 12:51

score -1 · Answer 5 · answered Aug 22 '17 at 12:49

-1

query="SELECT * FROM %s WHERE name=%s",(employees,smith)
cursor.execute(query)
rows = cursor.fetchall()

Try this one. Hopefully it works for you.

answered Aug 22 '17 at 12:49

Salman Ahmed

682
1
10
24

compose mysql query in python

5 Answers5