Re: Protection from SQL injection - Mailing list pgsql-hackers

* Gregory Stark <stark@enterprisedb.com> [080429 14:20]:
> "Aidan Van Dyk" <aidan@highrise.ca> writes:
> 
> > That said, though *I* like the idea (and since I develop against
> > PostgreSQL 1st and use params for my queries I would consider it a nice
> > tool to "keep me honest"), I can easily see that the cost/benefit ratio
> > on this could be quite low and make it not worth the code/support
> > necessary.
> 
> Note that using parameters even for things which are actually constants is not
> really very desirable. If you have a query like:
> 
> SELECT * FROM users WHERE userid = ? AND status = 'active'
> 
> a) It makes things a lot clearer to when you call Execute($userid) which
>    values are actually the key user-provided data. In more complex queries it
>    can be quite confusing to have lots of parameters especially if the query
>    itself only makes sense if you know what values will be passed.
> 
> b) It allows the database to take advantage of statistics on "status" that
>    might not otherwise be possible.
> 
> Parameters are definitely the way to go for dynamic user data but for
> constants which are actually an integral part of the query and not parameters
> you're passing different values for each time it's actually clearer to include
> them directly in the query.

These are all things to consider.  I haven't (yet) needed a dynamic
query like that in my published apps because I would have a prepared
statement for the various status options, and my choice was to have a
couple prepared statements around instead of having a dynamic statement
thats re-planned on every query.

Most of my published applications *are* simple, and I tend to
consolidate as much of my "business logic" in the database as possible
and a "known" set of queries shared by all the related apps, relying
heavily on view, triggers, and functions, so the queries in my web-side
and C-side applications really are very simple and straight forward.

I purposely choose to have "simple static queries" in my apps.  So a
mode which "rejects" queries with literals/constants in them would catch
"bugs" in my code.  Those "bugs" really could be cosmetic, and still
"valid SQL" queries, but one of them could be a valid one which could be
an injection vector.

And so far the statistic/plan selection problems haven't made any of my
queries yet become performance problems...

Again, everything is relative.

a.

-- 
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.