CODEWITHSUNDEEP
pythonsqlalchemy
Clyde Barrow
Clyde Barrow

Reputation: 2102

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

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 ?

Upvotes: 7

Views: 5014

Answers (2)

c8999c 3f964f64
c8999c 3f964f64

Reputation: 1627

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

Upvotes: 6

Jan
Jan

Reputation: 43169

You could add a list of allowed tables:

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

Upvotes: 4

Related Questions