Blind SQLi: ORDER BY Column Enumeration

The Foundation of a UNION Attack

The SQL UNION operator allows you to combine the result sets of two or more SELECT statements into a single result set. For an attacker, this means you can attach a query of your own design (e.g., SELECT username, password FROM users) to the application's original query.

However, SQL enforces strict rules for UNION to work:

1. Both SELECT queries must return the same number of columns.
2. The data types in each corresponding column must be compatible (e.g., you can't easily combine a text column with a date column).

As a security analyst performing a black-box test, you don't have access to the application's source code, so you don't know the original query. It might be SELECT product_name, price FROM products... (two columns) or SELECT id, name, description, image_path FROM articles... (four columns). Blindly attempting a UNION attack will almost certainly fail because of a column mismatch. Our first task, therefore, is reconnaissance.

To begin, let's get a formal overview of this concept from a definitive source.

Finding the Column Count with ORDER BY

The ORDER BY clause in SQL is used to sort the results of a query. A useful feature of this clause is that you can specify the column to sort by using its index number rather than its name. For example, ORDER BY 1 sorts by the first column in the SELECT list, ORDER BY 2 sorts by the second, and so on.

This is the feature we will exploit. If we inject an ORDER BY clause and request a column index that exists, the query will execute successfully. However, if we request an index that is out of bounds—for instance, asking to sort by column 5 when the query only returns 4 columns—the database will generate an error. This error, or the lack of one, is the signal we need.

The attack proceeds as follows:

1. Append ORDER BY 1 to your injection payload. If the application responds normally, you know there is at least one column.
2. Increment the number: ORDER BY 2. If it still works, there are at least two columns.
3. Continue incrementing the index (ORDER BY 3, ORDER BY 4, etc.) until the application's response changes. This change could be a visible error message, a generic "Internal Server Error," or simply no results being returned.
4. The number of columns in the query is the last number that did not produce an error. If ORDER BY 4 causes an error, you have confirmed the query returns three columns.

Let's watch a practical explanation of this technique.

A Practical Walkthrough

Now, let's see this technique applied in a realistic lab scenario. The following video demonstrates how to find the column count on a vulnerable web application, first by explaining the theory and then by executing the attack.

As you can see from the demonstration, the payload structure is simple. If the injection point is in a WHERE clause like ...WHERE category = 'Gifts', your injected URL parameter would look like this:

• ...category=Gifts' ORDER BY 1--
• ...category=Gifts' ORDER BY 2--
• ...category=Gifts' ORDER BY 3--
• ...category=Gifts' ORDER BY 4-- (This one fails)

Notice the use of the comment -- at the end of the payload. As you learned in the previous lesson, this is crucial for neutralizing the rest of the original query, preventing syntax errors and ensuring your injected clause is the last thing the database processes.

To reinforce your understanding, let's review a written guide that documents this same procedure. Being comfortable with both video and text-based technical documentation is essential for a security analyst.

An Alternative: UNION SELECT NULL

While ORDER BY is a highly reliable technique, it's worth knowing the other common method for enumerating columns: injecting UNION SELECT with NULL values. The logic is similar—you are probing for the correct number of columns—but the approach is different.

You iteratively inject payloads like:

• ' UNION SELECT NULL--
• ' UNION SELECT NULL,NULL--
• ' UNION SELECT NULL,NULL,NULL--

You continue adding NULLs until the database error disappears, which indicates you have found the correct number of columns. We use NULL because it's a special value that is data-type compatible with almost any column type (text, number, date), satisfying the second rule of UNION.

Rana Khalil's video, which you watched earlier, also contains an excellent demonstration of this method.

Conclusion

You have now learned a fundamental reconnaissance technique in SQL injection. By systematically injecting ORDER BY clauses, you can force a database to reveal the number of columns used in a hidden backend query. This is not an attack that yields data on its own, but it is the essential preparatory step that makes sophisticated data exfiltration possible.

Key Takeaways:

• UNION Attack Prerequisite: To use a UNION injection, the number of columns in your injected SELECT must match the original query.
• ORDER BY Injection: You can discover the column count by injecting ORDER BY [index] and incrementing the index until the database returns an error.
• Interpreting the Signal: The number of columns is the last index that resulted in a successful response before an error occurred.
• Alternative Method: An alternative approach is to inject ' UNION SELECT NULL,NULL,...-- payloads, adding NULL values until the query succeeds.

In our next lesson, we will put this knowledge to use. Now that you can determine the column count, you will learn how to craft a complete UNION statement to select and retrieve data from tables that were never meant to be seen.