SQL Injection attacks target the core of a web application: its
database. Their most significant impact enables an attacker to retrieve,
modify, or delete arbitrary data. It is a serious threat to any
application with a database back-end and a threat that should be fully
understood in order to develop adequate countermeasures.
Every web server administrator must acknowledge techniques that can be
used to identify an SQL Injection vulnerability (see Tobias Glemser's
Article SQL Injection Attacks with PHP and MySQL, hakin9 03/2005) and
assess the scope of its risk. The basic methodology for an SQL
Injection attack is to identify a potential vector, then exploit that
vector with customized SQL queries - all through the web browser.
Identification of the potential for a vulnerability is important, but
even more important is the ability to evaluate its impact. In some
cases, a SQL Injection vector may offer nothing more than the capability
to generate some syntax errors, such as trying to convert strings to
numeric values. In other cases, the vector may enable the attacker to
fully compromise a database's information. Although the examples refer
to MySQL databases, the techniques apply to any database platform and,
in most cases, can be applied without modification. The core of these
techniques targets the SQL language. Certain database extensions merely
make these techniques easier to accomplish.
To refresh the memory
SQL Injection tests can be classified into three categories based on which aspect of the query is targeted:
- attack the syntax of the query - insert common SQL characters
with the intention of generating errors to identify potential attack
vectors,
- attack the syntax of the language - target the SQL language
itself in order to generate database errors or perform simple queries
by manipulating language constructs and semantic identities,
- attack the logic of the query - rewrite the query to retrieve
arbitrary data from tables to which developers did not intend access.
These techniques can be combined to assess a web application and
determine its vulnerability to SQL Injection attacks. In the next
sections the SQL Injection payloads are presented without the entire
URL as an example. This makes it easier to understand the techniques
without cumbersome parameters and text.
This is also because the injection of these payloads is quite simple.
Given a URL of the form http://site/page.cgi?a=foo&b=bar, a SQL
Injection attack replaces the vulnerable parameter's value with its
payload: http://site/page.cgi?a=&b=bar. As a further reminder, one
has to remember to encode spaces and other characters in the payload so
that they do not disrupt the syntax of the URL.
Attack the syntax of the query
The single quote, while arguably the most popular character for
identifying SQL Injection vectors, is by no means the only character
necessary to generate a database error. This technique encompasses most
fundamental tests for potential vulnerabilities by using SQL language
metacharacters or formatting characters to disrupt the syntax of the
original query. For example, the following statements cannot be parsed
into valid queries because they have an ill-formed syntax due to an
unterminated single quote:
- SELECT foo FROM bar WHERE a = ''';,
- SELECT foo FROM bar WHERE a = '/*;,
- SELECT foo FROM bar WHERE a = ';--;,
- SELECT foo FROM bar WHERE a = '#;.
While the most common example is the single quote character (ASCII
0x27), many characters can be used to disrupt the syntax including:
- unmatched parenthesis,
- semi-colon,
- comment delimiter - /*, #, or --.
Validation filters that only prohibit single quote characters (or some
small set of characters) might prevent full exploitation of a
vulnerability, but such filters are often inadequate. They may simply
obscure more fundamental problems with the application's database
connection architecture.
Quotes vs. slashes
PHP developers face several challenges and potentially confusing
recommendations when creating strong input validation filters. PHP's
magic_quotes() function automatically escapes all single quotes with a
backslash character; however, if this feature is combined with a call to
the strip_slashes() function, then the escape characters have been
removed:
- SELECT foo FROM bar WHERE a = '\''; - single quote escaped,
- SELECT foo FROM bar WHERE a = '''; - backslash stripped, query ill-formed.
The other danger of focusing on the single quote character is that
developers may not be aware of the full range of characters and
techniques available to an attacker for exploiting a SQL query. The
attacker can combine SQL functions to generate errors in the syntax of a
query.
You can also use inherent SQL functions to generate errors. The SQL
CHAR()
function prints the ASCII equivalent of the argument. An attacker may
be able to inject quote characters by using odd or even amounts of
CHAR(0x27)
strings (hexadecimal 0x27 represents the ASCII code for the single
quote). This is important, because the attack consists of alphanumeric
characters plus the parentheses.
Consequently, monitoring input for quote characters will not catch or block the attack.
Variables may vary
Database-related errors can also be generated by attacking variable
types. This is most effective against numeric values, but is also
successful against date or time variables. For example, here is a list
of different values that you may try against parameters that expect
decimal numbers:
- 8-, 16-, 32- and 64-bit values - 256, 65536, etc.,
- integer overflows - 2^8 + 1, 2^16 +1, 2^32 + 1, or 2^64 + 1,
- unsigned vs. signed values - inserting negative values,
- floating-point overflows - for example 3.40282346638528860e+38, 1.79769313486231570e+308,
- alternate presentation - binary, octal, hexadecimal, or scientific notation.
These numeric attacks often succeed in generating errors because the
variables used to track these values are not strongly typed. In PHP the
parameter type of all
$_REQUEST variables is a string. This means that, although you can perform arithmetic operations on variables (
$a = 1;
$a++),
the actual type of the variable may be considered a numeric string.
The variable may even be silently promoted from a number to a numeric
string when the value would normally result in an overflow,
inf (infinity), or
NaN (not a number) equivalent. For example, PHP's
is_numeric("1e308") function returns true (it is a number), but
is_numeric("1e309")
returns false - neither a number or numeric string because it is
beyond the double float type that PHP supports. A variable must be set
to numeric explicitly using the
settype() function, but beware that large values may return a value of
inf - which can also lead to errors in the query if it is expecting numerals.
Fighting the synonyms
Robust input validation filters can be an effective countermeasures to
these techniques, but they are not sufficient. Database errors and
other exceptions should be trapped and prevented from being sent to the
browser. Verbose error information tends to provide useful information
for malicious users targeting a database. As we will see a bit later,
input validation filters may be inadequate. For example, we have
already seen that the value 1e309 is not a number (for most languages
and SQL databases) and will generate an error in less secure
applications. Yet 1e309 does not contain any characters that are
normally malicious. It is a purely alphanumeric value.
Note that SQL is a rich language that provides an attacker to create many synonomous permutations. For example,
CHAR(0x27) is equivalent to
ASCII(0x27) which can also be written as
x'27. We focus on using the
CHAR(0x27)
string to avoid raw quotes in the payload, but the specifics of each
test are highly mutable. This also implies that syntax-based filtering -
such as application-layer firewalls - must be very robust in order to
prevent these attacks. In fact, the combination of alternate encoding
schemes (URL encoding, Unicode) and creative SQL will bypass most
pattern-matching filters. Remember,
CHAR(0x27) is the same as
cH%41r(0x68-0x41).
Semantic doppelgangers - attack the syntax of the language
In SQL, Shakespeare's observation of roses might look like the decidedly unpoetic:
SELECT name FROM roses
WHERE scent='sweet';
Whether a rose might be called shoe, bumblebee, or clock, its
sweet-smelling attribute remains unchanged. SQL provides a rich set of
functions that can be used to create semanticly equivalent queries that
look quite different textually. This capability enables an attacker to
identify and exploit injection vulnerabilities even when the server
does not reveal error information or similar output.
While it is useful to break queries in order to find potential
vulnerabilities, it is also profitable to attack the query using the
semantics of built-in SQL functions. Thus, instead of attacking the
parser of the application language (PHP, JSP, etc.), the attack focuses
on the SQL language itself. This has the added benefit of not only
identifying attack vectors, but also provides more information about the
input validation filters used by the application. Another byproduct of
this technique is the ability to perform
blind SQL Injection attacks, or attacks that do not rely on error generation in order to identify or exploit.
Numeric data types
Numeric data types are the easiest candidates to test with this
technique. Figure 1 shows the original example URL, while Figures 2 and
3 present modified addresses We are using an older, insecure version
of FreznoShop online shopping system - releases newer than 1.4 branch
are quite invulnerable.
Consider the following list of name/value pairs:
- rowid = 111,
- rowid = 0x6f,
- rowid = 0157 (octal representation),
- rowid = 110+1 (use 110%2b1 in practice because the + stands for a space character in the URL),
- rowid = 112-1,
- rowid = MOD(111,112),
- rowid = REPEAT(1,3),
- rowid = COALESCE(NULL,NULL,111)
Figure 1. The original example URL
Figure 2. Modified URL string
Figure 3. The same string modified with usage of MOD() function
From a database's point of view, each one of these requests results in
the same value: 111. Also notice that none of these rely on the single
quote character. The first three look like numeric or alphanumeric
strings, the next two have apparently innocuous characters for the
addition and subtraction symbols, and the final three include
parentheses and a comma. If input validation were to focus on stripping
the single quote, then a vulnerable application would gain no benefit
from such a countermeasure.
Raw parameters
This technique, which uses semantic doppelgangers, enables the user to
identify SQL Injection vectors. If the result of each request is
identical, then it can be assumed that the application engine has parsed
the raw parameter value and inserted it into the underlying SQL query.
For example, consider this query for a rowid:
SELECT foo FROM table
WHERE rowid = 110+1;
The database calculates 110+1 = 111 before resolving the rest of the
query, according to its order of operations. This bears the same result
as the original query:
SELECT foo FROM table
WHERE rowid = 111;
Before we explain how to extend this attack to extract arbitrary data,
let us first examine some other cases that can be used for error
generation. Even though this technique does not require us to generate
database errors, such information is useful to determine versions and
names of tables or columns. If the application's input validation
filters have stripped quote characters, but not trapped database
errors, then we can target incorrect SQL function syntax. For example:
- BIN(-1),
- LIMIT a (this is useful because it does not require parentheses),
- MOD(0,a).
Of course, numeric values should also be tested for boundary conditions as mentioned in the previous section.
Premature termination characters
This technique lends itself to the creation of custom SQL queries. Such
queries often do not require quote characters, but often require
premature termination characters. Thus, a request might employ
/* or
-- in order to truncate additional, undesired statements. A string
SELECT foo FROM table WHERE rowid = MOD(111,112)+UNION+SELECT+USER()/*; is a good example.
String values present a greater challenge because there are fewer
functions in the SQL language that provide helpful semantic
doppelgangers. The
CONCAT() function is useful for these cases. In cases where the string argument only contains the letters
a-f , the
HEX() function can be used:
- op=add,
- op=HEX(2781),
- op=REVERSE(dda),
- LEAST(0x6d75736963,0x6e75736963),
- GREATEST(0x61,0x6d75736963).
Once again, we have consciously chosen to avoid using quote characters
because they set off alarms or may be blocked. Yet this doesn't prevent
us from creating complex strings. The
REVERSE(),
LEAST(), and
GREATEST() functions only need parentheses and commas. The following examples are all semantically identical:
- page.cgi?category=music,
- page.cgi?category=REVERSE(cisum),
- page.cgi?category=GREATEST(0x61,0x6d75736963),
- page.cgi?category=LEAST(0x6d75736963,0x6e75736963).
Countermeasures
The best countermeasures for these attacks use input validation filters
and strong data types when assigning user-supplied values to query
parameters. Even though 0x27 is a valid hexadecimal value, it should be
prohibited by the application because the raw value contains a
non-numeric character (or possibly silently coaxed into 27 decimal).
Likewise, octal 0157 should either be denied because of the leading
zero, or the leading zero could be stripped so the value becomes 157
decimal, which is merely a different row number. At the very least,
developers should be aware of alternate bases and understand where they
are interpreted: either in the application language or in the database.
It's very easy to handle all user-supplied data as strings, but if the
data are to be inserted into a query, then they should be explicitly
assigned (cast) to the appropriate data type. For interpreted languages
such as PHP, Perl, C#, or Visual Basic the assignment should be safe or
generate a conversion error. If the web application uses a compiled
language such as C or C++, then the type casting should be handled
carefully and checked for exceptions (think of format-string attacks).
Attack the logic of the query
Breaking the syntax of a query is useful for identifying SQL Injection
vulnerabilities, but it only demonstrates the existence of a problem.
Arbitrary data access is the true risk associated with SQL Injection
attacks.
MySQL supports a specific comment macro that triggers on the database
version /*! */, where is a 5-digit value that represents the MySQL
build. For example, version 3.23.02 looks like 32302, version 4.1.10
looks like 40110, and version 5.0.3 looks like 50003. The most
immediate way to test for embedded SQL attacks with MySQL is to combine
the comment extension with a statement that ensures the query will
fail:
- /*!32302+AND+0+*/,
- /*!32302+AND+0+*//* (it may be necessary to terminate the query).
Then, one can flip the query and ensure that it succeeds in order to
verify the injection vector - /*!32302+AND+1+*//* (it may be necessary
to terminate the query).
UNION SELECT
Once a parameter has been identified as a vector for SQL Injection
attacks, the next step is to determine the amount to which the database
is exposed. This is accomplished by manipulating the logic of the
original query. Most basic queries are of the form
SELECT foo FROM bar WHERE a=b; in which the
b of
a=b clause is the parameter that can be manipulated. Consequently, the new query must consider the previous
SELECT. The quickest technique is to use the
UNION keyword.
The
UNION statement combines multiple
SELECT statements and is supported by most databases. The basic form looks like
SELECT foo FROM bar WHERE a=b UNION SELECT foo2 FROM bar2 WHERE c=d;.
One useful
UNION clause is to display the user name under which the database connection has been established. On MySQL you would do this with
SELECT USER(). Inside a
UNION clause the request might look like
SELECT text FROM articles
WHERE id=0
UNION SELECT USER();
Several challenges present themselves when using
UNION statements for SQL Injection attacks:
- the UNION clause should terminate the query to ensure valid syntax - any additional logic must be truncated,
- UNION statements require matching column counts in each SELECT clause.
The first challenge is relatively easy to accomplish. Simply use one of
the common terminators described in the previous section. This can be a
comment delimiter (
#,
/*,
--) in combination - if necessary - with a semicolon or single quote.
Columns and bears
The second challenge is not difficult to overcome, but requires a few
iterative steps remniscent of Goldilocks and the three bears. The
injected
UNION
clause will either have too few columns or too many of them - what you
need is a number that is just right! If you can observe the database's
error messages, then you'll see something like
The used SELECT statements have a different number of columns.
Column undercounts can be fixed by adding extra columns or column place-holders to the
SELECT statement (see Figure 4). For example, consider the following statements:
- SELECT user FROM mysql.user,
- SELECT 1,user FROM mysql.user,
- SELECT 1,1,user FROM mysql.user,
- SELECT user,user,user,user FROM mysql.user.
Figure 4. A successful UNION SELECT attack
Each one of these queries is designed to grab the user name (or names) from the default
mysql.user
table. The number of columns increases from one to four in each
example. In practice, it is better to repeat the column name to ensure
that the value is displayed in the application. The first placeholder
works, but it's hard to tell which column the web application will
display.
Column overcounts can be addressed by using the CONCAT statement.
Overcounts occur when the first SELECT statement expects fewer columns
than your custom query. The CONCAT statement resolves this by
concatenating each column into a single string. Thus, multiple columns
are reduced to a single column. For example:
SELECT foo FROM table
WHERE a=b
UNION SELECT CONCAT(*)
FROM mysql.user;
This can be combined with the undercount technique when necessary:
SELECT foo,bar FROM table
WHERE a=b
UNION SELECT 1,CONCAT(*)
FROM mysql.user;
The major caveat is that any NULL value in one of the column results will cast the
CONCAT string to
NULL.
Aim at rows
Once you have matched column counts for the query, the next step is
often to specify an arbitrary row to retrieve from a table. When the
query returns multiple rows, often only the first one is displayed. To
some degree, a good
WHERE
clause can help target specific rows, but only if the table's general
structure (column names) is known before-hand. A much easier method
uses offsets within the
LIMIT clause. You can limit the result to one row by using
LIMIT 1, but you can control which row is returned by adding the optional offset beginning with 0. For example:
- SELECT foo FROM table WHERE a=b UNION (SELECT CONCAT(*) FROM mysql.user LIMIT 0,1);,
- SELECT foo FROM table WHERE a=b UNION (SELECT CONCAT(*) FROM mysql.user LIMIT 1,1);,
- SELECT foo FROM table WHERE a=b UNION (SELECT CONCAT(*) FROM mysql.user LIMIT 2,1);.
You can progress through the offsets until the query returns a
NULL
row. Unlike the previous examples of simple queries, it is necessary
to place parentheses around the clause that contains the
LIMIT statement. Otherwise it will be incorrectly applied to the entire query.
Defence by statements
The use of prepared statements (also known as parameterized queries) or
stored procedures are effective countermeasures to these techniques
because they separate the logic of the query from the data of the
query. Consequently, injection attacks can corrupt the original SQL
query, but will not be able to rewrite it in such a manner that
arbitrary tables or data can be accessed.
A potential drawback of prepared statements is that they require
additional set-up within the application. This could lead to a
performance degradation; however, such an impact may be minimal. The
security gains are definitely good.
Help yourself and separate
Inadequate input validation filters are an integral part of SQL
Injection countermeasures, but they are often not the underlying
problem. Strong data typing (assigning numbers to numeric data types,
etc.) is also key, but string data always presents a challenge (see
Frame
Additional SQL tricks).
Additional SQL tricks
Our core idea is to identify a SQL Injection vulnerability via creative
use of SQL formatting characters (syntax) or SQL functions
(semantics), then exploit the vulnerability by attacking the SQL logic.
Although it primarily focuses on numeric and string manipulation,
other functions can be used (or rather misused) to generate errors for
vulnerability identification:
- INET_ATON(),
- INET_NTOA(),
- SOUNDEX().
Enumeration is another important part of SQL Injection; one that is
beyond our scope here. Nevertheless, here are some simple queries that
can be used to further determine information about a database:
- SHOW VARIABLES,
- SHOW STATUS,
- SHOW DATABASES,
- SHOW TABLES,
- DESCRIBE
- EXPLAIN
- EXPLAIN SELECT FROM
,
- SHOW FULL COLUMNS FROM
,
- SELECT USER(),
- SELECT SESSION_USER(),
- SELECT CURRENT_USER(),
- SELECT SYSTEM_USER(),
- SELECT SUBSTRING_INDEX(USER(),'@',1),
- SHOW CHARACTER SET,
- SELECT CURDATE(),
- SELECT CURTIME().
