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.
Table of Contents
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();
Warning: Avoid concatenating strings inside the stored procedure.
7. ORM-Specific Protections
var users = await _dbConnection.QueryAsync<User>(
"SELECT * FROM Users WHERE Username = @Username",
new { Username = username });
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";
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.