SQL injection/Target Environments
- 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.
- 1 Navigating unfamiliar databases without the C API
- 2 Databasing engines compared and contrasted in light of SQL injection
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
- 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 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.
- 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
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.
- PROCEDURE ANALYSE might come in handy.
- 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.
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:
- BETWEEN ... AND ... conditional operator
MySQL and Postgres share the following:
- LIMIT ... OFFSET ... clause syntax
MySQL and MSSQL share the following:
- RLIKE clause for regular expressions
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
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.