Preventing SQL Injection in ASP.NET Core APIs

SQL injection (SQLi) is one of the most common and dangerous security vulnerabilities in web applications. Attackers exploit improperly handled SQL queries to access, modify, or delete sensitive data. In ASP.NET Core APIs, even experienced developers can inadvertently introduce SQL injection if proper precautions are not taken.

This article explains what SQL injection is, how it happens in ASP.NET Core APIs, and how to prevent it using best practices, safe coding techniques, and modern libraries.

What is SQL Injection?
How SQL Injection Happens in ASP.NET Core
Risk Assessment and Common Scenarios
Using Parameterized Queries
Using Entity Framework Core Safely
Stored Procedures
ORM-Specific Protections
Validating and Sanitizing Inputs
Avoiding Dynamic SQL
Logging and Monitoring
Security Best Practices
Conclusion

1. What is SQL Injection?

SQL injection is a vulnerability where an attacker can manipulate a SQL query by inserting malicious input. This can allow the attacker to:

Retrieve unauthorized data.
Delete or update sensitive records.
Bypass authentication.
Execute administrative operations on the database.

For example, consider a naive query:

string query = $"SELECT * FROM Users WHERE Username = '{username}' AND Password = '{password}'";

If username or password contains malicious SQL code, the query could be manipulated to bypass authentication or retrieve all user data.

2. How SQL Injection Happens in ASP.NET Core

In ASP.NET Core APIs, SQL injection typically occurs when developers:

Concatenate user input directly into SQL queries.
Build dynamic SQL strings for filtering, sorting, or paging.
Use raw SQL without proper parameterization.
Expose endpoints that accept unchecked query parameters.

Example of Vulnerable API Endpoint

[HttpGet("get-user")]
public async Task<IActionResult> GetUser(string username)
{
    var query = $"SELECT * FROM Users WHERE Username = '{username}'";
    using (var command = new SqlCommand(query, _sqlConnection))
    {
        var reader = await command.ExecuteReaderAsync();
        // Process reader...
    }
    return Ok();
}

If username = "admin' OR 1=1 --", the query becomes:

SELECT * FROM Users WHERE Username = 'admin' OR 1=1 --'

This returns all users — classic SQL injection.

3. Risk Assessment and Common Scenarios

SQL injection risks are higher when:

Using legacy ADO.NET raw queries.
APIs accept multiple query parameters for filtering or search.
Application constructs SQL dynamically for reporting, analytics, or dashboards.

Common scenarios:

Authentication bypass
Data exfiltration
Mass updates/deletions
Privilege escalation

4. Using Parameterized Queries

The first and most effective defense is parameterized queries, which ensure that user input is treated as data, not SQL code.

Example with ADO.NET

[HttpGet("get-user")]
public async Task<IActionResult> GetUser(string username)
{
    string query = "SELECT * FROM Users WHERE Username = @username";

    using (var command = new SqlCommand(query, _sqlConnection))
    {
        command.Parameters.AddWithValue("@username", username);
        var reader = await command.ExecuteReaderAsync();
        // Process reader...
    }

    return Ok();
}

Benefits:

SQL Server treats @username as a parameter, not executable SQL.
Injection attempts are neutralized automatically.

5. Using Entity Framework Core Safely

Entity Framework Core (EF Core) abstracts SQL queries and reduces the risk of SQL injection if used correctly.

Safe Usage

[HttpGet("get-user-ef")]
public async Task<IActionResult> GetUserEF(string username)
{
    var user = await _dbContext.Users
        .Where(u => u.Username == username)
        .FirstOrDefaultAsync();

    return Ok(user);
}

EF Core parameterizes queries automatically, so manual string concatenation is not needed.

Unsafe Usage in EF Core

var users = await _dbContext.Users
    .FromSqlRaw($"SELECT * FROM Users WHERE Username = '{username}'")
    .ToListAsync();

FromSqlRaw with string interpolation is vulnerable. Always use parameters:

var users = await _dbContext.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Username = @username", 
        new SqlParameter("@username", username))
    .ToListAsync();

6. Stored Procedures

Stored procedures, if written properly, can prevent SQL injection. However, they must avoid dynamic SQL inside the procedure.

Example

CREATE PROCEDURE GetUserByUsername
    @Username NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Users WHERE Username = @Username
END

Call in ASP.NET Core:

var users = await _dbContext.Users
    .FromSqlInterpolated($"EXEC GetUserByUsername {username}")
    .ToListAsync();

EF Core handles parameterization.
Input cannot break the SQL structure.

Warning: Avoid concatenating strings inside the stored procedure.

7. ORM-Specific Protections

Dapper: Use parameterized queries via anonymous objects.

var users = await _dbConnection.QueryAsync<User>(
    "SELECT * FROM Users WHERE Username = @Username",
    new { Username = username });

EF Core: Use LINQ and FromSqlInterpolated.
Raw SQL: Always parameterize and avoid string interpolation.

8. Validating and Sanitizing Inputs

While parameterization is primary, input validation adds an extra layer of security:

Use Data Annotations or Fluent Validation for request DTOs.
Restrict lengths of string fields.
Validate expected patterns (emails, phone numbers, usernames).

Example DTO

public class LoginRequest
{
    [Required]
    [StringLength(50)]
    [RegularExpression("^[a-zA-Z0-9_.-]+$")]
    public string Username { get; set; }

    [Required]
    [StringLength(100)]
    public string Password { get; set; }
}

9. Avoiding Dynamic SQL

Dynamic SQL is the main source of injection vulnerabilities. Instead of building queries at runtime:

Use LINQ or ORM query builders.
Use stored procedures with parameters.
For reporting dashboards, generate dynamic queries safely using libraries that support parameters.

Bad Example

string query = $"SELECT * FROM Users WHERE {filterColumn} = '{filterValue}'";

Good Example

var validColumns = new[] { "Username", "Email", "Role" };
if (!validColumns.Contains(filterColumn))
    throw new ArgumentException("Invalid filter column");

var parameter = new SqlParameter("@value", filterValue);
var query = $"SELECT * FROM Users WHERE {filterColumn} = @value";

Validate column names from a whitelist.
Only parameterize values, not SQL keywords.

10. Logging and Monitoring

Detecting potential injection attempts is crucial:

Log failed queries and unusual patterns.
Use tools like Serilog or NLog with structured logging.
Monitor for repeated injection patterns or unexpected query errors.

11. Security Best Practices

Principle of Least Privilege: Use database users with minimal permissions.
Avoid Admin Accounts: Never connect API using sa or admin accounts.
Use ORM Where Possible: Reduces manual query construction.
Parameterize Everything: Never concatenate strings in SQL.
Validate Inputs: Ensure user data conforms to expected formats.
Keep Libraries Updated: EF Core, Dapper, and SQL Server drivers often fix vulnerabilities.
Penetration Testing: Test APIs with tools like SQLMap or custom scripts.

Conclusion

Preventing SQL injection in ASP.NET Core APIs is essential for production-grade applications. Senior developers should focus on:

Parameterization over string concatenation.
Safe ORM usage (EF Core, Dapper).
Avoiding dynamic SQL whenever possible.
Input validation and sanitization.
Monitoring and logging suspicious activity.

By following these best practices, you can significantly reduce the risk of SQL injection and make your APIs secure, maintainable, and production-ready.

Key Takeaways

SQL injection is entirely preventable with proper coding practices.
EF Core, Dapper, and parameterized ADO.NET queries are your first defense.
Dynamic SQL should be avoided or heavily validated.
Security requires ongoing vigilance, including input validation, monitoring, and least-privilege database access.

Table of Contents