Psycopg2: Why table and column names should be interpolated using string formating

[aisbaa]

Hello, psycopg2 documentation states that I should interpolate table and column names using python string formating - but does not explain why. So I'm wondering what is the reason for that?

[abw333]

Hi @aisbaa, I'm not a psycopg2 developer, but my $0.02: variable values like the above should be bound through the method described above to make sure that they're sanitized, and that you're not vulnerable to an SQL injection attack. There's a funny warning about it in the docs:

Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

These variable values are usually the ones that might come from user (potential hacker) input.

psycopg2 is smart about looking at the type of the variable that you pass in. For instance, if you pass in a string, it will quote the string for you in the SQL query. If you pass in an integer, it will go straight through, etc. In this way a table name would get interpreted as a string and would be (incorrectly) quoted, likely making your SQL invalid.

The idea behind this is that table names and column names typically don't come from user input, and are instead decided in advance by the developer, and therefore the variable interpolation API is simplified by assuming that the values that you're passing in are proper variables, not table/column names. Does that make sense?

[fogzot]

That's exactly the idea. We will probably add an "identifier" adapter to correctly quote table and column names in a future release (or at least expose a function to do the escaping).

[aisbaa]

Yes, thank you, it does make sense. Could you also give an example of incorrect table name quoting?

I've tried to create few tables with interesting names. Only thing is I couldn't use " char in table name.

db=# \d
                         List of relations
 Schema |                  Name                  | Type  |  Owner   
--------+----------------------------------------+-------+----------
 public | %s'-*&#@$\%@                           | table | postgres
 public | %s'-*&#@$\%@,./?!@#$%^&*()_            | table | postgres
 public | %s'-*&#@$\%@,./?!@#$%^&*()_~`><+-*/=;: | table | postgres

[abw333]

you can use " in the table name, but you need to properly escape it:

db=# create schema test;
CREATE SCHEMA
db=# create table test."te""st" (test text);
CREATE TABLE
db=# \dt test.
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 test   | te"st | table | abw333
(1 row)

[aisbaa]

Sorry to bother you again, but could you give me example of table name what would be incorrectly quoted and result in invalid SQL?

[abw333]

here's an example that fails if the table name contains a space and is not quoted:

db=# create schema test;
CREATE SCHEMA
db=# create table test."hello world" (id text);
CREATE TABLE
db=# create table test.hello world (id text);
ERROR:  syntax error at or near "world"
LINE 1: create table test.hello world (id text);
                                ^

similarly you would run into trouble if your table name contains some other character that has special meaning in SQL (e.g. +, *, (, etc.) and is not quoted

[aisbaa]

Brilliant, thank you guys!

[shubhankarthakur12]

My column name is node-ip how should i use it in a query.
i.e. cur.execute("SELECT node-ip from nd_alarm_ack")

getting below error:
Traceback (most recent call last):
File "python.py", line 12, in
cur.execute("SELECT node-ip from nd_alarm_ack")
psycopg2.ProgrammingError: column "node" does not exist
LINE 1: SELECT node-ip from nd_alarm_ack

Works fine for column name region.
i.e. cur.execute("SELECT region from nd_alarm_ack")

[fogzot]

This is not psycopg fault: your SQL is wrong. Just try it in psql and you'll see the same error:

postgres=# SELECT node-ip from nd_alarm_ack;
ERROR:  column "node" does not exist
LINE 1: SELECT node-ip from test;

You should correctly quote identifiers that don't use the basic SQL character set:

postgres=# SELECT "node-ip" from nd_alarm_ack;

Also, please, don't reopen or comment old issues that have nothing to do with your problem.

[shubhankarthakur12]

in my above issue i am using psql.