Re: Difficulty in creating a dynamic query

2013-01-22 Thread Adam Cameron
That protects against SQL injection, but it doesn't help caching compiled execution plans, which is the main reason for not hard-coding values into the SQL string instead of passing them as parameters. Not parameterising queries properly is just a really poor practice. And it should not ever be e

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Adam Cameron
What version of CF? If it's 9+, you can use placeholders in the SQL string for the parameters, rather than tags, passing the param data to the query separately. You can't use for this approach, but can use Query.cfc instead. It's one of the few areas in which Query.cfc is superior to . That sa

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Nick Voss
If you really can't build it using conditions/loops/etc within your query tag then build it like you are but eliminate the cfqueryparam tags, and HEAVILY validate the variables being put into those tags. Then you can concatenate a string together that will work. You'll just need to be super car

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Nick Voss
If you really can't build it using conditions/loops/etc within your query tag then build it like you are but eliminate the cfqueryparam tags, and HEAVILY validate the variables being put into those tags. Then you can concatenate a string together that will work. You'll just need to be super car

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Claude Schnéegans
>> you might even go to prison over it. ;-)) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Tom McNeer
Hi Matt, On Tue, Jan 22, 2013 at 5:22 PM, Matt Quackenbush wrote: > > It is a fact that using outside of will result in > it being seen by CF as purely string text. It will never be executed as an > actual . > Good. Or rather, not "good," but thanks for the confirmation. > > I obviously do

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Matt Quackenbush
It is a fact that using outside of will result in it being seen by CF as purely string text. It will never be executed as an actual . I obviously do not know what you're dealing with, specifically, but it sounds like you have a "god" query on your hands, and it really needs to be split up into

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Tom McNeer
Thanks for the replies. The answer to Steve, Bill and Matt is, the query is far too dynamic to be built inside a cfquery without a million cfif or switch/case statements. In fact, that sort of thing is what we're trying to replace. The existing routine is a cfquery tag that has stacks of specific

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Matt Quackenbush
@ Anyone building dynamic queries: PLEASE **DO NOT** follow that advice. You will regret it, eventually. If the data you're dealing with is sensitive enough, you might even go to prison over it. The proper solution is the one already mentioned by Steve and Bill. Build the statement - with - insi

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Claude Schnéegans
You need to use the preserveSingleQuotes () pseudo function ie: #preserveSingleQuotes(sqlStatement)# ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffu

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Bill Moniz
Ditto Steve's question. You're trying to use cfqueryparam outside of a cfquery block. Coldfusion is just going to see #sqlStatement# as a block of literal text, so it will pass WHERE firstName LIKE into the DB exactly as written. And SQL will complain of course. You can successfully build

Re: Difficulty in creating a dynamic query

2013-01-22 Thread Steve Milburn
If you just output the sqlStatement variable, how does it look? Also, any particular reason you are not just building your sql statement inside the tags? On Tue, Jan 22, 2013 at 3:33 PM, Tom McNeer wrote: > > Hi, > > I need to build up a complex dynamic query statement. I have built methods

Difficulty in creating a dynamic query

2013-01-22 Thread Tom McNeer
Hi, I need to build up a complex dynamic query statement. I have built methods to add queryParam statements, and built up valid SQL. If I do: OR lastName LIKE )) )" /> And then do: #sqlStatement# I get an error from the SQL Server driver that points to the first part of the clie