Building SQL query string using table-name as given parameter

Question

I am trying to use string formatting with SQL. But while passing in variables, these are inserted with quotes and break the syntax.

Example

Here I am trying to pass in the table name to the function.

def see_results(cur, table):
    print("complete")
    cur.execute(''' SELECT * from %s ''', (table,))
    results = cur.fetchall()
    print(results)

Issue

If I pass "temp_yellow_pages" as argument the resulting query is: ''' SELECT * from "temp_yellow_pages" '''. This breaks.

I can't think of a way to assign anything to the variable table without using "'s as query = temp_yellow_pages would break as well.

You cannot pass the name of the table as a query parameter. You would have to insert it into the actual query string. — khelwood
– khelwood, Commented Jan 10, 2022 at 19:42
Depending on what library you are using, you might have something safer than f"SELECT * from {table}" available. — chepner
– chepner, Commented Jan 10, 2022 at 19:48
What if I set table to be table = "employee -- drop table employee" or some other "here be dragons" example of sql based on string concat? — JonSG
– JonSG, Commented Jan 10, 2022 at 19:48
psycopg2, for example, provides cur.execute(psycopg2.sql.SQL("select * from {}").format(sql.Identifier(table))). Note this isn't str.format, but a SQL.format method that knows about SQL syntax. — chepner
– chepner, Commented Jan 10, 2022 at 19:50
Thanks everyone. I figured that f-string literal might be the only way but was interested to see if there was some other common solution. — Justin Benfit
– Justin Benfit, Commented Jan 10, 2022 at 19:51

hc_dev · Accepted Answer · 2022-01-10 20:44:03Z

3

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])

edited Jan 10, 2022 at 20:44

answered Jan 10, 2022 at 20:29

hc_dev

9,6941 gold badge30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building SQL query string using table-name as given parameter

Example

Issue

1 Answer 1

String building (prone to SQL injection)

Query building (safer)

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example

Issue

1 Answer 1

String building (prone to SQL injection)

Query building (safer)

Comments

Your Answer

Sign up or log in

Post as a guest

Related