Параметрирование предложения SQL IN

Как я могу параметризовать запрос, содержащий предложение IN , с переменным числом аргументов, как этот?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

В этом запросе количество аргументов может быть от 1 до 5.

Я бы предпочел не использовать выделенную хранимую процедуру для этого (или XML), но если есть какой-то элегантный способ, характерный для SQL Server 2008 , я открыт для этого.

Всего 10 ответов


Вот быстрая и грязная техника, которую я использовал:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

Итак, вот код C #:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Два оговорки:

  • Спектакль ужасен. LIKE "%...%" запросы не индексируются.
  • Убедитесь, что у вас нет | , пустой или пустой тег, иначе это не сработает

Есть и другие способы сделать это, чтобы некоторые люди могли считать более чистыми, поэтому, пожалуйста, продолжайте читать.


Вы можете параметризовать каждое значение, например:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

Что даст вам:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

Нет, это не распространяется на SQL-инъекцию . Единственный введенный текст в CommandText не основан на пользовательском вводе. Он основан исключительно на жестко закодированном префиксе @tag и индексе массива. Индекс всегда будет целым числом, не генерируется пользователем и безопасен.

Введенные пользователем значения по-прежнему вставляются в параметры, поэтому там нет уязвимости.

Редактировать:

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

Не то, что кэшированные планы запросов не являются ценными, но IMO этот запрос не достаточно сложный, чтобы увидеть большую выгоду от него. Хотя затраты на компиляцию могут приближаться (или даже превышать) затраты на выполнение, вы все еще говорите миллисекунды.

Если у вас достаточно ОЗУ, я бы ожидал, что SQL Server, вероятно, будет кэшировать план для общего количества параметров. Я полагаю, вы всегда можете добавить пять параметров, и пусть неопределенные теги будут NULL - план запросов должен быть одним и тем же, но для меня это кажется довольно уродливым, и я не уверен, что это стоило бы микро-оптимизации (хотя, на Stack Overflow - это может быть очень полезно).

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


Для SQL Server 2008 вы можете использовать параметр таблицы . Это небольшая работа, но она, возможно, более чистая, чем мой другой метод .

Во-первых, вам нужно создать тип

 CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) ) 

Затем ваш код ADO.NET выглядит следующим образом:

 string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" }; cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name"; // value must be IEnumerable<SqlDataRecord> cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured; cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType"; // Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord> public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) { if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows var firstRecord = values.First(); var metadata = SqlMetaData.InferFromValue(firstRecord, columnName); return values.Select(v => { var r = new SqlDataRecord(metadata); r.SetValues(v); return r; }); } 

Первоначальный вопрос: «Как параметризовать запрос ...»

Позвольте мне прямо заявить, что это не ответ на исходный вопрос. Уже есть некоторые примеры этого в других хороших ответах.

С учетом сказанного, давайте пометить этот ответ, понизите его, отметьте как не ответ ... делайте то, что считаете правильным.

См. Ответ от Марка Брэкетта за предпочтительный ответ, который я (и 231 других) поддержал. Подход, данный в его ответе, позволяет 1) эффективно использовать переменные связывания, а 2) для предикатов, которые могут быть сопоставимыми.

Выбранный ответ

Здесь я хотел бы остановиться на подходе, приведенном в ответе Джоэла Спольского, ответом «выбранным» в качестве правильного ответа.

Подход Джоэла Спольского умный. И он работает разумно, он будет демонстрировать предсказуемое поведение и прогнозируемую производительность, учитывая «нормальные» значения и нормативные случаи краев, такие как NULL и пустую строку. И этого может быть достаточно для конкретного приложения.

Но в терминах, обобщающих этот подход, давайте также рассмотрим более неясные угловые случаи, например, когда столбец « Name содержит подстановочный знак (как признано предикатом LIKE.) Символом подстановки, который я вижу чаще всего, является % (знак процента). , Итак, давайте рассмотрим это сейчас, а затем перейдем к другим случаям.

Некоторые проблемы с символом%

