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:

/* By default, all tables can be read, unless revoked below */
grant SELECT on cocktailbuilder.* to cbuser@localhost identified by ‘xxxxxxx’;

/* Only the following tables can be modified, and only by performing
an INSERT (not UPDATE!). Note that some tables (i.e. securityevent)
will have INSERT, but not SELECT rights.
grant INSERT on cocktailbuilder.rating to cbuser@localhost;
grant INSERT on cocktailbuilder.securityevent to cbuser@localhost;
grant INSERT on cocktailbuilder.userfeedback tocbuser@localhost;

/* Some tables aren’t meant to be read by the app, only the DBA */
revoke SELECT on cocktailbuilder.securityevent from cbuser@localhost;
revoke SELECT on cocktailbuilder.userfeedback from cbuser@localhost;
flush privileges;

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).


Cocktail Builder: JavaScript Alcoholic