67

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.

Kara
  • 6,115
  • 16
  • 50
  • 57
jeffcook2150
  • 4,028
  • 4
  • 39
  • 51
  • 1
    What sort of parameterization do you want ? Pseudocode sample will be useful. – whatnick Sep 23 '09 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. – Bryan McLemore Nov 09 '09 at 18:55
  • For future reference, the answer is in the first page of the documentation: http://initd.org/psycopg/docs/usage.html – piro May 16 '11 at 16:07
  • 1
    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` – Mutai Mwiti Jul 25 '18 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. – Bill Gale May 09 '20 at 11:17

4 Answers4

124

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.

Hank Gay
  • 70,339
  • 36
  • 160
  • 222
  • 4
    @mascot6699 it does not, because the query is parameterized. – Nathan Hinchey Jun 02 '17 at 18:58
  • 15
    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! – tekHedd Jun 23 '20 at 18:26
  • is it a good way to use Python String format() Method ? – Arsen Jun 11 '22 at 18:25
  • How come this is accepted answer, when in SQL guy is injecting `DROP table`?..:) – Andrius Mar 15 '23 at 13:19
  • @Andrius Allow me to introduce you to [Bobby Tables](https://xkcd.com/327/). Also, the whole point of using parameterized statements is that it (unlike manual string concatenation) will prevent the injection attack. – Hank Gay Mar 27 '23 at 19:32
  • @HankGay I've read this:) – Andrius Mar 28 '23 at 14:06
40

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

Community
  • 1
  • 1
Fábio Dias
  • 636
  • 8
  • 18
4

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.

tedder42
  • 23,519
  • 13
  • 86
  • 102
adam
  • 6,582
  • 4
  • 29
  • 28
  • 11
    Well unless ou know what are you doing you shouldny just concat input into sql queries, since it is a SQL injection. – jb. Jan 25 '13 at 18:45
  • 26
    downvoted due to suggestion that leaves you open to SQL injection – Randy Syring Dec 23 '14 at 05:04
  • 5
    @RandySyring This is only open to SQL injection if the keys are not well defined and proper identifiers. The values are still properly parametrized. – Uyghur Lives Matter Apr 07 '15 at 14:17
  • 8
    Which is to say, in the general case, it leaves you open to SQL injection. – rspeer May 21 '18 at 21:06
  • 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`. – Alex Totheroh Sep 08 '20 at 23:20
  • Should use `psycopg2.extensions.quote_ident`: `fields = ','.join(quote_ident(key) for key in my_dict)`, or better: `use psycopg2.sql` – Ali Akbar Apr 11 '22 at 05:39
  • 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. – KenFar Jul 19 '23 at 01:17
2

I love the official docs about this:

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

enter image description here

Szabolcs Szepesi
  • 97
  • 1
  • 7
  • 1
    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](/review/late-answers/31673622) – Emi OB May 04 '22 at 12:12