原始数据：
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; }