Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

Question

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.

Debacle

What sort of parameterization do you want ? Pseudocode sample will be useful. – Selmaselman

Sidenote, you may want to look into SQLAlchemy, the cost of entry may be a bit higher in some ways, but it really is a very nice ORM. – Anorak

For future reference, the answer is in the first page of the documentation: initd.org/psycopg/docs/usage.html – Istria

On the documentation the examples are very easy. There is none that shows how a more complex query like an update would be done for dynamic values. Something like: set height=5, weight=70 – Bolden

Stack Overflow becomes a lot more useful if you pose a specific problem you are trying to solve. For example querying vs updating a table, etc. The answers here are quite generic as a result, and do not help resolve the problem I'm hitting despite the title which brought me in. – Seline

Answer 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 students WHERE last_name = %(lname)s", 
               {"lname": "Robert'); DROP TABLE students;--"})

Edit: tekHedd's comment rightly points out that the SELECT and the DROP TABLE used different table names, so I fixed it.

Kanya

@mascot6699 it does not, because the query is parameterized. – Perish

The good news is that since the table is named "student", not "students"; even if the code were insecure it would have failed. Lesson to you black-hats: test your exploits! – Voodoo

is it a good way to use Python String format() Method ? – Preciosity

How come this is accepted answer, when in SQL guy is injecting DROP table?..:) – Blackbeard

@Blackbeard Allow me to introduce you to Bobby Tables. Also, the whole point of using parameterized statements is that it (unlike manual string concatenation) will prevent the injection attack. – Kanya

@HankGay I've read this:) – Blackbeard

Bobby Tables never gets old... – Luminosity

@Preciosity The Python string format() method simply creates a new string from the original and unfiltered data. That’s where SQL injection comes from: untrusted data is mixed in with SQL code. – Barring

Answer 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

Kennet

Answer 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:

query = 'INSERT INTO some_table (%s) VALUES (%s)'
cursor.execute(query, (my_dict.keys(), my_dict.values()))

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

Bathysphere

Well unless ou know what are you doing you shouldny just concat input into sql queries, since it is a SQL injection. – Kodiak

downvoted due to suggestion that leaves you open to SQL injection – Canty

@RandySyring This is only open to SQL injection if the keys are not well defined and proper identifiers. The values are still properly parametrized. – Arsenic

Which is to say, in the general case, it leaves you open to SQL injection. – Shelli

Your first example does not work. From the docs: The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate. You used %d. – Capitol

Should use psycopg2.extensions.quote_ident: fields = ','.join(quote_ident(key) for key in my_dict), or better: use psycopg2.sql – Uretic

There are cases where sql injection is not a concern though. Such as in a unit test harness where you want to do functional testing - and actually insert completely static data into a database. – Cluff

@Cluff Or just use safe, injection-free queries always and you'll never have any problems. What seems to be harmless code in one context is often copied into unsafe parts of the codebase, or forgotten about as the usage changes over time, leading to future security problems. – Uniform

Answer 4

I love the official docs about this:

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

Outshout

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Outlawry

That's truely funny and worrying; I also love that the person above my comment doesn't see the problem! – Conall

Images of text use up bandwidth and aren't accessible to people with visual impairments. Please see this canonical. – Uniform