In the second post of the SQL Injection series, I would like to introduce you to a technique called UNION Attack, which can be used to read the contents of entire databases.
You can find the first post with the basics of this attack here.
To help you understand the technique directly, I’ll refer you once again to the training environment I put together using Docker containers, which you can find on Github:
git clone https://github.com/bugninja-de/SQL-injection-practice-course.git
What is the UNION Attack?
The UNION command is used in SQL queries to combine the returns of several statements into one result. We make use of this in the UNION attack. First, we need to find out how many fields the original SQL statement returns, since our injected statement must return the same number of fields to be stored in the same result. We do this simply by sorting the result of the statement by the first, second, … n-th column. If an error message or an empty result is returned when the result should not be empty, we have sorted by a column that does not exist and we know how many columns the statement returns.
Once you have cloned the training environment from Github and you have started the containers via
docker-compose up -d, you can simply open the browser of your choice and go to http://localhost/products.php. You should then see this page:
A beautiful page that shows you some sneakers. At the top of the dropdown you can narrow down the color of the shoes displayed.
The parameter, which color should be displayed, is passed as a
GET parameter via the URL and this is also our point of attack. Therefore we add, as in the first part of the SQL Injection series, a
' followed by our desired modification of the query to the URL. However, we need to note one special point: since we are entering the values directly from the browser’s address bar, we need to convert some special characters to a compatible format, because otherwise we might get errors. For the desired modification, it is the comment character
# which we enter URL-compatible as
To find out how many columns the original query returns, we first count the elements that are visible at first sight: Id, Image, Name, Description and Price makes a total of 5. So let’s try to sort by the 5th column first, just to be sure. So we add
' ORDER BY 5 %23 to the URL. This should look like this depending on the selected color:
The page is displayed as expected and since it is a test environment, the complete query is also displayed and we can see that the
%23 has been changed back to a
Of course, we can also see from the displayed query how many fields the result contains, but usually website developers are not so kind to put the database query directly on the page.
After that we know for sure that the query has at least 5 fields. Now we count up this value in the URL until we get an error message or an empty result. With 6 everything is still good, with 7 comes the error message:
Please disable such kind of error messages in production mode so that an attacker would not get too much information about your systems.
We now know that our result contains 6 fields. Next, it would be useful to see where each field is displayed on the page. This is where
UNION ALL comes into play. Why
UNION ALL and not
UNION ? A UNION without ALL does not show duplicate database entries. I personally find it better to get all rows of a table. I can also filter out duplicate entries afterwards. Also, UNION ALL is faster than UNION because it doesn’t filter, but that doesn’t really matter. In conclusion, it’s a matter of taste whether you choose UNION with or without ALL.
We modify our URL as follows to see which field appears where on the page:
http://localhost/products.php?color=blue' UNION ALL SELECT 1,2,3,4,5,6 %23
What have we done? We have added a record with the values 1, 2, 3, 4, 5 and 6 to the result. On the page you can now see at which position which field appears. Fields 2 and 3 are particularly suitable for text output here, so when we generate the next statements, we will focus on these fields.
Now it would be quite interesting to know the tables of the database, to see if there are login data for example, that we can read. Fortunately MySQL offers the database INFORMATION_SCHEMA, where information about the databases and tables are stored. The table TABLES is of special interest here, because it contains all table names. We modify our URL to display the table names:
http://localhost/products.php?color=blue' UNION ALL SELECT 1,TABLE_NAME,3,4,5,6 FROM INFORMATION_SCHEMA.TABLES %23
First, we are shown all the default tables that are present in every MySQL database. But if we scroll all the way down, we get to the interesting entries:
Here the tables ‘products’ and ‘users’ catch the eye. Let’s see what the columns of the ‘users’ table are called. For this we access the data of the COLUMNS table in the INFORMATION_SCHEMA database:
http://localhost/products.php?color=blue' UNION ALL SELECT 1,COLUMN_NAME,3,4,5,6 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' %23
Strike: there are the columns id, username and password. Then let’s have a look at the content of the table ‘users’:
http://localhost/products.php?color=blue' UNION ALL SELECT id,username,password,4,5,6 FROM users #
Looks like we have the user credentials here. Now we could take this data and try to log in to the http://localhost page.
When you are done with your UNION Attack tests, remember to exit the training environment using
This example shows why it is not a good idea to store passwords in plain text in a database. If user data from a website is lost for whatever reason, the attacker can use it directly. Therefore, passwords should always be hashed with a suitable algorithm – ideally with Salt and Pepper to make Rainbowtables unusable. What Salt, Pepper and Rainbowtables are and how this salt and pepper can be used to store passwords securely, I will explain in a later post.
The third SQL Injection post can be found here.