参数化动态SQL查询

Question

参数化动态SQL查询

6

我有一个关键词列表，存储在一个列表中。

为了从表中提取记录，我使用以下查询：

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() 来使用参数。我尝试了以下方法，但仍然无法使用：

foreach (string item in keywords)
            {    
                sqlBuilder.AppendFormat(" LOWER(Name) LIKE '%' + @searchitem + '%' AND", item);
                SqlCommand cmd = new SqlCommand(sqlBuilder.ToString());
                cmd.Parameters.AddWithValue("@searchitem",item);
             }

我可能犯了什么错误，或者说，我该怎么做才能做对呢？

- mutiemule

1

因为每次迭代 keywords 时，您都会创建新的 SqlCommand。我认为您应该在 foreach 循环中使用 AppendFormat，然后在 foreach 循环之外创建 SqlCommand 并添加参数。 - Soner Gönül

1

我建议打印出cmd.CommandText，你会发现它不是一个格式良好的SQL查询（你正在循环内部执行所有操作）。此外，通配符将更好地与参数值配合使用。 - Adriano Repetti

2个回答

4

不要将通配符字符放入您的查询字符串中，而是将其添加到参数值中：

sql = "SELECT name FROM members WHERE Name LIKE @p_name";
...
cmd.Parameters.AddWithValue("@p_name", "%" + item + "%");

当您在查询字符串中添加通配符字符时，参数将被转义，但通配符字符不会被转义。这将导致发送到数据库的查询看起来像这样：

SELECT name FROM members WHERE Name LIKE %'somename'%

这显然是不正确的。

此外，在循环中创建SqlCommand是不必要的。此外，由于您正在循环中添加参数，因此创建具有非唯一名称的参数，并且参数始终具有相同的名称。退出循环时，还需要删除最后一个AND关键字。

- Frederik Gheysels

2

有道理，但这并不是他的查询无法工作的原因。 - Heinzi

请注意，我正在使用一个 SQL 构建器。尝试在 SQL 构建器中使其工作，但没有成功。 - mutiemule

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Heinzi · Accepted Answer

你在这里做了一些错误的事情：

你给所有参数都取了相同的名字@searchitem。这样是行不通的，参数需要唯一的名称。
你为每个项创建了一个新的SqlCommand。这样是行不通的。在循环开始时创建SqlCommand一次，然后在创建SQL后一次设置CommandText。
你的SQL以AND结束，这不是有效的语法。

改进建议（不是错的，但也不是最佳实践）：

如Frederik建议的那样，通常的方法是将%标记放在参数中，而不是在SQL内部进行字符串连接。
除非你明确地为你的数据库使用区分大小写的排序规则，否则比较应该是不区分大小写的。因此，你可能不需要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();