Can I insert a table name by using a query parameter?

user_3764619 0
Viewed 60

I have an SQL Alchemy engine where I try to insert parameters via sqlalchemy.sql.text to protect against SQL injection.

The following code works, where I code variables for the condition and conditions values.

from sqlalchemy import create_engine
from sqlalchemy.sql import text

db_engine = create_engine(...)

db_engine.execute(
         text(
             'SELECT * FROM table_name WHERE :condition_1 = :condition_1_value'), condition_1="name", condition_1_value="John"
         )
).fetchall()

However, when I try to code the variable name for table_name, it returns an error.

from sqlalchemy import create_engine
from sqlalchemy.sql import text

db_engine = create_engine(...)

db_engine.execute(
         text(
             'SELECT * FROM :table_name WHERE :condition_1 = :condition_1_value'), table_name="table_1", condition_1="name", condition_1_value="John"
         )
).fetchall()

Any ideas why this does not work?

EDIT: I know that it has something to do with the table_name not being a string, but I am not sure how to do it in another way.

sqlalchemy python
1 Answers

Any ideas why this does not work?

Query parameters are used to supply the values of things (usually column values), not the names of things (tables, columns, etc.). Every database I've seen works that way.

So, despite the ubiquitous advice that dynamic SQL is a "Bad Thing", there are certain cases where it is simply necessary. This is one of them.

table_name = "table_1"  # NOTE: Do not use untrusted input here!
stmt = text(f'SELECT * FROM "{table_name}" …')

Also, check the results you get from trying to parameterize a column name. You may not be getting what you expect.

stmt = text("SELECT * FROM table_name WHERE :condition_1 = :condition_1_value")
db_engine.execute(stmt, dict(condition_1="name", condition_1_value="John"))

will not produce the equivalent of

SELECT * FROM table_name WHERE name = 'John'

It will render the equivalent of

SELECT * FROM table_name WHERE 'name' = 'John'

and will not throw an error, but it will also return no rows because 'name' = 'John' will never be true.

user_21443900
Related