Beyond Little Bobby Tables

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

xkcd: Exploits of a Mom

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.