12

I have a dictionary of database names. I take a name from the dictionary

database_name = database_dict[i]

lets say the value for database_name is 'foo'

Using Psycopg2 I am executing a statement:

cur.execute("INSERT INTO %s VALUES(...);", database_name)

I get A syntax error at foo, because it should be "INSERT INTO foo VALUES" not "INSERT INTO 'foo' VALUES"

Any advice how to pass in a string value for the name of the table and removing the single quotes? Should I place an escape character inside my database dictionary values?

EDIT: Something closer is here: How do I remove single quotes from a table in postgresql?

but I could not get it to work using REMOVE. It gave a syntax error at the single quote inside the remove statement.

Community
  • 1
  • 1
Steve Scott
  • 1,441
  • 3
  • 20
  • 30
  • 1
    Identifiers (such as table and column names) use double quotes in standard SQL (and PostgreSQL) so you need to use an identifier-specific quoting method. AFAIK this means [`quote_ident`](http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.quote_ident) and string concatenation but I don't know enough Python or Psycopg2 to be sure of the best/right way. – mu is too short May 09 '17 at 19:02
  • Is the value in `database_dict[i]` `'foo'` or `foo`? Where are the quotes coming from? – Schwern May 09 '17 at 22:57

4 Answers4

20
from psycopg2.extensions import AsIs
cur.execute("INSERT INTO %s VALUES(...);", AsIs(database_name))

https://www.psycopg.org/docs/extensions.html#psycopg2.extensions.AsIs

BTW that is not a database name, it is a table name.

James Dewes
  • 387
  • 4
  • 22
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
11

The structural components of an SQL query such as table and field names cannot be parameterized as you attempt in second argument of cursor.execute(query, params). Only numeric/literal data values can be parameterized.

Consider interpolating the database_name variable into the SQL query string but do so safely with psycopg2's sqlIdentifier() with str.format:

from psycopg2 import sql
...

cur.execute(sql.SQL('INSERT INTO {} VALUES(...)').format(sql.Identifier(database_name)))

Valid parameterizaiton in your case would be to bind the data values passed in the VALUES(...) in append query such as VALUES(%s, %s, %s). Alternatively in other queries: 

"SELECT %s AS NewColumn..."

"...WHERE fieldname = %s OR otherfield IN (%s, %s, %s)"

"...HAVING Max(NumColumn) >= %s"
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Direct interpolation without escaping the `database_name` invites a SQL injection attack. – Schwern May 09 '17 at 22:44
  • By using .format, I was able to overcome this limitation. Thank you @Parfait – Steve Scott May 09 '17 at 22:55
  • There is no risk of SQL injection because there are no fields to fill out in my application. – Steve Scott May 09 '17 at 22:56
  • @SteveScott The unquoted, unescaped `database_name` itself is the risk. It should be run through `quote_ident` if you're going to build a SQL query with string methods like `.format`. – Schwern May 09 '17 at 22:58
  • 1
    Indeed, thanks to @Schwern pyscopg2 has the [`sql.Identifier()`](http://initd.org/psycopg/docs/sql.html) method to separate the string and variable parts of query for safe interpolation for identifiers. See edit. – Parfait May 09 '17 at 23:44
  • @Parfait Great find! It's so weird they don't just have a `%i` and I'm not aware of any database interface that does. – Schwern May 09 '17 at 23:52
  • Python in general does use, `%`, for string interpolation where `str.format` is preferred but as docs show not safe for queries. Of course the safest for OP will be to code a stored function directly in postgres and call it at application layer. – Parfait May 10 '17 at 00:03
1

Note: I haven't use psycopg2, this is based on what I know from similar database libraries.

A table name is an identifier and they get quoted and escaped differently than values. I believe you should use psycopg2.extensions.quote_ident(str, scope) to quote and escape it. I believe it uses the PostgreSQL function PQescapeIdentifier().

PQescapeIdentifier escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise not be interpreted as part of the identifier by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved.

Then it will be quoted and escaped and can be safely added to the SQL string using normal string operations without risking a SQL injection attack, or using AsIs(quote_ident(database_name)) as a value to .execute.

Schwern
  • 153,029
  • 25
  • 195
  • 336
-2

If fact, database_name is "'foo'".

To drop the single quote:

database_name = database_name.replace("'", "")
Laurent LAPORTE
  • 21,958
  • 6
  • 58
  • 103
  • That is not correct. My value is a string. I cannot insert a string into the SQL statement without the single quotes. Your suggestion did not work. – Steve Scott May 09 '17 at 18:56
  • Right, %s wraps it in single quotes, but I cannot have quotes in the SQL statement. – Steve Scott May 09 '17 at 19:13