On Fri, Feb 17, 2012 at 9:18 AM, Puneet Kishor <punk.k...@gmail.com> wrote:
> > On Feb 17, 2012, at 11:05 AM, Greg Sabino Mullane wrote: > > > > > -----BEGIN PGP SIGNED MESSAGE----- > > Hash: RIPEMD160 > > > > > >> query 2 is at least an order of magnitude slower than query 1 > > > > To follow up on this, the current best practice for handling this > > is to prepare two statements, and have your app use the correct one. > > One could be prepared with pg_server_prepare=0, and one as normal. > > > Now that I know the reason behind this, thanks to all of you, I have > decided to stick with inline params `LIKE $q` instead of bind values. I am > not too worried about SQL attacks, and the above strategy works well > without having to toggle `pg_server_prepare`. Also, I do expect the query > to run often, but as the frontend is a web app, it will be called in > separate sessions... so, think many reloads of the same page as opposed to > one page request firing many instances of the same query. > You can be reasonably safe using inline params like that if you're careful to make sure $q contains only the sort of characters that make sense for your app, and/or if $q is quoted properly. You can use a regex to test that it is only alphanumeric for example. It's just easier to guarantee safety if you use bind values... but that's not always practical as you have discovered. -- Check out my LEGO blog at http://www.brickpile.com Follow/friend me: facebook.com/billward • flickr.com/photos/billward • twitter.com/williamward
