SqlParameter and IN statement

Question

I need following query:

createList(string commaSeparatedElements) {
    ...
    SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+commaSeparatedElements+")");
    ...
}

I would like to write it using a parameterized query, so every element from the string is checked to prevent against Sql-Injections.

Pseudo-code:

createList(string commaSeparatedElements) {
    ...
    SqlParameterList elements = new SqlParameterList("@elements", SqlDbType.Int);
    SqlParameterList.Values = commaSeparatedElements.split(new Char[1] {','});
    SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN (@elements)");
    query.Parameters.Add(elements);
    ...
}

Does anything like that exist in C#, or will I have to write it by myself?

EDIT: Thanks for all the answers. As I try not to use code I don't understand (too many bad experiences in the last days), dapper and table-valued parameters, even though they may be perfect for my needs, are off-limits. I just made a loop.

string[] elements = commaSeparatedElements.split(new Char[1] {','});
StringList idParamList = new StringList();
for(int i=0;i<elements.Count;i++) {
    query.Parameters.AddWithValue("@element"+i,Convert.ToInt32(elements[i]));
    idParamList.Add("@element" + i);
}
SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+String.Join(",",idParamList)+")");

[check this](http://stackoverflow.com/questions/4502821/how-do-i-translate-a-liststring-into-a-sqlparameter-for-a-sql-in-statement) — Microsoft DN, Nov 22 '13 at 10:59

score 8 · Answer 1 · answered Nov 22 '13 at 11:34

"dapper" has some voodoo for this:

var ids = new List<int> {1,2,3,4,5};
var rows = conn.Query<SomeType>("select * from table where id in @ids",
      new { ids }).ToList();

You'll notice the lack of parenthesis in the in usage; "dapper" spots this, and performs parameter expansion automatically. Plus it handles all the command details for you, including mapping the data back into SomeType instances.

In the above example, it will expand to be equivalent to (approximately):

int ids0 = 1, ids1 = 2, ids2 = 3, ids3 = 4, ids4 = 5;
var rows = conn.Query<SomeType>(
    "select * from table where id in (@ids0,@ids1,@ids2,@ids3,@ids4)",
      new { ids0, ids1, ids2, ids3, ids4 }).ToList();

score 3 · Answer 2 · answered Nov 22 '13 at 10:57

SQL Server has one data structure that's ideal for holding multiple values of the same "type" - and that's a table.

Thankfully, in recent years, they introduced Table-Valued Parameters so that you can construct a table in your client code and pass that across to the SQL Server in a natural way, e.g.:

// Assumes connection is an open SqlConnection.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(
    DataRowState.Added);

// Define the INSERT-SELECT statement.
string sqlInsert = 
    "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
    + " SELECT nc.CategoryID, nc.CategoryName"
    + " FROM @tvpNewCategories AS nc;"

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
    sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

// Execute the command.
insertCommand.ExecuteNonQuery();
}

This is the best way, but caveat: TVPs can suck with stored procedures due to parameter sniffing. — ta.speot.is, Nov 22 '13 at 11:12

score 2 · Answer 3 · answered Nov 22 '13 at 10:55

2

If you declare

List<int> Ids = new List<int>();

and add there the ids you want. It's easy to convert it in a comma seperated list as follows

string listOfIds = string.Join(",", Ids.Select(Id => Id.ToString()).ToArray());

Then you pass this string as a parameter to your parameterized query.

answered Nov 22 '13 at 10:55

Christos

53,228
8
76
108

2

Won't work because I don't ask `SELECT * FROM table WHERE id IN ("1,2,3")`, I ask `SELECT * FROM table WHERE id IN (1,2,3)` – Alexander Nov 22 '13 at 12:46

score 1 · Answer 4 · edited May 23 '17 at 11:46

1

which version of sql server you are using? You should try out table value parameter if you are using sql server 2008 or later

Check http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters

Pass table valued parameter using ADO.Net

edited May 23 '17 at 11:46

Community

1
1

answered Nov 22 '13 at 11:02

Kamran Shahid

3,954
5
48
93

score 1 · Accepted Answer · answered Nov 22 '13 at 11:10

I use a helper method like this

/// <summary>
/// Adds a sequence of parameters to an existing parameter collection
/// </summary>
/// <typeparam name="T">Type of parameter values</typeparam>
/// <param name="parameters">Existing parameter collection</param>
/// <param name="pattern">Name pattern of parameters. Must be a valid <see langword="int"/> format string</param>
/// <param name="parameterType">Database type of parameters</param>
/// <param name="length">Length of parameter. 0 for numeric parameters</param>
/// <param name="values">Sequence of values</param>
/// <returns>Comma separated string of parameter names</returns>
public static string AddParameters<T>(SqlParameterCollection parameters,
                                      string pattern,
                                      SqlDbType parameterType,
                                      int length,
                                      IEnumerable<T> values) {
    if (parameters == null)
        throw new ArgumentNullException("parameters");
    if (pattern == null)
        throw new ArgumentNullException("pattern");
    if (values == null)
        throw new ArgumentNullException("values");
    if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
        throw new ArgumentException("Pattern must start with '@'");

    var parameterNames = new List<string>();
    foreach (var item in values) {
        var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
        parameterNames.Add(parameterName);
        parameters.Add(parameterName, parameterType, length).Value = item;
    }

    return string.Join(",", parameterNames.ToArray());
}

It is used like this

string sql = "SELECT col1 " +
             "FROM Table " +
             "WHERE col2 IN ({@Values}) ";
var paramNames = SqlHelper.AddParameters(command.Parameters,
                                         "@Value0",
                                         SqlDbType.Int,
                                         0,
                                         listOfInts);
command.CommandText = sql.Replace("{@Values}", paramNames);
...

(Result is a query like SELECT ... IN (@Value0, @Value1, @Value2))

score 0 · Answer 6 · answered Jul 20 '15 at 11:56

This is what I've make in one project:

        StringBuilder sb = new StringBuilder("Id IN(");
        List<SqlParameter> parameters = new List<SqlParameter>();
        int i = 1;

        foreach (int item in items)
        {
            string currentItem = "@Item" + i++.ToString();
            sb.Append(currentItem + ",");
            parameters.Add(new SqlParameter(currentItem , item));
        }
        sb.Remove(sb.Length-1, 1);

        sb.Append(")");

I hope this helps

score -1 · Answer 7 · answered Nov 22 '13 at 11:02

-1

Alternatively you can put a if condition for the parameters. For Example:

if(commaSeparatedElements.Contains("'"))
   return;
else
   //Pass parameter & Execute query

answered Nov 22 '13 at 11:02

Ashish Charan

2,347
4
21
33

SqlParameter and IN statement

7 Answers7

Linked