Re: SQL injection

At 8:57 AM -0500 11/1/05, Tom Lane wrote:
>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.

[snippage]

>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).

The lack of entirely-constant SQL isn't necessarily a barrier to
using prepared statements and bound variables -- I'm doing this in a
pretty large app, and it's not that big a deal to safely and
dynamically generate the SQL and still use bound variables.

The single biggest issue it that the planner has no access to the
data in the where clause, so it unconditionally rules out some paths.
(LIKE clauses with constant prefix text, like "FOO%", get hit hard by
this)

It's certainly not as simple as all-constant SQL text, but it's
definitely doable, and worth looking into.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan(at)sidhe(dot)org have teddy bears and even
teddy bears get drunk