Placeholders and SQL injection, part 2|
Actually, what I really wanted to blog about before getting
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