大 pandas 中的SQL注入;将列表绑定到SQLAlchemy中的参数 [英] SQL injection in pandas; binding list to params in SQLAlchemy

问题描述

我有这个SQL查询:

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

其中dl是日期的元组.我可以通过在熊猫中执行string.format(dl=...)然后使用read_sql_query来进行查询，但是我读到这可能导致SQL注入，因此并不安全.

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.

但是，SQLAlchemy中似乎没有一个好的替代方法.您似乎无法使用text()将列表传递给参数，并且首先将列表转换为字符串会导致错误.我看到您可以遍历该列表并逐个传递参数，但是为什么有人要这么做呢?

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?

清理变量(删除引号，分号等)是否有助于降低SQL注入的风险?无法使用原始SQL字符串听起来很糟糕.

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.

推荐答案

您可以使用

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

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

请注意，您传递给dl的值必须是一个元组才能正确呈现.

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

这篇关于大 pandas 中的SQL注入;将列表绑定到SQLAlchemy中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！