Securing Dapper Queries in ASP.NET Core Against SQL Injection

Date- Apr 09,2026 0

dapper asp.net core

Overview

SQL Injection is a prevalent attack vector that allows attackers to execute arbitrary SQL code on a database, potentially compromising sensitive data and application functionality. It occurs when user input is improperly validated and directly concatenated into SQL queries, enabling malicious actors to manipulate queries and access, modify, or delete data. This vulnerability can lead to severe security breaches, data loss, and financial repercussions for organizations.

Dapper, a micro ORM (Object-Relational Mapper) for .NET, simplifies data access by providing a lightweight framework for executing SQL queries and mapping results to C# objects. However, just like any other data access technology, Dapper is not immune to SQL Injection if queries are not constructed securely. This article will explore how to use Dapper safely, emphasizing parameterized queries and other best practices to mitigate SQL Injection risks.

Prerequisites

C# knowledge: Familiarity with C# syntax and object-oriented programming.
ASP.NET Core experience: Understanding of ASP.NET Core web applications and middleware.
Dapper library: Basic knowledge of Dapper and how it interacts with databases.
SQL basics: Understanding of SQL syntax and database operations.
Entity Framework: Familiarity with ORM concepts, especially if transitioning from Entity Framework to Dapper.

Understanding SQL Injection

SQL Injection occurs when an application constructs SQL statements using unsanitized user input. Attackers can exploit this vulnerability by injecting malicious SQL code, which can lead to unauthorized access to data or even complete control over the database. The key to preventing SQL Injection lies in understanding how SQL queries are constructed and ensuring that user inputs are treated as data, not executable code.

The ramifications of SQL Injection attacks are extensive, affecting not only the integrity of the data but also the reputation of the organization and potential legal consequences. For example, a breach due to SQL Injection can lead to data leaks, requiring costly remediation efforts and potentially resulting in regulatory fines.

Real-World Use Cases

Organizations across various sectors, including finance, healthcare, and e-commerce, have fallen victim to SQL Injection attacks. For instance, a financial institution may expose sensitive customer information, leading to identity theft and fraud. An e-commerce site might suffer from unauthorized access to payment information, resulting in significant financial loss and damaged trust from customers. Understanding these implications underscores the importance of securing database queries.

using System.Data.SqlClient;
using Dapper;
using System.Collections.Generic;

public class UserRepository
{
    private readonly string _connectionString;

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<User> GetUsersByRole(string role)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            var sql = "SELECT * FROM Users WHERE Role = @Role";
            return connection.Query<User>(sql, new { Role = role });
        }
    }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Role { get; set; }
}

This code snippet illustrates a secure Dapper query using a parameterized SQL statement. The `UserRepository` class contains a method `GetUsersByRole` that takes a role as an argument and retrieves users based on that role. The SQL query uses a parameter placeholder (`@Role`), which prevents SQL Injection by ensuring that user input is treated as a parameter rather than executable SQL code.

Upon calling `GetUsersByRole` with a specific role, Dapper safely executes the query and returns a collection of `User` objects matching the specified role.

Parameterized Queries with Dapper

Parameterized queries are a fundamental mechanism for preventing SQL Injection. By using parameters, the database engine can differentiate between code and data, thus preventing execution of any injected SQL. Dapper supports parameterized queries natively, making it easy to implement this security measure.

When a parameterized query is executed, the database prepares the SQL statement with placeholders and binds the provided values to those placeholders. This process not only enhances security but also improves performance, as the database can cache the execution plan of the query.

public IEnumerable<User> GetUsersByIds(IEnumerable<int> userIds)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        var sql = "SELECT * FROM Users WHERE Id IN @UserIds";
        return connection.Query<User>(sql, new { UserIds = userIds });
    }
}

In this example, the method `GetUsersByIds` retrieves users based on a collection of IDs. The SQL uses the `IN` clause with a parameter. Dapper automatically handles the transformation of the collection into the appropriate SQL format, securely binding the values to the query.

The expected output is a list of `User` objects that match the specified IDs. Using parameterized queries in this way ensures that even if user input is malicious, it will not execute as SQL.

Named Parameters vs. Anonymous Parameters

Dapper supports both named and anonymous parameters. Named parameters are explicitly defined in the SQL string, while anonymous parameters use dynamic object initialization. Both approaches are safe against SQL Injection, but named parameters can enhance code readability.

public IEnumerable<User> GetUserByEmail(string email)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        var sql = "SELECT * FROM Users WHERE Email = @Email";
        return connection.Query<User>(sql, new { Email = email });
    }
}

This method demonstrates the use of a named parameter, enhancing clarity in the query. The SQL command clearly indicates that `@Email` is a parameter, making it easier to understand and maintain.

Using Stored Procedures with Dapper

Stored procedures are precompiled SQL statements stored in the database, providing an additional layer of security against SQL Injection. When using stored procedures, the SQL logic is encapsulated within the database, reducing the risk of SQL Injection from application-level queries.

