pyodbc 执行 SQL 代码 [英] pyodbc execute SQL code

问题描述

我正在尝试使用 pyodbc 游标执行正确的方法来防止注入攻击，如下所示:有什么作用?在 python pyodbc 模块中的意思

I am trying to use pyodbc cursor execute the right way to prevent injection attacks, as suggested here: what does ? mean in python pyodbc module

我的代码如下:

query = """\
SELECT 
    ?,count(*)
FROM 
    ?
WHERE 
    ?=?
""", ('date', 'myTable', 'date', '2017-05-08')
cursor.execute(query)

然后我收到一个错误:

TypeError: The first argument to execute must be a string or unicode query.

对于正确的答案，我想:

For the right answer I'd want to:

1. 保留问号格式以避免 SQL 注入攻击
2. 保持三引号格式，这样我就可以编写长 SQL 查询，而不会降低代码的可读性.

有没有办法做到这一点?我知道我可以使用 """ %s """ %('table') 格式类型，但这违背了这个问题的目的.

Is there a way to achieve this? I know I could use """ %s """ %('table') format type but that defeats the purpose of this question.

推荐答案

您有 2 个问题:

1. query 是一个元组.执行参数化查询的方法是如下:

1. query is a tuple. The way to execute a parameterized query is as follows:

query = """SELECT ?,count(*)
           FROM ?
           WHERE ?=? """
args = ('date', 'myTable', 'date', '2017-05-08')
cursor.execute(query, args)

您可以使用 * 传递 query.这会将 query 扩展为一个字符串和一个元组，这是 execute 所期望的:

You could pass query with *. This would expand query to a string and a tuple which is what execute expects:

cursor.execute(*query)  # 'query' here is defined as it is in your example

但是，这行不通.您不能使用参数化查询在 select 和 from 子句中使用参数.也不能在 where 子句中对列名使用参数.

But, that won't work. You can not use parameterized query to use parameters in the select and from clauses. You can also not use parameters for the column name in the where clause.

如果值不是由用户输入(或者用户无法更改它)，您(通常)不必担心 SQL 注入.

You (usually) don't have to worry about SQL injection if the value isn't inputted by the user (or if the user can't change it in anyway).

这篇关于pyodbc 执行 SQL 代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！