"Psycopg2.error column doesn't exist" error when trying to write injection-safe PostgreSQL query on Python

1
votes

I'm writing a function to dynamically create insert/update/select queries for PostgreSQL database using psycopg2 library.

I've been trying to write the function injection-safe according to the recommendations provided by psycopg2 documentation - using sql.Sql method to compose the query correctly. All the parameters (table name, columns to insert, the values) are passed dynamically to the function:

def insert(table, columns: list, values: list):

    query = sql.SQL('INSERT INTO {} ({}) VALUES ({});').format(
                    sql.Identifier(table),
                    sql.SQL(', ').join(map(sql.Identifier, columns)),
                    sql.SQL(', ').join(map(sql.Identifier, values))
                   )
    cursor = foo_connection.cursor()
    cursor.execute(query)

When I'm trying to test the function:

insert('test_table', ['col1', 'col2', 'col3'], ['1', '2', '3'])

I get the following error:

psycopg2.errors.UndefinedColumn: column "1" does not exist
LINE 1: ...e" ("col1", "col2", "col3") VALUES ("1", "2", ...

I don't undesrtand the error since technically it is not even a column, it is a value to be inserted.

I thought the query was composed incorrectly but the result of print(query.as_string(foo_connection)) shows that it seems to be correct:

INSERT INTO "test_table" ("col1", "col2", "col3") VALUES ("1", "2", "3");

The official documentation does not cover that situation. Googling did not give me the answers either.

So, the question are:

  1. What am I doing wrong here?
  2. How to make this code work?
3
pythonpostgresqlpsycopg2
Are you able to check your postgres database table columns? UndefinedColumn: column "1" does not exist seems to suggest that you are trying to insert a value into a column named "1", which isn't an actual column in your table. - ron_g
The error suggests that I'm trying to insert a column named "1" but it is the value to be inserted according to the query print mentioned in the question. - Vladimir Nedoroslev

3 Answers

2
votes

As Laurenz says, in psycopg2 documentation, it's written:

Identifiers usually represent names of database objects, such as tables or fields.

I also saw for Literals objects:

representing an SQL value to include in a query.

Maybe you can try replacing Identifiers by Literals when you are formatting your queries: 

query = sql.SQL('INSERT INTO {} ({}) VALUES ({});').format(
                sql.Identifier(table),
                sql.SQL(', ').join(map(sql.Identifier, columns)),
                sql.SQL(', ').join(map(sql.Literals, values))
               )
0
votes

I think it's your quotations of your query. Maybe try to use doubles on the outside:

def insert(table, columns: list, values: list):

    query = sql.SQL("INSERT INTO {} ({}) VALUES ({});").format(
                    sql.Identifier(table),
                    sql.SQL(', ').join(map(sql.Identifier, columns)),
                    sql.SQL(', ').join(map(sql.Literlas, values))
                   )
    cursor = foo_connection.cursor()
    cursor.execute(query)
0
votes

The problem is that you format the value constants as sql.Identifier.

This causes them to be quoted with double instead of single quotes, so PostgreSQL considers them table columns. Hence the error message.

The best thing would be to use %s placeholders for the literals and pass them as a second argument to execute().