CRASH COURSE FOR FINDING SQL INJECTION IN WEBAPPS:PART 2

When performing an SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

The first method involves injecting a series of ORDER BY clauses and incrementing the specified column index until an error occurs. For example, assuming the injection point is a quoted string within the WHERE clause of the original query, you would submit:

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
etc.

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an ORDER BY clause can be specified by its index, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:

The ORDER BY position number 3 is out of range of the number of items in the select list.

The application might actually return the database error in its HTTP response, or it might return a generic error, or simply return no results. Provided you can detect some difference in the application’s response, you can infer how many columns are being returned from the query.

The second method already discussed in PART 1 of series which involves submitting a series of UNION SELECT payloads specifying a different number of null values:

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--
etc.

If the number of nulls does not match the number of columns, the database returns an error, such as:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Again, the application might actually return this error message, or might just return a generic error or no results. When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column. The effect on the resulting HTTP response depends on the application’s code. If you are lucky, you will see some additional content within the response, such as an extra row on an HTML table. Otherwise, the null values might trigger a different error, such as a NullPointerException. Worst case, the response might be indistinguishable from that which is caused by an incorrect number of nulls, making this method of determining the column count ineffective.

Now lets see this in action

Our website contains a filter functionality which is vulnerable to sql injection we will determine the number of columns in the website database.Lets filter by ‘Gifts’

Now first lets try to inject

' union select null -- and see what will we get

Okay we got an server error it means that there are more than one column lets try to add null two times

'union select null,null--

and see what happens

again internal servor error lets keep adding null one at a time till we see no error

By adding null three times error disappears 'union select null,null,null--

This means that website has three columns in the table.

But just by getting columns is not sufficient we need to go deeper like need to have ability to extract data in order to be able to show impact or to exploit it

So this time we will also look for column which can support string as we need to extract data in string format

Having already determined the number of required columns, you can probe each column to test whether it can hold string data by submitting a series of UNION SELECT payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

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

If the data type of a column is not compatible with string data, the injected query will cause a database error, such as:

Conversion failed when converting the varchar value 'a' to data type int.

If an error does not occur, and the application’s response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

Lets see this in action

we have already determined number of columns so lets determine which columns support string data

first we will start with first column

'union select 'a',null,null--

this is giving server error it means column does not support string data lets move to the next column

'union select null,'a',null--

We are not getting any error so it means that second column supports string data.

lets check the last one

'union select null,null,'a'--

Again getting error so last one does not support string data so here only second column support data.Now second column can be used to extract information from database.

Using an SQL injection UNION attack to retrieve interesting data

When you have determined the number of columns returned by the original query and found which columns can hold string data, you are in a position to retrieve interesting data.

Suppose that:

• The original query returns two columns, both of which can hold string data.
• The injection point is a quoted string within the WHERE clause.
• The database contains a table called users with the columns username and password.

In this situation, you can retrieve the contents of the users table by submitting the input:

' UNION SELECT username, password FROM users--

Of course, the crucial information needed to perform this attack is that there is a table called users with two columns called username and password. Without this information, you would be left trying to guess the names of tables and columns. In fact, all modern databases provide ways of examining the database structure, to determine what tables and columns it contains.

Now we will extract username and password from database in our next engagement.Note here we need to know the name of the table in order to extract data from it so we are considering here a white box type pentest where

we have already have information regarding columns and tables in database.

Lets start our engagement

Here our website with same filter functionality lets filter by ‘Tech gifts’ and try to enumerate number of columns in table using NULL method.

Error occured,we used query 'union select null-- appended to the url.So it has more than one column lets add another null to it so query becomes 'union select null,null--

This time it has no error so it shows that table has two columns okay lets check which columns out of both support string data.

we will use query 'union select 'a',null-- for first column

first column support string data lets check second column by using our query

'union select null,'a'--

second column also supports string data which means both column support string data we can also confirm this again by using query 'union select 'a','b'--

Now lets extract the username and password from table users in database(see in white box scenario we already know the column names and table names in database)

we can extract data by using query 'union select username,password from users--

After using query like this here what we got

we are seeing the contents of the table users which has username and passwords of the users of applicaton now passwords may be obsfucated or may be not lets try to login with credentials of administrator of the website

Using administrator crendentials I successfully logged into the application this showed passwords were not encrypted this is another blow to the good security practices as password should be encrypted by strong hashing algorithms so that even if attacker gain access to database like in our scenario still he/she will not be able to use another application user accounts.

In the preceding example, suppose instead that the query only returns a single column.

You can easily retrieve multiple values together within this single column by concatenating the values together, ideally including a suitable separator to let you distinguish the combined values.

Different databases have different concatenation operator here is a cheatsheet

Oracle: 'foo'||'bar'

MSSQL: 'foo'+'bar'

PostgreSQL: 'foo'||'bar'

MySQL:'foo' 'bar' (Here space is the concatenation operator,look at the space between the two strings)

For example, on Oracle you could submit the input:

' UNION SELECT username || '~' || password FROM users--

This uses the double-pipe sequence || which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character.

This could result in for example,

administrator~s3cure
wiener~peter
carlos~montoya

Lets see this in scenario

Here again like in our previous engagement we have a whitebox pentest at our hands we already know the database tables names and database is of type postgresql. Lets use filter functionality to filter it by type ‘Gifts’

As you might have guessed our first starting point is we will determine how many columns are there in table using our null method

query will be as usual 'union select null--

we got error so lets move forward using query 'union select null,null--

This time we don’t get any error so there are two columns in table.

Next step is to determine which columns support string data we will first test first column 'union select 'a',null--

we got error so first column does not support string data lets check our second column using query 'union select null,'a'--

no error here means second column support string data.In our last pentest engagement we saw we got two columns which supported string data so we were able to extract both username and password using both the columns but here we only have one column supporting string data but we have two columns data to be found out in order to exploit other users so we will use concatenation techniques.In postgresql as already stated in cheatsheet we can use something like this || '~' || to concatenate data of two different columns in one single column here ‘~’ is the separator we can use any separator we want but lets stick with what we have in our cheatsheet so now our query to extract username and password from users table becomes

'union select null,username||'~'||password from users--

we can clearly see we were able to extract data from the table users which is separated by our separator ‘~’.Lets login to the application using administrator credentials we are seeing on screen.

We are able to succesfully logged in as administrator again passwords are not encrypted in database which is a bad practice.

To summarise things union based sql injections

First,we determine the number of columns in a table using our null method of detection

Then, we find which columns supports the string data so that we can use that column to extract data

Then we perform our attack

Here all engagements we have done are white -box but if we are on a bug bounty hunt then we our work will be increased because first we need to detect the database type in order to do that just determine the number of columns by our usual method and then try to use concatenation using different databases operators the one which fits will give you type of database but more important thing is we also need to know the database’s table name in order to extract data.

Here are some things to read

Vanilla disclosed on HackerOne: A SQL injection vulnerability in...

SQL Injection Using UNION

NetSPI SQL Injection Wiki

https://hackerone.com/reports/384397

Automattic disclosed on HackerOne: SQL Injection Union Based

Lets continue our journey in PART 3

CRASH COURSE FOR FINDING SQL INJECTION IN WEBAPPS:PART 3

written by

https://twitter.com/infosec_boy