Implicit Numeric Conversion in SQL

Numeric values between quotes.

Some database management systems support SQL syntax where numeric values are enclosed between quotes. As I know, only MySQL and SQL Server support this particular syntax. Let’s start with a quick example. It is important to mention here that the column id contains only numeric values (INTEGER data type).

Implicit numeric conversion in SELECT (MySQL and SQL Server).

SELECT username FROM members WHERE id='1'

This would return the username of the member who has id 1.

Impact of Implicit Conversion for SQL Injection

The previous case demonstrates that classic numeric injection testing would not be enough to detect the vulnerability when such syntax is used. Because the numeric parameter stands between quotes, the tester would have to test it as if it was a text parameter, otherwise the vulnerability would not be found. Below are presented a bad and a good testing for this situation.

Insufficient test for numeric parameter.

SELECT username FROM members WHERE id='1 AND 1=2'


Correct test.

SELECT username FROM members WHERE id='1' AND '1'='2'

In the first test, the implicit conversion returns 1. What follows the first number is not numeric and it is simply removed when the convertion is done. After doing a similar test, an inexperienced tester might think that no injection is possible since the AND condition is not considered. However, the right test reveals that it is totally the opposite.

Combining With Minimal SELECT Structure

A problem often met in when doing SQL injection with UNION is to determine the data type of all columns of the original query in order to inject a valid segment. Fortunately, this laborious task can sometimes be simplified with a quick and simple implicit conversion in combination with minimal query structure. Below is an example where we assume that the number of column in the first query is known.

The attacker wants to generate a valid query.

SELECT id, name FROM members WHERE id=1 AND 1=2 UNION SELECT '1', '2'

In the previous case, the injected SELECT statement will be valid in every case as long as the first query only selects 2 columns (on MySQL and SQL Server). If the column expects a text, the number will be considered as a string, otherwize it will be considered as a numeric value. As you can guess, building a valid query becomes much easier this way.