From: | Aidan Van Dyk <aidan(at)highrise(dot)ca> |
---|---|
To: | Gregory Stark <stark(at)enterprisedb(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tom Dunstan <pgsql(at)tomd(dot)cc>, Thomas Mueller <thomas(dot)tom(dot)mueller(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Protection from SQL injection |
Date: | 2008-04-29 20:01:44 |
Message-ID: | 20080429200144.GR6337@yugib.highrise.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
* 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2008-04-29 20:07:38 | Re: [COMMITTERS] pgsql: Remove typename from A_Const. |
Previous Message | Thomas Mueller | 2008-04-29 19:29:41 | Re: Protection from SQL injection |