Difference between revisions of "SQL injection/Target Environments/Compatibility"

Latest revision as of 02:32, 19 July 2012

SQL injection > Target Environments > Compatibility

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.