Psycopg2 String Formatting With Variable Names For Type Creation

When passing in a variable type name for creation in postgres using psycopg2 using its formatting option, an error was throw over bad string formatting for the name of objects. Are

Solution 1:

You should actually never python-format your queries, but let psycopg2 do it. Here is the way to make it: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries i.e:

cur.execute("INSERT INTO numbers VALUES (%s)", (42,))

Starting with the 2.7 version, some advanced formatting possibilities are available through the psycopg2.sql module: http://initd.org/psycopg/docs/sql.html

Solution 2:

As you guessed, you can't pass things like table names as query parameters.

They're sent as protocol level bind parameters and (critically) the query must be parseable as valid SQL with them as placeholders. It's as if you were to run an SQL-level PREPARE then separate EXECUTE. You must format them into the SQL string before preparing it, using appropriate identifier quoting.

Double quote the identifier(s) you're substituting in and beware of possible double quotes within the string you're being passed that might prematurely end your quoted sequence. These must be doubled. For example, for the table name some"table you would use:

'SELECT * FROM "{0}"'.format('some""table');

SQL injection is a very serious risk; you must get your quoting exactly right. Ideally find a client-side equivalent of the PostgreSQL quote_ident SQL function.

Note that double quoted identifiers are case sensitive. Make sure you create them with the same case in the DB by always using them double quoted, don't mix quoted and unquoted identifiers.

Solution 3:

I ran into this problem also and wrote a short blog post which covers the connection to a remote database using string formatting but this issue and my resolution is applicable to any situation where you need to pass variables to SQL commands.

Here is how I worked around this issue:

import psycopg2 as db
import sys

my_host = 'ec2-190-111-829-829.compute-1.amazonaws.com'
my_dbname = 'myDBNameGoesHere'
my_user = 'myUsernameGoesHere'
my_password = 'myPasswordGoesHere'
remote_connection_str = 'host={0} dbname={1} user={2} password={3}'.format(my_host, my_dbname, my_user, my_password)

if __name__ == '__main__':
     try:
          remote_connection = db.connect(remote_connection_str)
          cursor = remote_connection.cursor()
          cursor.execute('SELECT version()')
          version = cursor.fetchone()
          print"\n"print"Successful connection to database."print"Database information:\n", version
          print"\n"except db.DatabaseError, e:
          print'Error %s' % e
          sys.exit(1)

     finally:
          if remote_connection:
          remote_connection.close()