Welcome to another edition of Security Corner. This month's topic is SQL injection, an attack vector that frequents the minds of PHP developers, but for which there is a shortage of good documentation.

Most web applications interact with a database, and the data stored therein frequently originates from remote sources. Thus, when creating an SQL statement, you often use input in its construction. A typical SQL injection attack exploits this scenario by attempting to send fragments of valid SQL queries as unexpected values of GET and POST data. This is why an SQL injection vulnerability is often the fault of poor filtering and escaping, and this fact cannot be stressed enough.

This article explains SQL injection by looking at a few example attacks and then introducing some simple and effective safeguards. By applying best practices, you can practically eliminate SQL injection from your list of security concerns.

SQL Injection

For a moment, place yourself in the role of an attacker. Your goal is initially simple. You want to get any unexpected SQL statement executed by the database. You're only looking to get something to work, because that will reveal the fact that the application has a potential vulnerability. You have as many chances as you want, and you have a lot of information to work with. For example, consider the simple authentication form shown in Figure 1.

Figure 1:

In order to get more information about this form, you view the source:

1. <form action="/login.php" method="POST">
2. <p>Username: <input type="text" name="username" /></p>
3. <p>Password: <input type="text" name="password" /></p>
4. <p><input type="submit" value="Log In" /></p>
5. </form>

You can already make a very educated guess about the type of SQL statement that this application might use to verify the access credentials. It will most likely be a SELECT statement. You can also make a guess about the naming convention used in the database table, because it probably matches the simple names used in the HTML form. (It's also possible that you can cause an error that reveals this information to you.) Because this form is for authentication, there is probably WHERE clause that uses $_POST['username'] and $_POST['password'].

From all of this, you might predict the following:

1. <?php
2.  
3. $sql = "SELECT count(*)
4.         FROM users
5.         WHERE username = '{$_POST['username']}'
6.         AND password = '...'";
7.  
8. ?>

Assuming this guess is correct, what can you do to manipulate this query? Imagine sending the following username:

1. chris' /*

The SQL statement becomes the following:

1. SELECT count(*)
2. FROM users
3. WHERE username = 'chris' /*'
4. AND password = '...'";

This query suggests a successful authentication attempt as long as the chris account exists, regardless of the password. This particular attack is frequently used to steal accounts. Of course, any username can be used (admin is a popular target). Thus, by sending a malformed username, you can manage to log in without having a valid account.

Keep in mind that creativity plays a large role in most attacks. In the previous example, the attack is limited by the type of query (SELECT) and in the way the username and password are used. In other words, as an attacker, you are somewhat bound, and your attacks must try to exploit the situation within these bounds. Other types of queries present new opportunities, and the best practices mentioned in this article apply to all SQL injection attacks.

WHERE Hacking

The WHERE clause is used to restrict the records that a particular query matches. For a SELECT statement, it determines the records that are returned. For an UPDATE statement, it determines the records that are modified. For a DELETE statement, it determines the records that are deleted. If a user can manipulate the WHERE clause, there are many opportunities to make drastic changes - selecting, updating, and deleting arbitrary records in the database.

Imagine a SELECT statement intended to fetch all credit card numbers for the current user:

1. <?php
2.  
3. $sql = "SELECT card_num, card_name, card_expiry
4.         FROM credit_cards
5.         WHERE username = '{$_GET['username']}'";
6.  
7. ?>

In this particular case, the application might not even solicit the username but instead provide it in a link:

1. <a href="/account.php?username=shiflett">
2. Credit Card Information
3. </a>

If a user can have multiple cards, the application might loop through the results of a database query, displaying the card number, name on the card, and expiration date for each card.

Imagine a user who visits the following resource:

1. /account.php?username=shiflett%27+OR+username+%3D+%27lerdorf

This submits the following value for the username:

1. shiflett' OR username = 'lerdorf

If used in the previous SQL query, $sql has the following value:

1. SELECT card_num, card_name, card_expiry
2. FROM credit_cards
3. WHERE username = 'shiflett' OR username = 'lerdorf'

Now the user sees a list of all credit cards belonging to either shiflett or lerdorf. This is a pretty major security vulnerability. Of course, a larger vulnerability exists in this particular example, because a user can arbitrarily pass any username in the URL. In addition, a username that causes the WHERE clause to match all records can potentially expose all records:

1. shiflett' OR username = username

Imagine if this particular username is stored in the database (using a separate SQL injection attack) as the attacker's own username. Every query that is restricted by a WHERE clause in order to only apply the user's own record can potentially apply to all records instead. This is not only extremely dangerous, but it also makes further attacks very convenient.

Input Filtering

There are best practices that you should follow to prevent SQL injection attacks, and these offer a very high level of protection. The most important step is to filter all input (data that comes from a remote source). This includes $_GET, $_POST, $_COOKIE, etc. To help clarify this, consider the following HTML form:

1. <form action="/receive.php" method="POST">
2. <select name="color">
3.     <option value="red">red</option>
4.     <option value="green">green</option>
5.     <option value="blue">blue</option>
6. </select>
7. <input type="submit">
8. </form>

Clearly, the expected values are red, green, and blue. So, the input filtering should verify this:

1. <?php
2.  
3. $clean = array();
4.  
5. switch ($_POST['color']) {
6.     case 'red':
7.     case 'green':
8.     case 'blue':
9.         $clean['color'] = $_POST['color'];
10.         break;
11.     default:
12.         /* Error */
13.         break;
14. }
15.  
16. ?>

This code uses a separate array ($clean) to store the filtered data. It is a good idea to choose a naming convention that will help you identify potentially tainted data. In this example, $clean['color'] can be trusted to contain a valid color, because it is first initialized and then only assigned the value of $_POST['color'] if that value passes the test.

The two most important points for input filtering are:

• Only accept valid data rather than trying to prevent invalid data.
• Choose a naming convention that helps you distinguish tainted data from filtered data.

Escaping Output

With properly filtered input, you're already pretty well protected against malicious attacks. The only remaining step is to escape it such that the format of the input doesn't accidentally interfere with the format of the SQL statement. If you are using MySQL, this simply requires you to pass all data through mysql_real_escape_string() prior to use:

1. <?php
2.  
3. $mysql = array();
4.  
5. $mysql['color'] = mysql_real_escape_string($clean['color']);
6.  
7. $sql = "SELECT username
8.         FROM users
9.         WHERE favorite_color = '{$mysql['color']}'";
10.  
11. ?>

In this case, assuming $clean['color'] is created by the previous example, you can be sure that the color only contains alphabetic characters. (It's one of red, green, or blue.) Thus, the escaping might seem superfluous, and it is. However, it is still a good habit to always escape data. This practice will help you avoid forgetting this crucial step, and it adheres to the principle of defense in depth, which stresses the importance of redundant safeguards.

Until Next Time...

Preventing SQL injection is easy, but it is one of the most common web application vulnerabilities. Hopefully you will now always adhere to the following guidelines:

• Filter input.
• Escape output.

NYPHP has a helpful resource that explains SQL escaping.

I hope you can now safely protect your applications against SQL injection attacks. Until next month, be safe.