Executing Raw SQL Safely

In this lesson, you will learn how to bypass LINQ for complex queries while ensuring your application remains immune to SQL injection attacks.

Sometimes, you need to execute a raw SQL query because LINQ-to-Entities cannot express a specific database feature, or you are looking for peak performance on a complex operation. While EF Core provides the FromSqlRaw and FromSqlInterpolated methods, using them incorrectly can open the door to SQL injection, where malicious input alters your database commands.

The Danger of String Concatenation

Never build SQL queries by concatenating strings. For example, $"SELECT * FROM Users WHERE Username = '{userInput}'" is dangerous. If a user inputs ' OR '1'='1, they could bypass your logic and gain unauthorized access.

The Safe Way: Parameterization

To execute raw SQL safely, you must use parameterized queries. This technique separates the query structure from the data, treating user input strictly as a literal value rather than executable code.

EF Core handles this automatically when you use interpolated strings with FromSqlInterpolated:

string status = "Active";
int minOrders = 10;

// EF Core safely converts variables into SQL parameters
var customers = context.Customers
    .FromSqlInterpolated($"SELECT * FROM Customers WHERE Status = {status} AND OrderCount >= {minOrders}")
    .ToList();

When EF Core encounters the FromSqlInterpolated method, it parses the string and replaces your variables with internal DbParameter objects. The database engine receives the query template and the parameter values separately, ensuring the input is never interpreted as part of the SQL command itself.

Important Constraints

When using raw SQL in EF Core, remember two critical rules:

1. Full Entity Projections: The query must return all columns required by the entity type. If you omit columns or rename them, EF Core may fail to map the results correctly.
2. Read-only Results: These queries are intended to retrieve data. If you need to perform INSERT, UPDATE, or DELETE operations, use Database.ExecuteSqlInterpolated instead to ensure parameters are handled with the same level of protection.

Key Takeaway: Always use interpolated strings (FromSqlInterpolated) instead of raw string concatenation to force EF Core to parameterize your inputs and neutralize SQL injection vulnerabilities.