Chapter 7. SQL
Information in this chapter:
• SQL: A Short Introduction
Abstract
Structured Query Language (SQL) is a common language for directly interacting with databases and comparable systems. Although the database management systems available today have their particular quirks, SQL possesses the benefit of providing one major interface to many nonhomogeneous database management systems. Using basic SQL queries, it's possible to write and receive data from a MySQL database or an Oracle, PostgreSQL, or Microsoft SQL database. But SQL is not only about fetching data from a database table or comparable storage engine. It is also about including data manipulation, triggering structural changes to the database, granting and revoking privileges for database users, and dealing with data stored in different character sets. This chapter focuses on obfuscation and how the most widespread database management systems in Web application development can be tricked into accepting SQL code that is faulty and hard to read and detect.
Key words: Structured Query Language, Database management system, phpMyAdmin, PHP Data Object, Structured Query Language comment, Cross-site scripting
Structured Query Language (SQL) is one of the most common languages today for directly interacting with databases and comparable systems. Most Web applications providing interactive content use databases and are usually fueled by database management systems (DBMSs) such as MySQL, PostgreSQL, or Oracle, all of which are capable of understanding queries in SQL.
The usual usage pattern is easy to describe. In most cases, the Web application receives user input requesting a certain amount of data specified by certain filters and constraints. Consider the example URL of http://my-webapp.com/page.php?id=1id 1. To receive the requested information, the application generates a SQL query such as SELECT title, content from pages where id = 1, which tells the Web application that the visitor has requested the page and passes it on to the DBMS. If an entry in the table pages exists, the DBMS will return the found data to the Web application, and if all goes well, the visitor will see the requested data.
SQL: a short introduction
You might have noticed that the syntax for the SQL query is very easy to understand. The language elements are pretty close to English language elements. We have a verb, two subjects, and an object, as well as a conditional statement. This is not a coincidence—and it leads us directly to the origin of SQL back in the late 1970s. During those years, IBM was working on the first versions of SQL to find a successor to SEQUEL, the Structured English Query Language developed for the early DBMS known as System R. In 1979, the first version of SQL was released together with Oracle version 2. Seven years later, in 1986, the first major version, SQL 1, was released and standardized by the American National Standards Institute (ANSI).
Since then, the specification has been updated several times, gaining additional features and modules, including a specification on how to use Extensible Markup Language (XML) with SQL. Although the various available DBMSs each have their particular quirks, SQL possesses the benefit of providing one major interface to many heterogeneous DBMSs. Using basic SQL queries, it is possible to write and receive data from either a MySQL database or an Oracle, PostgreSQL, or Microsoft SQL (MS SQL) database. If a developer wants to craft more complicated queries, some problems might occur—for instance, one DBMS may provide a shorthand method and another may require more complex code. A legendary problem among Web developers is lack of support for the LIMIT statement on Oracle databases compared to MySQL, which have led to exotic workarounds and hacks. Many Web sites provide interesting comparisons regarding how to get the LIMIT feature, which simply limits the returned results with a numerically defined window, to work on several DBMSs. Table 7.1 shows some examples. 1
1Comparison of different SQL implementations. http://troels.arvin.dk/db/rdbms/#select-limit.
Although the Oracle example in Table 7.1 looks the quirkiest compared to the more streamlined version from the SQL 2008 specification or the MySQL and PostgreSQL examples, it is not surprising that Oracle chose to use a window function, since this is the method announced in the SQL 2003 specification. The other DBMS vendors wanted to give developers working on their systems a handy shortcut, which was a very welcome gesture and led to a comparable way to go in SQL 2008.
SQL is not only about fetching data from a database table or comparable storage engine. It is also about including data manipulation, triggering structural changes to the database, granting and revoking privileges for database users, and dealing with data stored in different character sets. To fulfill the requirements of highly critical applications, many DBMSs also ship with features such as transactions, commits, and rollbacks. Transactions ensure that if a query takes some time to be executed, other queries coming in from the same or different users cannot endanger the integrity of the data, or if multiple queries have to be executed, they are treated as one query in terms of the result. Imagine a case in which a complex query is meant to write several entries into a database table and returns the last inserted ID after finishing: what if another script instance has created entries itself and thus makes the last inserted ID invalid?
To make it easier to work with multiple DBMSs my coauthors and I created a small tool called the Universal SQL Connector, which is written in PHP and connects to the most important DBMSs if you have them installed and available. The tool is meant to send a single query to as many DBMSs as possible, to ease the process of fuzzing. It supports JSON output as well.
You can find the sources at http://pastebin.com/jPXPLGiy.
Most DBMSs support transactions, commits, and rollbacks. The following code snippet shows a simple transaction for a MySQL DBMS fetching data from an entry, storing it in a variable, and then updating another entry with it:
START TRANSACTION;
SELECT @A:=SUM(name) FROM test WHERE id=1;
UPDATE test SET name=@A WHERE id=2;
COMMIT;
The documentation on transactions for PostgreSQL also provides great examples and code snippets on why and how to use this feature correctly. It is available at www.postgresql.org/docs/8.4/interactive/tutorial-transactions.html.
In this chapter, we do not go into too much depth regarding the numerous features of DBMSs, since our focus is on obfuscation and how the various quirks and peculiarities of the most widespread DBMSs in Web application development can be tricked into accepting SQL code that is faulty and hard to read and detect. The examples in this chapter focus on three DBMSs: MySQL, PostgreSQL, and Oracle Express Edition. The following platform setup is used in this chapter, and is based on Ubuntu 9.10:
• MySQL 5.1.37-1ubuntu5.1
• PostgreSQL 8.4.2-0ubuntu9.10
• Oracle Database 10g Release 2 (10.2.0.1) Express Edition for Linux x86
• Apache 2.2.12
• PHP 5.2.10-2ubuntu6.3 (MySQL, Mysqli, PDO)
In our examples, we use either the phpMyAdmin SQL query from www.phpmyadmin.net/ or small PHP scripts to connect to the databases and execute the queries. phpMyAdmin (PMA) is a widespread, Web-based open source tool for administering MySQL databases. Many hosting providers have this tool preinstalled and many operating systems allow easy installation if it is not installed already. It is very useful for targeted testing against MySQL, although compared to Firebug, the test results are not always 100% correct. For example, the query SELECT ‘1’delimiter (delimiter followed by a whitespace) will cause a denial of service when executed with PMA, and will just throw an error when executed directly via the MySQL console. Also, PMA often changes comments, so when fuzzing with comments and comparable code elements, the results may not be precise. Figure 7.1 shows the PMA SQL console.
 |
