SQL Injection and Prevention in C#

SQL Injection is a common security vulnerability that occurs when an attacker manipulates SQL queries by injecting malicious input, potentially allowing unauthorized access to a database.

 

1. How SQL Injection Works

SQL injection occurs when user input is concatenated directly into SQL queries. Consider the following example:

string query = "SELECT * FROM Users WHERE Username = '" + username + "' AND Password = '" + password + "'";
SqlCommand cmd = new SqlCommand(query, conn);

If an attacker inputs:

username: ' OR '1'='1
password: anything

The query becomes:

SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = 'anything'

Since '1'='1' always evaluates to true, the query bypasses authentication.

 

2. Preventing SQL Injection in C#

 

2.1. Use Parameterized Queries (Recommended)

Using SqlCommand with parameters ensures that user input is treated as data, not SQL code.

Example:

string query = "SELECT * FROM Users WHERE Username = @username AND Password = @password";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.AddWithValue("@username", username);
    cmd.Parameters.AddWithValue("@password", password);
    SqlDataReader reader = cmd.ExecuteReader();
}

• Prevents SQL injection because input is treated as a value.
• Avoid .AddWithValue() for complex types (e.g., DateTime), as it may cause implicit conversion issues.

 

2.2. Use Stored Procedures

Stored procedures execute predefined SQL logic, reducing the risk of injection.

Example:

CREATE PROCEDURE ValidateUser
    @username NVARCHAR(50),
    @password NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Users WHERE Username = @username AND Password = @password
END

using (SqlCommand cmd = new SqlCommand("ValidateUser", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@username", username);
    cmd.Parameters.AddWithValue("@password", password);
    SqlDataReader reader = cmd.ExecuteReader();
}

 

2.3. Use ORM (Entity Framework, Dapper)

ORMs like Entity Framework (EF) and Dapper abstract direct SQL queries, making them safer.

 

Using Entity Framework (EF)

var user = dbContext.Users
    .FirstOrDefault(u => u.Username == username && u.Password == password);

EF generates parameterized queries automatically.

 

Using Dapper

string sql = "SELECT * FROM Users WHERE Username = @Username AND Password = @Password";
var user = connection.QueryFirstOrDefault<User>(sql, new { Username = username, Password = password });

 

2.4. Input Validation & Escaping

• Allowlist validation: Ensure input meets expected formats (e.g., alphanumeric for usernames).
• Escape special characters: If necessary, manually escape single quotes (' → '').

 

2.5. Principle of Least Privilege

• Avoid using sa (sysadmin) accounts for database access.
• Restrict user roles and deny direct access to sensitive tables.

 

2.6. Web Application Firewall (WAF)

Use a WAF to block suspicious requests containing SQL injection patterns.

 

3. Summary

Do it to prevent SQL Injection:

1. Use Parameterized Queries
2. Use Stored Procedures
3. Use ORMs like Entity Framework/Dapper
4. Validate user input

 

Don't:

1. Concatenate user input into SQL queries
2. Use dynamic SQL with string interpolation