* Gregory Stark <[EMAIL PROTECTED]> [080429 14:20]: > "Aidan Van Dyk" <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave.
signature.asc
Description: Digital signature
