Как создать параметризованный запрос для панд с динамическим именем таблицы, позволяющим избежать внедрения SQL? ⇐ Python

[Anonymous]

Я использую SQL Server, pandas и pyodbc.
Я пытаюсь создать функцию, которая может принимать имя столбца, имя таблицы, другое имя столбца (для столбца типа GUID) и значение GUID, и запускать оператор SQL, например:

Код: Выделить всё

SELECT columnName1
FROM tableName
WHERE columnName2 = guidValue

Эта функция работала нормально, когда я не беспокоился о внедрении SQL и просто вручную вставлял квадратные скобки там, где это необходимо:

Код: Выделить всё

def get_sql_entity_set_data(entity_set_name, filter_expression=None, query_params='*'):
connString = get_connection_string()
conn = pyodbc.connect(connString)
query = "SELECT " + query_params + " FROM [" + entity_set_name + "]"

if filter_expression:
query += " WHERE " + filter_expression

logger.debug(f'Attempting the following SQL command: {query}.')
retValue = pd.read_sql(query, conn)

returnCount = len(retValue.index)

if returnCount == 0:
logger.warning('Fetch failed: no data retrieved.')
else:
logger.info(f'{returnCount} row(s) retrieved.')

return retValue

Когда я делал это таким образом, я передавал полное предложение WHERE в качестве параметра, например, «[Name] = 'Bob Smith'».
Но теперь я пытаюсь защититься от SQL-инъекций и напрягаюсь. Ничего из того, что я пробовал, не работает. Я узнал, что вы не можете параметризовать имена таблиц. Я также узнал, что нельзя передавать звездочку '*' в качестве параметра - например, SELECT? FROM..., поэтому моя функция стала намного уродливее с логикой if для обработки того, было ли имя столбца SELECT указано в параметрах или оно просто было по умолчанию равно '*'.
У меня сложилось впечатление, что я смогу обойти проблему невозможности параметризации имени таблицы, создав подготовленный оператор с помощью EXEC, поэтому я попробовал различные подготовленные операторы, построенные на основе этой базовой идеи (просто пытаюсь прийти к придумал упрощенный пример)...

Код: Выделить всё

DECLARE @table nvarchar(128);
DECLARE @query nvarchar(max);
SET @table = ?;
SET @query = 'SELECT * FROM @table';
EXEC @query;

...но это не сработало.
На этом этапе я отказался от этого подхода и просто поместил имя таблицы прямо в строку подключения, как и раньше. Единственное, что я делаю, это использую другую функцию, чтобы заключить ее в квадратные скобки. Это состояние моего кода сейчас, и он все еще не работает (нет ошибок, он просто возвращает 0 результатов, хотя я подтвердил в SSMS, что параметры, которые я ему передаю, должны получить 1 результат):

Код: Выделить всё

def get_data_with_guid_filter(entity_set_name, filter_col, filter_val):
connString = get_connection_string()
conn = pyodbc.connect(connString)
logger.debug(f'Attempting to fetch all data from {entity_set_name} where {filter_col} = {filter_val}.')
entity_set_name = parameterize(entity_set_name, "sysid")
filter_col = parameterize(filter_col, "sysid")
filter_val = parameterize(filter_val, "guid")
params_tuple = (filter_col,) + (filter_val,)
query = f"SELECT * FROM {entity_set_name} WHERE ? = ?"

retValue = pd.read_sql(query, conn, params=params_tuple)

returnCount = len(retValue.index)

if returnCount == 0:
logger.warning('Fetch failed: no data retrieved.')
else:
logger.info(f'{returnCount} row(s) retrieved.')

return retValue

def parameterize(param, param_type):
if param_type == "guid":
return f"(SELECT CONVERT(uniqueidentifier, '{param}'))"
elif param_type == "string":
return f"'{param}'"
elif param_type == "sysid":
return f"[{param}]"
else:
return param

Предположим, это параметры «mytable», «ID» и «123456» [не буквально — просто представьте, что это настоящий GUID]. Я могу войти в SSMS прямо сейчас, ввести SELECT * FROM [mytable] WHERE [ID] = (SELECT CONVERT(uniqueidentifier, '123456')) и получить 1 результат. Но в Python я получаю нулевые результаты и никаких ошибок.
Что еще более странно, я по-прежнему получаю 0 результатов и никаких ошибок, даже если делаю одно из следующих действий:

• измените filter_val =parameterize(filter_val, "guid") на filter_val =parameterize(filter_val, "string") (это странно для меня, потому что, когда я пробовал подход EXEC, я получил ошибку, которая, как я понял, означает, что я не могу отфильтровать столбец GUID по значению nvarchar в подготовленном операторе!)
• удалить внешние скобки из значения, возвращаемого фильтром_val = параметризировать(filter_val, "guid"), хотя SSMS, похоже, требует этих скобок