What is SQL injection and how do you prevent it?
4 minintermediatesecuritySQL-injectiondatabase
Quick Answer
SQL injection is an attack where untrusted input is concatenated into a SQL query, letting an attacker alter the query to read, modify, or destroy data or bypass authentication. Prevent it by never building SQL with string concatenation: use parameterized queries/commands, an ORM like EF Core (which parameterizes), and stored procedures with parameters. Add least-privilege database accounts and input validation as defense in depth.
Detailed Answer
SQL Injection is a code injection attack where malicious SQL statements are inserted into application queries, allowing attackers to manipulate database operations, access unauthorized data, or even destroy data.
Example of Vulnerable Code:
// VULNERABLE - Never do this!
string query = $"SELECT * FROM Users WHERE Username = '{username}' AND Password = '{password}'";
var result = context.Users.FromSqlRaw(query).ToList();
Prevention in .NET Core:
- Use Parameterized Queries (Preferred Method):
var result = context.Users
.FromSqlRaw("SELECT * FROM Users WHERE Username = {0} AND Password = {1}", username, password)
.ToList();
- Use LINQ and Entity Framework Core:
var user = context.Users
.Where(u => u.Username == username && u.Password == password)
.FirstOrDefault();
- Use Stored Procedures:
var user = context.Users
.FromSqlRaw("EXEC GetUserByCredentials @Username, @Password",
new SqlParameter("@Username", username),
new SqlParameter("@Password", password))
.FirstOrDefault();
- Input Validation:
public class LoginModel
{
[Required]
[StringLength(50, MinimumLength = 3)]
[RegularExpression(@"^[a-zA-Z0-9_]+$")]
public string Username { get; set; }
}