Are my EF LINQ to SQL queries safe?

Have you ever asked yourself if your EF LINQ queries are susceptible to SQL injection attacks? Either because you are querying some user data from a text field or directly taking whatever your API hits against the database?

Well, for starters, you most certainly want almost always validate and sanitize input. But sure that isn't the point here - so let's say you don't, and then?

The good answer: Entity Framework is nice to you and sanitizes the input. So if you have something like this:

var userInput = "Steven \"OR 1 = +";
var evil = await myContext
  .People
  .Where(p => p.Name === userInput)
  .ToListAsync();

will generate SQL that looks similar to this:

SELECT "p"."Id", "p"."Name"
FROM "People" as "p"
WHERE "p"."Name" = 'Steven "OR 1 = 1'

And that's good, because the SQL injection attack will not work as the OR 1 = 1 will be treated as a string and not as a SQL command.

Now that will not hold true if you are using raw SQL queries. So if you have something like this:

var userInput = "Steven \"OR 1 = +";
var evil = await myContext
  .People
  .FromSqlRaw($"SELECT * FROM People WHERE Name = '{userInput}'")
  .ToListAsync();

This might be vulnerable to SQL injection attacks. So be careful with raw SQL queries.

Bonus Points: FromSqlInterpolated

Thanks to @kapsiR for pointing out that FromSqlInterpolated is a safer way to use raw SQL queries. It is part of the Microsoft.EntityFrameworkCore.Relational package, so it isn't generally available.

Here the very short difference:

var evil = await myContext.
  .People
  .FromSqlRaw($"SELECT * FROM People WHERE Name = {userInput}")
  .ToListAsync();

// VS
var notSoEvil = await myContext.
  .People
  .FromSqlInterpolated($"SELECT * FROM People WHERE Name = {userInput}")
  .ToListAsync();

While they look the same, the signature of methods is important: FromSqlRaw takes a string, while FromSqlInterpolated takes an FormattableString. The former is just one string, while the latter is a string with placeholders for the parameters. This way, EF does know what userInput is and can sanitize it properly.

If you want to execute an arbitrary SQL query, you can use ExecuteSqlInterpolated on top of the Database object (instead if DbSet<TYourEntity>).

Bonus Points: FromSql

There is another save version over FromSqlRaw that is FromSql. It is part of the same extension. It works the same as FroSqlInterpolated (builts on top of FormattableString). Also here: You can only use it on top of DbSet<TYourEntity>.

var notSoEvil = await myContext.
  .People
  .FromSql($"SELECT * FROM People WHERE Name = {userInput}")
  .ToListAsync();

If you are operating on Database, use ExecuteSql instead. By the way all of the methods have async versions as well. For the sake of simplicity, I omitted them here.

Now you might ask: What is the difference between FromSqlInterpolated and FromSql? There isn't. Under the hood they do the same calls. Why do we have two versions of the same thing then? There is a good answer on stackoverflow.

The TL;DR: We had FromSql in EF Core 2.x (with interpoloated strings). That brought some issues in part because of how C# treats some things. So strings were interpolated even though they shouldn't and vice versa. With EF Core 3, the distinction was made (FromSqlRaw and FromSqlInterpolated) to avoid these issues. Fast forward to EF 7: They brought back FromSql as a convenience method that is just a wrapper around / alias for FromSqlInterpolated. Less things to type and higher up in the intellisense list.

What should you use? FromSql. There might be still cases with formatting issues, but they are rare. And if you encounter them, you can still use FromSqlRaw but you should be aware of the risks.