SQL Injection UNION Attack, Finding a Column Containing Text

1 month ago 27
BOOK THIS SPACE FOR AD
ARTICLE AD

A Portswigger Lab

Marduk I Am

Welcome back my friends!

Lab Description:

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.

NOTE: I will be using Burpsuite Community Edition to help complete this lab, so you may want to have that up and running. If you’re not sure what Burp is or how to use it, check out this tutorial: Burpsuite Basics (FREE Community Edition) by John Hammond.

This lab is the second in a series of three labs designed to help us understand the process of retrieving data from from a site’s database.

In the previous lab we learned how to determine the number of columns in a database by using UNION SELECT and NULL values. Here we are going to build off of that and figure out which column contains string data.

In this lab we will:

Verify the existence of a SQLi vulnerability.Determine the number of columns being used by the database.Determine which column is compatible with string data.

Access the lab and you will be brought to the familiar shopping page with a list of products and their prices.

View of lab’s shopping page also showing where the product category filter are located.

#1 — Verify the existence of a SQLi vulnerability.

Our SQLi vulnerability lies in the product category filters. To verify this, click on a product category filter of your choice. I chose ‘Pets’.

After making your selection, head over to Burp and find your filter in the target site map on the left-hand side.

View of Burp showing where the product category filter is and where the Repeater tab is located.

Right-click on your filter and select ‘Send to Repeater’. In Burp Repeater we will be able to alter our request and send it multiple times and see the server’s response for each.

View of Burp repeater showing the original request and response.
Original request and response

To verify we are in the correct place for a SQLi, replace your filter with a single quote ( ‘ ) and click the orange ‘Send’ button.

In Burp repeater we replaced the filter with a single quote and our response is a ‘500 Internal Server Error’
Expect a ‘500 Internal Server Error’ response

We are in the right spot. The ‘500 Internal Server Error’ lets us know that this site may be susceptible to a SQLi attack.

When using a UNION SELECT attack, if the number of tables you use in your query does not match exactly the union will not work. That is why this step is an important one.

#2 — Determine the number of columns being used by the database.

From here we need to use our UNION SELECT and NULL values approach, we learned in the previous lab, to figure out how many columns we are dealing with.

With this approach we are going to use UNION SELECT and NULL for the column value, then comment out the rest of the line using a double hyphen.

-- Our payload
' UNION SELECT NULL --

-- URL encoded
'+UNION+SELECT+NULL+--

NOTE — Be sure to apply URL encoding to your payload before sending your request. Burp makes URL encoding easy, it can be done several ways. I usually highlight my payload and hit ‘Ctrl+u’.

We should get a ‘500 Internal Server Error’ response which tells us we have more than one column.

We do get a ‘500’ response.

To determine the number of columns, keep adding NULL values for columns, separated by commas, until you get a ‘200 OK’ response from the server.

Since this is the same page as the previous lab, I am just going to jump to using three columns.

-- Our payload
' UNION SELECT NULL, NULL, NULL --

-- URL encoded
'+UNION+SELECT+NULL,+NULL,+NULL+--

Using ‘+UNION+SELECT+NULL,+NULL,+NULL+ — we got the ‘200 OK’ response.
‘200 OK’ response. We have 3 columns.

Getting a ‘200 OK’ response from the server lets us know we have three columns in this database.

If you notice at the top of the lab web page, they provide us with a random string to use to solve this lab.

Labs web page showing the random string to use.

#3 — Determine which column is compatible with string data.

Use this string to replace the NULL values in your payload, one at a time, to determine which column is compatible with string data.

Remember, your string data need to be enclosed in single quotes.

-- Our Payload
' UNION SELECT 'Zi4rv2', NULL, NULL --

-- URL encoded payload
'+UNION+SELECT+'Zi4rv2',+NULL,+NULL+--

Send your payload.

‘ UNION SELECT ‘Zi4rv2’, NULL, NULL — returned a ‘500 Internal Server Error’ letting us know the first column is NOT compatible with string data.

We get a ‘500’ response, letting us know that the first column is not compatible with string data.

Let’s try the second column.

-- Our Payload
' UNION SELECT NULL, 'Zi4rv2', NULL --

-- URL encoded payload
'+UNION+SELECT+NULL,+'Zi4rv2',+NULL+--

‘ UNION SELECT NULL, ‘Zi4rv2’, NULL — returned a ‘200 OK’ response letting us know that column number 2 IS compatible with string data.
We did it!

That did it. We got a ‘200 OK’ response letting us know that column number two is compatible with string data.

The ‘Congratulations’ banner should have popped up on the lab web page.

Congratulations, you solved the lab!

Congratulations! You solved another one! Keep up the great work!

See you next time!

Read Entire Article