Parameterized dynamic sql query

c#, sql, sql-server

Solution

You are doing a few things wrong here:

- You give all your parameters the same name `@searchitem`. That won't work. The parameters need unique names.

- You create a new SqlCommand for each item. That won't work. Create the SqlCommand once at the beginning of the loop and then set `CommandText` once you are done creating the SQL.

- Your SQL ends with `AND`, which is not valid syntax.

Improvement suggestions (not wrong per se, but not best practice either):

- As Frederik suggested, the usual way is to put the `%` tokens in the parameter, rather than doing string concatenation inside the SQL.

- Unless you explicitly use a case-sensitive collation for your database, comparisons should be case-insensitive. Thus, you might not need the `LOWER`.

Code example:

SqlCommand cmd = new SqlCommand();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT name, memberid FROM members ");

var i = 1;
foreach (string item in keywords)
{
    sqlBuilder.Append(i == 1 ? " WHERE " : " AND ");
    var paramName = "@searchitem" + i.ToString();
    sqlBuilder.AppendFormat(" Name LIKE {0} ", paramName); 
    cmd.Parameters.AddWithValue(paramName, "%" + item + "%");

    i++;
}
cmd.CommandText = sqlBuilder.ToString();

Problem

I have a list of keywords that i store in a list. To fetch records from a table, am using the following query: ``` sqlBuilder.Append("SELECT name, memberid FROM members WHERE"); StringBuilder sqlBuilder = new StringBuilder(); foreach (string item in keywords) { sqlBuilder.AppendFormat(" LOWER(Name) LIKE '%{0}%' AND", item); } string sql = sqlBuilder.ToString(); ``` As you might have noticed, my query is vulnerable to sql injection, thus i want to use parameters using SqlCommand(). I have tried the following but still doesn't work: ``` foreach (string item in keywords) { sqlBuilder.AppendFormat(" LOWER(Name) LIKE '%' + @searchitem + '%' AND", item); SqlCommand cmd = new SqlCommand(sqlBuilder.ToString()); cmd.Parameters.AddWithValue("@searchitem",item); } ``` Where could i be making the mistake, or rather, how should i got about it?

Original source