SQL Injection: Union, Blind, Time-Based

SQL injection (SQLi) is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is one of the most common web application vulnerabilities, consistently ranking in the OWASP Top 10.

The root cause is the failure to separate data from commands. When an application constructs SQL queries by concatenating user-supplied input directly into the query string, an attacker can inject malicious SQL code that alters the query's intended logic. For example, a login form might execute:

SELECT * FROM users WHERE username = 'admin' AND password = 'password';

If the application takes the username and password fields and concatenates them without sanitization, an attacker can input ' OR '1'='1 as both fields, resulting in:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1';

This query returns all rows because '1'='1' is always true, bypassing authentication.

SQL injection exploits the syntax and structure of SQL. The attacker's goal is to break out of the intended data context and inject SQL keywords. The process typically involves:

Union-based SQL injection leverages the SQL UNION operator to combine the results of the original query with results from an attacker-controlled query. The key requirement is that the number of columns and data types in both queries must match.

Step-by-step mechanism:

1. Determine the number of columns: Use ORDER BY or UNION SELECT NULL to find the column count. For example:

' ORDER BY 1 --
   ' ORDER BY 2 --
   ... until an error occurs.

Or:

' UNION SELECT NULL --
   ' UNION SELECT NULL,NULL --
   ...

The number of NULLs that works without error indicates the column count.

2. Find the output columns: Determine which columns are displayed on the page. Inject:

' UNION SELECT 'a','b','c' --

Look for the letters 'a', 'b', 'c' in the output. Those columns are usable for data extraction.

3. Extract data: Replace the letters with database functions or subqueries. For MySQL:

' UNION SELECT database(), user(), version() --

This returns the current database name, database user, and version.

Limitations: Union-based injection requires the application to display the results of the query. If the application does not output the data (e.g., only returns a success/failure message), blind techniques are needed.

Blind SQL injection occurs when the application does not display the results of the injected query but still behaves differently based on whether the query returns true or false. The attacker can infer information by asking yes/no questions.

How it works: The attacker injects a condition that causes the application to respond differently (e.g., different page content, HTTP status code, or response time). For example:

' OR (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'a' --

If the first character of the admin password is 'a', the condition is true, and the page loads normally. If false, the page might show an error or no results.

Common techniques: - Substring extraction: Use SUBSTRING() or MID() to extract one character at a time. - Binary search: Instead of testing each character individually, use comparisons like ASCII(SUBSTRING(...)) > 64 to narrow down the character set faster. - Conditional errors: Some databases allow conditional errors (e.g., IF(condition, (SELECT 1/0), 'false')). If the condition is true, the division by zero causes an error; if false, no error.

Limitations: Boolean-based blind injection is slow because each character requires many requests. Automation tools like sqlmap are often used.

Time-based blind SQL injection is used when the application does not provide any visible difference between true and false responses. The attacker uses database functions that cause a delay (e.g., SLEEP(), WAITFOR DELAY) to infer the truth of a condition.

Mechanism: The attacker injects a conditional delay:

' OR IF((SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'a', SLEEP(5), 0) --

If the condition is true, the database sleeps for 5 seconds before responding. The attacker observes the response time. If the page takes ~5 seconds longer than normal, the condition is true; otherwise, false.

Database-specific functions: - MySQL: SLEEP(seconds) - MSSQL: WAITFOR DELAY '0:0:5' - Oracle: DBMS_LOCK.SLEEP(5) (requires privileges) or DBMS_PIPE.RECEIVE_MESSAGE('pipe', 5) - PostgreSQL: pg_sleep(5)

Limitations: Time-based injection is even slower than Boolean-based because each request takes several seconds. Network latency must be accounted for; a 5-second delay might be indistinguishable from a 4-second delay if the baseline is unstable.

- MySQL: -- (note space after double dash) or # - MSSQL: -- - Oracle: -- - String concatenation:

- MySQL: CONCAT('a','b') or 'a' 'b' (space concatenation) - MSSQL: 'a'+'b' - Oracle: 'a'||'b' - Database version functions:

- MySQL: @@version or VERSION() - MSSQL: @@VERSION - Oracle: SELECT banner FROM v$version - Current database:

- MySQL: DATABASE() - MSSQL: DB_NAME() - Oracle: SYS_CONTEXT('USERENV','DB_NAME') - Current user:

- MySQL: USER() or CURRENT_USER() - MSSQL: SYSTEM_USER or CURRENT_USER - Oracle: USER or SYS_CONTEXT('USERENV','SESSION_USER')

Manual testing with curl:

curl 'http://example.com/page?id=1'
curl 'http://example.com/page?id=1''  # Check for error
curl 'http://example.com/page?id=1' OR '1'='1'  # Check for true condition

Using sqlmap:

sqlmap -u 'http://example.com/page?id=1' --batch
sqlmap -u 'http://example.com/page?id=1' --dbs
sqlmap -u 'http://example.com/page?id=1' -D database --tables
sqlmap -u 'http://example.com/page?id=1' -D database -T users --dump

Verification of vulnerability: If injecting ' OR '1'='1 returns more results than expected, the parameter is vulnerable.

In a real-world penetration test, SQL injection vulnerabilities are often found in legacy applications or custom-built web interfaces that lack proper input validation. One common scenario is an e-commerce website with a product search feature. The search parameter is passed directly into a SQL query: SELECT * FROM products WHERE name LIKE '%' + userInput + '%'. An attacker can inject ' UNION SELECT creditCardNumber, expiry, cvv FROM payments -- to retrieve payment data. In production, this vulnerability could lead to massive data breaches, as seen in the 2017 Equifax breach where a SQL injection in a dispute portal exposed 147 million records. Another scenario is a corporate intranet login page. The authentication query might be SELECT * FROM users WHERE username='$user' AND password='$pass'. An attacker can bypass authentication by entering admin' -- as the username, effectively commenting out the password check. In penetration tests, we often find that developers use stored procedures thinking they are safe, but if the stored procedure concatenates input internally, it remains vulnerable. For example, a stored procedure that builds a dynamic query with EXEC() is still injectable. Performance considerations: Union-based injection on a large database can cause heavy load, potentially crashing the application. Time-based injection is slow; a single character extraction might take minutes. Automation tools like sqlmap handle this but can be noisy and trigger intrusion detection systems. Misconfigurations often occur when developers enable detailed error messages in production, giving attackers a roadmap. The best defense is parameterized queries (prepared statements) with strict input validation. In my experience, many organizations also fail to apply the principle of least privilege to database accounts; the application often connects with full read/write access, allowing data extraction or even data modification via injection.