How can I avoid SQL injection in this EF Core query?

Question

Since EF Core does not currently execute group by queries in the database, I'm using the following query to get the job done (InvoiceQuery is a DbQuery type):

long merchantId = 177;
var invTokens = new List<string> {"qasas", "efsac"};
string inClause = string.Join(",", invTokens);

string query = $@"SELECT i.Token, i.Balance, COALESCE(SUM(i.Amount), 0) AS ProcessingAmount
                  FROM inv.Invoices i
                  WHERE i.Token IN ({inClause})
                    AND i.MerchantId = {merchantId} 
                    AND i.Status = {(int)InvoiceStatus.Scheduled}
                  GROUP BY i.Token, i.Balance";

return await dbContext.InvoicesQuery.FromSql(query).ToListAsync();

The above code runs perfect but I don't like it because it gives me a warning of a 'Possible SQL injection vulnerability'.. I know I can use a #pragma to suppress this warning (that's what I'm currently using).

I have tried passing the query and provide the params to the FromSql method, and that works but the parameter that goes into the IN clause is not getting correctly mapped..

Tried using $"'{string.Join("', '", invTokens)}'" but it didn't work.

Any help would be appreciated

Why are you using raw SQL here anyway? It's not a complex query, you could do this with Linq. — DavidG
– DavidG, Commented Mar 26, 2019 at 23:15
Have you read learn.microsoft.com/en-us/ef/core/querying/raw-sql ? It shows how to pass parameters safely (in multiple different ways). — mjwills
– mjwills, Commented Mar 26, 2019 at 23:16
The above code runs perfect I am honestly surprised the query runs successfully, given you aren't quoting the individual inClause values. What is the exact value of query at runtime? — mjwills
– mjwills, Commented Mar 26, 2019 at 23:18
EF Core does support group by, but you need to use v2.1 or newer. — DavidG
– DavidG, Commented Mar 26, 2019 at 23:38
@DavidG o: you are right, i was thinking that group by support will be added in the 3.0 release. Thanks!! — Efrain Bastidas Berrios
– Efrain Bastidas Berrios, Commented Mar 27, 2019 at 0:42

kapsiR · Accepted Answer · 2019-03-27 07:40:39Z

Since EF Core 2.0, you can use a FormattableString, where you can use string interpolation for queries.
String interpolation in FromSql and ExecuteSqlCommand
FormattableString Class

In EF Core 2.0 we added special support for interpolated strings to our two primary APIs that accept raw SQL strings: FromSql and ExecuteSqlCommand. This new support allows C# string interpolation to be used in a 'safe' manner.

The example from the docs:

var city = "London";
var contactTitle = "Sales Representative";

using (var context = CreateContext())
{
    context.Set<Customer>()
        .FromSql($@"
            SELECT *
            FROM ""Customers""
            WHERE ""City"" = {city} AND
                ""ContactTitle"" = {contactTitle}")
            .ToArray();
 }

This will produce:

@p0='London' (Size = 4000)
@p1='Sales Representative' (Size = 4000)

SELECT *
FROM ""Customers""
WHERE ""City"" = @p0
    AND ""ContactTitle"" = @p1

Collectives™ on Stack Overflow

How can I avoid SQL injection in this EF Core query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related