原始数据：
1.拼接SQL：容易注入
2.参数化查询：
2.1.等于 + Like:
2.2.等于 + Like + IN:
2.2.1.结果：类似的参数传递，但是结果为0
2.2.2.分析：通过SQL Server Profile得到运行sql，发现 IN 的参数被处理成了一个字符串，类似于：
WHERE [Key] IN ('''01'', ''11'', ''21'', ''22'', ''23''')
2.3.IN 的解决方法
2.3.1.LIKE OR CHARINDEX: 网传的骚方法，本质上是，把IN的参数合并为字符串，检索字段是否匹配，容易误判（[Key] IN (1,2,3,4)）
WHERE @Key LIKE '%''' + [Key] + '''%'
OR
WHERE CHARINDEX('''' + [KEY] + '''', @Key) > 0
PS: 拼接和字段都需要加上分隔符，不然容易误判，比如：001 LIKE '%01%'，但结果不是预期
2.3.2.EXEC
exec sp_executesql N'EXEC(''SELECT 1 WHERE 1 IN (''+@Key+'')'')',N'@Key nvarchar(5)',@Key=N'1,2,3'
PS：只适用于INT查询，操作不当容易出现类型错误和语法错误
exec sp_executesql N'EXEC(''SELECT 1 WHERE ''01'' IN (''+@Key+'')'')', N'@Key nvarchar(28),@Value nvarchar(9),@Sort int'
,@Key=N'''01'', ''11'', ''21'', ''22'', ''23''',@Value=N'%Value_0%',@Sort=1
语法错误：
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near '01'.
PRINT 'SELECT 1 WHERE ''01'' IN (''+'''01'', ''11'', ''21'', ''22'', ''23'''+'')'
2.3.3.给 IN 集合的值都维护到参数数组中
PS：参数有限制，最大2100，根据实际情况使用
2.3.4.STRING_SPLIT：看了MSSQL抓取的SQL，可以看出 IN 的值在DB中都是字符串，那我把字符串处理成集合就不好啦，没有数量限制还灵活
AllCode
/// <summary>
/// 参数化查询
/// </summary>
[Fact]
public void TestParamQuery()
{
var date = DateTime.Now;
var date2 = date.AddDays(2).AddHours(17);
using (var context = new ODMSmartReportEntities())
{
context.Database.Log = NLogHelper.Info;
var sort = 1;
var value = "Value_0";
var keyList = new List<string> { "01", "11", "21", "22", "23" };
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine(" WITH V_ALL AS (");
sqlBuilder.AppendLine(" SELECT '01' AS [Key], 'Value_01' as [Value], 01 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '02' AS [Key], 'Value_02' as [Value], 02 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '03' AS [Key], 'Value_03' as [Value], 03 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '11' AS [Key], 'Value_11' as [Value], 01 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '12' AS [Key], 'Value_12' as [Value], 02 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '13' AS [Key], 'Value_13' as [Value], 03 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '21' AS [Key], 'Value_21' as [Value], 01 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '22' AS [Key], 'Value_22' as [Value], 02 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '23' AS [Key], 'Value_23' as [Value], 03 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '31' AS [Key], 'Value_31' as [Value], 01 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '32' AS [Key], 'Value_32' as [Value], 02 AS [Sort] UNION ALL");
sqlBuilder.AppendLine(" SELECT '33' AS [Key], 'Value_33' as [Value], 03 AS [Sort] ");
//sqlBuilder.AppendLine(" ), V_KeyList AS (");
//sqlBuilder.AppendLine(" SELECT * FROM STRING_SPLIT(@Key, ',')");
//sqlBuilder.AppendLine(" )");
//sqlBuilder.AppendLine(" SELECT * FROM V_ALL");
//{
// // 参数化查询 - IN: STRING_SPLIT
// SqlParameter[] parameters = {
// new SqlParameter("@Key", keyList.Join(",")),
// new SqlParameter("@Value", $"%{value}%"),
// new SqlParameter("@Sort", sort),
// };
// var sql = $"{sqlBuilder} WHERE[Key] IN (SELECT VALUE FROM V_KeyList) AND [Value] LIKE @Value AND Sort = @Sort";
// var result = context.Database.SqlQuery<DictionaryDto>(sql, parameters).ToList();
//}
{
// 拼接字符串
var sql = $"{sqlBuilder} WHERE [Key] IN ('{keyList.Join("', '")}') AND [Value] LIKE '{value}%' AND Sort = {sort}";
var result = context.Database.SqlQuery<DictionaryDto>(sql).ToList();
}
{
// 参数化查询: 等于 + LIKE
SqlParameter[] parameters = {
new SqlParameter("@Value", $"%{value}%"),
new SqlParameter("@Sort", sort),
};
var sql = $"{sqlBuilder} WHERE [Value] LIKE @Value AND Sort = @Sort";
var result = context.Database.SqlQuery<DictionaryDto>(sql, parameters).ToList();
}
{
// 参数化查询: 等于 + LIKE + IN
var keyStr = $"'{keyList.Join("', '")}'";
SqlParameter[] parameters = {
new SqlParameter("@Key", keyStr),
new SqlParameter("@Value", $"%{value}%"),
new SqlParameter("@Sort", sort),
};
var sql = $"{sqlBuilder} WHERE [Key] IN (@Key) AND [Value] LIKE @Value AND Sort = @Sort";
var result = context.Database.SqlQuery<DictionaryDto>(sql, parameters).ToList();
}
{
// 参数化查询 - IN: LIKE + CHARINDEX
var keyStr = $"'{keyList.Join("', '")}'";
SqlParameter[] parameters = {
new SqlParameter("@Key", keyStr),
new SqlParameter("@Value", $"%{value}%"),
new SqlParameter("@Sort", sort),
};
var sql = $"{sqlBuilder} WHERE @Key LIKE '%' + [Key] + '%' AND [Value] LIKE @Value AND Sort = @Sort";
var result = context.Database.SqlQuery<DictionaryDto>(sql, parameters).ToList();
}
{
// 参数化查询 - IN: EXEC
SqlParameter[] parameters = {
new SqlParameter("@Key", "1,2,3"),
};
var sql = $"EXEC('SELECT 1 WHERE 1 IN ('+@Key+')')";
var result = context.Database.SqlQuery<DictionaryDto>(sql, parameters).ToList();
}
{
// 参数化查询 - IN: 把 in 的参数都维护到参数数组中
var paramsList = BuilderList("@Key", keyList);
var keyParamsNameList = paramsList.Select(x => x.ParameterName).ToList();
SqlParameter[] parameters = {
new SqlParameter("@Value", $"%{value}%"),
new SqlParameter("@Sort", sort),
};
paramsList.AddRange(parameters);
var sql = $"{sqlBuilder} WHERE[Key] IN ({keyParamsNameList.Join(",")}) AND [Value] LIKE @Value AND Sort = @Sort";
var result = context.Database.SqlQuery<DictionaryDto>(sql, paramsList.ToArray()).ToList();
}
}
}
/// <summary>
/// 根据参数生产 参数化集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fieldName"></param>
/// <param name="valueList"></param>
/// <returns></returns>
public List<SqlParameter> BuilderList<T>(string fieldName, List<T> valueList)
{
var paramsList = new List<SqlParameter>();
valueList.ForEach(x => paramsList.Add(new SqlParameter($"{fieldName}_{paramsList.Count}", x)));
return paramsList;
}
View Code
标签：Sort,sqlBuilder,LIKE,EF,Value,Key,var,NET,SqlParameter From： https://www.cnblogs.com/CRobot/p/17566410.html