How to Use Raw SQL Queries Safely in Entity Framework Core

Introduction

Entity Framework Core (EF Core) is a powerful ORM in .NET that helps developers work with databases using C# instead of writing raw SQL queries. However, there are real-world scenarios where using raw SQL becomes necessary—for performance optimization, complex queries, or working with stored procedures.

But here is the challenge: if raw SQL is not handled properly, it can lead to SQL Injection attacks, which are one of the most common and dangerous security risks in web applications.

What is SQL Injection?

SQL Injection is a security vulnerability where an attacker can manipulate your SQL query by injecting malicious input.

Example of unsafe code:

var user = context.Users
    .FromSqlRaw($"SELECT * FROM Users WHERE Name = '{userInput}'")
    .FirstOrDefault();

If userInput is:

' OR 1=1 --

Then your query becomes:

SELECT * FROM Users WHERE Name = '' OR 1=1 --

This can expose all data in your database.

Why Use Raw SQL in EF Core?

Even though EF Core supports LINQ, raw SQL is useful for:

• Complex queries

• Stored procedures

• Performance-critical operations

• Legacy database integration

But it must be used carefully.

Safe Ways to Use Raw SQL in EF Core

Use Parameterized Queries (Most Important)

The safest way to prevent SQL injection is to use parameterized queries.

Example:

var user = context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Name = @name",
        new SqlParameter("@name", userInput))
    .FirstOrDefault();

Why it is safe:

• User input is treated as data, not SQL

• Prevents query manipulation

Use FromSqlInterpolated (Safer Alternative)

EF Core provides a safer method:

var user = context.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Name = {userInput}")
    .FirstOrDefault();

Why it is safe:

• Automatically parameterizes input

• Cleaner syntax

This is recommended over string concatenation.

Avoid String Concatenation (Dangerous)

Never do this:

var query = "SELECT * FROM Users WHERE Name = '" + userInput + "'";

This directly exposes your application to SQL injection.

Use Stored Procedures Safely

Stored procedures can also be safe if used correctly.

Example:

var users = context.Users
    .FromSqlRaw("EXEC GetUserByName @name",
        new SqlParameter("@name", userInput))
    .ToList();

Always pass parameters instead of concatenating strings.

Validate User Input

Even with parameterized queries, input validation is important.

Best practices:

• Check input length

• Validate format (email, numbers, etc.)

• Reject suspicious patterns

Example:

if (string.IsNullOrWhiteSpace(userInput))
{
    throw new ArgumentException("Invalid input");
}

Use Least Privilege Database Access

Your database connection should have limited permissions.

Example:

• Do not use admin account

• Allow only required operations (SELECT, INSERT, etc.)

This reduces damage even if an attack occurs.

Use Logging and Monitoring

Track database queries and unusual behavior.

Benefits:

• Detect suspicious activity

• Debug issues

• Improve security

Tools:

• Application logs

• Database monitoring tools

Prefer LINQ When Possible

EF Core LINQ queries are automatically safe from SQL injection.

Example:

var user = context.Users
    .FirstOrDefault(u => u.Name == userInput);

LINQ translates into parameterized SQL internally.

Use raw SQL only when necessary.

Common Mistakes Developers Make

Using FromSqlRaw with String Interpolation

FromSqlRaw($"SELECT * FROM Users WHERE Name = '{userInput}'")

❌ Unsafe

Trusting User Input Blindly

Never assume input is safe.

Overusing Raw SQL

Use raw SQL only when needed.

Ignoring Security Testing

Always test your application for vulnerabilities.

Real-World Example

Let’s build a safe search API:

app.MapGet("/users/{name}", (string name, AppDbContext context) =>
{
    var users = context.Users
        .FromSqlInterpolated($"SELECT * FROM Users WHERE Name = {name}")
        .ToList();

    return users;
});

This ensures:

• Safe parameter handling

• No SQL injection risk

Best Practices Summary

• Always use parameterized queries

• Prefer FromSqlInterpolated over FromSqlRaw

• Avoid string concatenation

• Validate all user inputs

• Use least privilege database access

• Prefer LINQ when possible

Summary

Using raw SQL in Entity Framework Core is sometimes necessary, but it must be done carefully to avoid SQL injection risks. By using parameterized queries, FromSqlInterpolated, input validation, and proper security practices, developers can safely execute raw SQL queries. Following these best practices ensures secure, scalable, and production-ready .NET applications.