Let's Talk
SQL Injection is an attack that uses cleverly formatted SQL to turn an otherwise benign SQL command into a database-dropping, authentication bypassing monster-beast. If you'd like to learn what SQL injection attacks can do, check out xkcd.com. It has a hilarious comic that demonstrates it nicely.
Before we can learn about the prevention of these attacks, we'll need to understand how they are accomplished. They start at a form, any form, a simple search form will do.
The (My)SQL Query that performs the search will look something like this:
SELECT * FROM blog WHERE MATCH(title, content) AGAINST ("[search string]")
All a would-be attacker has to do is replace [search string] with something malicious, like this:
'); DROP TABLE blog; #this is a comment
Let's see how this affects the SQL Query:
SELECT * FROM blog WHERE MATCH(title, content) AGAINST (''); DROP TABLE blog; #this is a comment')
(to those of you who don't read SQL, this query deletes the entire blog table. Adding insult to injury, you will not get any search results, either!)
As is the case with many internet security holes, this one can be fixed by simply escaping the data. In HTML, you use htmlentities (or some variant), in SQL you use a (usually database-specific) escaping function.
SELECT * FROM blog WHERE MATCH(title, content) AGAINST ("'. mysql_real_escape_string($_GET['search']) .'")';
Just for completeness, here is the terrible, no-good, very bad version:
SELECT * FROM blog WHERE MATCH(title, content) AGAINST ("'. $_GET['search'] .'")';
We've got a decent solution, using mysql_real_escape_string(), but it requires that the programmers be diligent about escaping all input. This is tedious stuff, and it's easy to forget (or be lazy!) and if just one input isn't escaped, that's plenty. What if we had a tool that did the escaping for us? We do! All modern ORMs offer input sanitization. Here is the same code, fully protected against SQL injection, using a WORM Query object:
$query = BlogQuery::select()
->where('MATCH(title, content) AGAINST (?)', $_GET['search']);
Instead of trusting our programmers to always use escape functions, we have instead trusted them to always use a really powerful and simple tool. WORM uses the "prepared statements" feature of databases to safely sanitize the input, and does so using a fluent syntax that most developers will find more aesthetically pleasing than simple strings. Everybody wins!
A similar approach can be used to escape HTML data (as does the Request class in the Fusionbox Framework). The trick is to learn and use tools that automatically escape possibly malicious input.
This approach has an inherit danger/flaw. If you assume your programmers are lazy and forgetful, and write tools that counter these habits, the result can be that your programmers become even lazier and more complacent! I'm afraid the only solution here is to hire the right people. Good luck with that; we already work at Fusionbox!