Kristian Nielsen (kristiannielsen) wrote,

Placeholders and SQL injection, part 2

Actually, what I really wanted to blog about before getting carried away with irony yesterday was an old idea on how to force my developers to use placeholders exclusively for SQL queries in applications. As should be apparent from yesterdays blog entry, I am strongly in favour of using placeholders for interpolating values into SQL queries, due to the great reduction in potential bugs (including, but not limited to, SQL injections).

Basically, wrap the database API so that all database access passes through the wrapper. This can usually be achieved, for example by subclassing DBI (for Perl) and returning such subclasses from the application connection pool, or other similar methods. Probably many large web applications already have such wrappers or use APIs that can be patched or extended appropriately.

Now add code that basically bombs out with a big error message if any SQL query contains a quote character. Something like "Always use placeholders for interpolating values into SQL queries! If in disagreement, go see your development lead for your regular spanking!" or words to the same effect.

Sometimes, the wrapper may sit below some code in the database API that emulates placeholders (for example, DBD::mysql used to emulate placeholders in the client using mysql_real_quote_string() or equivalent, since real server-side placeholders are only available with the newer version of the MySQL protocol for prepared statements). But even in this case, the wrapper can still force the use of placeholders by exploiting the fact that MySQL supports both single (')and double (") quotes. Basically, the wrapper would set some private global variable at random to either a single or a double quote, make placeholder emulation use one, and bomb out if the other is detected in query strings. Then any developer trying to sneak manual quoting into the application would quickly be caught, and subsequently taught.

The technique is not perfect. It does not catch completely unquoted number interpolation (shudder). It will also be somewhat of an annoyance to have to specify all string constants as placeholders (there is nothing wrong with "SELECT value FROM t WHERE id = ? AND color = 'red'"). In the end, I never got to implement it, also because my team was small enough and clue-full enough that normal face-to-face talk was sufficient to make placeholders be used throughout.

But if I ever find myself as lead or architect for a web application team, I will be sorely tempted to implement it, as an educational means for the developers and just to see what reactions it will cause.

Tags: developmentprocess, mysql, perl, security
  • Post a new comment


    default userpic

    Your reply will be screened