Secure String Interpolation in EF Core
In this lesson, you will learn how to build dynamic SQL queries safely using C# string interpolation without exposing your application to SQL injection vulnerabilities.
When executing raw SQL in Entity Framework Core, developers often need to inject variable data into their queries. While standard C# string interpolation ($"SELECT * FROM Users WHERE Name = '{name}'") is convenient, using it directly with database commands is dangerous because it allows malicious input to alter your SQL logic.
The FromSql Safety Mechanism
EF Core provides a specific way to handle interpolated strings. When you pass an interpolated string to methods like FromSql or ExecuteSql, EF Core does not perform a simple string concatenation. Instead, it treats the interpolated expression as a parameterized query.
Consider this example:
string status = "Active";
int minOrders = 10;
// EF Core detects the interpolated variables and maps them to SQL parameters
var customers = context.Customers
.FromSql($"SELECT * FROM Customers WHERE Status = {status} AND OrderCount >= {minOrders}")
.ToList();
Why it Works
When you pass that interpolated string to FromSql, EF Core performs a two-step process:
- Extraction: It pulls the values out of the placeholders (
{status}and{minOrders}). - Parameterization: It replaces those placeholders with standard database parameters (e.g.,
@p0,@p1) and sends the values to the database engine separately from the query command.
Because the values are handled as parameters rather than raw text, the database engine treats them strictly as data. Even if a user provided a malicious string like ' OR 1=1 --, the database will search for a user whose name is literally ' OR 1=1 --, neutralizing the threat entirely.
By using EF Core’s built-in interpolation support, you get the clean, readable syntax of C# strings while maintaining the robust security of parameterized queries. Never combine strings manually (like using +) when building SQL queries; always let the EF Core provider handle the interpolation to ensure your parameters are correctly escaped by the database driver.
Key Takeaway: Always use EF Core’s built-in interpolated string support for raw SQL to ensure that dynamic values are automatically converted into secure database parameters, preventing SQL injection.