Рассмотрим значение имени 'pe%ter' . (Для примеров здесь я использую буквальное строковое значение вместо имени столбца.) Строка с именем Name '' pe% ter 'будет возвращена запросом формы:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

Но эта же строка не будет возвращена, если порядок поисковых терминов будет отменен:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

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

Это почти само собой разумеется, что мы, возможно, не захотим, чтобы pe%ter соответствовало арахисовому маслам, независимо от того, насколько ему это нравится.

Неисправный угловой корпус

(Да, я соглашусь с тем, что это неясный случай. Вероятно, тот, который вряд ли будет проверен. Мы не ожидаем подстановки в значении столбца. Мы можем предположить, что приложение предотвращает сохранение такого значения. по моему опыту, я редко видел ограничение базы данных, которое специально запрещало символы или шаблоны, которые считались бы подстановочными знаками в правой части оператора сравнения LIKE .

Зачистка отверстия

Один из подходов к исправлению этого отверстия заключается в том, чтобы избежать символа подстановки % . (Для тех, кто не знаком с предложением escape на операторе, вот ссылка на документацию SQL Server .

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape ''

Теперь мы можем сопоставить литерал%. Конечно, когда у нас есть имя столбца, нам нужно будет динамически избежать шаблона. Мы можем использовать функцию REPLACE для поиска вхождений символа % и вставки символа обратной косой черты перед каждым из них, например:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape ''

Таким образом, эта проблема решается с помощью шаблона%. Почти.

Побег побега

Мы признаем, что наше решение ввело еще одну проблему. Эквивалентный символ. Мы видим, что нам также нужно избегать любых проявлений самого escape-символа. На этот раз мы используем! как символ побега:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

Подчеркивание тоже

Теперь, когда мы в рулоне, мы можем добавить еще один дескриптор REPLACE подстановочный знак подчеркивания. И просто для удовольствия, на этот раз мы будем использовать $ в качестве escape-символа.

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

Я предпочитаю этот подход к экранированию, поскольку он работает в Oracle и MySQL, а также в SQL Server. (Я обычно использую обратную косую черту как символ escape, так как это символ, который мы используем в регулярных выражениях. Но зачем ограничиваться конвенцией!

Эти призрачные скобки

SQL Server также позволяет подстановочные символы обрабатываться как литералы, заключая их в скобки [] . Таким образом, мы еще не закончили исправление, по крайней мере, для SQL Server. Поскольку пары скобок имеют особый смысл, нам также нужно избегать их. Если нам удастся сбежать из скобок, то, по крайней мере, нам не придется беспокоиться о дефисах и карате в скобках. И мы можем оставить любые символы % и _ внутри скобок, экранированных, так как мы в основном отключили особое значение скобок.

Поиск совпадающих пар скобок не должен быть таким сложным. Это немного сложнее, чем обработка событий singleton% и _. (Обратите внимание, что недостаточно просто избежать всех вхождений скобок, потому что одноэлементная скобка считается литералом и не нуждается в экранировании. Логика становится немного туслее, чем я могу справиться, не выполняя больше тестовых примеров .)

Встроенное выражение становится беспорядочным

Это встроенное выражение в SQL становится все длиннее и уродливее. Мы можем, возможно, заставить его работать, но небеса помогают бедному духу, который приходит и должен его расшифровать. Поскольку я поклонник встроенных выражений, я склонен не использовать его здесь, главным образом потому, что я не хочу оставлять комментарий, объясняющий причину беспорядка, и извиниться за него.

Функция где?

Итак, если мы не будем обрабатывать это как встроенное выражение в SQL, ближайшая альтернатива у нас есть пользовательская функция. И мы знаем, что это не ускорит работу (если мы не сможем определить индекс на нем, как мы могли бы с Oracle.) Если нам нужно создать функцию, мы можем сделать это в коде, который вызывает SQL заявление.

И эта функция может иметь некоторые различия в поведении, в зависимости от СУБД и версии. (Крик всем разработчикам Java, которые так сильно заинтересованы в возможности использовать любой движок базы данных.)

Базовые знания

Мы можем иметь специализированные знания о домене для столбца (т. Е. Набор допустимых значений, применяемых для столбца. Мы можем знать априорно, что значения, хранящиеся в столбце, никогда не будут содержать знак процента, подчеркивание или скобку В этом случае мы просто включаем быстрый комментарий о том, что эти случаи охвачены.

Значения, хранящиеся в столбце, могут содержать символы% или _, но ограничение может потребовать, чтобы эти значения были экранированы, возможно, с использованием определенного символа, так что значения LIKE сравнения «безопасны». Опять же, быстрый комментарий о разрешенном наборе значений, и в частности, какой символ используется в качестве escape-символа, и идти с подходом Джоэла Спольского.

Но, не имея специализированных знаний и гарантий, важно, чтобы мы, по крайней мере, рассмотрели возможность использования этих неясных краевых дел и рассмотрели, разумно ли поведение и «в спецификации».


Другие выпущенные проблемы

Я считаю, что другие уже достаточно подробно указали на некоторые из наиболее часто встречающихся проблемных областей:

  • SQL-инъекции (принимая то, что, по-видимому, представляло собой предоставленную пользователем информацию, и включало это в текст SQL, а не предоставляло их через переменные связывания. Использование переменных привязки не требуется, это всего лишь один удобный подход для предотвращения SQL-инъекции. способы борьбы с ним:

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

  • использование литеральных значений вместо переменных связывания влияет на масштабируемость


Заключение

Мне нравится подход Джоэла Спольского. Это умно. И это работает.

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

Да, я ушел далеко от исходного вопроса. Но где еще оставить эту заметку относительно того, что я считаю важным вопросом с «отобранным» ответом на вопрос?


Вы можете передать параметр в виде строки

Итак, у вас есть строка

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

Тогда все, что вам нужно сделать, это передать строку как 1 параметр.

Вот функция разделения, которую я использую.

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

Я слышал об этом Джеффе / Джоэле на сегодняшнем подкасте ( эпизод 34 , 2008-12-16 (MP3, 31 МБ), 1 ч 03 мин 38 сек - 1 ч 06 мин 45 сек), и я думал, что вспомнил «Переполнение стека» использовал LINQ to SQL , но, возможно, он был отброшен. Вот то же самое в LINQ to SQL.

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

Вот и все. И, да, LINQ уже выглядит достаточно назад, но предложение Contains кажется мне лишним назад. Когда мне приходилось делать аналогичный запрос для проекта на работе, я, естественно, пытался сделать это неправильно, выполнив соединение между локальным массивом и таблицей SQL Server, полагая, что переводчик LINQ to SQL будет достаточно умным, чтобы обрабатывать перевод как-то. Это не так, но оно предоставило сообщение об ошибке, которое было описательным, и указало мне на использование Содержит .

В любом случае, если вы запустите это в рекомендованной LINQPad и запустите этот запрос, вы можете просмотреть фактический SQL, сгенерированный поставщиком SQL LINQ. Он покажет вам каждое значение, параметризуемое в предложение IN .


Если вы звоните из .NET, вы можете использовать Dapper dot net :

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

Здесь Даппер думает, так что вам не нужно. Что-то подобное возможно с LINQ to SQL , конечно:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

Это, возможно, наполовину неприятный способ сделать это, я использовал его один раз, был довольно эффективным.

В зависимости от ваших целей это может пригодиться.

  1. Создайте временную таблицу с одним столбцом.
  2. INSERT каждое значение поиска в этот столбец.
  3. Вместо использования IN вы можете просто использовать свои стандартные правила JOIN . (Гибкость ++)

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

Я никогда не добирался до профилирования точно, как быстро это было, но в моей ситуации это было необходимо.


У нас есть функция, которая создает переменную таблицы, к которой вы можете присоединиться:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'')

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END 

Так:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

Это грубо, но если вам гарантировано хотя бы одно, вы можете сделать:

SELECT ...
       ...
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

Наличие IN ('tag1', 'tag2', 'tag1', 'tag1', 'tag1') будет легко оптимизировано SQL Server. Кроме того, вы получаете прямые указатели


Есть идеи?

10000