如何在C#中将字符串列表作为SQL参数传递

Question

我正在使用entity framework。有人告诉我，执行直接sql的建议方法是使用参数而不是直接将值插入到查询字符串中。我尝试制作单独的sql参数并了解到存在2100个参数限制（我的列表中有8000个字符串参数）...我可以通过将值直接传递给mySQLQuery字符串来使其工作，但我想知道如何将其作为参数传递以避免sql注入攻击？我尝试了下面的代码

var mySQLQueryString = "...WHERE DataID IN (@mySQLVariable)";
List<string> dataList = new List<string>();
SqlParameter param = new SqlParameter("@mySQLVariable", string.Join(",", dataList.ToArray()));
return context.Database.SqlQuery<ConcretePOCO>(mySQLQueryString, param).ToList();

在使用分析器查看时，发送到 SQL 的数据是'1,2,3,4'...我需要它变成'1'，'2'，'3'，'4'...

当作为参数发送时，我该怎样实现这一点呢？

- Daron

---

如果你想传递2100个参数，那么你有一个设计问题。你不能传递那么多的参数或者在IN子句中使用太多的值是有原因的。 - Panagiotis Kanavos

你想要做什么？为什么不将数据存储在临时或暂存表中，然后与查询的其余部分进行连接？您可以在连接列上添加索引，并获得非常快的性能。 - Panagiotis Kanavos

@jcruz 尽管 OP 说了什么，但没有任何理由进行引用。该参数是一个单一的值，无论它包含什么。 - Panagiotis Kanavos

@Daron，我认为@PanagiotisKanavos说的有道理。这里的大局尚不清楚。但我怀疑获得'1', '2', '3', '4'可能无法解决您的问题。您是否尝试过在SSMS中硬编码该值并运行查询？如果是这样，它是否有效？如果没有真正掌握您要做什么的整个想法，很难建议解决方案。但我有一个问题：dataList中将有多少项的上限范围是多少？它会超过2100吗？ - Casey Crookston

@Daron 现在，如果你真的想发送8千行，你可以使用 SQL Server 中的表值参数。其他数据库使用不同的类型，例如数组类型。这些技术都不可移植，并且都有不同的性能问题。例如，在 SQL Server 中，假设 TVP 只有1行，因为没有统计信息。 - Panagiotis Kanavos

Answer 1

如何在EF中使用表值参数：

在SQL中创建一个新的Type

CREATE TYPE DataIds AS TABLE  
(  
    DataID int 
) 

在你的方法中，从你的列表中创建一个新的DataTable。

using System.Ling;
using System.Data;
// ...

var dataIds = new DataTable();  
dataIds.Columns.Add("DataID", typeof(int));

List<string> dataList = new List<string>();
// do what ever to fill your list with parameters

foreach (String item in dataList)
{
    DataRow row = dataIds.NewRow();
    row["DataID"] = item;
    table.Rows.Add(row);
}

// When fetching data
var mySQLQueryString = "...WHERE DataID IN (SELECT DataID FROM @mySQLVariable)";
SqlParameter param = new SqlParameter("@mySQLVariable", SqlDbType.Structured);  
param.Value = dataIds;  
param.TypeName = "dbo.DataIds";  
return context.Database.SqlQuery<ConcretePOCO>(mySQLQueryString, param).ToList();

- Fourat

Answer 2

在传递给 SQL 时，请使用分隔符，然后在 SQL 中使用 STRING_SPLIT 将其拆分成表。

例如：

SqlParameter param = new SqlParameter("@mySQLVariable", string.Join("**~**", dataList.ToArray()));

然后在 SQL 中执行

select value FROM STRING_SPLIT(@mySQLVariable, '~')

- Bar

---

STRING_SPLIT 仅存在于支持 JSON 的 SQL Server 版本中。将输入数据序列化为 JSON 并在服务器上解析会更安全。更好的选择是使用 TVP，但所有这些都会很慢。 - Panagiotis Kanavos

这个解决方案是可行的，Casey（我已经测试过了），但我试图避免在数据库上创建函数和存储过程。我不想处理依赖关系。 - Daron

（@Daron...不是我的答案。我只是编辑了这个答案，使其更易读。我现在正在撰写自己的答案。） - Casey Crookston

Answer 3

我可以想到几种方法来完成这个任务。一种可能的方法是将工作留在服务器上，而不是在查询中进行。请记住，根据我们处理的数据量大小，这可能不是最快的过程。但如果我们只处理9000条数据，那么应该没问题。

使用Entity Framework和LINQ，如果您的ID是字母数字组合，则应该可以实现。根据您的实现方式，语法可能会有所不同...

var myResults= context.MyTable.Any(t => t.DataID.Intersect(dataList));

现在，如果这太慢了，你可以将 ID 列表转换为 HashSet。

 var myHashSet = new HashSet<string>(dataList);   
 var myResults = context.MyTable.Where(t => myHashSet.Overlaps(t.DataID));

（免责声明：我没有测试过这些内容。我只是凭记忆写的。）

- Casey Crookston

---

这个解决方案的问题在于 Id 是字母数字混合的（它们不能是整数）。 - Daron

好的！这就引出了一个问题...为什么你的ID是字母数字混合的？但无论如何，只需将所有int实例更改为string，你就可以继续了。 - Casey Crookston