在 tsql 和 pyodbc 中参数化 TOP 值

小能豆

我尝试参数化从表中获取的顶行数。

我尝试过

db.cursor.execute(
        '''
        SELECT TOP ? VALUE FROM mytable 
        WHERE param = ? 
        ''',
        top_value, param
    )

并且它表明

pyodbc.ProgrammingError:(‘42000’，“[42000] [Microsoft][SQL Server 的 ODBC 驱动程序 17][SQL Server]’@P1’ 附近的语法不正确。(102) (SQLExecDirectW)”)

使用像下面这样的字符串插值就可以工作。

    db.cursor.execute(
        f'''
        SELECT TOP {top_limit} VALUE FROM mytable 
        WHERE SITE_SK_FK = ? 
        ''',
        param
    )

我是否需要将其作为参数传递，或者字符串插值就足够了？

阅读 6

2024-11-20

共1个答案

小能豆

在你遇到的问题中，关键点在于 SQL Server 查询中的 TOP 子句。SQL Server 的 SQL 语法不支持使用占位符 (?) 来参数化 TOP 子句中的值。这是导致你遇到 pyodbc.ProgrammingError 错误的原因。

在 SQL Server 中，TOP 关键字期望的是一个数字（常量或计算结果），并且不能像 SELECT 语句中的其他部分一样，使用占位符 (?) 来动态替代它。因此，你尝试通过 ? 来传递 top_value，会导致 SQL 语法错误。

正如你所发现的，使用字符串插值（或者格式化字符串）可以使 TOP 子句的值成为查询的一部分，并避免了 SQL 语法问题。这在实际情况下是可行的，但需要特别小心 SQL 注入攻击的风险。为了避免这种风险，可以对插值的值进行适当的验证，确保它是一个合法的数字。

示例：

python db.cursor.execute( f''' SELECT TOP {top_value} VALUE FROM mytable WHERE param = ? ''', param )

在这种情况下，你通过插值将 top_value 的值嵌入到 SQL 查询字符串中，且能够避免占位符的问题。

如果你希望保持参数化查询的风格，并且避免手动拼接字符串，你可以使用类似下面的代码进行处理，但请注意，这会牺牲掉参数化查询的安全性：

python query = f''' SELECT TOP {top_value} VALUE FROM mytable WHERE param = ? ''' db.cursor.execute(query, param)

但正如前面提到的，这种方法带来的风险是：如果你直接插入外部输入（如 top_value）而没有验证其合法性，可能会导致 SQL 注入攻击。因此，在使用插值时，务必确保传入的值是合法的。

由于 TOP 关键字不支持参数化，因此 字符串插值 是解决这个问题的有效方法，但你必须确保传入的 top_value 是一个正整数，并且避免将来自不受信任的外部输入直接插入到 SQL 查询中。

如果你不希望手动拼接查询字符串，另一种选择是将 top_value 作为一个数字传递，并将查询的其余部分保持参数化。例如：

query = f"SELECT TOP {top_value} VALUE FROM mytable WHERE param = ?"
db.cursor.execute(query, param)

这可以确保你仍然可以传递其他参数，但避免了 TOP 子句的参数化限制。

2024-11-20