5

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 ?

Clyde Barrow
  • 1,924
  • 8
  • 30
  • 60

2 Answers2

5

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

c8999c 3f964f64
  • 1,430
  • 1
  • 12
  • 25
  • 1
    you can read the metadata from the db using your engine `metadata = db.MetaData(bind=engine)` (db is the sqlalchemy package here). It contains all your tables, their columns, etc... – c8999c 3f964f64 Aug 07 '20 at 11:36
  • also, since you're here already, I do not recommend using raw sql syntax when working with SQLAlchemy. the Sqlalchemy core syntax is much more elegant, because its very well integrated with python. you can do excellent stuff like selected_table.insert().values(**some_dict) , where some_dict is a dictionary with key-value pairs which get inserted into their respective columns. This is much more maintainable than writing potentially extremely long prepared statements, and its all sql-injection safe. – c8999c 3f964f64 Aug 07 '20 at 11:44
  • ok thanks, and last question how to add where clause and select only specific columns ? – Clyde Barrow Aug 07 '20 at 14:13
  • 1
    you shouldnt ask follow-up questions in comments, but since its fairly basic, I recommend reading the "SQLAlchemy Core" documentation. the two methods you're looking for are .select().with_only_columns(select_columns) (using a list) and query.where() (which you can loop through with getattr(table_name.c, k) == v) – c8999c 3f964f64 Aug 10 '20 at 06:55
3

You could add a list of allowed tables:

allowed_tables = ["tmp1", "tmp2", ...]
if table in allowed_tables:
    # do sth. useful here
Jan
  • 42,290
  • 8
  • 54
  • 79