The Quest for a Cure

Now that we know the threat of SQL Injection, the next natural question is, what do we need to do to protect code from being exploited? We may sometimes read a blog or an article that describes some single technique and claims it to be the universal remedy against SQL Injection. In reality, none of these techniques provides security against every form of SQL Injection, so we need to use all of them in different cases.

Escaping values

The oldest way to protect SQL queries from accidental unmatched quote characters is to escape any quote characters to prevent them from becoming the end of the quoted string. In standard SQL, we can use two quote characters to make one literal quote character:

Most brands of the database also support the backslash to escape the following quote character, just like most other programming languages do:

The idea is that we transform application data before we interpolate it into SQL strings. Most SQL programming interfaces provide a convenience function. For example, in PHP’s PDO extension, we use the quote() function to both delimit a string with quote characters and escape any literal quote characters within the string.

<?php 
$project_name = $pdo->quote($_REQUEST["name"]);
$sql = "SELECT * FROM Projects WHERE project_name = $project_name";
?>

This technique can reduce ...