| ||
SQL injection vulnerabilities can arise in any application parameter that influences a database query. This includes the usual suspects of URL parameters, POST data, and Cookie values. Consequently, it's necessary to test all of these aspects of an application to determine if a vulnerability is present. The easiest way to identify a SQL injection vulnerability is to add some invalid or unexpected character to a parameter's value and watch for errors in the application's response. This syntax-based approach is most effective when the application doesn't suppress error messages from the database. When such error handling is implemented (or some simple input validation is present), then vulnerabilities can also be identified through semantic techniques that test the application's behavior to valid SQL constructs.
Syntax tests inject some character into a parameter with the intent of disrupting the syntax of the database query. The goal is to find some character that generates an error in the database, which is then propagated back through the application and returned in the server's response. We'll start with the most common injection character: the single quote ('). Remember that the single quote is used to delineate string values in a SQL statement. So, our first SQL injection test looks like this:
http://website/aspnuke/module/support/task/detail.asp?taskid=1'
The server's response as seen in a browser shows a database error and the invalid query that the application tried to submit to the database. Look for the WHERE tsk.TaskId=1' string near the end of the error message in Figure 7-1 to see where the injected character ended up.
Now let's take a look at how and why this works: string concatenation. Many queries in a web application have a clause that is modified by some user input. In the previous example, the detail.asp file uses the value of the taskid parameter as part of the query. Here is a portion of the source code. Look at the underlined section where the taskid parameter is used (some lines have been removed for readability):
sStat = "SELECT tsk.TaskID, tsk.Title, tsk.Comments" &_ ... "FROM tblTask tsk " &_ ... "WHERE tsk.TaskID = " & steForm("taskid") & " " &_ "AND tsk.Active <> 0 " &_ "AND tsk.Archive = 0" Set rsArt = adoOpenRecordset(sStat)
The use of string concatenation to create queries is one of the root causes of SQL injection. When a parameter's value is placed verbatim into the string, then an attacker can easily rewrite the query. So, instead of creating a valid query with a numeric argument like this:
SELECT tsk.TaskID, tsk.Title, tsk.Comments FROM tblTask tsk WHERE tsk.TaskID = 1 AND tsk.Active <> 0 AND tsk.Archive = 0
The attacker disrupts the syntax by introducing an unmatched quote character:
SELECT tsk.TaskID, tsk.Title, tsk.Comments FROM tblTask tsk WHERE tsk.TaskID = 1' AND tsk.Active <> 0 AND tsk.Archive = 0
The incorrect syntax creates an error, which is often transmitted back to the user's web browser. A common error message looks like this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax...
We'll look at more errors in a little bit. Right now we're just focusing on what payloads identify SQL injection vulnerabilities. Inserting a single quote and generating an error won't reveal passwords or enable the attacker to bypass access restrictions, but it's often a prerequisite. We'll explore more advanced ways of rewriting the query in the next section. For now, let's examine other ways to identify vulnerabilities when the application might have simple input filters that strip or inoculate the single quote character.
The single quote character is by no means the only character that can disrupt a query's syntax. Table 7-2 lists some additional characters useful to the identification of SQL injection vulnerabilities.
Characters | Relation to SQL |
---|---|
' | Single quote. Used to delineate string values. An unmatched quote will generate an error. |
; | Terminate a statement. A prematurely terminated query will generate an error. |
/* | Comment delimiter . Text within comment delimiters is ignored. |
--%20 | This may prematurely terminate a query. |
( | Parentheses. Used to group a logical subclause. Unmatched |
) | parentheses will generate an error. |
a | Any alphabet character will generate an error if used in a numeric comparison. For example, WHERE TaskID = 1 is valid because the TaskID column is numeric and the number 1 is numeric. On the otherhand, WHERETaskID=1a isinvalidbecause 1a isnotanumber. |
Of course, this technique is predicated on the fact that the application will return some sort of message to indicate a database error occurred. Otherwise, it's not possible to definitively say whether a vulnerability exists or not. Table 7-3 lists some common error strings produced by databases. The list is by no means comprehensive, but it should give you an idea of what errors look like. In many cases, the actual SQL statement accompanies the error message. Also note that these errors range across database platform and development language.
Platform | Example Error String |
---|---|
ODBC, ASP | Microsoft OLE DB Provider for odbc Drivers error '80040e21' |
ODBC, C# | [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark |
.NET | Stack Trace: [SqlException (0x80131904): |
Oracle, JDBC | SQLException: ORA-01722: invalid number |
ColdFusion | Invalid data for CFSQLTYPE |
MySQL, PHP | Warning: mysql_errno(): supplied argument is not a valid MySQL |
PostgreSQL, Perl | Warning: PostgreSQL query failed: |
Finally, some errors occur in the application layer before a statement is constructed or a query is sent to the database. Table 7-4 lists some of these error messages. It is important to distinguish where an error occurs. The threat to an application differs greatly between an attack that generates a parsing error (such as trying to convert a string to an integer) and an attack that can rewrite the database query.
Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. ERROR: column "foo" cannot be cast to type "int4" Overflow: 'cInt' error. Syntax error converting the varchar value 'a b ' to a column of data type int. |
Throughout this chapter we mostly refer to a URL parameter or POST data as the point of entry for a SQL injection attack. In fact, any dynamic data that can be modified by the user represents a potential attack vector. Keep in mind that cookie values should be tested just like other parameters. Figure 7-2 shows an error when a single quote is appended to a cookie value for a very old version of phpBB.
An application is not necessarily secure from SQL injection even if a single quote didn't produce an error string. A system administrator may have configured the server to respond with a default error page, or even the home page, whenever the application encounters an error. A developer may have been savvy enough to strip the quote character from any parameter received from the user. Yet the absence of an error only means that the application was secure from one type of SQL injection attack. We'll establish some guidelines for this technique:
Do not rely on error strings to determine the presence of a vulnerability.
Do not rely on single quotes to determine the presence of a vulnerability.
Minimize the necessity for "suspicious" characters as part of the payload. As you'll see, commas and parentheses are often useful for these tests.
Semantic-based attacks take a 180-degree turn from the idea of injection characters to disrupt a query. A semantic-based attack, or "blind" SQL injection, does not rely on the error information produced by an invalid query. These attacks try to rewrite the query in such a way that its meaning stays the same, but its content differs. Before we dive into how this works with SQL, think back to basic algebra and the associative and commutative properties:
x + (y + z) = (x + y) + z
x + y = y + x
We'll adopt these properties to the concept of SQL injection to create "semantic doppelgangers"queries that yield identical results with alternate constructions. SQL, of course, supports many math functions, so we'll start with them. Imagine an online store that has thousands of products to choose from. One way to ease the user's browsing experience is to organize the products into catalogs and use a URL parameter to track the current catalog:
http://website/browse.cgi?catalog=17
Now, take a look at some alternate queries that are intended to determine the presence of a SQL injection vulnerability:
http://website/browse.cgi?catalog=10%2b7 (10+7)
http://website/browse.cgi?catalog=MOD(17,18)
http://website/browse.cgi?catalog=0x11
Tip | Remember that the plus symbol (+) represents a space (ASCII 0x20) in a URL parameter. Encode it as %2b to ensure the application receives the correct symbol. |
Since this technique doesn't rely on error messages, there are no specific patterns or strings to look for in the web server's response. Instead, you're looking to see if two requests with different parameter values return the same information. For example, Figures 7-3 and 7-4 have identical responses even though the id parameter has different values.
For example, consider a URL that includes the MOD(17,18) value for catalog. The raw string, MOD(17,18), is intended to be passed verbatim to the database. Then, the database will resolve the query because it conforms to a valid syntax, albeit one that uses a function to determine the catalog number. For example,
SELECT Name,Price FROM ProductTable WHERE Catalog=MOD(17,18)
is equivalent to
SELECT Name,Price FROM ProductTable WHERE Catalog=17
Addition is probably the easiest test to apply to numeric parameters. Table 7-5 describes some other payloads that use SQL capabilities to resolve the parameter's value.
Payload | Description |
---|---|
n+m | Addition |
MOD(n, n+1) | Modular arithmetic |
0xhh | Hexadecimal representation |
0nnn | Octal representation |
COALESCE(NULL,n) | Return first non-NULL value in list |
String-based tests present an additional challenge to this SQL injection technique because they are often enclosed by single quotes. Consequently, values like 'foo' and '0x666f6f' will be different because the latter value is interpreted as a string rather than the hexadecimal equivalent of a string. See Table 7-6.
Payload | Description |
---|---|
0x666f6f | Hexadecimal representation of ASCII character string. 0x666f6f = foo |
CONCAT(0x666f6f) | CONCAT() function. Concatenate a list of strings. |
LEAST(0x670000,0x666f6f) | Return the least/greatest value in a list. |
GREATEST(0x61,0x666f6f) | MySQL, Oracle |
REVERSE(0x6f6f66) | Reverse a string. |
REVERSE(REVERSE(0x666f6f)) | MySQL, SQL Server |
COALESCE(NULL,0x666f6f) | Return first non-NULL value in list. |
CHAR(0x66,0x6f,0x6f) | Create the string character by character (MySQL). |
If the application does not strip single quotes, then you can perform some different types of alphanumeric tests. These tests, listed in Table 7-7, would be necessary when the parameter is wrapped with single quotes in the query, e.g., SELECT * FROM table WHERE a='foo'.
Payload | Description |
---|---|
foo'%3b'bar' | String concatenation in Microsoft SQL Server Split a string into components and use the + operator to re-create the string. For example, foo+bar = foobar |
foo''bar' | String concatenation in Oracle. |
At the beginning of this section, we put forth a guideline whereby we would avoid generating errors and not rely on error strings to identify vulnerabilities. Of course, if we can generate an error, then we can obtain some useful information including the type of database and possibly even a listing of the original SQL query. Table 7-8 presents some useful payloads that will generate a database error. These are most successful against parameters that expect numeric arguments.
Payload | Description |
---|---|
1e309 | Arithmetic overflow |
MOD(0,a) | Non-numeric argument to MOD() function |
COS(a) | Non-numeric argument to COS() function |
1/0 | Divide by zero error |
Behavior-based tests, or "blind" SQL injection, can identify vulnerabilities where syntax-based tests do not. Blind SQL injection does not rely on "suspicious" characters like the single quote, nor does it require an error message to determine success.
Tip | One of the easiest ways to defeat these techniques when used against numeric parameters is to explicitly assign their values to a numeric data type (for example, an integer). The value "1" can be considered a string or an integer, but the value "MOD(1,2)" is definitely a string. |
SQL injection payloads can often be rewritten to bypass input validation filters. Alternate character encodings are also useful when an application explicitly strips one particular character necessary to SQL queries. Tables 7-9 and 7-10 list alternate characters that many databases will consider equal to space delimiters. You can also try the comment characters, for example:
SELECT/**/column/**/FROM/**/table/**/WHERE/**/clause
URL Encoded Value | URL Encoded Value | URL Encoded Value |
---|---|---|
%01 | %12 | %1a |
%09 | %13 | %1b |
%0a | %14 | %1c |
%0b | %15 | %1d |
%0c | %16 | %1e |
%0d | %17 | %1f |
%10 | %18 | %20 |
%11 | %19 |
URL Unicode Value | URL Unicode Value |
---|---|
%u2000 | %u2004 |
%u2001 | %u2005 |
%u2002 | %u2006 |
%u2003 | %u3000 |
Of course, other encodings like Unicode and URL encoding might bypass filtersalthough they should be blocked by any decent one. You can use the SPACE() function on Microsoft SQL Server to serve as a delimiter, as in this example:
SELECT(SPACE(1))column(SPACE(1))FROM(SPACE(1))table(SPACE(1))WHERE (SPACE(1))clause
| ||