SQL injection in pandas; parameter binding list in SQLAlchemy
I have this SQL query:
sql = "select * from table where date in {dl}"
where dl is a tuple of dates. I can execute the query by executing string.format(dl=...)
, then using read_sql_query
in pandas, but I read that this can lead to SQL injection and is therefore unsafe.
However, there seems to be no good alternative in SQLAlchemy. You cannot pass a list to parameters with text()
, and converting the list to a string will initially fail . I see that you can iterate over the list and pass parameters one by one, but why would anyone do that?
Would clearing the variable (removing quotes, semicolons, etc.) help reduce the risk of SQL injection? Not being able to use a raw SQL string sounds like a terrible prospect.
source to share
You can use .bindparams()
to bind parameters to values ββin a construct text()
:
sql = text("select * from table where date in :dl").bindparams(dl=...)
Please note that the value you pass to dl
must be a properly parsed tuple.
source to share