Section 3.2. SQL Injection

3.2. SQL Injection

SQL injection is one of the most common vulnerabilities in PHP applications. What is particularly surprising about this fact is that an SQL injection vulnerability requires two failures on the part of the developera failure to filter data as it enters the application (filter input), and a failure to escape data as it is sent to the database (escape output). Neither of these crucial steps should ever be omitted, and both steps deserve particular attention in an attempt to minimize errors.

SQL injection typically requires some speculation and experimentation on the part of the attackerit is necessary to make an educated guess about your database schema (assuming, of course, that the attacker does not have access to your source code or database schema). Consider a simple login form:

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

Figure 3-1 shows how this form looks when rendered in a browser.

An attacker presented with this form begins to speculate about the type of query that you might be using to validate the username and password provided. By viewing the HTML source, the attacker can begin to make guesses about your habits regarding

Figure 3-1. A basic login form displayed in a browser

naming conventions. A common assumption is that the names used in the form match columns in the database table. Of course, making sure that these differ is not a reliable safeguard.

A good first guess, as well as the actual query that I will use in the following discussion, is as follows:

 <?php $password_hash = md5($_POST['password']); $sql = "SELECT count(*)         FROM   users         WHERE  username = '{$_POST['username']}'         AND    password = '$password_hash'"; ?> 

Using the MD5 of a user's password is a common approach that is no longer considered particularly safe. Recent discoveries have revealed both weaknesses in the MD5 algorithm , and many MD5 databases minimize the effort required to reverse an MD5. To see an example, visit

The best protection is to salt the user's password using a string that is unique to your application. For example:

 <?php $salt = 'SHIFLETT'; $password_hash = md5($salt . md5($_POST['password'] . $salt)); ?> 

Of course, it's not necessary that the attacker guess the schema correctly on the first try. Some experimentation is almost always necessary. An example of a good experiment is to provide a single quote as the username, because this can expose some important information. Many developers use functions such as mysql_error( ) whenever an error is encountered during the execution of the query. The following illustrates this approach:

 <?php mysql_query($sql) or exit(mysql_error()); ?> 

While this approach is very helpful during development, it can expose vital information to an attacker. If the attacker provides a single quote as the username and mypass as the password, the query becomes:

 <?php $sql = "SELECT *         FROM   users         WHERE  username = '''         AND    password = 'a029d0df84eb5549c641e04a9ef389e5'"; ?> 

If this query is sent to MySQL as illustrated in the previous example, the following error is displayed:

 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 'WHERE username = ''' AND password = 'a029d0df84eb55 

With very little work, the attacker already knows the names of two columns (username and password) and the order in which they appear in the query. In addition, the attacker knows that data is not being properly filtered (there was no application error mentioning an invalid username) nor escaped (there was a database error), and the entire WHERE clause has been exposed. Knowing the format of the WHERE clause, the attacker can now try to manipulate which records are matched by the query.

From this point, the attacker has many options. One is to try to make the query match regardless of whether the access credentials are correct by providing the following username:

 myuser' or 'foo' = 'foo' -- 

Assuming mypass is used as the password, the query becomes:

 <?php $sql = "SELECT *         FROM   users         WHERE  username = 'myuser' or 'foo' = 'foo' --         AND    password = 'a029d0df84eb5549c641e04a9ef389e5'"; ?> 

Because begins an SQL comment, the query is effectively terminated at that point. This allows an attacker to log in successfully without knowing either a valid username or password.

If a valid username is known, an attacker can target a particular account, such as chris:

 chris' -- 

As long as chris is a valid username, the attacker is allowed to take control of the account because the query becomes the following:

 <?php $sql = "SELECT *         FROM   users         WHERE  username = 'chris' --         AND    password = 'a029d0df84eb5549c641e04a9ef389e5'"; ?> 

Luckily, SQL injection is easily avoided. As mentioned in Chapter 1, you should always filter input and escape output.

While neither step should be omitted, performing either of these steps eliminates most of the risk of SQL injection. If you filter input and fail to escape output, you're likely to encounter database errors (the valid data can interfere with the proper form of your SQL query), but it's unlikely that valid data is going to be capable of modifying the intended behavior of a query. On the other hand, if you escape output but fail to filter input, the escaping will ensure that the data does not interfere with the format of the SQL query and can protect you against many common SQL injection attacks.

Of course, both steps should always be taken. Filtering input depends entirely on the type of data being filtered (some examples are provided in Chapter 1), but escaping output in the case of data being sent to a database generally requires only a single function. For MySQL users, this function is mysql_real_escape_string( ):

 <?php $clean = array(); $mysql = array(); $clean['last_name'] = "O'Reilly"; $mysql['last_name'] = mysql_real_escape_string($clean['last_name']); $sql = "INSERT         INTO   user (last_name)         VALUES ('{$mysql['last_name']}')"; ?> 

Use an escaping function native to your database if one exists. Otherwise, using addslashes( ) is a good last resort.

With all data used to create an SQL query properly filtered and escaped, there is no practical risk of SQL injection.

If you use a database library that offers support for bound parameters or placeholders (PEAR::DB, PDO, etc.), you can enjoy an extra layer of protection. For example, consider the following query using PEAR::DB:

 <?php $sql = 'INSERT         INTO   user (last_name)         VALUES (?)'; $dbh->query($sql, array($clean['last_name'])); ?> 

Because the data cannot directly manipulate the format of the query, the risk of SQL injection is mitigated. PEAR::DB automatically escapes and quotes the data according to the requirements of your database, so your responsiblity is reduced to filtering input.

If you use bound parameters, your data never enters a context where it is considered anything other than data. This removes the necessity of escaping, although you can consider the escaping to be a step that essentially does nothing (if you prefer to stick to the habit of always escaping output) because there are no characters that need to be represented in a special way. Bound parameters offer the strongest protection against SQL injection.

Essential PHP Security
Essential PHP Security
ISBN: 059600656X
EAN: 2147483647
Year: 2005
Pages: 110

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: