SQL injection in pandas; binding list to params in SQLAlchemy

Question

I have this SQL query:

sql = "select * from table where date in {dl}"

where dl is a tuple of dates. I can do the query by doing string.format(dl=...) then using read_sql_query in pandas, but I read that this could lead to SQL injection and so isn't safe.

However, there doesn't seem to be a good alernative in SQLAlchemy. You can't seem to pass a list to the params using text(), and converting the list into a string first leads to an error. I see that you can iterate over the list and pass the parameters one by one, but why would anyone want to do that?

Would cleaning up 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.

univerio · Accepted Answer · 2014-10-03 18:26:11Z

7

You can use .bindparams() to bind parameters to values in your text() construct:

sql = text("select * from table where date in :dl").bindparams(dl=...)

Note that the value you pass to dl has to be a tuple to be rendered correctly.

answered Oct 3, 2014 at 18:26

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tangfucius Over a year ago

Thanks, this works for a tuple of dates. What if the param is a string? Would bindparams escape this properly and avoid the possibility of a SQL injection?

univerio Over a year ago

@tangfucius It's never actually escaped -- it's passed directly into the DBAPI execute() as parameters, separate from the SQL, which the database handles.

tangfucius Over a year ago

Great! That makes it totally safe then.

Collectives™ on Stack Overflow

SQL injection in pandas; binding list to params in SQLAlchemy

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related