Recap
In our previous article, we have covered the basics of SQL injection (SQLi), including the fundamental concepts of database, SQL language, and the key elements of SQL injection attacks. We even walked through a simple example on how attackers exploit SQL injection to bypass authentication systems.
Types of SQL injection attacks
As we delve deeper, it is important to understand SQL injection attacks thoroughly.
In this article, we will explore the various types of SQL injection attacks, which can generally be classified into three major categories:
As covering all of them in one article would be lengthy, we decided to split them into each types, per article. The articles will only cover the basic understanding of it. A further elaboration of the exploitation would be covered on a different series.
For the first part of this series, we will look into the In-band SQL Injection on MySQL DBMS.
In-band SQL injection
In-band SQL injection is the common and straightforward type of SQL injection. In this scenario, the attacker utilises the same communication channel to launch attacks and retrieve the results.
The two most common techniques of in-band SQL injection are:
- Union-based
- Error-based
Union-based
In union-based SQL injection, the attacker uses the UNION SQL operator to retrieve desired data by combining multiple SELECT statements into a single HTTP response.
As an example of a query is look similar to the following:
SELECT a, b FROM table1 UNION SELECT c, d FROM table2
The UNION keyword allows user to execute one or more additional SELECT queries and adjoin the results to the original query.
Now let us take a look using the SQL Fiddle example that we used in the previous article. This time, we are utilising DB Fiddle, an alternative for SQL Fiddle. The following SQL query will select the first two columns from the staff table, and then adjoin the other two columns selected through the UNION operator, result in all data from the selected columns returned.
Do note that, for a UNION query to work, it must met the following:
- The query must return the same number of columns.
- The data types in each column must be compatible between the query.
Let us take a look what would happen if the column number is mismatched.
Correct, it will throw the error as shown in the picture above.
So how we could ensure the number of columns are correct? Two efficient ways:
- Incrementing the number of columns by submitting a series of
UNION SELECTpayloads; or - Utilising
ORDER BYclauses to determine the number
The first approach would look similar to the following:
' UNION SELECT NULL--
' UNION SELECT NULL, NULL--
' UNION SELECT NULL,NULL,NULL--
and so on until we found the correct number of columns.
The second approach almost similar:
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
and so on until we found the correct number of columns.
Once the vulnerable application returns an error message such as Unknown column '5' in 'order clause', it indicates that the number of columns should be below than that.
With the knowledge of determining the number of columns, the next attacking payload should be straightforward using the UNION SELECT.
As in the example we provided, the number of columns should be 4 and the final UNION SELECT payload should be:
' UNION SELECT 1,2,3,4--
or
' UNION SELECT NULL, NULL, NULL, NULL --
Observe that, from the result, the numbers that we queried (1,2,3,4) are returned by the application. If we attempt to change them to a different number or string, it would still be visibile to us.
This means, we could as well inject a different SQL query on it with the aim to disclose the information on the application. As an example, we could query the current database version using version() function on the first column (id) , and the result will appear on that column as shown below.
We also could retrieve multiple values into the same single column by concatenating the values together. This can be done using one of the following approaches:
Using group_concat()
Example: group_concat(version(),0x3a,user())
Using concat_ws()
Example: concat_ws(0x3a,version(),user())
Note: 0x3a is a hexadecimal value for : which is used as the separator. It can be replaced with any character that you like
Hence, it is important that to ensure on which column number that we would like to further inject our SQL queries, in order to retrieve the data.
Let’s look on a different example. We tested on the Acunetix Test Page, http://testphp.vulnweb.com/
One of the endpoints that is vulnerable to SQL Injection attack is http://testphp.vulnweb.com/listproducts.php?cat=1
We determined that by the error showed by the application when a single quote was inserted on the value.
http://testphp.vulnweb.com/listproducts.php?cat=1'
Next we determined the number of columns using ORDER BY. We found the number of columns was 11. This was from the error returned after we counted on the 12th tries.
Then we continued using the UNION SELECT and assigning each columns count with an incrementing value from 1 to 12
http://testphp.vulnweb.com/listproducts.php?cat=1 UNION SELECT 1,2,3,4,5,6,7,8,9,10,11--
We observed numbers 2, 7, and 9 were reflected on the application once we injected the above payload. This means we can further extract the data only from these three column numbers. Using the similar examples we shared before, we confirmed the user() and version() using the SQL Injection.
http://testphp.vulnweb.com/listproducts.php?cat=1 UNION SELECT 1,2,3,4,5,6,concat_ws(0x3a,user(),database()),8,9,10,11--
Error-based
In error-based SQL Injection, it is much straightforward. The attacker takes advantage of the error messages produced by the SQL error messages to extract information from the database. Error-based SQL Injection could also be performed for inferential types, however in this article we are focusing on the visible error-based.
Sometimes, due to misconfiguration of the database, the application could throw verbose error messages which is useful for an attacker. Unlike the union-based approach, error-based technique does not require us to count the column numbers. But instead, the technique leveraging SQL functions that could be abused to disclose the information from the database on the error messages. Worth noting that the technique also relies on the DBMS version.
The following are some examples of SQL functions that are generally used in error-based approach:
extractvalue()
updatexml()
exp()
geometrycollection()
polyglon()
ST_LatFromGeoHash()
GTID_SUBTRACT()
BIGINT
and more..
Let’s take a look at the examples below:
http://testphp.vulnweb.com/listproducts.php?cat=1 and extractvalue(1,concat(0x3a,version()))--
http://testphp.vulnweb.com/listproducts.php?cat=1 and ST_LatFromGeoHash(version())--
That’s all for this part 1 of “Understanding Types of SQL Injection Attacks” series. We hope the reader could gain something from this post. We will continue with the other parts soon.
Thank you
References: