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):

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.

What happens if the application is vulnerable to SQL injection attacks and the attacker enters "

email UNION SELECT username, password FROM users-- 

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.

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:


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 

 function will cause an error if it fails, which will output the XPATH selector.

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 

 and subqueries) should already be familiar to you.

One easy option is to add another sorting parameter to the 

 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.

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

 sorting parameter to the query (comma-separated after the previous one), and set its value as a new subquery.

Create a subquery using the ExtractValue function, with the first parameter being a number 

 (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.

CONCAT(email, ":", password) FROM user WHERE admin=True LIMIT 1

, meaning the email and password of the first admin user, ensuring with the 

 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.

In this lab you will get to know the ORDER BY clause in more detail, practice using subqueries, and above all, practice the error-based SQL injection technique.

MySQLi ORDER BY 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):

```sql
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.

![](sanity:image-012eacc28a003db157ddb6f488ccddad587c1a86-842x252-png)

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

![](sanity:image-38f6779da0d98bd6b3f0632535619a475fa800da-397x64-png)

```sql
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:

![](sanity:image-e34544bd7c14c04a63883f1c74791a6b5aa9fd50-683x214-png)

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:

```sql
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.*

```sql
SELECT EXTRACTVALUE(0, "<INCORRECT")
```

![](sanity:image-21e9c4a0e9517aebdf5a50f3aab200a4ff2d3f07-644x86-png)

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.

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

![](sanity:image-66a81bce7b43297a3878824f8ca845df31a3da42-576x83-png)

---

## 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.

```sql
SELECT * FROM products ORDER BY price, in stock
```

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

```sql
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.

@exercise 3893f283d18c


(MySQL) ORDER BY and Error-Based Technique

SQL injection is probably the cause of the majority of serious data breaches, mainly because vulnerability is very likely in a large application if the technology choices are bad, and the result is usually leaking the entire database to the attacker.

SQL injection is likely the cause of the majority of serious data breaches, mainly because vulnerability is very likely in a large application if the technology choices are poor, and the consequence is usually the leakage of the entire database to the attacker.

(MySQL) ORDER BY and Error-Based Technique

ORDER BY

Learn to hack — start here