警告EF1000.在"IN"中写入参数的最佳方法是什么?陈述? [英] warning EF1000. What the best way to write parameters in "IN" statement?

问题描述

EF Core 2.1引发有关SQL注入的警告.这是我的代码段

EF Core 2.1 throws warnings about SQL injection. There is my code snippet where it happens

var query = await _ctx.Set<TABLE>()
                      .FromSql("Select * from TABLE where Artikelnummer IN (" + string.Join(',', artNum) + ")")
                      .AsNoTracking()
                      .Select(z => new
                      {
                        z.Artikelnummer,
                        z.Property
                      }).ToArrayAsync();

其中artNum(代表Artikel数字)是整数数组，例如[1,22,34等].

where artNum(stands for Artikel Numbers) is array of int numbers, like [1,22,34 etc.].

我无法在FromSql中将artNum作为参数传递，因为会出现单引号，并且SQL会引发异常.

I can't pass artNum as parameters in FromSql, because there will be single quotes and SQL throws an exception.

我可以将FromSql重写为

I can rewrite the FromSql to

.Where(x => artNum.Contains(x.Artikelnummer))

但是我要查询的内容比这个要复杂得多，因此 LINQ to Entities 在这种情况下不是很好. 应该存在，问题是如何重写此查询，这样就不会发生警告?

But I have queries that much bigger and complicated than this one, so LINQ to Entities isn't good in this situation. there should be So, the question is how to rewrite this query so warning won't occur?

LINQ to Entities 的执行时间要长于 LINQ to SQL ，因此这不是解决我的问题的合适方法.

LINQ to Entities executes much longer than LINQ to SQL, so it's not appropriate way to solve my problem.

我调查了级联会引起警告，因此应该没有级联的解决方案.

I investigated that concatenation causes warnings, so there should solution without concatenation.

推荐答案

我似乎在EF Core文档中找不到这种警告.有趣的是，EF Core 2.1查询转换器本身并未参数化生成的SQL IN values子句.如果将查询的.FromSql行替换为

I can't seem to find such warning in EF Core documentation. And the (sort of) funny things is that the EF Core 2.1 query translator itself does not parameterize the generated SQL IN values clauses. Which can be seen if you replace the .FromSql line of your query with

.Where(x => artNum.Contains(x.Artikelnummer)

哪个btw是相当于LINQ to Entities的查询，它可以翻译和执行就好了，所以我不知道为什么在这种特殊情况下为什么要打扰FromSql.

which btw is the LINQ to Entities equivalent of your query which translates and executes just fine, so I don't know why you bother with FromSql in this particular case.

但是无论如何，您可以参数化

But anyway, you can parameterize the FromSql query by including {0}, {1} etc. placeholders inside the sql string and pass values through params object[] parameters:

与任何接受SQL的API一样，对任何用户输入进行参数化以防止SQL注入攻击也很重要.您可以在SQL查询字符串中包含参数占位符，然后提供参数值作为附加参数.您提供的任何参数值都将自动转换为DbParameter

As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter

在您的情况下，可能是这样的:

In your case it could be like this:

var placeholders = string.Join(",", atrNum.Select((v, i) => "{" + i + "}"));
var values = atrNum.Cast<object>().ToArray();

.FromSql("Select * from TABLE where Artikelnummer IN (" + placeholders + ")", values)

这篇关于警告EF1000.在"IN"中写入参数的最佳方法是什么?陈述?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！