SQL Injection (SQLi)

SQL Injection (SQLi) is a security vulnerability that occurs when untrusted user input is directly concatenated or interpolated into a dynamic SQL query without proper sanitization or parameterization.

This allows an attacker to manipulate the query's structure, tricking the database management system (DBMS) into executing arbitrary, unauthorized SQL commands.

How SQL Injection Works

Consider a standard authentication query designed to validate a user's login credentials:

-- Vulnerable dynamic query logic
SELECT * FROM Users 
WHERE Username = 'USER_INPUT' AND Password = 'USER_INPUT';

If the application constructs this query by directly concatenating raw user input strings, an attacker can input crafted SQL syntax into the Username field:

• Attacker Input: admin' --

The resulting SQL query executed by the database becomes:

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

What Happened?

1. The single quote (') closes the string literal for the Username parameter early.
2. The sequence -- (or # in MySQL) comments out the remainder of the query, removing the Password check entirely.
3. The database authenticates the user as admin without validating the password.

Types of SQL Injection

SQL injection attacks are categorized based on how the attacker extracts data or interacts with the target server:

                          SQL INJECTION TYPES
                                   |
        +--------------------------+--------------------------+
        |                          |                          |
   In-Band (In-Band SQLi)    Inferential (Blind SQLi)    Out-of-Band (OOB)
        |                          |                          |
  +-----+-----+              +-----+-----+              +-----+-----+
  |           |              |           |              |           |
Error-Based  UNION-Based   Boolean-Based Time-Based    DNS/HTTP Exfiltration

1. In-Band SQLi (Direct)

The attacker uses the same communication channel to launch the attack and gather results.

• Error-Based SQLi: The attacker intentionally triggers database runtime errors (e.g., type conversion errors) to force the DBMS to output sensitive internal schema details or column values in the error message.
• UNION-Based SQLi: Exploits the UNION operator to combine the results of the original application query with a custom query designed to extract data from other database tables:

SELECT Name, Description FROM Products WHERE Category = 'Electronics'
UNION
SELECT Username, PasswordHash FROM Users;

2. Inferential SQLi (Blind)

The application does not display database data or SQL error messages on the screen. The attacker sends payloads and infers data character-by-character based on application behavior.

• Boolean-Based (Content-Based): The attacker injects logic that evaluates to TRUE or FALSE. By observing whether the HTTP response page changes (e.g., "Item Found" vs "Item Not Found"), the attacker reconstructs data:

SELECT * FROM Products WHERE ID = 1 AND SUBSTRING(username, 1, 1) = 'a';

• Time-Based: The attacker injects database commands that force the server to pause execution (e.g., pg_sleep(5), WAITFOR DELAY '0:0:5'). If the server delays responding, the injected condition is confirmed as true.

3. Out-of-Band (OOB) SQLi

Used when the attacker cannot view direct results and server responses are unstable. The attacker triggers DNS or HTTP requests from the database server directly to a server owned by the attacker, exfiltrating data via request payloads.

Impact of SQL Injection

A successful SQL Injection attack can result in severe consequences:

• Authentication Bypass: Gaining administrative access to applications without valid credentials.
• Data Exfiltration: Reading confidential data, financial records, or personal identifiable information (PII).
• Data Destruction & Modification: Modifying or permanently deleting records using UPDATE, INSERT, or DROP TABLE statements.
• Remote Code Execution (RCE): In misconfigured environments, database features (like xp_cmdshell in SQL Server or SELECT INTO OUTFILE in MySQL) can be exploited to execute shell commands directly on the host operating system.

Prevention & Mitigation Strategies

1. Parameterized Queries (Prepared Statements) — Primary Defense

Prepared statements enforce a strict separation between the SQL code structure and user-supplied data parameters. The database compiles the SQL query structure first, then treats all injected user inputs strictly as literal parameters—never executable code.

Example: Vulnerable vs. Secure Implementation

# ❌ VULNERABLE: String Interpolation / Concatenation
cursor.execute(f"SELECT * FROM Users WHERE Email = '{user_email}'")

# ✅ SECURE: Parameterized Query (Prepared Statement)
cursor.execute("SELECT * FROM Users WHERE Email = %s", (user_email,))

// ✅ SECURE Java (JDBC) Implementation
String query = "SELECT * FROM Users WHERE Username = ? AND Status = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, inputUsername);
stmt.setString(2, inputStatus);
ResultSet results = stmt.executeQuery();

2. Stored Procedures

While stored procedures parameterize input by default in most cases, they must not construct queries using dynamic string concatenation inside the procedure body (e.g., executing raw strings via EXEC() or EXECUTE IMMEDIATE).

3. Allow-listing Input (for Dynamic Identifiers)

Parameterized queries cannot parameterize database structural components such as table names, column names, or sort order (ASC/DESC). For these components, use explicit allow-lists:

# Securing dynamic column sorting
allowed_columns = {"username": "Username", "created": "CreatedDate"}
sort_column = allowed_columns.get(user_input, "Username") # Fallback default

query = f"SELECT UserID, Username FROM Users ORDER BY {sort_column} ASC"

4. Principle of Least Privilege

Database accounts used by web applications should only possess the minimum permissions necessary for their tasks:

• Applications should never connect to the database using sa, root, or postgres administrative accounts.
• Restrict DROP TABLE, ALTER TABLE, and system execution permissions for web application accounts.