SQLAlchemy + MySQL - pass table name as a parameter in a raw query

Question

In my app I use SQLAlchemy and mysql-connector-python. I would like to perform such query SELECT * FROM :table LIMIT 10 on my mysql database. However my code doesn't work

table_name = "tmp1"
QUERY = "SELECT * FROM :table LIMIT 10"
conn = create_sql_connection()
res = conn.execute(
    QUERY,
    {'table': table_name}
).fetchall()
print(res)

I've read that you cannot use table name as a parameter and I should just use python string format. However I'm really scared that it's absolutely not safe against sql injection. How to solve it ? Is there any utility that would escape my table name variable ?

Postgres has a solution - Passing table name as a parameter in psycopg2 - Do you know how to solve it while using mysql ?

Answer 1

you can pass the user provided string to a Table statement and build queries from this: (here I assume you get the user data from a post request json)

table_name_string = request.get_json().get('table')
selected_table = db.Table(table_name_string, metadata, autoload=True)
query = selected_table.select()

you can also go on an use this query as a raw sql string at this point if you really want to

query_string = str(query)

some input validation or restrictions what tables are valid is definitely recommended, but it is sql injection safe, since the table must exist in the metadata, or it will throw an Exception (sqlalchemy.exc.NoSuchTableError:)

Answer 2

You could add a list of allowed tables:

allowed_tables = ["tmp1", "tmp2", ...]
if table in allowed_tables:
    # do sth. useful here