.NET SQL Injection Prevention

Introduction

SQL Injection is one of the most dangerous and common web application vulnerabilities. It occurs when untrusted user input is directly incorporated into SQL queries without proper validation or sanitization. In this article, we'll explore what SQL injection is, why it's dangerous, and how to effectively prevent it in .NET applications.

SQL injection attacks can lead to unauthorized data access, data manipulation, and even complete system compromise. For .NET developers, understanding how to prevent these vulnerabilities is essential for building secure applications.

What is SQL Injection?

SQL injection happens when an attacker can insert or "inject" malicious SQL code into queries that your application sends to its database. When successful, attackers can:

Access sensitive data they shouldn't be able to see
Modify database data (insert, update, delete)
Execute administrative operations on the database
Recover the contents of files on the database server
In some cases, issue commands to the operating system

A Simple Example of SQL Injection

Consider this vulnerable C# code:

csharp
string username = Request.QueryString["username"];
string query = "SELECT * FROM Users WHERE Username = '" + username + "'";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(query, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    // Process the results
}

If a user provides the input: admin' OR 1=1--, the resulting query becomes:

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

The -- starts a comment in SQL, effectively ignoring the rest of the intended query. The condition 1=1 is always true, so this query returns all users in the database instead of just validating a single user.

Prevention Techniques in .NET

1. Parameterized Queries / Prepared Statements

The most effective way to prevent SQL injection is to use parameterized queries. In .NET, this means using parameters with SqlCommand objects:

csharp
string username = Request.QueryString["username"];

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT * FROM Users WHERE Username = @Username";
    SqlCommand command = new SqlCommand(query, connection);
    
    // Add parameter and set its value
    command.Parameters.AddWithValue("@Username", username);
    
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    // Process the results
}

With parameterized queries, the database engine treats the parameter values as data, not as part of the SQL command, effectively preventing injection attacks.

2. Using ORM Frameworks

Object-Relational Mapping (ORM) frameworks like Entity Framework Core abstract away the direct SQL handling and typically use parameterized queries behind the scenes:

csharp
using (var context = new ApplicationDbContext())
{
    var user = context.Users
        .Where(u => u.Username == username)
        .FirstOrDefault();
        
    // Process the user
}

However, be cautious with raw SQL in ORMs. If you need to use raw SQL, ensure you use parameterization:

csharp
// Entity Framework Core raw SQL with parameters
var users = context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Username = {0}", username)
    .ToList();

3. Stored Procedures

Stored procedures can also reduce the risk of SQL injection when properly implemented:

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand("GetUserByUsername", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Username", username);
    
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    // Process the results
}

4. Input Validation

While not a substitute for parameterized queries, input validation adds an additional layer of security:

csharp
// Simple validation example
if (string.IsNullOrWhiteSpace(username) || username.Length > 50 || 
    !Regex.IsMatch(username, @"^[a-zA-Z0-9_]+$"))
{
    // Reject the input
    return BadRequest("Invalid username format");
}

// Then proceed with parameterized query

5. Least Privilege Principle

Ensure your database connection uses accounts with the minimum required permissions:

csharp
// Connection string with a limited-privilege account
string connectionString = @"Data Source=ServerName;
                          Initial Catalog=DatabaseName;
                          User ID=ReadOnlyUser;
                          Password=SecurePassword;";

Real-world Example: Safe Registration Form

Let's build a simple but secure user registration form that prevents SQL injection:

csharp
public class UserController : Controller
{
    private readonly string _connectionString;
    
    public UserController(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }
    
    [HttpPost]
    public IActionResult Register(RegisterViewModel model)
    {
        if (!ModelState.IsValid)
        {
            return View(model);
        }
        
        // Input validation
        if (string.IsNullOrWhiteSpace(model.Username) || 
            !Regex.IsMatch(model.Username, @"^[a-zA-Z0-9_]{3,50}$"))
        {
            ModelState.AddModelError("Username", "Invalid username format");
            return View(model);
        }
        
        try
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // Check if user exists
                string checkQuery = "SELECT COUNT(*) FROM Users WHERE Username = @Username";
                SqlCommand checkCommand = new SqlCommand(checkQuery, connection);
                checkCommand.Parameters.AddWithValue("@Username", model.Username);
                
                connection.Open();
                int userCount = (int)checkCommand.ExecuteScalar();
                
                if (userCount > 0)
                {
                    ModelState.AddModelError("Username", "Username already exists");
                    return View(model);
                }
                
                // Hash the password (NEVER store plain text passwords)
                string passwordHash = HashPassword(model.Password);
                
                // Insert new user
                string insertQuery = @"
                    INSERT INTO Users (Username, PasswordHash, Email, CreatedDate) 
                    VALUES (@Username, @PasswordHash, @Email, @CreatedDate)";
                
                SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
                insertCommand.Parameters.AddWithValue("@Username", model.Username);
                insertCommand.Parameters.AddWithValue("@PasswordHash", passwordHash);
                insertCommand.Parameters.AddWithValue("@Email", model.Email);
                insertCommand.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow);
                
                insertCommand.ExecuteNonQuery();
                
                return RedirectToAction("Login");
            }
        }
        catch (Exception ex)
        {
            // Log the exception
            ModelState.AddModelError("", "An error occurred during registration");
            return View(model);
        }
    }
    
    private string HashPassword(string password)
    {
        // Use a secure hashing algorithm like BCrypt
        return BCrypt.Net.BCrypt.HashPassword(password);
    }
}

