Passing Table Name as a Parameter in Psycopg2

Passing table name as a parameter in psycopg2

According to the official documentation:

If you need to generate dynamically an SQL query (for instance
choosing dynamically a table name) you can use the facilities
provided by the psycopg2.sql module.

The sql module is new in psycopg2 version 2.7. It has the following syntax:

from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {table} values (%s, %s)")
        .format(table=sql.Identifier('my_table')),
    [10, 20])

More on: https://www.psycopg.org/docs/sql.html#module-usage

[Update 2017-03-24: AsIs should NOT be used to represent table or fields names, the new sql module should be used instead: https://stackoverflow.com/a/42980069/5285608 ]

Also, according to psycopg2 documentation:

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

Unable to Pass in Table Name for Query psycopg2

According to:

Passing table name as a parameter in psycopg2

You should be using this

template:

from psycopg2 import sql
cur.execute(
    sql.SQL("insert into {table} values (%s, %s)")
        .format(table=sql.Identifier('my_table')), # table name here
    [10, 20]) ## other parameters here

psycopg2 passing in table name

You are receiving this error because the arguments passed into the second argument, (table) (which really should be (table,)), are escaped in the SQL statement that is run.

In this example, the select * from %s as a is transformed into select * from '#temp_table' as a which is an error. To correctly insert a table name, you need to format the SQL statement string directly like so:

query = 'select * from "{}" as a'.format(table)
cursor.execute(query)

You should be very careful about what data you insert into the query this way because it's highly susceptible to SQL-injection exploits. Do not use this with untrusted data.

psycopg2 table name as a parameter fails with double quotes

I changed the query to be formatted as follows and it works much better now:

query = sql.SQL('SELECT * FROM {}.{} LIMIT 0').format(sql.Identifier(schema), sql.Identifier(table))

Thanks!

Psycopg2 Use Table and Column Name as Parameters in a Function

Credit should go to Adrian who commented on my question. Issue was related to the search path due to multiple schemas being in the database. Link below.

https://www.postgresql.org/docs/14/ddl-schemas.html#DDL-SCHEMAS-PATH

psycopg2 cursor.execute() pass in variable table names and items

As mentioned by Antoine's comment, the documentation now suggests this method for composing table names.

from psycopg2 import sql

cur.execute(
sql.SQL("insert into {} values (%s, %s)")
    .format(sql.Identifier('my_table')),
[10, 20])

python psycopg2 table name from a variable

Have you tried to replace the variable in the string before executing the statement?

Like this:

table_name_list = [
    'alfa',
    'beta',
]

for name in table_name_list:
    sql = 'CREATE TABLE IF NOT EXISTS {} ("department" text)'.format(name)
    print(sql)
    c.execute(sql)

Building SQL query string using table-name as given parameter

String building (prone to SQL injection)

What khelwood means:

def selectFrom(table):
    return 'SELECT * FROM ' + table

def see_results(cur, table):
    print("complete")
    cur.execute(selectFrom(table))
    results = cur.fetchall()
    print(results)

or even using f-strings cur.execute(f"SELECT * FROM {table}" directly.

But what if there is malicious input in passed argument table like an appended DROP or TRUNCATE statement (SQL injection)?

Query building (safer)

Using SQL capable libraries (SQL framework or database-frontend) like psycopg, you can build the SQL using safe methods which apply input-validation.

See the examples in module psycopg2.sql to compose an SQL-statement for a given table parameter.

from psycopg2 import sql

cur.execute(
    sql.SQL("SELECT * FROM {} WHERE values IN (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])

---

Related Topics