Prevent SQL Injection With EF Core, Dapper, and ADO.NET

SQL Injection is a severe security vulnerability that can harm applications that use databases. In this article, we’ll learn about SQL Injection attacks, what they can do, and how to protect our applications from them. We’ll focus on using popular ORM tools like EF Core, Dapper, and ADO.NET to make our applications safer.

Let’s begin.

What Is SQL Injection?

SQL Injection is a type of cyber attack where attackers exploit weaknesses in an application’s input validation and handling of user data. They can inject additional conditions or commands into the SQL queries and manipulate the original query’s behavior.

To understand the attack, let’s create a simple login SQL query:

SELECT * FROM Users WHERE Username='" + username + "' AND Password='" + password + "'

Here, we validate the user based on the username and password and return any record with the given credentials. However, an attacker can pass malicious values here:

var username = "' OR 1=1 --";

Now, the OR 1=1 condition always evaluates to true, effectively bypassing the password check and granting unauthorized access. Thus, the attacker can potentially expose sensitive information.

In the same example, the attacker can also completely compromise the application:

var username = "'; DROP TABLE Users;--";

This command when run results in permanent loss of the “Users” table.

Thus, SQL Injection attacks can have grave impacts on our applications leading to sensitive data disclosure, unauthorized access, and compromise of the application. However, we have various Object-Relational Mapping (ORM) tools EF Core, Dapper, and ADO.NET that offer built-in mechanisms to prevent SQL Injection attacks. 

Let’s look at each of these tools and how they help us prevent SQL Injection attacks. However, before we dive further, let’s set up our application.

Application Setup

Let’s create a simple ASP.Net Core Web application that deals with books. The application will allow us to show all books present in the database, search for an existing book by its title, and add a new book to the database.

To start, let’s navigate to the folder we want our application and run the dotnet new webapp command:

dotnet new webapp -n PreventSQLInjectionAttacks

This creates a new .NET web application using Razor Pages.

Nuget Packages Installation

We’ll use SQLite for our database. So, once the application creation is complete, let’s install the required packages.

In Visual Studio, we can open the NuGet Package Manager Console by navigating to “Tools > NuGet Package Manager > Package Manager Console.”

Here, let’s install the Entity Framework SQLite package and the tools package to enable migrations:

Install-Package Microsoft.EntityFrameworkCore.Sqlite
Install-Package Microsoft.EntityFrameworkCore.Tools

Model Class Creation

Next, let’s create our Book model class:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Author { get; set; } = string.Empty;
}

Here, we create a simple class that represents how a Book record would look in our database with Id, Title, and Author columns.

Database Context Configuration

Let’s create a class DataContext:

public class DataContext : DbContext
{
    public DataContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Book> Books { get; set; }
}

This class derives from DbContext provided by Entity Framework. The DbContextOptions injection in the constructor allows us to configure the database connection. The Books property represents the “Books” table in the database.

In the Main() method of the Program class, while configuring services we can make sure this DataContext instance is available throughout the application using ASP.Net Core’s dependency injection:

builder.Services.AddDbContext<DataContext>(options =>
{
    options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnectionString"));
});

Finally, we need to create and apply initial migrations in Package Manager Console:

Add-Migration InitialCreate
Update-Database

With these steps completed, we now have an ASP.Net Core Web application. Next, let’s look at how to prevent SQL Injection attacks.

SQL Injection Attack Prevention With Entity Framework Core

Entity Framework Core (EF Core) is an Object-Relational Mapping (ORM) framework from Microsoft. It serves as a bridge between our code and the relational database.

It supports various database providers like SQL Server, SQLite, PostgreSQL, etc. With EF Core, we can define data models as C# classes, and automatically map them to database tables. This allows us easy schema changes in code-first migrations.

EF Core provides us with mechanisms to avoid SQL Injection attacks. However, we need to be aware of the best practices and make use of features such as LINQ query syntax and parametrized queries.

We should minimize the use of raw SQL queries with the FromSqlRaw() method:

private IList<Book> SearchBooksUsingTitleEFCoreSqlQuery(string title)
{
    return _dataContext.Books.FromSqlRaw($"SELECT * FROM Books WHERE Title LIKE '%{title}%'").ToList();
}

Here, we introduce a SQL Injection vulnerability due to the title parameter not being properly sanitized.

Instead, one of the most effective ways to prevent SQL Injection attacks is to use parametrized queries. Here, instead of directly injecting literals into the SQL query as we saw in the vulnerable examples earlier, EF Core parametrizes queries internally when using the LINQ query syntax:

