Обработка данных в предложении IN с параметрами SQL?

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

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (1,2,3)

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

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (@IDVAL_1,@IDVAL_2,@IDVAL_3)

Можно ли просто передать массив как значение параметра запроса и использовать запрос следующим образом?

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (@IDArray)

В случае, если это важно, я работаю с SQL Server 2000, в VB.Net

Ответ 1

Здесь вы идете - сначала создайте следующую функцию...

Create Function [dbo].[SeparateValues]
(
    @data VARCHAR(MAX),
    @delimiter VARCHAR(10) 
) 
    RETURNS @tbldata TABLE(col VARCHAR(10))
As
Begin
    DECLARE @pos INT
    DECLARE @prevpos INT

    SET @pos = 1 
    SET @prevpos = 0

    WHILE @pos > 0 
        BEGIN

        SET @pos = CHARINDEX(@delimiter, @data, @prevpos+1)

        if @pos > 0 
        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, @[email protected]))))

        else

        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, len(@data)[email protected]))))

        SET @prevpos = @pos 
    End

    RETURN

END

то используйте следующее...

Declare @CommaSeparated varchar(50)
Set @CommaSeparated = '112,112,122'
SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (select col FROM [SeparateValues](@CommaSeparated, ','))

Я думаю, что sql server 2008 позволит выполнять функции таблицы.

UPDATE

Вы сжимаете дополнительную производительность, используя следующий синтаксис...

SELECT ID,Column1,Column2 FROM MyTable
Cross Apply [SeparateValues](@CommaSeparated, ',') s
Where MyTable.id = s.col

Поскольку предыдущий синтаксис заставляет SQL Server запускать дополнительную команду "Сортировка", используя предложение "IN". Плюс - по-моему, это выглядит лучше: D!

Ответ 2

Если вы хотите передать массив, вам понадобится функция в sql, которая может превратить этот массив в подвыбор.

Эти функции очень распространены, и большинство домашних систем используют их.

Большинство коммерческих или, скорее, профессиональных ORM делают, выполняя кучу переменных, поэтому, если у вас это работает, я думаю, что это стандартный метод.

Ответ 3

Вы можете создать временную таблицу TempTable с одним столбцом VALUE и вставить все идентификаторы. Затем вы можете сделать это с помощью подзапроса:

SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (SELECT VALUE FROM TempTable)

Ответ 4

Пойдите с решением, отправленным digiguru. Это отличное многоразовое решение, и мы используем ту же технику. Новые члены команды любят это, так как это экономит время и сохраняет наши хранимые процедуры согласованными. Решение также хорошо работает с SQL Reports, поскольку параметры, переданные хранимым процедурам для создания наборов записей, проходят в varchar (8000). Вы просто подключите его и уходите.

Ответ 5

В SQL Server 2008 они, наконец, добрались до этой классической проблемы, добавив новый тип данных таблицы. По-видимому, это позволяет вам передать массив значений, который можно использовать в подвыборке, чтобы выполнить то же самое, что и оператор IN.

Если вы используете SQL Server 2008, вы можете изучить это.

Ответ 6

Здесь один из методов, которые я использую

ALTER   Procedure GetProductsBySearchString
@SearchString varchar(1000),
as
set nocount on
declare @sqlstring varchar(6000)
select @sqlstring = 'set nocount on
select a.productid, count(a.productid) as SumOf, sum(a.relevence) as CountOf 
 from productkeywords a 
 where rtrim(ltrim(a.term)) in (''' + Replace(@SearchString,' ', ''',''') + ''')
 group by a.productid order by SumOf desc, CountOf desc'

exec(@sqlstring)