如何使用带有 IN 子句的 SQL 参数来获取 pyodbc 的可变数量的值? [英] How to use SQL parameters with IN clause for a variable number of values with pyodbc?

问题描述

我有一个值列表，我想在 IN 子句中使用要使用 pyodbc 执行的 SQL (SQL Server) 语句.示例:

I have a list of values that I'd like to use in an IN clause for an SQL (SQL Server) statement to be executed with pyodbc. Example:

files = ['file1', 'file2', ...]  # this list can have a variable number of elements
con = pyodbc.connect(...)

# What I'd like to do
result = con.cursor().execute('SELECT * FROM sometable WHERE file_name IN (?)', files)

然而，当我执行上面的语句时，我得到了这样的错误:

However when I execute the statement above I get an error such as this:

ProgrammingError: ('SQL 包含 1 个参数标记，但提供了 18 个参数', 'HY000')

ProgrammingError: ('The SQL contains 1 parameter markers, but 18 parameters were supplied', 'HY000')

我可以使用以下内容生成可变参数字符串:

I can generate a variable parameter string using something like:

params = ','.join(['?']*len(files))
query = 'SELECT * FROM sometable WHERE file_name IN ({})'.format(params)
result = con.cursor().execute(query, files)

但如果我理解正确的话，这样做会使我面临 SQL 注入的风险.有没有办法安全地做到这一点?

But doing so would put me at risk for SQL injection, if I understand correctly. Is there a way to accomplish this safely?

推荐答案

您可以使用 JSON 将列表传递给 SQL Server.EG

You can use JSON to pass the list to SQL Server. EG

import numpy as np
import pandas as pd
import pyodbc
import json 

files = ['file1', 'file2', 'file3']  # this list can have a variable number of elements
json_files = json.dumps(files)
print(json_files)
conn = pyodbc.connect('Driver={Sql Server};'
                      'Server=localhost;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

cursor.execute("create table #sometable(id int, file_name varchar(255)); insert into #sometable(id,file_name) values (1,'file2')")
# What I'd like to do
result = cursor.execute('SELECT * FROM #sometable WHERE file_name IN (select value from openjson(?))', json_files)
rows = cursor.fetchall()
print(rows)

这篇关于如何使用带有 IN 子句的 SQL 参数来获取 pyodbc 的可变数量的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！