Problem Statement :
This lab contains an 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 an SQL injection UNION attack that returns an additional row containing null values.
Understanding the UNION ATTACK.
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, the UNION keyword can be used to retrieve data from other tables within the database. This results in an SQL injection UNION attack.
The UNION keyword lets you 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 will return 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 an SQL injection 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?
Determining the number of columns required in an SQL injection UNION attack
When performing an SQL injection 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--
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, 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--
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.
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.
LAB
PRACTITIONER
Solution:
' UNION SELECT NULL, NULL, NULL---
Steps:
- First load the page and go to one of the Category’s .
https://0ab1007104363007c0f3863f000700f3.web-security-academy.net/filter?category=Accessories
- In the URL let’s type the order by clause to see how many columns are there.
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
' ORDER BY 4-- Order by 4 is returning an error that means there are only 3 columns in the db.
In order to solve the Lab, the question was to find number of columns and number of Null Values
The rule for UNION attack to work is
- The individual queries must return the same number of columns.
- The data types in each column must be compatible between the individual queries.
So When we did ORDER BY we found 3 columns
We then returned one extra row with NULL values in it.
' UNION SELECT NULL--
' UNION SELECT NULL, NULL --
' UNION SELECT NULL, NULL, NULL --
Now let’s see how this will work in MSSQL Database.
In the below example, there are 2 tables
- City,
- Colors.
Now when I did SELECT * FROM dbo.city It showed me everything inside the table name city.
Similarly if I do SELECT * FROM dbo.Colors, it’s gonn show me all items inside that table.
Now Let’s see what UNION does here.
When I added UNION In between both the queries it brought everything in the results below. As in City + Colors items in one single list here.
SELECT * FROM dbo.city UNION SELECT * FROM dbo.Colors;
OK now, we know that there are 2 Tables inside this Database called Test.
But as we have seen above, in this UNION Attack:
Lab 3 : SQL injection UNION attack, determining the number of columns returned by the query.
The basic rule was:
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.
So in our case City and colors both has same amount of data In it, as in both has same number of Columns.
Now let’s see with the ORDER BY how we can find out how many Columns are there.
Example query:
SELECT * FROM dbo.city ORDER BY 1;
This will sort the rows based on the 1st column here.
SELECT * FROM dbo.city ORDER by 2;
As you can see the result below, it has now sorted the 2nd column by Alphabetical order.
SELECT * FROM dbo.city ORDER by 3;
This will return nothing but error. Because, there is not 3rd column here in our table called City.
” 3 is out of range of the number of Items in the select list.”
So now we know there are 2 Columns in this table.
Next now we called one extra row with NULL values in it.
So same way, I can also return other tables columns values using UNION
I can also use WHERE clause to return a specific value
Here under City table we see Color Table value .
Scroll Unlock 