Application data
.NET Core Best Practices C# Entity Framework Core OWASP

How to Use Raw SQL Queries in Entity Framework Core

Welcome to today’s blog.

In today’s post I will be discussing how to use raw queries in EF Core and how to use them safely.

EF Core is the ORM middleware that is used within .NET Core to allow access to a backend database such as SQL server. The purpose behind using am ORM tool is to provide a layer of abstraction for the developer to be able to access backend data through data context DTOs without using SQL directly.

There will however be systems and projects written in legacy .NET Framework 4.x and ADO.NET libraries that access data directly using raw SQL. In this case, converting the application to use an EF Core backend can be done in two stages:

  1. Convert the backend from ADO.NET to EF Core and use raw queries.
  2. Convert the raw queries and stored procedures to logic within backend .NET Core Web service APIs using EF Core DTO.

Bearing this in mind, it would be beneficial to port the raw SQL to using EF Core raw queries until there is an opportunity to upgrade the hard-coded queries to ORM models.

There are some key extension methods that are provided in .NET Core 3.1 which allow use to use raw SQL in our EF Core data context.

The previous version of .NET Core up to 2.2 had a single extension method FromSQL(), which allowed both parametrized and un-parametrized queries. 

OWASP Recommendation: Always use parametrization with raw SQL queries.

We should avoid passing a concatenated or interpolated invalid strings values into a FromSqlRaw or ExecuteSqlRaw method.

The following implementation of a FromSqlRaw call is bad and is open to potential injection attacks:

public async Task<List<BookViewModel>> GetNLatestBooks(int numberBooks)
{
    var latestNBooks = await _db.Books
        .FromSqlRaw("SELECT TOP " + numberBooks.ToString() + " * " + " FROM Books ")
        .OrderByDescending(b => b.DateCreated)
        .ToListAsync();
    return latestNBooks;
}

Making use of the FromSqlInterpolated and ExecuteSqlInterpolated methods using string interpolation protects against SQL injection attacks.

The following table filter call is safe as it uses an interpolated parameter:

public async Task<List<BookViewModel>> SearchBooks(string query)
{
    var latestNBooks = await _db.Books
        .FromSqlInterpolated($"SELECT * FROM SearchedBooks({query})")
        .OrderByDescending(b => b.DateCreated)
        .Take(numberBooks)
        .ToListAsync();
    return latestNBooks;
}

Using Raw Stored Procedure Calls

Suppose we have a stored procedure in our backend SQL data base:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetNLatestBooks 
    @NumberOfLatest int = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@NumberOfLatest) *  
    FROM Books 
    ORDER BY DateCreated DESC
END
GO

Calling the SP directly from the SSMS query command is as follows:

EXEC GetNLatestBooks @NumberOfLatest = 5

Within our API service class, calling to the above stored procedure is safe as it uses positional parameters:

public async Task<List<BookViewModel>> GetNLatestBooks(int numberBooks)
{
    var latestNBooks = await _db.Books
        .FromSqlRaw("EXECUTE GetNLatestBooks {0}", numberBooks)
        .ToListAsync();
    return latestNBooks;
}

The following stored procedure call uses named parameters:

public async Task<List<BookViewModel>> GetNLatestBooksV4(int numberBooks)
{
    var latestNBooks = await _db.Books
        .FromSqlInterpolated($"EXECUTE GetNLatestBooks {numberBooks}")
        .ToListAsync();
    return latestNBooks;
}

The FromRawSql extension method also allows an SqlParameter parameter to be included, so the with following we can setup our parameter before applying it in a stored procedure call:

public async Task<List<BookViewModel>> GetNLatestBooksV5(int numberBooks)
{
    SqlParameter nthLatestParam = new SqlParameter("latestNBooks", numberBooks);

    var latestNBooks = await _db.Books
        .FromSqlRaw("EXECUTE GetNLatestBooks @latestNBooks", nthLatestParam)
        .ToListAsync();
    return latestNBooks;
}

Using LINQ Extension Methods with Raw SQL

One of the rules we need to bear in mind when using raw SQL within an EF context is that only queries that begin with a SELECT can be composed with LINQ extension methods.

In this case a stored procedure call cannot be composed with LINQ extension methods.

The following SQL SELECT call is valid:

var latestNBooks = await _db.Books
    .FromSqlInterpolated($"SELECT * FROM SearchedBooks({query})")
    .OrderByDescending(b => b.DateCreated)
    .Take(numberBooks)
    .ToListAsync();

We have used additional LINQ extension methods Take() and OrderByDescending().

The following SQL SP call can only return a list after the call:

var latestNBooks = await _db.Books
    .FromSqlRaw("EXECUTE GetNLatestBooks @latestNBooks", nthLatestParam)
    .ToListAsync();

As we can see, it is possible to use raw SQL queries including stored procedure calls with EF Core DbSet collections. This makes it ideal when transitioning from an EF to EF Core ORM even though most of our application logic might still be embedded within SQL commands.

That is all for today’s post.

I hope you found this post useful and informative.

Social media & sharing icons powered by UltimatelySocial