How to Use an SQL Injection

This tip is just a link to a useful resource with no discussion on how to use it. Studying several permutations of a specific attack can be useful, but your time is spent better learning to protect against it. In addition, there is much more in Web application security than in 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 a much needed context, but because we do not want to focus too much on an attack, we will first take a step back. Every developer should be familiar with good security practices, and applications should be designed with these practices in mind. A fundamental rule is to never trust the data you get from somewhere else. Another rule is to escape data before sending it to another location. Combined, these rules can be simplified to form a basic safety principle: filter inlet, exhaust outlet (FIEO).The root cause of SQL injection is a failure to exit the 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 applications that construct queries as follows:


<?php

$query = "SELECT *
          FROM   users
          WHERE  name = '{$_GET['name']}'";
         
?>


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:

<?php

// Initialize arrays for filtered and escaped data, respectively.
$clean = array();
$sql = array();

// Filter the name. (For simplicity, we require alphabetic names.)
if (ctype_alpha($_GET['name'])) {
    $clean['name'] = $_GET['name'];
} else {
    // The name is invalid. Do something here.
}

// Escape the name.
$sql['name'] = mysql_real_escape_string($clean['name']);

// Construct the query.
$query = "SELECT *
          FROM   users
          WHERE  name = '{$sql['name']}'";

?>

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:

<?php

// Provide the query format.
$query = $db->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.