private IList<Book> SearchBooksUsingTitleEFCore(string title)
{
    return _dataContext.Books.Where(b => b.Title.Contains(title)).ToList();
}

When EF Core translates this LINQ query into SQL, it will parameterize the title parameter:

SELECT * FROM Books WHERE Author = @param

While executing the query, the @param variable is replaced by the actual value of title thus preventing SQL Injection attacks.

Now that we’ve explored SQL Injection attack prevention using Entity Framework Core, let’s delve into how we can keep our application safe when using Dapper.

SQL Injection Attack Prevention With Dapper

Dapper is a lightweight and efficient micro-ORM that allows us to interact with databases using raw SQL queries or stored procedures. Similar to EF Core, Dapper also provides mechanisms to defend against SQL Injection attacks.

Like EF Core, Dapper supports parameterized queries. We should prefer parameterized queries when executing raw SQL with Dapper:

private IList<Book> SearchBooksUsingTitleDapper(string title)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var query = "SELECT * FROM Books WHERE Title LIKE @SearchTerm";
    return connection.Query<Book>(query, new { SearchTerm = $"%{title}%" }).ToList();
}

Here, @SearchTerm is safely handled as a parameter. This ensures that user inputs are treated as parameters rather than directly concatenated into the SQL statement.

The Query() method is a query execution helper method available in Dapper. It helps us execute a SELECT statement and retrieve multiple records.

There are other query execution helper methods like QueryFirstOrDefault() to return only the first row in case of multiple records and Execute() to execute SQL statements that return no data.

Now, let’s look into how to prevent SQL Injection attacks with ADO.NET.

SQL Injection Attack Prevention With ADO.NET

ActiveX Data Objects .NET (ADO.NET) is a lower-level data access framework. Similar to Entity Framework, it serves as a bridge between the code and the database.

It allows us to use parametrized queries to handle user inputs. This prevents attackers from manipulating the SQL queries to introduce vulnerabilities.

Let’s implement the same method to search books by title using ADO.NET:

private IList<Book> SearchBooksUsingTitleADO(string title)
{
    using var connection = new SqliteConnection(_connectionString);
    connection.Open();

    var command = new SqliteCommand("SELECT * FROM Books WHERE Title LIKE @SearchTerm", connection);
    command.Parameters.Add(new SqliteParameter("@SearchTerm", DbType.String) { Value = $"%{title}%" });

    var books = new List<Book>();

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var book = new Book
            {
                Id = reader.GetInt32(0),
                Title = reader.GetString(1),
                Author = reader.GetString(2)
            };

            books.Add(book);
        }
    }

    return books;
}

Here, we query the Books table to find books with titles containing the title parameter.

Then, we populate a list of Book objects with the results. It uses parametrized queries thus making our database interactions more robust and less vulnerable to risks.

Thus, leveraging parametrized queries is one of the fundamental practices to prevent SQL Injection attacks in our application. 

Best Practices to Avoid SQL Injection Attacks

Let’s take a look at some of the other best practices to make our application more secure and avoid SQL vulnerabilities.

Stored Procedures

Using stored procedures allows us another layer of security. They encapsulate the database logic and we can take care of input sanitization before executing SQL statements.

Additionally, stored procedures inherently use parametrized queries. Thus, user inputs are never embedded directly in the SQL query and it helps us avoid SQL Injection attacks.

Input Sanitization

We should validate and sanitize user inputs before adding them to queries. For example, we can use the Trim() method to remove any unnecessary whitespace around the input parameter.

It’s also important to validate the user input and reject any malicious attempt before it reaches the point of database interaction. This prevents any unsafe code from reaching the database layer, thus preventing any SQL Injection attack attempt.

Least Privilege Principle

The Least Privilege Principle is a security concept where we assign each user only the minimum privileges necessary to complete a task. This could include restricted data access, restrictions against schema manipulation, or not allowing commands that can cause the highest damage like DROP or TRUNCATE.

This makes sure that even if there is a successful SQL Injection attempt, the attacker is unable to completely compromise our application. Thus, we can limit the potential damage using the least privilege principle. However, it is important to not rely on this as our sole security practice. Instead, we should complement it with other security practices.

Conclusion

In this article, we learned about SQL Injection attacks, and how we can use different security policies using multiple ORMs such as EF Core, Dapper, and ADO.NET to prevent them. Also, we looked into general best practices to make our database interactions robust and minimize security vulnerabilities.