| Figure 7.1 The SQL Query Form in PMA. |
Most of the following code examples are copy and paste ready with the aforementioned setup. The following script can be used to test whether all installed databases can be connected to by PHP:
<?php
// MySQL
$link = mysql_connect(‘server’, ‘username’, ‘password’);
mysql_select_db(‘database’,$link);
mysql_query(‘SELECT 1’, $link);
// Mysqli
$link = new mysqli(‘server’, ‘username’, ‘password’, ‘database’);
$link->query(‘SELECT 1’);
// PDO
$link = new PDO(‘mysql:host=server;port=3306;dbname=database’, ‘username’, ‘password’);
$link->query(‘SELECT 1’);
// PGConnect
$link = pg_connect(
‘host=server port=5432 dbname=database user=username password=password’
);
pg_query($link, ‘SELECT 1’);
// OCI Connect
$link = oci_connect(‘username’, ‘password’, ‘//server/’);
oci_execute(oci_parse($link, ‘SELECT * FROM database WHERE 1’));
For testing queries on Oracle Express Edition, the bundled Web interface can be used if no other quick solution is available. After installing the latest Oracle XE version the Web interface can be used after visiting http://localhost:8080/apex/ and provides a SQL console as well as tools for maintaining schema and table structures along with data maintenance. Figure 7.2 shows what this tool looks like. For production use, the tool should be avoided, though, since the interface is riddled with easily exploitable cross-site scripting vulnerabilities.
 |
| Figure 7.2 The SQL Command Form of the Oracle Web Interface. |
When dealing with SQL and Web applications there is one important thing to consider, in almost all situations. In the previous code snippets, we can see that executing a query with a function such as mysql_query() (see http://php.net/manual/en/function.mysql-query.php) allows execution of one and only one statement per transaction: mysql_query(‘SELECT 1’, $link).
It is usually not possible to concatenate statements with MySQL or other common Web application DBMSs, whether via select 1;select 2; or other mechanisms. Even worse, once it is possible to manipulate a SELECT query you cannot execute an UPDATE or comparable query from the inside—for example, via subqueries. The only allowed actions are to concatenate more SELECT queries under several constraints via UNION or to use subqueries, as shown in the next code example:
mysql_query(‘SELECT 1; SELECT 2;’, $link); // won't work
mysql_query(‘SELECT 1 UNION SELECT 2;’, $link); // works
mysql_query(‘SELECT 1 from test WHERE 1=(SELECT 1)’, $link); // works
It would be extremely dangerous if stacking queries were allowed. Just imagine a small SQL injection vulnerability that could be turned into an extremely dangerous problem, allowing free reading, manipulation of data, creation and privilege assignment of new users, and in the worst case, remote code execution—for example, via SELECT 1;INSERT INTO OUTFILE…;. A SQL injection cheat sheet2 by Ferruh Mavituna shows a deprecated but still interesting table or DBMS supporting stacked queries, stating that stacked queries are at least supported with PostgreSQL and PHP as well as on MS SQL Server and several programming languages.
2SQL injection cheat sheet by Ferruh Mavituna. http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/#LangDbFigure.
Note that MySQL is not affected; however, if an application uses the PHP Data Objects (PDOs, see http://php.net/manual/en/book.pdo.php) connection library instead of PHP MySQL or Mysqli, MySQL will accept stacked queries. In other words, the PDO engine is capable of separating multiple queries and executing them sequentially. The tricky thing is that PDOs do not easily reveal this secret. If SELECT 1;SELECT 2 is executed, only the 1 will be found in the result set. Also, SELECT 1; foobar will not throw an error, but it will return 1, which might let us think everything after the semicolon will be ignored. But with an easy benchmark test, we can determine that the second query is really being executed:
<?php
$link = new PDO(
‘mysql:host=server;port=3306;dbname=database’,
‘username’, ‘password’
);
if($result = $link->query(‘SELECT 1; SELECT BENCHMARK(5000000,MD5(1));’)) {
foreach($result as $row) {
var_dump($row);
}
}
A more up-to-date and accurate SQL cheat sheet, by Roberto Salgado and other authors, addresses this issue and is available at http://docs.google.com/Doc?docid=0AZNlBave77hiZGNjanptbV84Z25yaHJmMjk.
In the next section, we will learn what kind of language elements the DBMSs provide and how we can use them for obfuscation.
Relevant SQL language elements
SQL knows several basic language elements, including statements, select specifications, and search conditions over operators, functions, attributes, and objects. Most DBMSs allow basic obfuscation techniques for statements already. For example, the case of the characters used in the statement does not matter; we can use SELECT, select, or even sElECt. This is true for most keywords as well, but usually not for table names and other strings pointing to actual database data and structures; those elements are treated in a case-sensitive manner. So, whereas sELecT * frOm test works if the table test exists, sELECt * fROm tEsT will fail and raise a “Table not found” error. The most important statements are usually SELECT, INSERT, UPDATE, and DELETE for direct data retrieval and manipulation, as well as ALTER, DROP, and TRUNCATE for structural changes. Most DBMSs ship with features allowing direct interaction with the file system, manipulating the operating system Registry, or even executing arbitrary code. MySQL, for example, ships with INTO OUTFILE to actually write data to the hard disk of the DBMS server if the privilege context allows this.
Many DBMSs also support comments, and thereby allow you to mix comments into the statement declaration, as in SE/**/LE/**/CT. Most DBMSs support two kinds of comments: block comments via /**/ and one-line comments via #. But there are several special ways to work with comments and use them to prematurely end statements or just to perform basic obfuscation. We look at SQL and comments later in the section “Comments.”
Functions
The functions a DBMS provides are very interesting in terms of obfuscation. We will primarily look at the numerical and string functions the various DBMSs have in stock, since they enable interesting encoding possibilities and even the ability to encrypt the executed code. Of course, most DBMSs support base64 or hex and even octal and binary representation of strings and other data. MySQL even supports several proprietary hashing algorithms as well as MD5, SHA-1, and others.
Many filters assume that a SQL injection requires a bunch of characters to work, including whitespace. This is not true, as many characters in SQL, and especially in MySQL, can be replaced with other characters to fool a filter. Remember the character   as a whitespace substitute, as well as parentheses, as in SELECT(*)FROM(tablename)…
The manual provides a good overview of what can be used inside MySQL queries to encrypt and decrypt strings, which we will discuss more thoroughly in the section “Strings in SQL” (also see http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html for more information). Functions in SQL can also be used in a nested way to make sure a query is bloated, and thus harder to read; plus, many functions returning empty strings or 0 as well as false can be used in concatenations or regular expressions.
# MySQL
SELECT !!!ord(char(mid(lower(1),1,2))); # selects 1
SELECT substr(hex(unhex(01)),2,1); # selects 1
SELECT(1)IN(GREaTEST(1,1,1,1,1,1)); # selects 1
SELECT(if(“1”",((!!!~0)),0)); # selects… 1
The most commonly used functions for obfuscating in SQL queries are the functions that turn characters or other values into a string necessary for a successful query, usually including several concatenation chains. The most common function is chr() on PostgreSQL and Oracle, and char() on MySQL. These functions do nothing more than receive a numerical value and return the character found at the given decimal index of the ASCII table. Since the ASCII table has a limited number of indexes, it is interesting to see how the DBMS will react on higher integers such as 127 and 255. Also, note that MySQL exhibits behavior that is useful in the context of obfuscation. For instance, it is possible to generate strings comprising up to four characters by overflowing the char() function with large numbers:
#Oracle
SELECT CHR(84)||CHR(69)||CHR(83)||CHR(84)a FROM user_tables;
#MySQL (example abuses an integer overflow)
SELECT concat(char(1885434739),char(2003792484)) #“password”
SELECT concat(char(x‘70617373’),char(b‘1110111011011110111001001100100’)) #“password”
This MySQL example is easy to understand. The number 1885434739 is represented in hex with 70617373, which, when shown as a string such as 0x70617373, will result in “pass”; the other sequence, of course, results in “word”.
As the code examples showed, we can also make use of the operators the DBMS provides for us. Usually, the list of available operators is not that different from what most programming languages provide. There are the usual mathematical operators, Boolean operators, and more DBMS- and string-comparison-specific operators such as NOT, LIKE, RLIKE, and others. The DBMS documentation pages usually provide good lists with explanations of what is available. An example for MySQL is available at http://dev.mysql.com/doc/refman/5.1/en/non-typed-operators.html.
Operators
In terms of operators, we can use mathematical operators as well as Boolean and concatenation or size comparison operators. Both PostgreSQL and Oracle provide a dedicated operator for string concatenation, which unfortunately is missing in MySQL, and looks like this:
SELECT ‘foo’ || ‘bar’ # selects foobar
PostgreSQL also ships with several operators that are useful for regular-expression-based comparisons and operations, among them ~ and ~* for case-sensitive and case-insensitive matches, and the !~ and !~* variation for nonmatches. PostgreSQL also supports a shorthand operator for LIKE and NOT LIKE that looks like this: ~~ and !~~.
Comprehensive lists of operators for MySQL, PostgreSQL, and Oracle are available at the following URLs:
As a side note, MS SQL allows string concatenation “JavaScript style” by using the plus character (+).
MySQL does feature possibilities for concatenating strings without using concat() or similar functions. The easiest way to do this is to just select several correctly delimited strings with a space as the separator. The following example selects the string aaa with the column alias a:
#MySQL
SELECT ‘a’ ‘a’ ‘a’a;
SELECT‘adm’/*/ ‘in’ ‘’ ‘’ ‘’;
An operator available in MySQL that is especially interesting for more advanced obfuscation techniques is the := assignment operator. MySQL and other DBMSs allow the creation of variables inside a query for later reference. Usually, the SET syntax is used for this purpose, as in SET @a=1;—but it cannot be used inside another query. The := operator circumvents this limitation, as the following examples show. The first example is rather simple and just shows how the technique works in general, whereas the second example shows a way to use large integers to generate hexadecimal representations which then can be represented in string form (e.g., 0x41 as A).
#MySQL
SELECT @a:=1; # selects 1
SELECT@a:=(@b:=1); # selects 1 as well
SELECT @a:=26143544982.875,@b:=16,unhex(hex(@a*@b)); #‘admin’
SELECT@,/*!00000@a:=26143544982.875,@b:=x‘3136’,*/unhex(hex(@a*@b)) #‘admin’
The last code snippet in the preceding example makes use of MySQL-specific code, a feature comparable to conditional comments in JScript. We discuss this further in the section “MySQL-Specific Code.”
Intermediary characters
Thus far, we have seen most of the relevant language elements of SQL queries, and we know how to work with functions and operators as well as how to use them for extra obfuscation. But the most important topic is still to follow: the intermediary characters that we can use between several language elements to separate them. We talked about those in combination with markup in Chapter 2 and learned that often, a surprisingly high number of different characters can be used between tags and attributes. With SQL, the situation is a bit different, since SQL is not a markup language and characters might actually have more semantic and syntactic uses in SQL than in HTML. Let us look at a small script that generates a loop to learn more about these intermediary characters on MySQL with PHP.
<?php
$link = mysql_connect(‘localhost’, ‘username’, ‘password’);
mysql_select_db(‘_test’,$link);
for($i = 1; $i<=255;$i++) {
$chr = chr($i);
if(mysql_query(‘SELECT’.$chr.’1’, $link)) {
echo ‘0x’.dechex($i).‘ (‘.$chr.’)’. “<br>”;
}
}
The result of the preceding code is not very surprising. The usual candidates, such as the Tab key and Spacebar, are working, as are the line breaks, and all characters working as mathematical operators for the 1 can be used as well. What is working too is the character at decimal table position 160 (0xA0), the nonbreaking space. This was documented in 2007, 3 but it is still not very well known and often can be used to sneak a vector through intrusion detection system rules. Oracle and PostgreSQL seem to be rather strict compared to MySQL in this regard, but Oracle allows the null byte to be part of a query, which again leaves a lot of room for filter circumvention. Table 7.2 lists other characters that can be used on the tested DBMSs (the query used in this case was SELECT[intermediary character]1).
3MySQL syntax. http://websec.wordpress.com/2007/11/11/mysql-syntax/.
Things get even more interesting if we change the structure of the loop script slightly and add more characters to test on—this time not only a character in front of the 1 but also a character at the end of the query. Here is the code:
<?php
$link = mysql_connect(‘server’, ‘username’, ‘password’);
mysql_select_db(‘database’, $link);
for($i = 0; $i<=255;$i++) {
$chr = chr($i);
for($j = 0; $j<=255;$j++) {
$chr2 = chr($j);
if(mysql_query(‘SELECT’.$chr.‘1’.$chr2.’’, $link)) {
echo dechex($i).‘,’.dechex($j).‘<br>’;
}
}
}
These results are more interesting than the results from the previous loop, since we can see some interesting DBMS behavior here. For example, the loop unveiled the fact that it is possible on PHP and MySQL, regardless of the connector being used, to actually end a query not only with comments and the null byte plus semicolon combination but also with the character at ASCII table position 96, which is the accent grave or back tick. SQL code such as this actually works, and returns the expected 1 and 2: SELECT 1,2`whatever you might add here. The loop also unveiled the possibility of using a shortcut for setting aliases on MySQL. A query setting the alias for the returned value usually looks like this: SELECT 1 AS A. But it also works if you omit the AS keyword and just execute SELECT 1 A, or if you omit the whitespace, as in SELECT(1)A.
On PostgreSQL, a null byte or a semicolon can be used to end queries, and syntax such as SELECT 1 !2 will not throw an error but will return the result 1. SELECT 1M2 will return 1 as well, and will have the application assume the column name is m, while the field value is also 1 with an unknown column name for SELECT~1!2. A simple SELECT@1 works also, as does SELECT@1ù, and so on.
Fuzzing against DBMSs for intermediary characters and more makes a lot of sense, and basic implementations of fuzzers and loops can be built very quickly, as the example code showed. Especially, when you combine them with more than two different characters, a lot of research can be done and a lot of issues will likely be found, particularly with the rather tolerant and quirky parsers of MySQL and PostgreSQL. In the next section, we will see what possibilities for obfuscation exist in this regard.
Strings in SQL
Strings play an important role in SQL in the context of Web applications. Almost all data being passed from the application to the database as selection criteria or actual data to store are arriving in the form of a string, except for some numerical values. Strings, as we know from many other programming languages, have to be delimited in some way; if that is not possible for some reason, they must be brought into a form of representation that is least likely to interfere with the actual code.
Regular notation and delimiting
In MySQL, we can use two different types of quotes to delimit strings: single quotes and double quotes. PostgreSQL only allows single quotes; double quotes are equivalent to the back tick in MySQL and delimit database, table, and column names. Most DBMSs allow us to equip the delimited string with additional information regarding the character set or the current representation. This is particularly interesting for obfuscation, since this technique is not very well known, and it avoids calling functions such as hex(), unhex(), ascii(), or convert() explicitly.
#MySQL and others
SELECT N‘1’;
SELECT _binary‘1’;
SELECT x‘31’;
SELECT b‘110001’;
SELECT 1’’’;
#PostgreSQL
SELECT E’\101\101’; # AA
Make sure the filter you use to protect your Web site is aware of all the possibilities for creating strings in SQL, starting with quoted data and ranging from hexadecimal representations to the prefixes we saw earlier in this section. A regular expression capable of matching all available kinds of string delimitations is difficult to compose.
Oracle knows an interesting feature for query obfuscation, called the rowid. The rowid is an 18-digit-long string that directly points to the location of the data set, stored as a pseudo-column. The last characters reference the actual file in which the data are being stored, while the preceding characters point to the data record and the data block. We are not going to dive deep into how Oracle stores data, but it is important to know that if an attacker can determine the rowid of the desired data set he can use it for extra obfuscation.
SELECT rowid FROM test WHERE id = 1; /* AAADVOAAEAAAADYAAA = 1 */
SELECT * FROM test WHERE rowid = ‘AAADVOAAEAAAADYAAA’
Also interesting is the ability to set arbitrary quote delimiters in Oracle SQL queries and use them later on. This feature can be used as soon as a string is preceded by a q, followed by a quote, an almost arbitrary character, the actual string, again the character, or a matching character and the final quote.
SELECT q‘(foobar)’ FROM test -- selects foobar
SELECT q‘foobar’ FROM test -- selects foobar
SELECT q‘<foobar>’ FROM test -- selects foobar
SELECT q‘AfoobarA’ FROM test -- selects foobar
Hexadecimal notation
Other characters besides the single quote on all tested DBMSs and double quotes on MySQL do not work for string delimiting. But there are ways around this. MySQL and other DBMSs also know the hexadecimal string notation, which doesn't need any quotes at all, but is introduced by a 0x and a sequence of characters in the range 0-9 and a-F. In hexadecimal notation, the sequence 0x41 represents the uppercase letter A, since it's located at the 41st position of the ASCII table. If the MySQL function unhex() is being used, the preceding 0x can be omitted.
#MySQL
SELECT 0x414141 # AAA
SELECT unhex(414141)
Unfortunately, PostgreSQL does not accept this kind of syntax, but as a slight excuse it allows use of hex entities in the form of the well-known backslash-x notation. So, in PostgreSQL, SELECT ‘x41x41x41’ is equivalent to SELECT 0x414141 in MySQL. The octal notation works fine as well, with SELECT ‘ 61’ returning 1 as expected. PostgreSQL also knows the function to_hex() and, of course, the direct type conversion, which can be bloated to look like this and still work: SELECT varchar‘x3c’::varchar.
#PostgreSQL
SELECT ‘x41x41x41’ # AAA
Unicode
One of the interesting quirks of MySQL is its behavior when Unicode character sets are used. When this occurs, MySQL shows interesting behavior in terms of string comparison, which is documented in the MySQL docs. 4 As soon as a generalized collation is chosen, MySQL starts to lack precision in string comparison for the sake of better performance. However, what sounds great in theory has an interesting impact on Web applications in many situations, and means the character A will be the same for MySQL in a string comparison as the character Ä. The following code snippet shows an example of this behavior:
#MySQL
SELECT ‘A’ <=> ‘Ä’, ‘é’ = ‘E’, ‘u’ = ‘Ü’;
4MySQL Reference Manual, Unicode charsets. http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html.
This can have a major impact in terms of Web application security, especially in a scenario where passwords should be reset or new user accounts will be created. Imagine an application using an entry in its database tables to identify a user with the username admin. If an attacker is able to register another user called ädmin, during a password reset the script might create a reset link for the actual admin account, but send the password mail to the attacker's mail account. Whether this does occur depends on which user entry is selected first, because most likely, both will be selected. The range of characters allowing this imprecise quick matching is large, and includes not only ä, á, and à but also â, as well as many others. The next code snippet shows a more bloated example:
#MySQL utf8_general_ci
SELECT * FROM test WHERE name = ‘ädMÏň’ # selects admin
Escaping
Generally, escaping in SQL works with backslashes and in some situations single or double quotes. The latter is just for quotes which can be escaped by another preceding quote. The following code snippet shows this behavior:
SELECT ‘fo’o‘; # fo’o
SELECT ‘fo’‘o’; # fo’o
SELECT “fo”"o"; #fo"o
This allows an attacker to add an almost arbitrary number of quotes to a string to confuse WAFs and intrusion detection systems. Not only can those quotes be added in the middle of the string but they can also be added at the end of the string, which makes perfect sense but can be used to slip through a filter using bad rules.
SELECT ‘fo''''''''''o’;
SELECT ‘foo''''''''''’;
Another behavior of both MySQL and PostgreSQL is that they allow arbitrary usage of backslashes inside quoted strings. This means both of the following queries will work without any problems on MySQL. Note the extra trailing that was added for the second example. MySQL will ignore any form of whitespace attached to the string as well, whereas PostgreSQL will not.
# MySQL and PostgreSQL
SELECT ‘foobar’ = ‘foobar’; # selects 1
#MySQL
SELECT ‘foobar’ = ‘fooar ’; # selects 1
SELECT ‘foobar’ = ‘foo’ + /* foo */ + ‘bar ’; # selects 1
MySQL seems to set any string to the numerical value null if the string does not start with numerical characters and optional preceding operators to make queries such as this work without throwing errors: SELECT ‘-1foooo’+0. If a string is being used instead of a digit the most probable numerical value will be chosen by the DBMS: 1 for ‘1foo’ and 0 for ‘foo’.
Most DBMSs do not allow direct string evaluation. MySQL and PostgreSQL provide features for executing strings as SQL code in combination with prepared statements and functions. This only works inside the obligatory BEGIN blocks, so tricks such as those shown in Chapter 3 and Chapter 6 cannot be adapted for use with SQL. However, Oracle knows the EXECUTE IMMEDIATE functionality5 which is basically plain string evaluation. Thus, EXECUTE IMMEDIATE ‘SELECT 1 from test’ will work as expected and will return 1.
5Oracle, Security Considerations for Data Conversion. http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements002.htm#CIHJCCEB.
SQL and XML
MySQL and other DBMSs are able to deal with XML in several situations. The basic concept is that strings can contain valid XML and the DBMS is capable of parsing it correctly and retrieving and transforming certain values, usually with XPath-like6 selectors. However, MySQL only provides two rather basic functions, called ExtractValue() and UpdateXML() (see http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html).
6W3C, XPath. www.w3.org/TR/xpath/.
PostgreSQL has more XML features to offer. The PostgreSQL XML function documentation gives a good overview of what developers can use: http://developer.postgresql.org/pgdocs/postgres/functions-xml.html.
Let us look at some code examples to demonstrate how the XML functions in modern DBMSs can be used for payload obfuscation.
#MySQL
SELECT UpdateXML(‘<_/>’, ‘/’, ‘<script>alert(1)</script>’);
SELECT UpdateXML(‘<script x=_></script>’, ‘/script/@x’, ‘src=//0x.lv’);
SELECT(extractvalue(0x3C613E61646D696E3C2F613E,0x2f61));
Depending on the type of attack an attacker tries to perform, it might make more sense to use XML-based obfuscation to generate strings that are useful in conditions or other constructs, or as shown in the preceding example, to generate HTML and JavaScript fragments to get past cross-site scripting filters with an error-based SQL injection. PostgreSQL, as mentioned, provides far more complex XML support and allows us, for example, to create new XML nodes with the given native functions, such as xmlelement().
SELECT xmlelement(name img,xmlattributes(1as src,‘alx65rt(1)’as 117nx65rror))
Equally interesting for generating strings are the functions xmlcomment(), xmlconcat(), and xmlforest(), as well as many others that are capable of generating XML, reading data from valid XML strings, and more.
The next section covers SQL comments and how they can be used to create code and payloads that are hard to read and parse.
Comments
Comments in SQL are usually meant to make it easier for the developer to debug and, more importantly, to add inline documentation to longer or complex queries. In an attack scenario, comments might also help by truncating an existing query and making it stop at the point the attacker needs it to. The different DBMSs know several techniques for using comments—usually the C-style block comments we know that are introduced with /* and end with */, as well as the more database-specific double-hyphen (−−) inline comments. MySQL also features Perl comments (#) and in some situations accepts unclosed comment blocks or a combination of null byte and semicolon as a line ender.
Regular in-query comments
MySQL allows us to use unclosed block comments to end a query, as well as # and double-dash comments. Therefore, SELECT1/* will execute without any errors. However, block comments are especially useful for very effective code obfuscation, as the next examples will demonstrate.
#MySQL
seL/*ect 0
*/e/**/Ct--
/**/1
The problem with block comments is that any filtering solution or intrusion detection system attempting to normalize the string and free it from an obfuscation pattern based on regular expressions will have a hard time dealing with those comments. Similar to the comments in JavaScript, the SQL comments can be nested safely and single characters can again be escaped, so a tool trying to remove only the comments to get more clarity on the vector itself has to know all those obfuscation techniques and quirks. The following example might illustrate why this can be rather difficult:
S/*/e/**//*e*//*/l/*le*c*//*/ect~~/**/1
It is very hard to determine what an actual comment is—where a construct that looks like a comment is nested in an existing comment and where the characters reside that are actually being evaluated by the DBMS. This vector can only be fully understood when you realize that MySQL not only accepts /**/ as a valid block comment but also /*/.
Let us now look at the other comment variations most DBMSs allow us to use: the Perl-style comments and the double-dash.
#MySQL
SEL# inline comment inside the statement
ECT 1;
S/**/ELECT(-- inline-comment and newline + parenthesis
1);
SEL/**/E# combined block and inline comments
CT 1;
The most interesting fact regarding comments is the ability to actually rip apart keywords and even operators, such as ||; for instance, ‘1’/*/*/|/*/*/|2 works as well for concatenation as ‘1’||2.
Since several DBMSs use the @@ notation to address environment and system variables, it might be interesting to see if comment obfuscation can help in this case too. Many intrusion detection system signatures match input such as @@w+, but at least MySQL allows us to use SELECT@/**/@version or even SELECT@#[newline]@version. This is, of course, the same for function calls such as version/**/().
MySQL-specific code
Thus far, we have seen examples for MySQL-specific code in some of the example snippets in this chapter, but we did not go into further explanation. A nonstandard feature that has been available since the early versions of MySQL 3 allows developers to create statements containing conditional comments that will be executed depending on the given minor version of the DBMS. If, for example, a specific statement should be doing different things on MySQL version 3 than it should do on MySQL version 4, or even any other DBMS, the block comment syntax with an additional exclamation mark plus five-digit version number can be utilized. Let us look at an example that selects the major version of the MySQL database:
SELECT--/*!500005#*//*!400004#*//*!300003#*/
The query might look a bit complicated, but it is not. The conditional comments are introduced by the character sequence /*! followed by an optional five-digit code specifying the version number. We can use either 50000 for all MySQL 5 versions or 51371 which is the MySQL 5.1.37.1 version mentioned at the beginning of this chapter. Directly after the five-digit code is the code to execute; if MySQL 5 is present, the query will result in SELECT --5#. The two minus signs were used to avoid having spaces for extra sneakiness. If MySQL 4 is present, this part will be skipped and the next conditional comment will be parsed, and so on.
#MySQL
SELECT(/*!1*/);
SELECT /*!111111*/;
SELECT@:=/*!111111||1*/;
SELECT@:=/*!00000UNHEX(*//*!99999x*/N'3136′/*!00000)*/
It is possible to generate conditional statements and other constructs with this technique by providing absurdly small or high version number information. The version number can, of course, also be omitted, if code length is important. Also, it is possible to use /*!1#*/ as a line-ending comment which can be helpful now and then.
Browser Databases
The most recent generation of Web browsers at least partly supports HTML5, including interfaces supporting complex client-side storage mechanisms. Details on the specification are available in the W3C document titled “Offline Web Applications” (see www.w3.org/TR/offline-webapps/). Those features are particularly interesting for rich client-side applications and Web sites also working in offline mode, providing us the ability to store data if no connection to the server is given. At the time of this writing, two user agents from our test setup mentioned in Chapter 2 supported the openDatabase object and could be used for testing: Opera 10.51 and Chromium 5. The openDatabase object provides a transaction function which is capable of executing actual SQL queries for data storage and retrieval. Let us look at some example code, working on Opera 10.5 and Chromium:
<script>
openDatabase(‘’,1,1,0).transaction(function($){
$.executeSql(
SELECT “alert(1)”', [],function($,results){
for(i in results.rows.item(0)) eval(results.rows.item(0)[i])
}
)
});
</script>
At the time of this writing, not many Web applications made actual use of this feature, but it is expected that over time more and more Web sites will adopt client-side database usage for a better user experience. Also for the mobile sector, offline applications are interesting since those Web sites using openDatabase can still work even if no network coverage is provided.
A cross-site scripting attack against a Web site using openDatabase() can easily lead to a rarely documented form of persistent cross-site scripting. An attacker will have the ability to search for both client-side and server-side SQL injection vulnerabilities, both of which can lead to even more problems, such as sensitive data retrieval, or worse.
From a security perspective, client-side SQL injection attacks will probably become more dangerous over time. A cross-site scripting vulnerability might be capable of harvesting user data not only from the DOM but also from the client-side databases the Web site might be using. Regarding obfuscation, those attacks merge two different worlds: the worlds of JavaScript and of SQL obfuscation, both providing a huge array of possibilities for making code and payload hard to read.
But the different implementations even ship with their own glitches, which can also be used for obfuscation. The following code snippets show several examples of this. Please note that the several mandatory parameters for executeSql() have been omitted for better readability. Usually the user agents use SQLite 3.1+ or an implementation behaving in a similar manner, so most actual SQLite features can be used. For more information on SQLite, refer to the online documentation at http://sqlite.org/lang.html.
$.executeSql(‘SELECT`alert(1)`’); // Chromium
$.executeSql(‘SELECT-1e11”alert(1)”’); // Opera and Chromium
$.executeSql('SELECT~00.000”"alert(1)”); // Opera and Chromium
$.executeSql(‘;;;;SELECT"alert(1)”’); // Opera and Chromium
$.executeSql(‘SELECT-1”"ale
t(1)”’); // Opera and Chromium
$.executeSql(‘SELECT"alert(1)”/**’); // Opera and Chromium
The specification also mentions the ability to use prepared statements. As in many other SQL dialects, the question mark is the placeholder for variable parts of the statement, while the actual replacements will be passed as array elements with the second parameter of executeSql(). Also, the AS keyword can be used to bloat the query with more padding.
$.executeSql(‘SELECT?“alert(1)”’,[1],…);
$.executeSql(‘SELECT ? alnumstring’,[0,],…);
$.executeSql(‘SELECT-~-+1. as"alert(1)”’,…);
$.executeSql(‘SELECT ?1’,[‘alert(1)’],…);
We can use arbitrary numerical prefixes for the value to select; we can also escape any character besides the standard escapes, such as
,
, and others, as well as use x to introduce hexadecimal entities and numerical values to introduce octal entities. This works for any quoted JavaScript string on most tested platforms. The comments we can use in client-side SQL queries are the standard C block comments, /**/, and the double-dash for a one-line comment. What also works is the comment format /** without a trailing slash, as in MySQL.
SQLite allows string concatenation with the || operator, which enables us to execute the following code snippets. An attacker can also use the JavaScript string obfuscation techniques as well as SQL obfuscation in combination.
$.executeSql(‘;;;SELECT"alert”||x‘28’||‘1x29’’,…)
You might have noticed the X prefix in the previous snippet. SQLite also allows us to use entities to represent characters. Similar to MySQL and PostgreSQL, the X prefix can, for example, be used to select the canonical form of a string encoded in hexadecimal entities.
$.executeSql(‘SELECT x‘616c657274283129‘’,…); -- alert(1)
SQLite knows three basic ways to declare variables and assign values to them: by introducing them with a $, the usual @ character, or a colon. Both the colon and the @ character can hold either named or just numbered variables, the latter defined by the order of the passed parameters.
$.executeSql(‘SELECT @0’,[‘x61lert(1)’],…);
$.executeSql(‘SELECT:0::1’,[‘x61lert(1)’],…);
$.executeSql(‘SELECT/**/$a::a::a’, [‘x61lert(1)’],…);
As we can see, client-side databases and SQL executing in the user agent and triggered via JavaScript open up a whole new world of opportunities for attacks against the client, payload obfuscation, and more. At the time of this writing, the implementations available were very young, and it is quite possible that several months will have to pass until those features gain more attraction and are used more widely. Still, client-side SQL injections and comparable attacks can be considered the next step in the evolution of attacks related to the user agent. Even if mitigations for cross-site scripting attacks are successful, as in Mozilla's Content Security Policy (CSP) 7 or the various attempts for dealing with reflected cross-site scripting attacks via Chrome, NoScript, or the internal IE8-9 cross-site scripting filter, those new attack patterns will first have to be enumerated and understood before effective protection is possible.
7Mozilla Content Security Policy. https://wiki.mozilla.org/Security/CSP.
Summary
In this chapter, we saw ways to obfuscate SQL queries, starting with easy string obfuscation, use of encoding functions, and other tricks. Again, small pieces of code just looping over some characters and executing queries against different DBMSs helped a lot in terms of unveiling weird parser behavior and shorthand as well as other useful quirks. We have not covered the whole range of SQL injection, starting with data retrieval, data manipulation, and structural changes and ranging to privilege escalation, out-of-band data extraction, and even remote code executions; other books are dedicated to those topics already. But we did learn about the small things—tricks that attackers can use to make their vectors unreadable and have them slip through the grid which intrusion detection systems and other protection mechanisms created. But SQL injection, and especially SQL obfuscation, is not always just a way to attack the database and Web server.
Another, often-underestimated aspect of SQL obfuscation in connection with even unexploitable SQL injection vulnerabilities is the fact that the encodings understood by the various DBMSs are not part of the feature set of common client-side cross-site scripting defense mechanisms such as NoScript and the IE8 cross-site scripting filter. Imagine a situation where a Web application can be triggered to output SQL error information or just the result from SELECT ‘a’. In this situation, it is often possible, for example, to abuse the vulnerability to smuggle HTML and JavaScript code into the Web site's output using SQL encodings, and thereby likely bypass NoScript or other filters. Although the DBMS will translate the string to its canonical representation, as the following code example illustrates, the client-side protection mechanism will not be able to determine that it is a cross-site scripting attempt.
#MySQL
SELECT 0x3C7363726970743E616C6572742831293C2F7363726970743E;
SELECT Char(60%3600),Char(115),Char(99),Char(114),Char(105),Char(112),#
Char(116),Char(62),Char(97),Char(108),Char(101),Char(114),#
Char(116),Char(--40),Char(49),Char(32+9),Char(60),Char(47),#
Char(115),Char(99),Char(114),Char(105),Char(112),Char(116),Char(62);
SELECT UpdateXML(concat(0x3c,‘script’,0x3e,‘alert(1)’,0x3c,‘/script’,0x3e),‘/x’, 0);
# all queries select <script>alert(1)</script>
Most Web application frameworks, meanwhile, deliver decent protection against SQL injection attacks. Nevertheless, this range of attack techniques will not drastically lose relevance, since many developers still write their SQL queries themselves, use concatenation, and thereby are likely to destroy any protective mechanisms provided by the frameworks and other mechanisms. However, the rise of client-side databases will be a breath of fresh air for SQL injection techniques, and thereby obfuscation as well.
In Chapter 8, we will look at the current situation regarding Web application firewalls and intrusion detection systems, and see what we can accomplish with the knowledge about the topics we discussed in this and earlier chapters.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.