CODEWITHSUNDEEP
c#entity-framework-core
Efrain Bastidas Berrios
Efrain Bastidas Berrios

Reputation: 377

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

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

Upvotes: 3

Views: 4661

Answers (1)

kapsiR
kapsiR

Reputation: 3186

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

Upvotes: 1

Related Questions