(MySQL) ORDER BY and Error-Based Technique

ORDER BY

The ORDER BY clause in SQL, as you have already learned when familiarizing yourself with the UNION technique, sorts the rows retrieved from the database either by the column name or by the ordinal number. In addition, ORDER BY takes a direction parameter, whose value can be ASC (ascending) or DESC (descending).

For example, a query like this would retrieve all rows from the products table and sort them in ascending order by price (cheapest first):

SELECT * FROM products ORDER BY price ASC

Injection

But what if the SQL injection is in the ORDER BY clause? This type of vulnerability occurs quite often when applications allow users to order a table based on a specific column.

Let's take a practical example of a familiar online bank. The contact list can be sorted based on first name, last name, email, or account number.

The application takes the sorting parameter as a query parameter (from the URL address) and uses the parameter to construct an SQL query.

SELECT * FROM user ORDER BY email ASC

What happens if the application is vulnerable to SQL injection attacks and the attacker enters "email UNION SELECT username, password FROM users-- " as the value for the sort parameter?

Good idea! Unfortunately, however...

UNION does not work

The SQL syntax no longer allows UNION statements after ORDER BY. In other words, the injection point is too late in the structure of the SQL query to use the UNION technique anymore. You can of course try it! But here's what happens:

We will therefore have to resort to a slightly more creative, so-called error-based technique.

Error-based technique

The basic idea of error-based technology is as follows:

• Deliberately construct a SQL query that causes an error.
• Ensure that the error contains the information wanted to be exported from the database.
• Expose the vulnerability to us (the attacker).

In order to succeed, error-based techniques require that the application returns technical error messages as they are from the server to the browser, visible to us. This is one reason why security auditors always remind us of the importance of error handling in applications. Leaking errors makes it significantly easier to discover and exploit vulnerabilities, and in this case, even possible at all.

SQL syntax includes various functions. So far, we have only familiarized ourselves with the concat function, which combines text. Now we will get to know another function called ExtractValue, which is related to XML processing. The "correct" purpose of the function is not important, but here is an example of how it is supposed to be used:

EXTRACTVALUE('<cars><car>Download</car></cars>', '/cars/car')
-> Download

The function takes XML as the first parameter and an XPATH selector as the second parameter to extract information from the XML. It doesn't matter if you don't know what XML and XPATH are, as you will learn about them in another course. The important thing is that the ExtractValue function will cause an error if it fails, which will output the XPATH selector.

SELECT EXTRACTVALUE(0, "<INCORRECT")

So how can we get the value of the XPATH selector to contain both the < character (which causes the necessary error) and whatever we want to see from the database? Of course, with the CONCAT function, by combining the < character and the subquery! Both (concat and subqueries) should already be familiar to you.

EXTRACTVALUE(0, CONCAT("<", (SELECT @@version)))

But where does ExtractValue come in?

One easy option is to add another sorting parameter to the ORDER BY query. The ORDER BY syntax supports multiple parameters, like this: The query would list the products sorted by price, and those with the same price would be displayed first if they are in stock.

SELECT * FROM products ORDER BY price, in stock

We can set the subquery as the second order parameter, like this:

SELECT * FROM products ORDER BY price, (SELECT ExtractValue(...))

Summary

• Add a new ORDER BY sorting parameter to the query (comma-separated after the previous one), and set its value as a new subquery.
• Comment out the rest of the query (-- ).
• Create a subquery using the ExtractValue function, with the first parameter being a number 0 (this is not important), and the second parameter being the CONCAT function call, which in turn takes the first parameter as the < character and the second parameter as the subquery that retrieves the data you want to see.
• The information you want to see is CONCAT(email, ":", password) FROM user WHERE admin=True LIMIT 1, meaning the email and password of the first admin user, ensuring with the LIMIT clause that no subquery returns extra rows, which would ruin the attack.
• The application should return an error message with the information you want to see.