The following section provides a few SQL basics. Table 16-1 shows some of the popular SQL commands with examples of their uses. SQL includes much more than these, but almost every database application uses some of these commands.

In addition to the commands in Table 16-1, there are some command modifiers that we use throughout this chapter. Table 16-2 shows some of the important ones.

The commands in Table 16-1 may be executed on a database system in many different ways. The simplest is the database shell. Here’s how to run some of the above commands using the MySQL database shell called “mysql” on a Linux system.

#mysql
$ use FPdb;
$ select count(*) from events;
74568576

The commands above first specify a database to use (called “FPdb”) and then query the table called “events” for a total number of records, which is returned on the next line. For most databases, the command needs to be terminated by a “;” character.

Other commands may also be run from a shell, and the results are captured in a file. In the case of a database-driven web site or web application, the commands are likely run on a database through some sort of an API, such as JDBC^[2] or ODBC.^[3]

Before we delve into attacks, we will show how relational databases and SQL are used in modern applications, using examples from database-driven web sites deployed on Windows and Unix.

A modern, database-driven web site is characterized by a conspicuous lack of the classic *.html or *.htm extensions for files, preferring instead the newer extensions *.asp, *.php, or many others. Such extensions indicate the use of scripting languages with embedded database commands. The *.asp (which stands for Active Server Pages) extension is common on Windows as it is a Microsoft format. *.php (which uses the PHP language; see http://www.php.net) is common on all *.php platforms.

Each file, such as index.php, contains scripting language commands and usually at least some SELECT queries. These queries are used to combine the content taken from the database with some site-specific formatting performed by the script.

For example, the PHP-Nuke’s web site framework builds various types of web site content (user forums, polls, news, ads, and others) using PHP and a SQL database. The user is responsible for populating the database with content, while the scripting language code builds the actual site structure. Ultimately, the dynamically generated HTML is sent to a visiting user’s browser for display without being stored on a disk on the server.

The database scripting PHP code is full of statements such as the following:

SELECT main_module from ".$prefix."_main
SELECT * FROM ".$prefix."_referrer
SELECT pwd FROM ".$prefix."_authors WHERE aid='$aid'
SELECT user_password FROM ".$user_prefix."_users WHERE user_id='$uid'
SELECT active FROM ".$prefix."_modules WHERE title='$module'
SELECT topicname FROM ".$prefix."_topics WHERE topicid='$topic'
SELECT mid, title, content, date, expire, view FROM ".$prefix."_message WHERE 
active='1' $querylang

Without going into specifics of the PHP scripting language and the details of the application, we can say that most such commands extract various pieces of data from the database and then pass this data to other parts of the application for display. Some others (most likely those mentioning $password) ^[4] refer to user authentication. These likely extract user credentials from the database and compare them with user input supplied through the web site.

There are many other ways that SQL is used to drive the frontend application (that is, the part of the application visible to the user—the opposite of “backend” components such as the database), but web site frameworks provide the most graphic and familiar example. Thus, we use them as examples throughout the chapter.

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.

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.

SELECT first,last,preferences FROM main_table;

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

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

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

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

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

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

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

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

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

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

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

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')

$user='anton'
$pwd='correcto'

INSERT INTO users (username, password) VALUES ('anton','correcto'),

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

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

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.

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.

Security by obscurity, or trying to make the controls opaque and hard to understand, is demonized by most security professionals. The important aspect to understand is that security by obscurity is not inherently evil; it is simply poor practice to make it the only defense against the adversary. It’s obviously a “good security practices” if the application does not provide unnecessary information to the attacker in addition to being coded correctly.

Unfortunately, skilled attackers have successfully penetrated obfuscation defenses against SQL injection. Such defenses will easily foil simple attacks, such as by adding an apostrophe to the web application URL. The probing methodology of such attacks relies on seeing a response from a web application or even, in some cases, directly from the database. The application might therefore be coded to always provide a generic error page or even to redirect the user back to the referring page. In this case, searching for holes and determining whether an attack succeeded becomes a nightmarish pursuit for the attacker. However nightmarish it is, though, it can be done. Attackers have developed sophisticated probing techniques (such as relying on timing information from a query or a command) to indirectly determine the response of the new injection strings.

Overall, the specific tips for thwarting obfuscation by “blindfolded SQL injection” lie outside of the scope of this book. Some excellent papers on the subject are listed in Section 16.5 at the end of this chapter.

The legend of a “magic firewall,” a box that just needs to be turned on to make you secure, continues to flourish. However, there are certain solutions that can protect you from poorly written database-driven applications that are vulnerable to SQL injection. Remember that the attacker interacts with a web application through a browser via a remote connection. Next, the application sends a request for data to the database.

You can try to block the attacks between the attacker and the web application, between the application and the database, or even on the database itself. The conspicuously missing defense—fixing the application—is covered in the next section. Possible defense methods are provided in Table 16-7.

Overall, trying to fix the application problem by dancing around the issue with various tools works to a certain extent. Filters, scanners, and stringent access controls do make the web application harder to hit by SQL injection. These solutions are cost-effective (and may be the only available option) if there is no way to modify the application. Additionally, they provide the needed in-depth defense for database-driven applications. After all, bugs happen, and even the best applications are known to contain errors.

The only true defense against SQL injection is “doing things right.” As we mentioned in the very beginning of this chapter, SQL injection attacks are successful when the user input is allowed to unduly influence the SQL query, such as by adding parameters or even entire queries to the command. Thus, the user input need to be cleaned. But what are the available options?

First, if the type of user input is well known, the application should only allow that sort of data in the input. For example, if a required field is numeric, the application should not allow anything but a number. The options include rejecting anything else or trying to convert the input to the appropriate format. This is the “default deny” policy, which is always a good security decision.

Second, if the user-input type is not well known, at least what should definitely not be there might be known. In this case, you will have to resort to the “default allow” policy by filtering quotes, commands, or other metacharacters.^[7] Some of the filtering decisions can be made for the entire application (never pass quotes to the database) and some depend upon the input type (no commas in the email address).

While writing an in-house, database-driven application, or when deploying an open source application, it makes sense to pay attention to such issues and to design the proper input verification. This measure alone will help protect you from SQL injection attacks so that you won’t end up as an example in some security book, like PHP-Nuke did (see below).

In order to make life simpler, small snippets of code exist for many of the web application languages. Here is a blurb of PHP code, reported on the mailing list (http://www.securityfocus.com/archive/107/335092/2003-08-24/2003-08-30/0), which can check whether a variable is a number. The code rejects all non-numeric input.

function sane_integer($val, $min, $max) 
{ 
   if (!is_numeric($val)) 
     return false; 
   if (($val < $min) or ($val > $max)) 
     return false; 
   return true; 
}

Being aware of coding defenses is important even if you are deploying a commercial application. Just keep in mind that the developers likely made errors and that you will have to take steps to compensate. Such a practice is prudent even if there are no publicly reported vulnerabilities in the application.

Overall, it makes sense to combine several of the above techniques. For example, a well-designed and properly deployed application will do the following:

The above might sound like overkill, and we admit that it probably is overkill for a personal site. However, if your business depends solely on a web site, then those excessive measures and the extra expense suddenly start to sound more reasonable.

We assume that you have a modern Linux system. PHP-Nuke requires that MySQL, PHP, and Apache are installed. You might also need to install the following RPM packages, if you are using Red Hat Linux (all of these are included in the distribution; some other prerequisites might need to be satisfied):

The application is surprisingly easy to install and configure and will produce a flexible database-driven web site, complete with all the latest SQL injection vulnerabilities, in minutes.

Follow these steps to get the application up and running:

We are ready to hit PHP-Nuke with everything we have. If you search Google for “PHP-Nuke SQL hack” you will find dozens of different holes and attack URLs. Here we will demonstrate an attack that saves confidential data into a file.

Launch a browser and access the following URL:^[8]

Now, check the system where PHP-Nuke is running. In the /tmp directory, a file is created which contains the passwords needed to update the banners on the site. Note that those are not the default passwords for site access but rather are the banner passwords, which might not exist by default. In this case, the file will end up empty. The file will be owned by the user “mysql”.

Let’s look at the above attack URL in more detail. We will split it into parts and explain each of them, as in Table 16-8.

This URL contains some of the attack elements we have studied. There is an evil quote character, an “OR 1=1” blast, and a SQL command. Note that we do not use any UNIONs or SELECTs but instead go for the less common INTO OUTFILE.

So we could see what we’ve accomplished, we started the “mysql” database in logging mode (using the “—log” flag), which logs all the executed SQL queries in a file (usually /var/lib/mysql/query.log). In the case of this attack, we find the following statement in the log:

SELECT passwd FROM nuke_bannerclient WHERE cid='' OR 1=1 INTO OUTFILE '/tmp/secret.txt'

This command runs on the “mysql” server and dumps the output into a file, just as desired by the attacker. It can be loosely divided into the legitimate part (“SELECT passwd FROM nuke_bannerclient WHERE cid='’”) and the injected part (“OR 1=1 INTO OUTFILE `/tmp/secret.txt'’”).

There are dozens of other possible attacks against this application; look for them and try them on your system (for educational purposed only, of course). Run SQL in debug mode to observe the malicious queries.

The code was fixed to patch some of the vulnerabilities used above after they were disclosed. Let’s look at some applied fixes.

The above exploit was caused by the following PHP code within the “banners.php” module, in the change_banner_url_by_client( ) function:

$sql = "SELECT passwd FROM ".$prefix."_bannerclient WHERE cid='$cid'";

The function is called from another location within the same script:

case "Change":
change_banner_url_by_client($login, $pass, $cid, $bid, $url, $alttext);
break;

The unfortunate variable $cid is populated by the client’s request, which leads to the SQL injection.

This bug can be easily fixed by making sure that $cid contains only numbers (as it should). The PHP function is_numeric( ) can be used to accomplish this. Another fix, suggested by the original researcher of this bug, is also valid. It uses the PHP command $cid=addslashes($cid) to escape any special characters and thus neutralize attacks. It was such an easy thing to fix, but sadly was slow to be done. At least three subsequent versions of PHP-Nuke came out with the same vulnerability.