MySQL syntax

November 11, 2007

While playing with the PHP-IDS filters and trying to circumvent them in the past months I came across some interesting MySQL syntax characteristics I’d like to share. Note that this will only be interesting if you try to evade filters or want to learn more about the syntax ;).

First of all there are hundreds of possibilities to return a true statement in order to bypass logins or to return the full table content on select queries. Besides the simple ‘ OR ‘1’=’1 trick you can also compare the input directly. The shortest one would be ‘=’. You can also use functions(md5(0)>1), column names (column!=0), user vars (e.g. @var!=1), system vars(e.g. @@version>4) combined with all kinds of whitespaces, operators, prefixes and brackets.

$prefixes = array(“”, “+”, “-“, “~”, “!”, “@”, ” “);

$operators = array(“^”, “=”, “!=”, “%”, “/”, “*”, “&”, “&&”, “|”, “||”, “<“, “>”, “>>”, “<<“, “>=”, “<=”, “<>”, “<=>”, ” XOR “, ” DIV “, ” LIKE “, ” RLIKE “, ” SOUNDS LIKE “, ” REGEXP “);

$whitespaces = array(“%20”, “%09”, “%0a”, “%0b”, “%0c”, “%0d”, “%a0”);

Note that you can use prefixes and whitespaces as often as you want, so ‘ OR- +-1=- + – ( + 1 ) /* works as well.
Also consider that some prefixes affect their follower. This shows the following table:

MySQL prefix

Note that ~ inverts bits and you can also use @ as prefix, which will return into a user variable with the value null.

You can also do alot with statics like null, for example 1′ is not null /* or, to avoid comment types, 1′ is not null – ‘ (MySQL<=4). Instead of null you can also use \N (case sensitive). Or you can use true and false, for example ‘or true#. In WHERE clauses you can also use not, like ‘or not’.

As comment types you can use all three known on MySQL: /*, # (%23), --. However make sure that you will write at least one space behind --, like -- aa, otherwise your query will fail.

(update: As read on sla.ckers, there will be an mysql update which will disallow unclosed comments. However this wont make any difference in the most cases)

I hope you learned how flexible the MySQL syntax is and that it’s always better to secure your WebApp than filtering for suspicous input. For a lot of example vectors take a look at this thread at sla.ckers.

Some of them:
aa’*@a is null-‘

Here is a little PHP script I wrote to bruteforce some filter evasions. Just rename the file to .php.