Enhancing Database Security with Parameterized Queries in EF Core

May 1, 2024

When developers are taught how to write database queries, they should be told to use prepared statements with variable binding (aka parameterized queries). - OWASP

SQL Injection attacks are a real threat to our database. They involve sneaking in malicious code into SQL queries to mess up our database. If we’re using EF Core for our app, we might end up exposing our database to these attacks if we’re not careful with how we develop.

To prevent this attack, one of the best practices is to use parameterized queries in EF Core. The concept of parameterized queries involves using parameters in SQL queries. In this post, I will demonstrate how to use parameterized queries in EF Core.

The code below shows an example of a parameterized query in EF Core. We can store the search value in a variable and then include the variable in the database query. EF Core will translate the variable into a parameterized query in SQL.

var name = "Jorge"; // search value
var result = _context.Students.Where(i => i.FirstName == name).ToList(); // using variable name

Then, EF Core will translate it into an SQL command that uses parameters.

DECLARE @P1 NVARCHAR(4000);
SET @P1 = 'Jorge';
SELECT * FROM Students WHERE Students.FirstName = @P1;

As a comparison, the code below does not use a parameterized query. The search value is included inside the queries. An attacker can use this command to inject malicious code via the search value. Typically, this search value is derived from user input in the frontend; this is where an attacker can initiate SQL injection.

// without parameterized query
var result = _context.Siswa.Where(i => i.FirstName == "Jorge").ToList();

EF Core translates it to this SQL command.

SELECT * FROM Students WHERE Students.FirstName = 'Jorge';

Without parameterized query, our SQL command can be vulnerable to malicious code. As you can see, implementing parameterized queries is both easy and powerful for protecting our database. I hope this example can inspire you to write more secure code for your application.

Happy hacking!