CODEWITHSUNDEEP
pythonsql
Justin Benfit
Justin Benfit

Reputation: 483

Building SQL query string using table-name as given parameter

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.

Upvotes: 2

Views: 2611

Answers (1)

hc_dev
hc_dev

Reputation: 9387

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

Upvotes: 3

Related Questions