Understanding SQL Injection and How to Prevent It in C# with Entity Framework

Recently I saw code, in which user strings from a web page were used in an SQL query string by string interpolation. That’s an open door to everybody using an (pretty old) attack, called SQL injection. SQL injection is a classic security vulnerability, which typically arises when user inputs are directly embedded in SQL queries without proper validation or sanitation. With frameworks like Entity Framework in C#, it’s easier to avoid these risks, as it abstracts database queries and helps prevent SQL injection.

Consider a basic SQL query in which a user’s input is concatenated directly into the query string. This approach opens the door to SQL Injection:

var userName= "userName";
var query = $"SELECT user FROM Users WHERE Username = {userName}";

If an attacker inputs something like '; DROP TABLE Users; --, he or she can delete the table. Here, the -- symbol comments out the rest of the query to be on the safe side. If you think this is harmless, just try dropping all system tables 😉

In C# with Entity Framework, a safe way to handle user input is using LINQ queries or parameterized queries, which automatically escape inputs to prevent SQL injection:

using Microsoft.EntityFrameworkCore;

using var context = new UserDbContext();
var userName= "userName";
var user = context.Users.FirstOrDefault(u => u.Name == userName);
Console.WriteLine(user != null ? $"User found: {user.Name}" : "User not found.");

[Keyless]
public class User
{
    public string Name { get; set; }
}

public class UserDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
optionsBuilder.UseSqlServer(
@"Server=localhost;Database=Users;Trusted_Connection=True;TrustServerCertificate=True");
    }
}

In this example LINQ makes sure that user input cannot be used to inject SQL statements. Of course additional checks would be desireable, but are not relevant for this example.

So the key takeaways are: 1. Avoid string interpolation and concatenation in SQL queries. 2. Use LINQ or parameterized queries when handling user input-derived strings. 3. Validate and sanitize user inputs! Have fun and Happy coding 🚀!