We all heard of it. Sites going down because of SQL injections. Embarrassed admins. Credibility loss among users. Really simple concept – if your server-side script takes user input verbatim, and issues SQL statements based on it, you will be screwed. There are great scripts out there that will screw you automatically, without personal involvement of someone evil.
SQL injection is scary. Let’s talk about ways you can (and must!) protect your web app against it.
1. Verify data types of all non-string user inputs
Here’s what I do: create a separate class that does all communications to the database. I call it DataRetrieval. In that class, every method just gets you some data from the DB (i.e. executes a SQL query given some parameters), for example,
class DataModification {
public static function saveCocktailFeedback
($ cocktailID, $ starRating, $ userInfo)
{ … }
public static function saveSiteFeedback($ feedbackText, $ userInfo)
{ … }
public static function registerUserSession($ userToken)
{ … }
…
}
Make it a habit to verify each and every non-string parameter in the method body before doing any SQL commands, for example:
public static function saveCocktailFeedback
($ cocktailID, $ starRating, $ userInfo) {if (!is_numeric($ cocktailID) || !is_numeric($ starRating)) {
// someone is trying to hack us; do something (more below)
}…
}
2. Escape all string inputs
On MySQL, this is as simple as
$ str = mysql_escape_string(stripslashes($ str));
I usually create a wrapper class for working with the database (even when I’m using a prepackaged DB class as excellent as ezSQL), and define a function called DB::escape(), that does exactly that.
3. Keep track of security violation attempts
If someone’s trying to hack you, it would be nice to know (1) when it happens and (2) who was it (3) how hard did they try
Remember the “someone’s trying to hack us, do something” comment in suggestion 1? Set up a function similar to the following
public static function log_security_event() {
// get stack trace into the $ trace variable$ sql = “VALUES INSERT INTO securityevent (Trace, IP)
VALUES (‘” .DB::escape($ trace) . “‘, ‘”.
$ _SERVER[‘REMOTE_ADDR’] . “‘)”;// execute the statement
}
This way, when someone tries to hack you, you know exactly what they tried to use, and you can see patterns in their search; you could then determine whether you’re dealing with an input error (oops, someone typed in a quote in a field accidentally) or an intentionally malicious user. You can even gauge the experience level of the hacker by the queries he tries to issue.
This function would fit very nicely into the DB wrapper class that I mentioned earlier.
4. Defense-in-depth: limit SQL user rights
Your PHP application issues a ton of queries to the production database. Poor database, I tell you. If I had to do all those queries, I’d just spit out junk and see if the app server can handle it :-).
Now, seriously: if the attacker was somehow able to run queries on your MySQL server, you want to limit the impact. For example, in the cocktail builder, PHP code should be able to query the known-and-sexy list of cocktails (read only! not modify!); report security violations (write only – only insert, not even modify; no reading allowed, either), and track and report on user feedback around cocktails (read and write).
So, as a part of your build script, you need to set the most-restrictive-possible permissions for the MySQL user that your PHP script will be running as. Here’s what mine looks like, abridged:
Unfortunately, this script can’t be generated automatically (mysqldump can’t dump permissions – at least I don’t know of a good portable way to do this), so you just have to keep this file updated by hand. Good news is that this shouldn’t be too hard: you don’t add tables to your DB that often, do you?
Oh, and one more thing: if you have several applications running on a single MySQL box, make sure that each app gets its own login (like cbuser in my sample script above), and that the logins have zero permissions outside of the designated database. This way, if a hacker screws up one of your apps, others stand a chance.
5. Defense-in-depth: production database backups
This is good against hackers, fires, and your cat chewing the network cable while database sync is happening. Do the (full, not incremental!) database backups regularly, and keep the dumps on a different box (or, even better, at a remote site).
cb