Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
PythonPostgresqlPsycopg2Python Problem Overview
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.
Python Solutions
Solution 1 - Python
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;--"})
Solution 2 - Python
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
Solution 3 - Python
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.
