SQL Injection Prevention

What is SQL Injection?

SQL Injection is a code injection technique where malicious SQL statements are inserted into queries.

Vulnerable Code (DO NOT USE)

// Vulnerable to SQL injection
public User GetUser(string username)
{
    string query = "SELECT * FROM Users WHERE Username = '" + username + "'";
    // Attacker could input: ' OR '1'='1
    // Result: SELECT * FROM Users WHERE Username = '' OR '1'='1'
}

Prevention Techniques

1. Parameterized Queries (ADO.NET)

public User GetUser(string username)
{
    string query = "SELECT * FROM Users WHERE Username = @Username";
    
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("@Username", username);
        // Safe from injection
    }
}

2. Entity Framework Core (Recommended)

public async Task<User> GetUserAsync(string username)
{
    // EF Core automatically parameterizes queries
    return await _context.Users
        .FirstOrDefaultAsync(u => u.Username == username);
}

3. Dapper

public User GetUser(string username)
{
    using (var conn = new SqlConnection(connectionString))
    {
        return conn.QueryFirstOrDefault<User>(
            "SELECT * FROM Users WHERE Username = @Username",
            new { Username = username });
    }
}

4. Stored Procedures

// Use parameterized stored procedures
public User GetUser(string username)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand("sp_GetUser", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Username", username);
        // Safe
    }
}

Input Validation

// Validate and sanitize input
public bool IsValidUsername(string username)
{
    // Check length
    if (string.IsNullOrWhiteSpace(username) || username.Length > 50)
        return false;
    
    // Check for invalid characters
    if (!Regex.IsMatch(username, "^[a-zA-Z0-9_]+$"))
        return false;
    
    return true;
}