Sql/Dapper:如何在值数组的WHERE子句中执行LIKE

Question

我有一个表，里面有很多员工，每个人都有一个全名列。

然后我想在搜索人员时进行类似的查询：

SELECT * FROM Employees WHERE Name LIKE '%' + @value1 + '%' AND Name LIKE '%' + @value2 +'%' AND so forth...

对于任意的值数组。

我的Dapper代码看起来像这样：

public IEnumerable<Employee> Search(string[] words)
{
using var connection = CreateConnection();
connection.Query<Employee>("SELECT * etc.", words);
}

有没有任何方法可以在不使用字符串串联的情况下使用SQL实现这一点，以及随之而来的SQL注入攻击的风险？

注意：我不知道Dapper是如何将数组传递给查询的，这限制了我处理以下问题的创造性想法：-D

而且：不幸的是，改变表格结构是不可能的。我宁愿避免把每个人都带到.Net内存中，并在那里进行过滤。

Answer 1

有没有任何方法可以在不使用字符串串联的情况下使用SQL实现这一点，以及随之而来的SQL注入攻击的风险？

因为where条件集不是固定的，所以需要动态构建查询。但这并不意味着你不能参数化查询，你只需要在构建查询的同时构建参数列表。每次列表中的一个单词添加到条件中并添加一个参数时。

由于Dapper不直接包含任何需要DbParameter集合的内容，请考虑使用ADO.NET获取IDataReader，然后使用Dapper的

IEnumerable<T> Parse<T>(this IDataReader reader)

用于映射。

这样的建设者将是非常粗略的

var n = 0;
for (criterion in cirteria) {
var cond = $"{crition.column} like @p{n}";
var p = new SqlPatameter($"@p{n}", $"%{crition.value}%";
conditions.Add(cond);
cmd.Parameters.Add(p);
}
var sql = "select whetever from table where " + String.Join(" and ", conditions);
cmd.CommandText = sql;
var reader  = await cmd.ExecuteReaderAsync();
var res = reader.Parse<TResult>();

Answer 2

出于性能原因，将其作为基于集的操作来执行要好得多

您可以将数据表作为表值参数传递，然后以LIKE为条件加入该数据表。在这种情况下，您希望所有值都匹配，因此需要一点关系划分。

首先创建您的表类型：

CREATE TYPE dbo.StringList AS TABLE (str varchar(100) NOT NULL);

您的SQL如下：

SELECT *
FROM Employees e
WHERE NOT EXISTS (SELECT 1
FROM @words w
WHERE e.Name NOT LIKE '%' + w.str + '%' ESCAPE '/'   -- if you want to escape wildcards you need to add ESCAPE
);

然后按如下方式通过列表：

public IEnumerable<Employee> Search(string[] words)
{
var table = new DataTable{ Columns = {
{"str", typeof(string)},
} };
foreach (var word in words)
table.Rows.Add(SqlLikeEscape(word));    // make a function that escapes wildcards
using var connection = CreateConnection();
return connection.Query<Employee>(yourQueryHere, new
{
words = table.AsTableValuedParameter("dbo.StringList"),
});
}

Answer 3

如果要添加where条件，并且在where之上可以添加循环，以运行数组中的所有元素。

对于带有dapper的Sql，您可以添加

var builder = new SqlBuilder();
/**Add Loop Here**/
.Where($"LOWER(ColumnName) LIKE @Value", new { Value = "%"+Class.Object.ToLower()+"%" });

var selector = builder.AddTemplate("SELECT * FROM TableName /**where**/");
using (var connection = new SqlConnection(connectionString))
{
var results = await connection.QueryAsync(selector.RawSql, selector.Parameters);
return results.ToList();
}