Re: Protection from SQL injection

* Gregory Stark <stark(at)enterprisedb(dot)com> [080429 14:20]:
> "Aidan Van Dyk" <aidan(at)highrise(dot)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(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.