Correct format of list parameters in composed sql query

Question

I am trying to execute a sql query with a joined list as a parameter . I already established a database connection and I want to have a query like:

SELECT * FROM MYDATABASE.MY_TABLE
WHERE ( name IN ('name1','name2') );

So I want to pass the list as a parameter , and the variable filters is the variable that has the right format stored , but not actually used. The code that was supposed to do that is :

my_list = ['name1', 'name2']
filters = ",".join(("'{}'".format(key) for key in my_list))

q = sql.SQL('''SELECT * FROM MYDATABASE.MY_TABLE WHERE ( name IN ({}) );''')
       .format(sql.SQL(', ')
       .join(sql.Placeholder() * len(my_list)))

cur = self.con.cursor()

query = q.as_string(context=self.con)
cur.execute(q)
results = cur.fetchall()
cur.close()

The string query is :

'SELECT * FROM MYDATABASE.MY_TABLE WHERE ( name IN (%s, %s) );'

, which seems right.
But there is an error bout the the escape character and I cannot find a way around.
Last , I have to use the placeholder and not a simple
".... where name = {}".format() solution , mostly for security reasons. Any help ?

EDIT: I tried all the suggestions and they all give me the Syntax Error :

'ERROR: syntax error at or near ","
LINE 1: ....MY_TABLE WHERE (name IN (?,?)) ...
..................................................................... ^'

Possible duplicate of [imploding a list for use in a python MySQLDB IN clause](https://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause) — Olvin Roght, Aug 30 '19 at 13:25

score 0 · Answer 1 · answered Aug 30 '19 at 13:18

0

Try this:

cursor.execute("SELECT * FROM MYDATABASE.MY_TABLE WHERE (name IN (?,?))",my_list[0], my_list[1])

answered Aug 30 '19 at 13:18

Manuel

117
2
9

You can apply unpacking there `cursor.execute("SELECT * FROM MYDATABASE.MY_TABLE WHERE (name IN (?,?))", *my_list)` – Olvin Roght Aug 30 '19 at 13:20
This is a solution for a list with only 2 items , but the length of the list can and it will be different in every call . So how would this work for a different length in every call ? – Flora Biletsiou Aug 30 '19 at 13:21

Manuel · Answer 2 · 2019-08-30T14:03:51.867

0

placeholder = '?'
placeholders = ', '.join(placeholder for unused in mylist)
query = "SELECT * FROM MYDATABASE.MY_TABLE WHERE (name IN (%s))" % placeholders
list = ",".join(my_list)    
cursor.execute(query, list)

edited Aug 30 '19 at 14:03

answered Aug 30 '19 at 13:29

Manuel

117
2
9

I get an exeption : not all arguments converted during string formatting – Flora Biletsiou Aug 30 '19 at 13:35
Sorry I miss one more line, check it now – Manuel Aug 30 '19 at 13:38
This was supposed to work , I get the same error as the beggining :syntax error at syntax error at or near "," LINE 1: ...E.MY_TABLE name IN (?, ?) AND – Flora Biletsiou Aug 30 '19 at 13:53
try now with the last change – Manuel Aug 30 '19 at 14:04

Mahmoud Sagr · Answer 3 · 2019-08-30T14:43:50.650

0

You can simply use ",".join(my_list), this will concatenate your table with "', '" as a separator:

my_list = ['name1', 'name2', 'name3', 'name4']
joined_list = "', '".join(my_list)   # Returns: "name1,name2,name3"

formatted = f"SELECT * FROM MYDATABASE.MY_TABLE WHERE ( name IN ('{joined_list}') )"
print(formatted) # Result: "SELECT * FROM MYDATABASE.MY_TABLE WHERE ( name IN ('name1', 'name2', 'name3', 'name4') )"

EDIT: Here is a better solution to avoid SQL injection

my_list = ['name1', 'name2', 'name3', 'name4']
place_holders = "?," * (len(my_list) -1) + "?"

formatted = f"SELECT * FROM MYDATABASE.MY_TABLE WHERE ( name IN ({place_holders}) )"
print(formatted) # Result: "SELECT * FROM MYDATABASE.MY_TABLE WHERE ( name IN (?,?,?,?) )"

edited Aug 30 '19 at 14:43

answered Aug 30 '19 at 13:50

Mahmoud Sagr

106
5

This gives me an syntax error because for the query to be successfull , the names have to be single quoted as : 'name1' . This will work only if the joined_list is : filters = ",".join(("'{}'".format(key) for key in my_list)) But the question in that case is if it is safe from sql injection,etc. – Flora Biletsiou Aug 30 '19 at 14:08
Nope, it's not safe. I added another way to do the placeholders thing to the answer, so you can unpack the list to pass it as arguments when executing it – Mahmoud Sagr Aug 30 '19 at 14:46
So , now I get a TypeError , propably because doing something wrong , "function takes at most 2 arguments (3 given)" . I unpack the list as : cur.execute(q, *my_list) – Flora Biletsiou Aug 30 '19 at 14:58
Ouch, I missed that, when you execute the query just pass a tuple to it, it will get unpacked during executing. it should be like cur.execute(q, tuple(my_list)) in your case – Mahmoud Sagr Aug 30 '19 at 15:15
I get the same SyntaxError that I was getting since the beginning: syntax error at or near "," LINE 1: ....MY_TABLE WHERE (name IN (?,?)) ... ^ – Flora Biletsiou Aug 30 '19 at 15:24

Correct format of list parameters in composed sql query

3 Answers3