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: ('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)
But doing so would put me at risk for SQL injection, if I understand correctly. Is there a way to accomplish this safely?
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)
doing so would put me at risk for SQL injection
No, it wouldn't, because you are in complete control over what you are injecting into the SQL command text (i.e., a comma-separated string of question marks). Your approach is fine, provided that your list doesn't contain more than approximately 2100 items.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With