16.2 SQL Injection Attacks

We can define SQL injection as an abuse of a database-connected application by passing an untrusted and unauthorized SQL command through to an underlying database.

Let us step back and study this definition in more detail. The first thing to notice is that SQL injection is not an attack against a database. It is an attack against the application using the database. In some sense, the database makes the attack possible by simply being there. While one might argue (and people do, if flames on the corresponding security mailing lists are an indication) that certain steps taken on the database level can prevent SQL injection, the attack is ultimately an abuse of a poorly written application. Thus, most SQL injection defenses are focused on the application and not on the database.

Second, the attacks consist of passing untrusted SQL statements to the database. In a way, the application flaws allow these statements to be passed to the database, with one of several results (to be discussed below) occurring as a result.

Third, you might notice that since SQL is a standard and is used by most databases, the attacks are multi-platform. In fact, the attacks are not only multi-platform, but also multi-application and multi-database. As we will see, many different applications and databases fall victim to these attacks. The vulnerabilities are by no means limited to web applications and web sites; it is just that those are the most common database-driven applications.

A brief look at history is appropriate here. The first public description of a SQL injection attacks was the exciting "How I hacked PacketStorm," by Rain Forest Puppy (posted in February 2000 at http://www.wiretrip.net/rfp/txt/rfp2k01.txt). It is also obvious that the attack was known in the hacking underground well before this account became public. Now, let's look at SQL injection attacks in more detail.

16.2.1 Attack Types

We will first categorize SQL injection attacks by their results to the attacker (see Table 16-3). We will then further refine the categories by the type of SQL statement used.

Table 16-3. SQL injection types

Attack type	Results
Unauthorized data access	Allows the attacker to trick the application in order to obtain from the database data that is not supposed to be returned by the application or is not allowed to be seen by this user
Authentication bypass	Allows the attacker to access the database-driven application and observe data from the database without presenting proper authentication credentials
Database modification	Allows the attacker to insert, modify, or destroy database content without authorization
Escape from a database	Allows the attacker to compromise the host running the database application or even attack other systems

As you can see from Table 16-3, SQL injection attacks are not to be taken lightly. Databases form the core of many online businesses and play crucial roles in other business transactions. Allowing attackers to view, modify, or penetrate databases can pose a catastrophic risk to your organization. Even without breaking out of the database application, the range of attacks that are possible is staggering. With this in mind, let's look at unauthorized data access first.

16.2.1.1 Unauthorized data access

How does one trick an application into revealing more database content than it was originally designed to reveal?

The key is a SQL statement containing variables . For example, if the application runs the following SQL statement:

 SELECT first,last,preferences FROM main_table; 

then SQL injection is impossible , as there is no variable input passed to the query.

Now consider the following:

 SELECT first,last,preferences FROM main_table WHERE $user = $good_guy; 

This statement has a potential vulnerability. However, the mere presence of variable input within the query does not make the statement vulnerable to SQL injection, as there might be no way for the user to influence the value of such a variable.

Admittedly, the example below is highly artificial, but it does drive the point home:

 $user="anton" $good_guy="anton" SELECT first,last,preferences FROM main_table WHERE  $good_guy=$user; 

The above statement is not vulnerable to SQL injection, no matter how poorly the rest of the application is coded. Now, consider the following example:

 $good_guy="anton" SELECT first,last,preferences FROM main_table WHERE $good_guy=$user; 

where $user is passed from the web page input. Does it seem as safe as the previous one? No, nothing could be further from the truth. Imagine that the value of $user is set to "whatever OR 1=1". Now, the statement becomes:

 SELECT first,last,preferences FROM main_table WHERE $good_guy=whatever OR 1=1; 

Suddenly, the WHERE clause matches every record in the database! The first condition ("anton=whatever") is not fulfilled, but the second ("1=1") is always true; thus, the SELECT statement runs across every username in the system, potentially revealing this information to the attacker.

We considered a simplistic case to show how SQL injection may be performed. The important thing to note is that the attack succeeded, since we were able to embed our own SQL code to pass to the database. The attack does not rely on any database vulnerabilities and will in fact succeed with just about every database, provided the access permissions allow the web user to see all the records in the table (which is likely, as it is required for the application to function). The application that allowed us to pass SQL in the variable is the one to blame, not the database.

It is true that application programmers are not prone to coding such elementary mistakes ”at least, not anymore. Thus, applications will not likely allow simple attacks; rather, attackers will have to rely on inadvertent mistakes caused by design decisions made by the developers.

Let us consider some more complicated scenarios for SQL injection. These involve abusing various other queries and possibly getting more out of the database. For example, the above WHERE manipulation allowed us to access more data from the table used by the original query. What if we want to look at some other table? In this case, the UNION abuse technique comes to the rescue. As we mentioned above, UNION is used to merge the results of two separate queries and to show them together.

Let's look back at the query from above:

 SELECT first,last,preferences FROM main_table WHERE $good_guy=$user; 

Suppose we want to look at another table, called "admin_users". The following SQL statement will help:

 SELECT first,last,preferences FROM main_table WHERE $good_guy=$user UNION ALL SELECT  first,last,preferences FROM admin_users 

Obviously, we should inject the following into $user :

 $user="whatever UNION ALL SELECT first,last,preferences FROM admin_users" 

"whatever" should not coincide with any real value in the database; otherwise , this entry will be removed from the results. Additionally, the columns in the above queries should be the same.

So far, we've omitted a couple of points on how to make these attacks a reality. Now, it is time to bring them into the discussion. One of these points is related to the use of quotes. In real life, the queries passed to the database have the following form:

 SELECT first,last,preferences FROM main_table WHERE username = 'whatever' 

or

 SELECT first,last,preferences FROM main_table WHERE  'whatever' = 'compare_with' 

The quotation marks are needed to tell the database application that a string is being passed. Thus, when we inject we have to take care of the quotes, which isn't always easy.

16.2.1.2 Authentication bypass

We can look at the data in the database, which is already a considerable breach of security, but how else can we use our newfound powers? We can try to trick the application into authenticating us without having the proper credentials, such as a username and password. SQL injection again helps us. Here is a SQL query that verifies the login name and password.

 SELECT login FROM admin_users WHERE login = $login_in AND password=$password_in 

How is the above query used? The user submits a login name and password through the web application. This data is then placed into the variables $login_in and $passwdord_in by the web application. The above SELECT query is run with the provided parameters. If there is a row in the database with the same login name and password as provided by the user, the query returns them. The "admin_users" database is depicted in Table 16-4.

Table 16-4. Database table used for authentication

login	password
john	ubersecure
admin	imlametoo
anton	correcto

If such data is unavailable ”say, due to an incorrect login, incorrect password, or both ”nothing is returned. If the data is present, the application then makes a decision on whether to let the user in

Thus, the goal of our SQL injection attack is to make the query return something. We suspect that it is already obvious to the reader that "users" such as "OR 1=1" have a free ticket to use this application.

The following query:

 SELECT login FROM admin_users WHERE login = $login_in AND password=$password_in OR 1=1 

will always return some data, provided the table is populated .

Thus, by injecting data, we can trick the application into making an access control decision on our behalf .

16.2.1.3 Database modification

By now, it should be painfully obvious that SELECT statements may be manipulated by a malicious user. But can we do more, such as INSERT or DELETE data? Inserting data requires finding a part of the application where a legitimate INSERT is made. For example, the web site might provide free registration for all interested users. INSERTs may be manipulated in a similar fashion to SELECTs. For example, the following somewhat unwieldy query is used in PHP-Nuke to insert a new user entry:

 INSERT INTO ".$user_prefix."_users (user_id, username, user_email, user_website,  user_avatar, user_regdate, user_password, theme, commentmax, user_lang, user_ dateformat) VALUES (NULL,'$name','$email','$url','$user_avatar','$user_ regdate','$pwd','$Default_Theme','$commentlimit','english','D M d, Y g:i a') 

Depending upon from where the data is coming (and some is bound to come from untrusted input), we might be able to INSERT something unauthorized.

Manipulating INSERTs is more complicated for the attacker, but it also provides advantages to the attacker. For example, if the application itself does not let you see the data, abusing SELECT is worthless. However, an attacker can tamper with the database for fun and profit (e.g., by adding an account to the system) without seeing any output (known as "blindfolded SQL injection").

In some cases, the attacker might also get a "free ride" if the database allows her to pass several SQL statements in a single command. Thus, a relatively innocuous command such as:

 $user='anton' $pwd='correcto' INSERT INTO users (username, password) VALUES ('anton','correcto'); 

becomes an evil:

 INSERT INTO users (username, password) VALUES ('anton','correcto'); INSERT INTO users  (username, password, is_admin) VALUES ('evil','thouroughly','yes') 

If an attacker can set the $pwd value to be as follows :

 $pwd='correcto'; INSERT INTO users (username, password, is_admin) VALUES  ('evil','thouroughly','yes')' 

Insertion may often be thwarted by proper database access controls and other factors, and overall it is considered to be less reliable than various SELECT abuses .

16.2.1.4 Escape from a database

Up to this point, most of our SQL injection activities centered on the database application itself. However, can we dream of breaking out of the confines of the database onto the underlying computing platform, be it Unix or Windows? In certain cases, this is indeed possible. However, most such techniques are fairly advanced and utilize weaknesses (or, at least, features) of specific database solutions.

Most of the documented "escape from the database" attacks center around Microsoft SQL server and its powerful stored procedures functionality. Such procedures allow attackers to execute commands on the machine itself, to connect to other servers, and even to scan ports using the built-in server tools.

For example, Microsoft SQL Server contains an extended stored procedure called "xp_cmdshell" that allows execution of arbitrary commands on the server. If an attacker manages to inject a call to this procedure (provided it is not removed or disabled), she can control the operating system and other applications. Thus, if you see a URL similar to the following ^[5] being accessed on your web application:

^[5] Here, for illustrative purposes, we disregarded the fact that some characters , such as the apostrophe, might need to be escaped in the URL string.

http://www.examples.com/ecom/bad.asp?';xp_cmdshell+'nmap+10.11.12.13'+;--

then trouble is near.

16.2.2 Looking for Errors

We have looked at some of the goals and possibilities of SQL injection. But how does we actually go and look for the errors that allow them in real-life web applications? There are two possible approaches. First, you can browse through the source code of the application to find potential instances where untrusted user input is passed to the database. This approach is only applicable to open source solutions. Looking for SELECTs, INSERTs, UPDATEs and other statements utilizing input from the web user, and then figuring out a way to influence such input, will go a long way toward finding more SQL injection vulnerabilities. We will illustrate some of these techniques in the later section on PHP-Nuke hacking.

The second (and by far most common) approach is "black-box" testing of the real deployed application. While full web penetration testing is beyond the scope of this book, we can identify some of the simple but effective steps one might try with a web application. The application is probed through a browser by modifying the access URLs, appending parameters to them, and so on. Such attacks can only succeed on a database-driven web site, and no amount of "index.html?whatever=SELECT" will get you the desired result.

The basic things to try on a new web application are shown in Table 16-5.

Table 16-5. Basic SQL "attack strings"

String	Expected result
'	Checking whether the application escapes quotes is the first step to learning its flaws and its vulnerability to the simplest of SQL injection attacks.
'OR 1=1	This is a part of a common attack tactic (described above) where the WHERE clause is bypassed by being set to `true', thus increasing the amount of data extracted from a database.
'OR 1=1'	Another version of the above.
;	Checking whether the application escapes the semicolon character helps to determine its vulnerability to multiple query attacks (described above for the INSERT case).

Keep in mind that in such tests using the URL, spaces and some other characters need to be escaped. For example, a space becomes a "%20" character, based on its ASCII code.

Looking for a flaw using black-box methods might take a long time, might not succeed anyway, and might be highly visible to the site owners . However, if preliminary tests (such as the quote test) show that the application is indeed coded incorrectly and contains flaws, exploitation is just be a matter of time.