Dapper allows for easy execution of stored procedures, enabling you to leverage their advantages while maintaining the lightweight nature of Dapper. By calling stored procedures, you can also enforce stricter access control at the database level.

public void CreateUser(User user)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        var sql = "CreateUser";
        var parameters = new
        {
            user.Name,
            user.Email,
            user.Role
        };
        connection.Execute(sql, parameters, commandType: CommandType.StoredProcedure);
    }
}

This method demonstrates how to create a new user using a stored procedure. The `CreateUser` stored procedure is called with parameters representing the user's details. The use of a stored procedure guards against SQL Injection by not exposing the underlying SQL logic to the application layer.

When this method is executed, a new user is created in the database, with the expected side effect of inserting a record into the `Users` table. The encapsulation of logic within the stored procedure further mitigates risks associated with SQL Injection.

Edge Cases & Gotchas

Even with secure coding practices, pitfalls can arise. One common edge case occurs when using dynamic SQL, which can inadvertently expose your application to SQL Injection. For instance, constructing SQL queries by concatenating strings based on user input can lead to vulnerabilities.

public IEnumerable<User> SearchUsers(string searchTerm)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        var sql = $"SELECT * FROM Users WHERE Name LIKE '%{searchTerm}%'"; // Vulnerable to SQL Injection
        return connection.Query<User>(sql);
    }
}

This code is vulnerable because it directly embeds `searchTerm` into the SQL query, allowing an attacker to manipulate the query. Instead, the query should use parameterization:

public IEnumerable<User> SearchUsers(string searchTerm)
{
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();
        var sql = "SELECT * FROM Users WHERE Name LIKE @SearchTerm";
        return connection.Query<User>(sql, new { SearchTerm = "%" + searchTerm + "%" });
    }
}

This corrected version safely uses parameterization to prevent SQL Injection while still allowing for flexible search functionality.

Performance & Best Practices

To ensure secure and performant applications, consider these best practices when using Dapper:

Always use parameterized queries: This is the most effective way to prevent SQL Injection.
Limit user input: Validate and sanitize all user inputs before processing them.
Use stored procedures when appropriate: Encapsulating SQL logic can enhance security and performance.
Monitor database access: Regularly audit database access and query performance to identify potential vulnerabilities.
Keep libraries updated: Regularly update Dapper and other dependencies to benefit from security patches and improvements.

Measuring Performance

When measuring the performance of Dapper versus other ORMs, it is important to consider execution speed and resource usage. Dapper is known for its high performance, particularly in scenarios involving large datasets or complex queries. Benchmarking Dapper against Entity Framework or raw ADO.NET can provide insights into performance benefits.

var stopwatch = new Stopwatch();
stopwatch.Start();
var users = repository.GetUsersByRole("Admin");
stopwatch.Stop();
Console.WriteLine($"Execution Time: {stopwatch.ElapsedMilliseconds} ms");

This code snippet measures the execution time of a Dapper query. By utilizing a `Stopwatch`, developers can identify performance bottlenecks and optimize queries accordingly.

Real-World Scenario

Imagine you are developing an ASP.NET Core web application for managing user accounts. In this scenario, the application needs to securely create, read, update, and delete user data while preventing SQL Injection attacks. Below is a complete implementation demonstrating these concepts:

using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Data.SqlClient;
using Dapper;

namespace UserManagement.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UsersController : ControllerBase
    {
        private readonly string _connectionString;

        public UsersController(string connectionString)
        {
            _connectionString = connectionString;
        }

        [HttpGet]
        public ActionResult<IEnumerable<User>> GetAllUsers()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                var sql = "SELECT * FROM Users";
                var users = connection.Query<User>(sql);
                return Ok(users);
            }
        }

        [HttpPost]
        public ActionResult CreateUser(User user)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                var sql = "CreateUser";
                var parameters = new
                {
                    user.Name,
                    user.Email,
                    user.Role
                };
                connection.Execute(sql, parameters, commandType: CommandType.StoredProcedure);
                return CreatedAtAction(nameof(GetAllUsers), new { id = user.Id }, user);
            }
        }
    }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Role { get; set; }
}

This ASP.NET Core controller provides a RESTful API for user management. The `GetAllUsers` method retrieves all users securely, while the `CreateUser` method demonstrates how to create a new user using a stored procedure. Both methods utilize Dapper to interact with the database securely.

When deployed, this application will allow safe management of user data, significantly reducing the risk of SQL Injection attacks while providing a robust and performant API.

Conclusion

SQL Injection is a critical vulnerability that can have severe consequences for applications.
Using Dapper with parameterized queries is an effective way to prevent SQL Injection.
Stored procedures can provide additional security and encapsulation of SQL logic.
Regularly validate and sanitize user inputs to further enhance security.
Monitor and measure performance to ensure optimal application behavior.