SQL Injection Attacks - Understanding and Prevention | Colin Mackay

SQL injection remains one of the most critical and common web application vulnerabilities. Despite being well understood for decades, it continues to appear in vulnerability reports and security breaches. Understanding how SQL injection works is essential for any developer working with databases.

This article explains the mechanics of SQL injection attacks, demonstrates why certain code patterns are vulnerable, and provides proven prevention techniques with practical code examples.

What is SQL Injection?

SQL injection is a code injection technique where an attacker can execute malicious SQL statements by inserting them into an application's input fields. When applications construct SQL queries by concatenating user input directly into query strings, they become vulnerable to this attack.

The fundamental problem is a confusion between code and data. SQL queries are code that runs on a database server. When user-provided data is inserted directly into that code, the database cannot distinguish between the intended query structure and the attacker's injected commands.

The OWASP SQL Injection page provides additional background and attack variations beyond what's covered here.

How SQL Injection Works

Consider a simple login query:

string query = "SELECT * FROM Users WHERE Username = '" + username + "' AND Password = '" + password + "'";

With normal input like username = "alice" and password = "secret123", this produces:

SELECT * FROM Users WHERE Username = 'alice' AND Password = 'secret123'

Now consider what happens if an attacker enters:

username: admin' --
password: anything

The resulting query becomes:

SELECT * FROM Users WHERE Username = 'admin' --' AND Password = 'anything'

The -- sequence starts a SQL comment, causing everything after it to be ignored. The query now authenticates as "admin" without checking the password.

Why This Happens

The database receives a string and parses it as SQL. It has no way to know which parts came from the developer's code and which parts came from user input. The single quote (') that the attacker includes terminates the string literal early, and the following text becomes part of the SQL command structure.

Types of SQL Injection

In-Band SQL Injection

The most common type, where the attacker uses the same communication channel to launch the attack and gather results.

• Error-based: Intentionally triggering database errors that reveal information about the database structure
• UNION-based: Using UNION statements to append additional query results to the legitimate output

Blind SQL Injection

When the application doesn't display query results directly, attackers can still extract information through indirect means.

• Boolean-based: Crafting conditions that change the application's behaviour based on true/false results
• Time-based: Using database functions that cause delays to infer information bit by bit

Out-of-Band SQL Injection

Using different channels (like DNS or HTTP requests) to extract data, typically when other methods are blocked or impractical.

Prevention Techniques

1. Parameterised Queries (Prepared Statements)

The primary defence against SQL injection. Parameters are sent separately from the query structure, so the database can never confuse data with code.

This approach works because:

• The query structure is parsed and compiled first
• Parameter values are bound after compilation
• Values are always treated as data, never as SQL syntax

2. Stored Procedures (with Parameterisation)

Stored procedures can help by pre-defining queries, but only if they use parameters properly. A stored procedure that concatenates strings is just as vulnerable as application code that does the same.

3. Input Validation

Validate and sanitise input as a defence-in-depth measure, but never rely on it as the primary protection. Whitelist expected formats where possible (e.g., numeric IDs should only contain digits).

4. Principle of Least Privilege

Database accounts used by applications should have minimal necessary permissions. If an application only needs to read data, don't give it write or administrative access.

5. Web Application Firewalls (WAF)

WAFs can detect and block some SQL injection attempts, but they're a supplementary measure. Sophisticated attacks can often bypass them.

Code Examples

Vulnerable Code (C#)

// VULNERABLE - Do not use
string query = "SELECT * FROM Products WHERE Category = '" + userInput + "'";
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader reader = cmd.ExecuteReader();

Safe Code with Parameters (C#)

// SAFE - Use parameterised queries
string query = "SELECT * FROM Products WHERE Category = @Category";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@Category", userInput);
SqlDataReader reader = cmd.ExecuteReader();

Better: Using SqlParameter Explicitly

string query = "SELECT * FROM Products WHERE Category = @Category";
SqlCommand cmd = new SqlCommand(query, connection);

SqlParameter param = new SqlParameter("@Category", SqlDbType.NVarChar, 50);
param.Value = userInput;
cmd.Parameters.Add(param);

SqlDataReader reader = cmd.ExecuteReader();

Explicitly specifying the parameter type and size provides additional clarity and can prevent some edge cases.

Using Dapper (C#)

// Dapper handles parameterisation automatically
var products = connection.Query<Product>(
    "SELECT * FROM Products WHERE Category = @Category",
    new { Category = userInput }
);

Entity Framework

// LINQ queries are parameterised automatically
var products = context.Products
    .Where(p => p.Category == userInput)
    .ToList();

// Raw SQL in EF also supports parameters
var products = context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {userInput}")
    .ToList();

Stored Procedure Example (SQL Server)

CREATE PROCEDURE GetProductsByCategory
    @Category NVARCHAR(50)
AS
BEGIN
    SELECT ProductId, Name, Price 
    FROM Products 
    WHERE Category = @Category
END

// Calling from C#
SqlCommand cmd = new SqlCommand("GetProductsByCategory", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Category", userInput);

Detection and Testing

Manual Testing

Test input fields with common SQL injection payloads:

• Single quote: '
• Comment sequences: --, /*
• Boolean tests: ' OR '1'='1, ' OR '1'='2
• Time-based: '; WAITFOR DELAY '0:0:10'--

Automated Tools

• SQLMap: Open-source penetration testing tool for SQL injection
• OWASP ZAP: General-purpose web application security scanner
• Burp Suite: Commercial web security testing platform

Code Review

Look for patterns indicating vulnerable code:

• String concatenation in SQL queries
• String.Format or interpolation with SQL
• Dynamic SQL construction
• Stored procedures using EXEC with concatenation

Common Mistakes

Escaping Instead of Parameterising

Some developers try to escape special characters manually. This is error-prone and database-specific. Parameterised queries are simpler and more reliable.

Parameterising Only Some Inputs

All user input that reaches a SQL query must be parameterised, not just the "obvious" ones. Attackers will find the overlooked fields.

Dynamic Table/Column Names

Parameters work for values, not for identifiers (table names, column names). If you must use dynamic identifiers, whitelist them against a known list of valid values.

// Whitelist approach for dynamic column names
var allowedColumns = new[] { "Name", "Price", "Category" };
if (!allowedColumns.Contains(sortColumn))
{
    throw new ArgumentException("Invalid column name");
}
string query = $"SELECT * FROM Products ORDER BY {sortColumn}";

Trusting Client-Side Validation

Client-side validation improves user experience but provides no security. All validation must be repeated server-side.

Frequently Asked Questions