77

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.

Improve this question
5
  • 2
    What sort of parameterization do you want ? Pseudocode sample will be useful. Commented Sep 23, 2009 at 15:53
  • 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. Commented Nov 9, 2009 at 18:55
  • For future reference, the answer is in the first page of the documentation: initd.org/psycopg/docs/usage.html Commented May 16, 2011 at 16:07
  • 2
    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 Commented Jul 25, 2018 at 16:01
  • 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. Commented May 9, 2020 at 11:17

4 Answers 4

Reset to default
132

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.

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

9 Comments

@mascot6699 it does not, because the query is parameterized.
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!
is it a good way to use Python String format() Method ?
How come this is accepted answer, when in SQL guy is injecting DROP table?..:)
@Andrius 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.
|
44

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

Improve this answer

Comments

5

I love the official docs about this:

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

enter image description here

Improve this answer

3 Comments

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
That's truely funny and worrying; I also love that the person above my comment doesn't see the problem!
Images of text use up bandwidth and aren't accessible to people with visual impairments. Please see this canonical.
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.

Improve this answer

8 Comments

Well unless ou know what are you doing you shouldny just concat input into sql queries, since it is a SQL injection.
downvoted due to suggestion that leaves you open to SQL injection
@RandySyring This is only open to SQL injection if the keys are not well defined and proper identifiers. The values are still properly parametrized.
Which is to say, in the general case, it leaves you open to SQL injection.
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.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.