Блог

Возможно ли создать параметризованную инструкцию SQL, которая будет принимать произвольное количество параметров? Я пытаюсь разрешить пользователям фильтровать список на основе нескольких ключевых слов, каждое из которых разделено точкой с запятой. Таким образом, ввод будет чем-то вроде «Окленд;Город;Планирование», и предложение WHERE будет эквивалентно приведенному ниже:

ГДЕ Имя проекта, ТАКОЕ КАК " %Окленд%", И имя проекта, такое как " %Город%", И имя проекта, такое как " %Планирование%"

Создать такой список с помощью объединения действительно легко, но я не хочу использовать такой подход из-за уязвимостей SQL-инъекции. Каковы мои варианты? Должен ли я создать кучу параметров и надеяться, что пользователи никогда не попытаются использовать больше параметров, которые я определил? Или есть способ безопасно создавать параметризованный SQL на лету?

Производительность не является большой проблемой, потому что сейчас в таблице всего около 900 строк, и она будет расти не очень быстро, возможно, от 50 до 100 строк в год.

Базовое доказательство концепции… Фактический код был бы меньше, но так как я не знаю ваших имен таблиц/полей, это полный код, поэтому любой может проверить, работает ли он, настроить его и т. Д.

 --Search Parameters

DECLARE @SearchString VARCHAR(MAX)
SET @SearchString='Oakland;City;Planning' --Using your example search
DECLARE @Delim CHAR(1)
SET @Delim=';' --Using your deliminator from the example

--I didn't know your table name, so I'm making it... along with a few extra rows...

DECLARE @Projects TABLE (ProjectID INT, ProjectName VARCHAR(200))
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 1, 'Oakland City Planning'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 2, 'Oakland City Construction'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 3, 'Skunk Works'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 4, 'Oakland Town Hall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 5, 'Oakland Mall'
INSERT INTO @Projects (ProjectID, ProjectName) SELECT 6, 'StackOverflow Answer Planning'

--*** MAIN PROGRAM CODE STARTS HERE ***

DECLARE @Keywords TABLE (Keyword VARCHAR(MAX))

DECLARE @index int 
SET @index = -1 

--Each keyword gets inserted into the table
--Single keywords are handled, but I did not add code to remove duplicates
--since that affects performance only, not the result.

WHILE (LEN(@SearchString) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@Delim , @SearchString)  
    IF (@index = 0) AND (LEN(@SearchString) > 0)  
      BEGIN   
        INSERT INTO @Keywords VALUES (@SearchString)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Keywords VALUES (LEFT(@SearchString, @index - 1))   
        SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index))  
      END  
    ELSE 
      SET @SearchString = RIGHT(@SearchString, (LEN(@SearchString) - @index)) 
END


--This way, only a project with all of our keywords will be shown...

SELECT * 
FROM @Projects
WHERE ProjectID NOT IN (SELECT ProjectID FROM @Projects Projects INNER JOIN @Keywords Keywords ON CHARINDEX(Keywords.Keyword,Projects.ProjectName)=0)
 

Я решил объединить несколько разных ответов в один 😛

Это предполагает, что вы передадите список ключевых слов поиска с разделителями (переданный через @searchString) в виде VARCHAR(MAX), который-реально-вы не столкнетесь с ограничением для поиска по ключевым словам.

Каждое ключевое слово разбивается из списка и добавляется в таблицу ключевых слов. вы, вероятно, захотите добавить код для удаления повторяющихся ключевых слов, но в моем примере это не повредит. Просто немного менее эффективно, так как в идеале нам нужно оценивать только один раз по ключевому слову.

Оттуда любое ключевое слово, которое не является частью имени проекта, удаляет этот проект из списка…

Таким образом, поиск «Окленд» дает 4 результата, но «Окленд;Город;Планирование» дает только 1 результат.

Вы также можете изменить разделитель, поэтому вместо точки с запятой он может использовать пробел. Или что там плавает на твоей лодке…

Кроме того, из-за соединений и того, что не вместо динамического SQL, он не рискует внедрением SQL, как вы беспокоились.

Возможно, вы также захотите рассмотреть возможность полнотекстового поиска и использования CONTAINS или CONTAINSTABLE для более «естественного» поиска.

Может быть излишним для 1K строк, но он написан и его нелегко разрушить путем инъекции.

Обычно хитрость заключается в том, чтобы просто передать список в виде строки, разделенной запятыми (в стиле csv), проанализировать эту строку в цикле и динамически построить запрос.

Вышеприведенный пост также прав в том, что, возможно, лучшим подходом является не T-SQL, а уровень бизнеса/приложений.

Как насчет использования типа данных XML для хранения параметров? Он может быть неограничен и собран во время выполнения…

Я передаю неизвестное количество PK для обновления таблицы, а затем загружаю их во временную таблицу. Затем легко обновить, где PK в PKTempTable.

Вот код для анализа типа данных XML…

     INSERT INTO #ERXMLRead (ExpenseReportID)
    SELECT ParamValues.ID.value('.','VARCHAR(20)')
    FROM @ExpenseReportIDs.nodes('/Root/ExpenseReportID') as ParamValues(ID)
 

использование такого инструмента, как NHibernate, позволит вам безопасно динамически создавать запросы без необходимости использования хранимых процедур.

У Франса Боума есть отличная статья о хранимых процедурах по сравнению с динамическим sql и о некоторых преимуществах использования генератора SQL по сравнению с использованием инструкций, созданных вручную

Если вы используете хранимые процедуры, вы можете включить значение по умолчанию для параметров, затем вы можете передать их или не передавать в клиентском коде, но вам все равно придется объявлять их по отдельности в хранимой процедуре… Кроме того, только если вы используете сохраненный процесс, вы можете передать один параметр в виде строки значений с разделителями и проанализировать отдельные значения внутри sproc (доступны некоторые «стандартные» функции T-SQL, которые разделят записи на динамическую табличную переменную для вас).

Если вы используете SQL server 2008, ознакомьтесь с этой статьей, передающей параметр с табличным значением

Каким бы способом вы ни пошли, следите за ограничением параметров SQL Server: ~2000 параметров.

Как и в некоторых других ответах, вы можете проанализировать строку с разделителями или XML — документ. Смотрите эту превосходную ссылку, которая демонстрирует оба метода с SQL Server.