warning EF1000. What the best way to write parameters in "IN" statement?

user_8948007 0
Viewed 1821

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();

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

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

I can rewrite the FromSql to

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

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 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-2.1 asp.net-core-2.1 sql linq c#
1 Answers

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)

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:

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)
user_52025630
Related