​
SQL Injection: A Comprehensive Guide

SQL Injection is a security vulnerability that occurs when an attacker can manipulate an SQL query by injecting malicious SQL code. This can lead to unauthorized access to data, data corruption, or even complete control over the database. Understanding and preventing SQL injection is crucial for building secure applications.

​
Key Concepts of SQL Injection

  1. How SQL Injection Works:
    • Attackers exploit vulnerabilities in input validation to inject malicious SQL code.
    • This can happen when user input is directly concatenated into SQL queries.
  2. Common Attack Scenarios:
    • Bypassing Authentication: Injecting SQL to bypass login screens.
    • Data Extraction: Injecting SQL to retrieve sensitive data.
    • Data Manipulation: Injecting SQL to modify or delete data.
    • Database Takeover: Injecting SQL to execute system commands.
  3. Prevention Techniques:
    • Use parameterized queries or prepared statements.
    • Validate and sanitize user input.
    • Use stored procedures with proper input validation.
    • Apply the principle of least privilege to database accounts.

​
Example of SQL Injection

​
Vulnerable Query

Consider a login form where the username and password are directly concatenated into an SQL query: 
SELECT * FROM Users WHERE Username = 'user' AND Password = 'pass';
If an attacker enters ' OR '1'='1 as the username and password, the query becomes: 
SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = '' OR '1'='1';
This query will return all rows in the Users table, effectively bypassing authentication.

​
Example 1: Bypassing Authentication

​
Vulnerable Code

DECLARE @Username NVARCHAR(50) = 'admin';
DECLARE @Password NVARCHAR(50) = 'password';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Users WHERE Username = ''' + @Username + ''' AND Password = ''' + @Password + '''';
EXEC sp_executesql @SQL;

​
Attack

If the attacker provides the following input:
  • Username: ' OR '1'='1
  • Password: ' OR '1'='1
The query becomes: 
SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = '' OR '1'='1';
This query will return all rows, allowing the attacker to bypass authentication.

​
Example 2: Data Extraction

​
Vulnerable Code

DECLARE @UserId NVARCHAR(50) = '1';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Users WHERE UserId = ' + @UserId;
EXEC sp_executesql @SQL;

​
Attack

If the attacker provides the following input:
  • UserId: 1; DROP TABLE Users; --
The query becomes: 
SELECT * FROM Users WHERE UserId = 1; DROP TABLE Users; --';
This query will drop the Users table, causing data loss.

​
Preventing SQL Injection

​
1. Parameterized Queries

Use parameterized queries to separate SQL code from user input. 
DECLARE @Username NVARCHAR(50) = 'admin';
DECLARE @Password NVARCHAR(50) = 'password';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Users WHERE Username = @Username AND Password = @Password';
EXEC sp_executesql @SQL, N'@Username NVARCHAR(50), @Password NVARCHAR(50)', @Username, @Password;
Explanation:
  • The user input is passed as parameters, preventing SQL injection.

​
2. Stored Procedures

Use stored procedures with parameterized inputs. 
CREATE PROCEDURE AuthenticateUser
    @Username NVARCHAR(50),
    @Password NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Users WHERE Username = @Username AND Password = @Password;
END;

-- Execute the stored procedure
EXEC AuthenticateUser @Username = 'admin', @Password = 'password';
Explanation:
  • The stored procedure ensures that user input is treated as parameters, not executable code.

​
3. Input Validation and Sanitization

Validate and sanitize user input to ensure it conforms to expected formats. 
DECLARE @UserId INT;

-- Validate that @UserId is an integer
IF ISNUMERIC(@UserId) = 1
BEGIN
    SET @UserId = CAST(@UserId AS INT);
    DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Users WHERE UserId = @UserId';
    EXEC sp_executesql @SQL, N'@UserId INT', @UserId;
END
ELSE
BEGIN
    PRINT 'Invalid UserId';
END
Explanation:
  • The input is validated to ensure it is a numeric value before being used in the query.

​
4. Least Privilege Principle

Grant database accounts the minimum permissions necessary to perform their tasks. 
-- Create a restricted user with limited permissions
CREATE USER RestrictedUser WITHOUT LOGIN;
GRANT SELECT ON Users TO RestrictedUser;
Explanation:
  • The RestrictedUser can only perform SELECT operations on the Users table, reducing the impact of a potential SQL injection attack.

​
5. Use ORM Frameworks

Use Object-Relational Mapping (ORM) frameworks like Entity Framework, which automatically parameterize queries. 
var user = context.Users
    .Where(u => u.Username == username && u.Password == password)
    .FirstOrDefault();
Explanation:
  • ORM frameworks generate parameterized queries, reducing the risk of SQL injection.

​
Key Takeaways

  1. SQL Injection occurs when attackers inject malicious SQL code into queries.
  2. Prevent SQL Injection by:
    • Using parameterized queries or prepared statements.
    • Validating and sanitizing user input.
    • Using stored procedures with proper input validation.
    • Applying the least privilege principle to database accounts.
  3. ORM frameworks can help automate the prevention of SQL injection.