SQL injection/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.