Строить строку SQL-запроса, используя пользовательский ввод

Если вы собираетесь делать это с помощью динамического SQL, вам нужно построить вызов функции IN. (например, In(id1, id2, id3...)

private string GetSql( IList<int> machineIds )
{
    var sql = new StringBuilder( "SELECT FileID FROM Files Where MachineID In(" );
    for( var i = 0; i < machineIds.Count; i++ )
    {
        if ( i > 0 )
            sql.Append(", ")
        sql.Append("@MachineId{0}", i);
    }

    sql.Append(" ) ");

    //additional parameters to query
    sql.AppendLine(" And Col1 = @Col1" );
    sql.AppendLine(" And Col2 = @Col2 ");
    ...

    return sql.ToString();
}

private DataTable GetData( IList<int> machineIds, string col1, int col2... )
{
    var dt = new DataTable();
    var sql = GetSql( machineIds );
    using ( var conn = new SqlConnection() )
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        using ( var cmd = new SqlCommand( sql, conn ) )
        {
            conn.Open();

            for( var i = 0; i < machineIds.Count; i++ )
            {
                var parameterName = string.Format("@MachineId{0}", i );
                cmd.Parameters.AddWithValue( parameterName, machineIds[i] );
            }

            cmd.Parameters.AddWithValue( "@Col1", col1 ); 
            cmd.Parameters.AddWithValue( "@Col2", col2 ); 
            ...

            using ( var da = new SqlDataAdapter( cmd ) )
            {
                da.Fill( dt );
            }
        }
    }

    return dt;
}

Вы можете использовать WHERE MachineID IN ('Machine1', 'Machine2', 'Machine3', ... 'MachineN')

Затем в вашем цикле вы просто добавите машины 1..n. Предложение IN работает с 1 элементом или n элементами, так что все должно быть в порядке.

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

Создайте настоящую таблицу и загрузите в нее идентификаторы машин.

Тогда ваш SQL будет:

where MachineID in ( select MachineID from userMachine where userID = x)

Когда вы закончите, удалите все строки для идентификатора пользователя:

delete from userMachine where userID = x.

Обычно, когда я хочу создать запрос типа «поиск», я использую необязательные параметры. Это позволяет мне отправлять что-то или ничего в параметр, превращая запрос из расплывчатого в очень конкретный.

Пример:

SELECT
  COL1,
  COL2,
  COL3
FROM TABLE
WHERE (@COL1 IS NULL OR @COL1 = '' OR @COL1 = COL1)

Как вы заметили выше, если вы передадите NULL или BLANK, параметр не будет добавлен в запрос. Если вы введете значение, оно будет использовано при сравнении.

В идеале вы пытаетесь прийти к решению, аналогичному динамическому созданию «MachineID в (1, 2, 3, 4)».

Опция 1

Существует много способов выполнить эту задачу: передать строку, разделенную запятыми, в хранимую процедуру и динамически построить строку sql, а затем вызвать "EXEC sp_executesql @sql" ГДЕ В (массив идентификаторов)

Вариант 2

Вы можете передать строку значений, разделенных запятыми, а затем проанализировать значения в их собственной временной таблице, а затем присоединиться к ней http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Вариант 3 - мой выбор

Теперь вы можете передавать массив значений с помощью XML, а затем легко выбирать элементы массива. http://support.microsoft.com/kb/555266

.

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

Что-то типа:

a' or 1=1; -- Do bad things

Вы можете использовать sp_executesql в SQL для запуска оператора SQL, созданного с предложением where, как предлагает @dcp, и, хотя он не будет хорошо оптимизирован, это, вероятно, быстрая команда для запуска в любом случае.

Атаки с внедрением SQL-кода на примере

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

declare @machine table (machineId int, machineName varchar(20))
declare @files table (fileId int, machineId int)

insert into @machine (machineId, machineName) values (1, 'machine')
insert into @machine (machineId, machineName) values (2, 'machine 2.0')
insert into @machine (machineId, machineName) values (3, 'third machine')
insert into @machine (machineId, machineName) values (4, 'machine goes forth')
insert into @machine (machineId, machineName) values (5, 'machine V')

insert into @files (fileId, machineId) values (1, 3)
insert into @files (fileId, machineId) values (2, 3)
insert into @files (fileId, machineId) values (3, 2)
insert into @files (fileId, machineId) values (4, 1)
insert into @files (fileId, machineId) values (5, 3)
insert into @files (fileId, machineId) values (6, 5)

declare @machineText1 varchar(100)
declare @machineText2 varchar(100)
declare @machineText3 varchar(100)

set @machineText1 = '1 3 4'
set @machineText2 = '1'
set @machineText3 = '5 6'

select * from @files where charindex(rtrim(machineId), @machineText1, 1) > 0
-- returns files 1, 2, 4 and 5

select * from @files where charindex(rtrim(machineId), @machineText2, 1) > 0
-- returns file 4

select * from @files where charindex(rtrim(machineId), @machineText3, 1) > 0
--returns file 6

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

create procedure FilesForMachines (@machineIds varchar(1000))
as
select * from [Files] where charindex(rtrim(machineId), @machineIds, 1) > 0

Совет по charindex взят с сайта BugSplat.