Section 3.2. SQL Injection
3.2. SQL InjectionSQL 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'"; ?>
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.
|
