Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

All we need is an easy explanation of the problem, so here it is.

What’s the best way to make psycopg2 pass parameterized queries to PostgreSQL? I don’t want to write my own escpaing mechanisms or adapters and the psycopg2 source code and examples are difficult to read in a web browser.

If I need to switch to something like PyGreSQL or another python pg adapter, that’s fine with me. I just want simple parameterization.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

psycopg2 follows the rules for DB-API 2.0 (set down in PEP-249). That means you can call execute method from your cursor object and use the pyformat binding style, and it will do the escaping for you. For example, the following should be safe (and work):

cursor.execute("SELECT * FROM student WHERE last_name = %(lname)s", 
               {"lname": "Robert'); DROP TABLE students;--"})

Method 2

From the psycopg documentation

(http://initd.org/psycopg/docs/usage.html)

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

The correct way to pass variables in a SQL command is using the second argument of the execute() method:

SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes

data = ("O'Reilly", )

cur.execute(SQL, data) # Note: no % operator

Method 3

Here are a few examples you might find helpful

cursor.execute('SELECT * from table where id = %(some_id)d', {'some_id': 1234})

Or you can dynamically build your query based on a dict of field name, value:

fields = ', '.join(my_dict.keys())
values = ', '.join(['%%(%s)s' % x for x in my_dict])
query = 'INSERT INTO some_table (%s) VALUES (%s)' % (fields, values)
cursor.execute(query, my_dict)

Note: the fields must be defined in your code, not user input, otherwise you will be susceptible to SQL injection.

Method 4

I love the official docs about this:

https://www.psycopg.org/psycopg3/docs/basic/params.html

Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply