This particular tip is just a link to a useful resource with no discussion on how to use it. Studying various permutations of one specific attack can be useful, but your time is better spent learning how to safeguard against it. Additionally, there is much more to Web app security than SQL injection. XSS (Cross-Site Scripting) and CSRF (Cross-Site Request Forgeries), for example, are at least as common and at least as dangerous.
We can provide some much-needed context, but because we don’t want to focus too much on one attack, we’ll first take a step back. Every developer should be familiar with good security practices, and apps should be designed with these practices in mind. A fundamental rule is to never trust data you receive from somewhere else. Another rule is to escape data before you send it somewhere else. Combined, these rules can be simplified to make up a basic tenet of security: filter input, escape output (FIEO).
The root cause of SQL injection is a failure to escape output. More specifically, it is when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. This is common in PHP apps that construct queries as follows:
view plaincopy to clipboardprint?
In this case, the value of $_GET['name'] is provided by another source, the user, but it is neither filtered nor escaped.
Escaping preserves data in a new context. The emphasis on escaping output is a reminder that data used outside of your Web app needs to be escaped, else it might be misinterpreted. By contrast, filtering ensures that data is valid before it’s used. The emphasis on filtering input is a reminder that data originating outside of your Web app needs to be filtered, because it cannot be trusted.
Assuming we’re using MySQL, the SQL injection vulnerability can be mitigated by escaping the name with mysql_real_escape_string(). If the name is also filtered, there is an additional layer of security. (Implementing multiple layers of security is called “defense in depth” and is a very good security practice.) The following example demonstrates filtering input and escaping output, with naming conventions used for code clarity:
view plaincopy to clipboardprint?
Although the use of naming conventions can help you keep up with what has and hasn’t been filtered, as well as what has and hasn’t been escaped, a much better approach is to use prepared statements. Luckily, with PDO, PHP developers have a universal API for data access that supports prepared statements, even if the underlying database does not.
Remember, SQL injection vulnerabilities exist when the distinction between the format of an SQL query and the data used by the SQL query is not carefully maintained. With prepared statements, you can push this responsibility to the database by providing the query format and data in distinct steps:
view plaincopy to clipboardprint?
1. prepare('SELECT *
5. FROM users
6. WHERE name = :name');
7.
8. // Provide the query data and execute the query.
9. $query->execute(array('name' => $clean['name']));
10.
11. ?>
prepare('SELECT *
FROM users
WHERE name = :name');
// Provide the query data and execute the query.
$query->execute(array('name' => $clean['name']));
?>
The PDO manual page provides more information and examples. Prepared statements offer the strongest protection against SQL injection.
Posts
