Re: SQL injection

Kevin Murphy <murphy(at)genome(dot)chop(dot)edu> writes:
> Can some knowledgeable person set the record straight on SQL injection,
> please? I thought that the simple answer was to use prepared statements
> with bind variables (except when you are letting the user specify whole
> chunks of SQL, ugh), but there are many people posting who either don't
> know about prepared statements or know something I don't.

That's a good way to do it if you are using a client library that
supports it. It's not the Only Way though.

In my mind the issue is not just whether a particular technique is
bulletproof; it's how sure you can be that you have not missed applying
it anywhere. If you rely on applying an escaping function then it's
pretty easy to forget it in one or two places, and it only takes one
hole to be vulnerable :-(. The nice thing about prepared statements is
that it's relatively easier to be sure you haven't messed up, because
the coding rule is pretty simple: if all SQL commands are constant
strings you're definitely safe.

The downside of course is that this approach doesn't handle all cases,
for instance if you have to generate AND/OR where-conditions on the fly
(think of a search engine for example).

regards, tom lane