参数化动态SQL查询 [英] Parameterized dynamic sql query

问题描述

我有我存储在一个列表中的关键字列表

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

正如你可能已经注意到，我的查询很容易受到SQL注入，因此我想用使用的参数的SqlCommand（）。我曾尝试以下，但仍然不能正常工作：

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?

推荐答案

您错在这里做了几件事情：

You are doing a few things wrong here:

• 您给您的所有参数相同的名称 @searchitem 。这是行不通的。这些参数需要唯一的名称。


• 您创建的每个项目的新的SqlCommand。这是行不通的。在循环的开始创建的SqlCommand的一次的，然后将的CommandText 一旦你完成创建SQL。


• 您的SQL与和，这是无效的语法。

• 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):

• 作为弗雷德里克认为，通常的方式是把％中的参数标记，而不是做在SQL中的字符串连接。


• 除非你明确地使用区分大小写的排序规则为你的数据库，比较应区分大小写。因此，你可能不需要在下

• 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.

代码，例如：

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

这篇关于参数化动态SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！