SQL : pass a list as a parameter using psycopg2

Hi,

Despite Postgres course, I am still unable to pass a list as parameter with WHERE statement.

Let say we want 10 item_id so that:
list_id = [0,1,2,3,4,5,6,7,8,9]

conn = psycopg2.connect...
query = "SELECT * FROM table WHERE item_id = list_id;"
pd.read_sql(query, con=conn)

Obviously it doesn’t work, but someone knows how to pass a list as parameter ? I searched on the Web, but I didn’t find clear solution working for me.

Thank you.

W.

Hello @WilfriedF,

Is something like this is what you were hoping for?

list_id = [1, 2, 3, 4]
pd.read_sql("SELECT * FROM customer WHERE customer_id IN {ids};".format(ids=tuple(list_id)),
             con=conn) 
1 Like

Wow ! Great @veena.sanjeeve.line it works !!!
So the correct format is a tuple and I need WHERE IN syntax. Do you know an alternative method without using .format since formating an input parameter is not recommanded (SQL injections risk) ?


First let’s verify SQL Injection with the proposed solution.

If list_id = [1, 2, 3, 4, 'Boom!', 'OR 1=1; --']

Then, with

pd.read_sql("SELECT * FROM customer WHERE customer_id IN {ids};".format(ids=tuple(list_id)),
             con=conn)

it won’t be able to inject malicious code because with tuple() conversion, anything injected will be part of the list like this:

print("SELECT * FROM customer WHERE customer_id IN {ids};".format(ids=tuple(list_id)))

Output:
"SELECT * FROM customer WHERE customer_id IN (1, 2, 3, 4, 'Boom!', 'OR 1=1; --');"


Give it a try and let me know with the updated list_id.

1 Like

Thanks again @veena.sanjeeve.line it works and you made me understand it better. I realized that I could use print like you and check if my ouput is correct or not, and it becomes easy when you know you need a tuple and WHERE IN! I finally found another syntax, slightly different, but this is the same principle (Python 3 only If I am not wrong):

list_id = tuple([1, 2, 3, 4])
pd.read_sql(f"SELECT * FROM table WHERE id IN {list_id};", con=conn)

Wait, regarding SQL injections, this syntax is weakest than using .format like you did it seems !

list_id="(1) OR 1=1"
pd.read_sql(f"SELECT * FROM table WHERE id IN {list_id};", con=conn)

And it’s enough to hack all ID ! Pfff…

Best
W.

1 Like

Bravo @WilfriedF ! :clap:t3:

Hehe my career as hacker is starting today.
I tried to hack also your query, but no way! :slightly_smiling_face:

1 Like

Hi, @WilfriedF

Since Pandas under the hood uses SQLAlchemy it already does all the SQL Injection exception work for you. So you can use any input format you like.

Like format for python 3.0+. Or in addition f-string for python 3.5+.
Or you can use %s if you want it to work in Python 2.7 as well.

1 Like

True that one can use any style (.format(), f-strings, or the C-style %s).
It’s just a matter of personal choice and should stay consistent.

SQL-injection-handling under-the-hood didn’t happen when tuple conversion was removed.
If one isn’t careful while formulating the query string, no matter what style is preferred, it’s bound to hacking.

Although, I’m curious to know more about the pandas under-the-hood action.
Care to share link/source?

Much appreciated!

1 Like

Thanks @moriturus7

As highlighted by @veena.sanjeeve.line, f-strings is fine too, but I have been able to hack all the IDs with (1) OR 1=1 so I am not sure to understand you when you seem to say that SQLAlchemy is preventing under the hood SQL injection exceptions !