c# - Parameterized dynamic sql query

Question

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?

Answer 1

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();