Web Hacking Lesson 2 - SQL Injection

SQL Injection

Note: If you haven't read Lesson 1 go check it out first for test application install instructions.

SQL injection attacks bear many of the same fundamental hallmarks as XSS attacks. At its core and SQL injection abuses the web application to introduce unintended functionality. SQL injection aims to escape out of the confines of a developer crafted SQL statement to alter the SQL. Take the following example:

$name = $_POST['name'];
$query = "select * from users where user_name = '$name'";
$result = mysql_query($query);

If the post variable "name" is set to an expected value, say, Bob, then the resulting SQL will execute:

select * from users where user_name = 'Bob'

This is a perfectly valid SQL statement and probably functions in the exact manner that the developer intended. Problems creep into the system when users are allowed to change the way that the SQL executes. Imagine what would happen if a user decided to input "Bob'; delete database; --". The query that result from such input would be:

select * from users where user_name = 'Bob'; delete database; --'

in SQL the semi colon is used to delineate statements. Thus, instead of one SQL statement, the input breaks up the actual SQL that will be executed into three statements. In SQL the double dash "--" indicates the start of a comment, so each of these statements would end up being valid, resulting in:

select * from users where user_name = 'Bob';
delete database;
--'

You can see the catastrophic damage that could be done with such a query. Luckily for us, we're utilizing a PHP/MySQL application that uses the mysql_query() function to pass queries to the database. This function won't allow stacked queries (meaning only one query can be passed into the function at a time). This means that the above described attack won't actually work on a PHP/MySQL application. This doesn't, however, mean that PHP/MySQL applications are immune from the SQL injection threat!

Knowing how a SQL injection attack works can you see any SQL injection vulnerabilities in the test application? There are lots of them so keep your eyes out for them.

Finding SQL Injection Vulnerabilities

The most difficult thing about a SQL injection attack is finding a vulnerability. Pulling off a successful SQL injection requires some precise information about the type of database, the database scheme, and even table structures. Finding this information can be quite difficult. Of course, one could always just attempt blind guessing, but this isn't very effective. Fortunately many developers unwittingly provide mechanisms for an attacker to reconnoiter an application for SQL injection attacks.

It is quite common for developers to include debugging messages when SQL queries fail. This is completely unnecessary since the errors are useless to an end user and only help to aid an attacker in mapping the data structures. Ideally error messages should be logged and a helpful message should be displayed to the user that indicates whom to contact and perhaps even alerts a developer to the fact that an error log has been produce. All to commonly, however, you will find PHP that looks something like:

if (! $result = mysql_query($query)) {
	echo "Problem with query:  $query<hr/>";
	die(mysql_error());;
}

Not only will this expose the error message, it will also expose the query that caused the error. This gives a nice blueprint to an attacker as to the layout of an application.

In the test application all the error messages are constructed using this poor reporting. You can trip a SQL error by attempting to log in using a username with a single quote in the name. This will cause a SQL injection that will throw an error. For instance attempting to log in using the username 'foo (leave the password field blank) will cause the following error to be displayed:

Problem with query: select user_password from user where user_name = ''foo' and user_password = 'd41d8cd98f00b204e9800998ecf8427e' 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foo' and user_password = 'd41d8cd98f00b204e9800998ecf8427e'' at line 1

This error message provides a real wealth of information. Let's start by analyzing what caused the error. It's easy to spot the orphaned single quote in the query at the "name = ''foo'" portion. The extra quote in the name is causing malformed SQL. This can be worked around with some cleverness, but proves that SQL injection is possible. The second error message is perhaps the most helpful in identifying the exact source of the error message.

The second interesting tidbit is the password part of the query. Instead of passing a blank entry there is a 32 character string. This is pretty much a dead giveaway that the application is, quite wisely, storing hashes of passwords in the database. You can guess that the value inserted by a user is hashed and that hash value is compared to the value in the database.

Despite the use of a secured password we can actually use the SQL injection to bypass the authentication of the test application. Looking at the blog posts it seems like there is a user named 'admin'. We can try to authenticate as admin by exploiting the logic in the user authentication query. Because we can manipulate the query with SQL injection we can introduce new parameters to the query. See if you can figure out how to do this on your own.

Exploiting the Vulnerability

Once you've tried a few combinations we can look at one of the several ways that we can end run this authentication. The authentication SQL query is structured as such:

select user_password from user where user_name = '[value]' and user_password = '[value]' 

Because SQL is evaluated left to right with respect to AND and OR, what if we threw an OR statement into the query that always evaluated as true. For instance, what if we manipulate the query so that it ends up being:

select user_password from user where user_name = 'admin' AND 1=1 OR user_name='admin' and user_password = '[hash value]' 

We can do this by logging in using the username:

admin' AND 1=1 OR user_name='admin

Go ahead and try this out and notice how the site actually lets you log in as the administrator. This is because the SQL query ends up selecting the password if the name is 'admin' and 1=1 (which it always does) OR if the name is 'admin' and the password is whatever hash we got. You can see the second part of the query fails, but the first part succeeds, and because:

IF TRUE OR FALSE = TRUE

The query returns the requested value. You can also see how a good understanding of SQL helps an attacker with this sort of attack.

There are several other SQL injection possibilities throughout the test site. Because query stacking is illegal using the mysql_query() function the damage from SQL injection is limited to the type of query being executed. Thus, an INSERT statement could be manipulated to insert new or malformed data. More dangerous would be an UPDATE statement. In fact, there is an UPDATE statement available in the test application. If you log in and create a new user account you can update your account name and password. You can perform a SQL injection attack to update the admin password, which would cause a denial of service for the real admin and elevate your own privilege to that of admin. The most dangerous type of SQL injection would occur with a DELETE statement, but there aren't any of those in this particular test application.

Protecting Against SQL Injection

The easiest way to protect against SQL injection attacks is to sanitize data as it is passed in by a user, as well as sanitizing any data that is pulled out of the database. PHP has several amazingly useful functions for this purpose. The most effective is mysql_real_escape_string(). Using this will prevent any SQL injection by escaping single quotes properly.

Using strict data typing can also help. Functions like intval() can be used to translate user input into strict data types. This prevents bad data from even making it into the query.

Other functions such as htmlentities(), htmlspecialchars(), and even urlencode() can be used to craft safer data for display. This can prevent problems down the road, protecting against threats such as XSS.

The bottom line is that you should never trust user input. Never trust any input from the client. Even hidden form fields can be manipulated, and Javascript checks can easily be evaded (just check out the Tamper Data extension for Firefox to see how easy this is). You have to be sure you sanitize data on the server side before passing it to a SQL query. Even once data is in the database, it should be handled with care when extracted and displayed.

Using prepared statements and libraries like MDB2 can greatly enhance your application security. Using a central library for all SQL queries can insure that each query is properly sanitized before being executed (rather than trying to keep track of and secure queries all over your code). Using object oriented code can also help, forcing variables to be passed into objects before being passed to SQL queries provides further opportunities to sanitize your data.

Further Reading

• MySQL SQL Injection - this white paper provides an excellent resource that covers PHP SQL injection with respect to MySQL specifically, but also covers other database technologies.
• Advanced SQL Injection In SQL Server Applications, by Chris Anley - an excellent, and extremely thorough, examination of SQL injection covering various platforms and technologies.
• OWASP - SQL Injection - OWASP's page about SQL injection which provides an excellent overview as well as further resources.