Entity Framework Core Approach

Here's the same functionality implemented with Entity Framework Core:

csharp
public class UserController : Controller
{
    private readonly ApplicationDbContext _context;
    
    public UserController(ApplicationDbContext context)
    {
        _context = context;
    }
    
    [HttpPost]
    public async Task<IActionResult> Register(RegisterViewModel model)
    {
        if (!ModelState.IsValid)
        {
            return View(model);
        }
        
        // Input validation
        if (string.IsNullOrWhiteSpace(model.Username) || 
            !Regex.IsMatch(model.Username, @"^[a-zA-Z0-9_]{3,50}$"))
        {
            ModelState.AddModelError("Username", "Invalid username format");
            return View(model);
        }
        
        try
        {
            // Check if user exists
            bool userExists = await _context.Users.AnyAsync(u => u.Username == model.Username);
            
            if (userExists)
            {
                ModelState.AddModelError("Username", "Username already exists");
                return View(model);
            }
            
            // Hash the password
            string passwordHash = HashPassword(model.Password);
            
            // Create and save new user
            var user = new User
            {
                Username = model.Username,
                PasswordHash = passwordHash,
                Email = model.Email,
                CreatedDate = DateTime.UtcNow
            };
            
            _context.Users.Add(user);
            await _context.SaveChangesAsync();
            
            return RedirectToAction("Login");
        }
        catch (Exception ex)
        {
            // Log the exception
            ModelState.AddModelError("", "An error occurred during registration");
            return View(model);
        }
    }
    
    private string HashPassword(string password)
    {
        // Use a secure hashing algorithm like BCrypt
        return BCrypt.Net.BCrypt.HashPassword(password);
    }
}

Common Mistakes to Avoid

String concatenation for SQL queries: Never build SQL queries by concatenating strings.

csharp
// DON'T DO THIS
string query = "SELECT * FROM Users WHERE Username = '" + username + "'";

Using SqlParameter with ExecuteScalar, ExecuteReader, or ExecuteNonQuery but forgetting it with other methods:

csharp
// DON'T DO THIS
string query = "SELECT * FROM Users WHERE Username LIKE '%" + searchTerm + "%'";

Instead, use parameterized queries even for LIKE clauses:

csharp
// DO THIS
string query = "SELECT * FROM Users WHERE Username LIKE @SearchTerm";
command.Parameters.AddWithValue("@SearchTerm", "%" + searchTerm + "%");

Trusting input validation alone: Even with input validation, always use parameterized queries.
Disabling security features: Never disable features like parameterization or input validation for convenience.

Testing for SQL Injection

It's essential to test your application for SQL injection vulnerabilities:

Manual testing: Try basic SQL injection patterns in form fields, URLs, and other inputs.
Automated testing: Use tools like OWASP ZAP or SQLMap to scan your application.
Code reviews: Regularly review code for unsafe SQL practices.

Here's a simple test case example:

csharp
[Fact]
public void UserRepository_GetUserByUsername_UsesParameterizedQuery()
{
    // Arrange
    var mockConnection = new Mock<IDbConnection>();
    var mockCommand = new Mock<IDbCommand>();
    var mockParameter = new Mock<IDbParameter>();
    
    mockConnection.Setup(c => c.CreateCommand()).Returns(mockCommand.Object);
    mockCommand.Setup(c => c.CreateParameter()).Returns(mockParameter.Object);
    
    var repository = new UserRepository(mockConnection.Object);
    
    // Act
    repository.GetUserByUsername("test' OR 1=1--");
    
    // Assert
    mockCommand.VerifySet(c => c.CommandText = It.Is<string>(s => s.Contains("@Username")));
    mockParameter.VerifySet(p => p.ParameterName = "@Username");
    mockParameter.VerifySet(p => p.Value = "test' OR 1=1--");
}

Summary

SQL injection remains one of the most dangerous vulnerabilities in web applications. In .NET applications, you can protect against it by:

Using parameterized queries - Always use SqlParameter objects or ORM equivalents
Implementing proper input validation - Validate and sanitize all user inputs
Employing ORMs like Entity Framework - Let proven frameworks handle SQL generation
Utilizing stored procedures - When properly implemented with parameters
Following the principle of least privilege - Use database accounts with minimal permissions
Regularly testing your applications - For SQL injection vulnerabilities

By following these practices, you can significantly reduce the risk of SQL injection attacks in your .NET applications and keep your users' data safe and secure.

Additional Resources

Exercises

Convert this vulnerable code to use parameterized queries:

csharp
string id = Request.QueryString["id"];
string query = "SELECT * FROM Products WHERE ProductID = " + id;

Write a secure data access method that retrieves orders by customer ID using Entity Framework Core.
Create a simple login form that securely validates user credentials against a database.
Implement a search functionality that allows users to search products by name without exposing the application to SQL injection.
Review an existing project for potential SQL injection vulnerabilities and document how you would fix them.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

What is SQL Injection?​

A Simple Example of SQL Injection​

Prevention Techniques in .NET​

1. Parameterized Queries / Prepared Statements​

2. Using ORM Frameworks​

3. Stored Procedures​

4. Input Validation​

5. Least Privilege Principle​

Real-world Example: Safe Registration Form​

Entity Framework Core Approach​

Common Mistakes to Avoid​

Testing for SQL Injection​

Summary​

Additional Resources​

Exercises​