The Quest for a Cure
Explore effective techniques to prevent SQL Injection vulnerabilities in application development. Understand the use and limitations of escaping, query parameters, stored procedures, and frameworks. Learn why no single method is foolproof and how combining approaches with good coding hygiene protects your database securely.
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 the risk of ...