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.

Preparation

1. Login to Immersive Labs and start the SQL Injection: UNION lab.

2. 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.

3. 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.

   Warning: The IP address of the target may change between sessions, so be sure to check the address again when you return to the lab, if you don't complete it in a single session.

4. 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.

Probing Database Structure

1. Find the part of the application that allows user input and experiment with it. Try inputs such as james or 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.

2. 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.

3. 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.

Extracting Data

1. 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.

2. 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.

Optional Extra Work

If you want to investigate a more automated approach to probing a database and extracting data, try the lab on sqlmap.

Hints

• 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 #
  

  The group_concat() function concatenates all the names into a single value, thereby ensuring that we are selecting 3 things – a requirement for the UNION to work in this scenario, where the unseen SELECT 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 WHERE clause 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 WHERE clause.

  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" #
  

□