This exercise follows on from Basic SQL Injection, so make sure you have completed that exercise before starting this one. Some additional SQL knowledge is needed for this exercise, specifically related to SQL’s UNION operator. UNION will be used to combine two SELECT statements: one baked into the application, which we cannot change, and another which we are injecting into the application in order to probe the database or extract data from it.
The exercise requires you to use the Immersive Labs platform, so make sure you have set up access before you begin.
Login to Immersive Labs and start the SQL Injection: UNION lab.
Click on the Info button at the top-right of the screen to reveal the information panel and read through its contents. Refer back to this where needed as you work through the exercise.
When the virtual desktop is visible, click on the Network button at the top-right of the screen and make a note of the target’s IP address. To access the web application, run the Chromium web browser and enter the aforementioned IP address in the browser address bar.
Click on the Tasks button at the top-right of the screen to reveal the tasks panel. Review the nine questions. Return to the tasks panel as you work through the exercise, answering each question. You can click Submit after entering each answer, to check whether you have answered correctly.
Find the part of the application that allows user input and experiment
with it. Try inputs such as
ruby, for which you should see
some output displayed by the application. Then answer Question 1.
See the Hints section at the bottom of this page if you need help.
Using the discussion of
UNION on the information panel as a guide,
inject SQL that will help you determine how many columns are vulnerable.
Then answer Questions 2 & 3. See the Hints section at the bottom of
this page if you need help.
Now inject SQL that will help you to answer Questions 4 & 5. As before, use the information panel to guide you. See the Hints if you need help. Make a note of the table names and the column names for both tables, as you’ll need these later.
In probing the database structure, you will have determined that the
customers table stores email addresses and credit card numbers.
Questions 6 & 7 ask you to find the email address of one individual and
the credit card number of a different individual. To answer these
questions, inject some SQL that uses
UNION with a
SELECT on the
customers table. See the Hints section if you need help.
Questions 8 & 9 ask you to find the company of one individual and the balance owed by a different individual. Both of these tasks involve the other table, whose name and column names you determined when probing database structure. You can use the same approach here as for Questions 6 & 7, modifying the table name and column names as appropriate. See the Hints if you need help.
If you want to investigate a more automated approach to probing a database and extracting data, try the lab on sqlmap.
For Question 1, examine the URL of the search results page to identify the relevant parameter. Alternatively, right-click on the page and choose View page source, then examine the HTML code for the form.
For Question 2, you’ll need to use
ORDER BY n. For example, try
this as input:
ruby' ORDER BY 1 #
You should still see 3 search results. Increase the value of
n by 1
each time, until you get an error. An error in this application will be
indicated by the search now failing to return any results, because you are
trying to order the results by a column that doesn’t exist.
The last value of
n that worked gives you the number of columns, from
which you’ll be able to deduce that there is only one possible correct
answer to Question 3.
For Question 4, you can just adapt the example given on the information panel under Determine the table names, changing it so that it uses the correct number of columns. The output should show you two table names, separated by a comma.
Similarly, for Question 5, just adapt the example given under Determine the column names so that it uses the correct number of columns. The example already has the correct table name. Remember to make a note of the column names that you see, and repeat for the other table, as you’ll need this information for later questions.
If you wanted to extract the first names of every customer, you could inject something like this:
999' UNION SELECT 1,2,group_concat(Firstname) FROM customers #
group_concat() function concatenates all the names into a single
value, thereby ensuring that we are selecting 3 things – a requirement
UNION to work in this scenario, where the unseen
statement used by the application is selecting from 3 columns.
To answer Question 6, you need to modify this example so that it
works for email addresses. You should also introduce a
that allows you to limit the results to showing the email of the specified
individual. A similar approach will work for Question 7. All you
need to do is change the column name and the
The same approach will work for Question 8 and Question 9, with appropriate modifications to table name and column names. If you haven’t already determined the column names for the other table, you can do so by injecting this:
999' UNION SELECT 1,2,group_concat(column_name) FROM information_schema.columns WHERE table_name="Private" #