Union Based SQLi


This post will demonstrate how an entire DBMS can be enumerated and dumped directly on the webpage using SQLi Union Based injection


In SQL a union statement is a way to combine results from multiple statements. For example selecting all columns from one table and combining them into all columns from another table. Take the blogs table and accounts table for example:
To display the date from the column “comment” in blogs_table table and firstname from the accounts table the union statement would look like this:

SELECT comment FROM blogs_table UNION SELECT firstname FROM accounts;

You can see the data from the comments column in light blue and the data from the firstname column in dark blue however both are displayed in the one column together.

Using these union statements in an injection allows us to dump database contents onto the webpage in place of where the intended data was meant to go. We can use union statements to pull multiple columns from multiple tables regardless of size directly to the webpage and all in one statement making this a very fast and very dangerous vulnerability.

Confirming vulnerable input field

Testing the Name and Password fields for SQL injection vulnerabilities using the ‘ character confirms that the Name field is vulnerable by displaying an error. Note the red box where the ‘ character closed off the sql statement and caused the error.

Enumerating the number of columns

Before jumping right in and throwing sql statements around, in order for a union select to pull down data the column numbers must be the same. For example, this statement would throw an error as we are selecting 2 columns from the users table and 3 columns from the merchandise table:  SELECT username,password FROM users UNION SELECT product_id, cost, quantity FROM merchandise;

To enumerate the number of columns we need to input an extra column count until we stop returning an error message. The injection syntax goes like this:


Keep adding a number to the statement until the error stops. Once the error stops we know how many columns are in the database.

At: ‘ UNION SELECT 1, 2, 3, 4, 5, 6,7 — the database returns the below. The reason it has only returned 3 is that although it pulled down all columns, only the username, password and signature fields are meant to be displayed to the screen. The rest of the columns still get returned only there is no place for them to print to screen as the page is only designed to display the username password and signature fields since it is an account viewing page.

This actually raises an important point about this attack. In order to view the database contents you must first find a place on the page where you are able to view output from database queries.
Since we used numbers in our union statement we can see that columns 2, 3 and 4 are displayed to screen so that is where we will place our syntax.

Enumerating the current database, version and user
Using the 3 columns available to us we can input the sql methods to return the current running database, the version and the username.

‘ UNION SELECT 1, database(), version(), user() , 5, 6,7 —


Enumerating the databases in the dbms
Each SQL server has whats called an information_schema database. This is used to keep track of and link all of the database together. We use this to enumerate databases but generally it is not something of value in itself.

‘ UNION select 1,schema_name,3,4, 5, 6, 7 from INFORMATION_SCHEMA.SCHEMATA–

We can see the dbms has 4 databases. information_schema, mysql, performance_scheme and mutillidae. The first 3 are default databases that come installed and the custom database is mutillidae.

Enumerating the tables in the mutillidae database 

We again use the information_schema database to pull down information about the tables in the mutillidae database:
‘ UNION select 1,TABLE_NAME,TABLE_SCHEMA,4, 5, 6, 7 from INFORMATION_SCHEMA.TABLES where table_schema=’mutillidae’– 
This statement finds 12 tables. Two tables to note are credit_cards and accounts.

Enumerating the columns in the credit_cards table 
‘ UNION select 1,COLUMN_NAME,TABLE_NAME, 4, 5, 6, 7 TABLE_SCHEMA from INFORMATION_SCHEMA.COLUMNS where table_name=’credit_cards’– 

Dumping the columns from the credit_cards table
You might have noticed that we only have 3 columns to view data but 4 columns in the credit_cards table. There is a sql statement that we can use to get around this. The CONCAT statement will concatenate 2 collumns into one allowing us to squeeze all 4 credit_card columns into 3. The final syntax is:

‘ UNION select 1, ccid, ccnumber, CONCAT(`ccv`, ‘ ‘, `expiration`), 5, 6, 7 from credit_cards– 

You can see that ccid was dumped into column 2(Username), ccnumberwas dumped into column 3(Password) and both ccv and expiration was dumped into column 4(Signature) via concatenation.