Passing param to DB .execute for WHERE IN... INT list

Question

With Python's DB API spec you can pass an argument of parameters to the execute() method. Part of my statement is a WHERE IN clause and I've been using a tuple to populate the IN. For example:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

But when I run into a situation where the parameter tuple is only a tuple of 1 item, the execute fails.

ProgrammingError: ERROR: syntax error at or near ")"
LINE 13: WHERE id IN (3,)

How can I get the tuple to work with clause properly?

mechanical_meat · Accepted Answer · 2021-09-16 16:55:31Z

13

Edit: If you think this answer circumvents the built-in protections against SQL-injection attack you're mistaken; look more closely.

Testing with pg8000 (a DB-API 2.0 compatible Pure-Python interface to the PostgreSQL database engine):

This is the recommended way to pass multiple parameters to an "IN" clause.

params = [3,2,1]
stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in params)
cursor.execute(stmt, params)

Full example:

>>> from pg8000 import DBAPI
>>> conn = DBAPI.connect(user="a", database="d", host="localhost", password="p")
>>> c = conn.cursor()
>>> prms = [1,2,3]
>>> stmt = 'SELECT * FROM table WHERE id IN (%s)' % ','.join('%s' for i in prms)
>>> c.execute(stmt,prms)
>>> c.fetchall()
((1, u'myitem1'), (2, u'myitem2'), (3, u'myitem3'))

edited Sep 16, 2021 at 16:55

answered Feb 12, 2010 at 17:48

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Giotta Over a year ago

Correct me if I'm wrong, but doesn't your example only pass the first item only to the IN sub-clause? > SELECT * FROM table WHERE id IN (3)

OpenAI stole this from rspeer Over a year ago

This answer is dangerously wrong! Substituting parameters yourself in Python code, instead of letting the database driver do it, is a security hole called "SQL injection". Imagine if one of the items in the list were the string "); DROP TABLE table; --".

OpenAI stole this from rspeer Over a year ago

Protection from SQL injections isn't something you should add on to code that allows SQL injections. You will rarely get it right. Instead, you should not write the code that allows SQL injections.

BrenBarn Over a year ago

@rspeer: This isn't substituting parameters in Python code, it's generating placeholders in Python code and then letting the DB-API do the substitution into those placeholders.

Oliver Bock Over a year ago

It might be a little less easy to misunderstand without the comprehension: ['%s'] * len(params).

|

Daniel · Accepted Answer · 2010-02-12 16:58:38Z

1

The error is coming from the comma after the 3. Just leave it off for the single values and you're set.

params = ((3), ... )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

answered Feb 12, 2010 at 16:58

Daniel

6771 gold badge7 silver badges10 bronze badges

3 Comments

John Giotta Over a year ago

Yeah I know why the error happened, but I'm not building the tuple. The tuple is populated by another SQL result. So in passing, the single item tuple retains a hanging comma.

John Giotta Over a year ago

I also wanted to point out that a single item tuple must have a trailing comma.

Daniel Over a year ago

Ah, I misunderstood. Well in that case you could use len() to get the length of the tuple and if its one the use tuple[0] to extract the value without the comma.

OpenAI stole this from rspeer · Accepted Answer · 2016-08-16 22:07:36Z

This may not be an answer to exactly the question you asked, but I think it may solve the problem you have.

Python's DB-API doesn't seem to give you a way to pass tuples as safely substituted parameters. The accepted answer from bernie is using the Python % operator for substitution, which is unsafe.

However, you may not have to pass tuples as parameters, particularly when the tuple you want is the result of another SQL query (as you indicated to Daniel). Instead, you can use SQL subqueries.

If the set of IDs you want in your IN clause is the result of SELECT id FROM other_table WHERE use=true, for example:

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE use=true)"
db.execute(stmt)

And this can be parameterized (the safe way), too. If the IDs you want to select are the ones with a given parent_id:

stmt = "SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE parent_id=%s)"
params = (parent_id,)
db.execute(stmt, params)

Tony Locke · Accepted Answer · 2020-08-20 08:13:16Z

1

As the question said, the following will fail:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id IN %s"
db.execute(stmt, params)

Following the pg8000 docs the IN can be replaced with an ANY() to give the same result:

params = ((3, 2, 1), )
stmt = "SELECT * FROM table WHERE id = ANY(%s)"
db.execute(stmt, params)

This sends the query and parameters separately to the server, avoiding SQL injection attacks.

answered Aug 20, 2020 at 8:13

Tony Locke

4543 silver badges9 bronze badges

Comments

Frédéric H. RAMANDANIARIVO · Accepted Answer · 2019-10-15 06:42:32Z

0

A solution with f-string.

params = [...]
stmt = f"SELECT * FROM table WHERE id IN ({','.join(['%s']*len(params ),)})"
db.execute(stmt, params)

If there is another param placeholder it will be like this

age = 18
params = [...]
stmt = f"SELECT * FROM table WHERE age>%s AND id IN ({','.join(['%s']*len(params ),)})"
db.execute(stmt, tuple([age] + params))

answered Oct 15, 2019 at 6:42

Frédéric H. RAMANDANIARIVO

1

Comments

kielni · Accepted Answer · 2017-04-07 19:15:04Z

-1

The accepted answer risks SQL injection; you should never ever pass user input directly to the database. Instead, generate a query with the correct number of placeholders, then let pg8000 do the escaping:

params = [3,2,1]
# SELECT * from table where id in (%s,%s,%s)
stmt = 'SELECT * FROM table WHERE id IN ({})'.format(','.join(['%s']*len(params)))
cursor.execute(stmt, tuple(params))

answered Apr 7, 2017 at 19:15

kielni

5,0571 gold badge26 silver badges21 bronze badges

2 Comments

BrenBarn Over a year ago

Isn't this the same as what the accepted answer does? It is also generating a query with the right number of placeholders, not substituting the params directly into the query string.

hipertracker Over a year ago

For params = [3] you will get the SQL syntax error "IN(3,)"

Collectives™ on Stack Overflow

Passing param to DB .execute for WHERE IN... INT list

6 Answers 6

6 Comments

3 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

6 Comments

3 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related