Thursday, January 20, 2011

Beyond Little Bobby Tables

The xkcd comic about 'little bobby tables' is a classic, widely known and often quoted:

the strip title is 'Exploits of a Mom', and the punch-line is 'I hope you've learned to sanitize your database inputs'.

The comic gets quoted and linked often, generally to illustrate the point about sanitizing database inputs.  While the verb sanitize is a terse comic-scale expression to represent avoiding SQL injection attacks, it is only part of securing the database.   To sanitize is to remove the dangerous parts of the input.  Sanitizing the input in the xkcd example would presumably involve escaping the singe quote, and would have protected the database.

However, there are other ways to accomplish the desired safety.

  • If you use parameterized queries, the inputs are quoted for you.  
  • If you embed the query in a procedure, with typed parameters, the parameters are firstly forced to match the parameter type, and are then safely interpolated into the embedded query.
  • Using a limited role to run the query, a role that does not have permissions to do more hazardous operations than are required for the query.  For example a role sufficient to the xkcd example would require INSERT privilege, but not DROP nor DELETE nor CREATE.
These three can all be used together, providing multi-layered security.  Using procedures with typed parameters will neutralize SQL injections which are matched to parameter types other than char, varchar, text, and blob.   The SQL injections which map to valid textual types reach the query, but are implicitly quoted and escaped.  Should someone defeat the escaping mechanism somehow (there have been a few exploits in PostgreSQL over the years, not necessarily in the escaping), then the role privilege limits would prevent the most severe of data losses.  


  1. I just sent this classic to a client yesterday, along with this advice:

    You should be using parameterized queries, and passing the parameters separately. Interpolating string values, whether or not you think they are properly escaped, into SQL literals is an SQL injection attack waiting to happen. The best defense is to get out of that game altogether. See the php manual. Use of interpolated data vales is one of the first things security auditors look for in PHP apps. It's a pervasive pattern, unfortunately, and has resulted in huge numbers of security breaches over the years.

  2. On some databases such as Oracle parameterized queries also gave the advantage of the shared query plan cache. They perform better.