Difficulty in creating a dynamic query
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: cfset sqlStatement = SELECT DISTINCT tableName.ID FROM tableName LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND tableName.otherID IN (SELECT otherID FROM thirdTable WHERE (firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / OR lastName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% /)) ) / And then do: cfquery name=foo #sqlStatement# /cfquery I get an error from the SQL Server driver that points to the first part of the client ID - Incorrect syntax near 'D35DAF11' and a nextException that says Incorrect syntax near ''. Yet if I paste the above statement directly into the cfquery tag, it runs perfectly. Can anyone suggest where I'm going wrong, please? I suppose it could be a single quotes problem of some sort. But certainly preserveSingleQuotes has no affect. And if I wrap the first varchar (the clientID) in a queryParam statement, it simply eliminates the first error and immediately shows the Incorrect syntax near ''. error. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354006 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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 cfquery tags? On Tue, Jan 22, 2013 at 3:33 PM, Tom McNeer tmcn...@gmail.com wrote: 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: cfset sqlStatement = SELECT DISTINCT tableName.ID FROM tableName LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND tableName.otherID IN (SELECT otherID FROM thirdTable WHERE (firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / OR lastName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% /)) ) / And then do: cfquery name=foo #sqlStatement# /cfquery I get an error from the SQL Server driver that points to the first part of the client ID - Incorrect syntax near 'D35DAF11' and a nextException that says Incorrect syntax near ''. Yet if I paste the above statement directly into the cfquery tag, it runs perfectly. Can anyone suggest where I'm going wrong, please? I suppose it could be a single quotes problem of some sort. But certainly preserveSingleQuotes has no affect. And if I wrap the first varchar (the clientID) in a queryParam statement, it simply eliminates the first error and immediately shows the Incorrect syntax near ''. error. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354007 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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 cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / into the DB exactly as written. And SQL will complain of course. You can successfully build dynamic queries if you do it within the cfquery block, like: cfquery SELECT otherID FROM thirdTable WHERE firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / cfif someCondition OR lastName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / /cfif /cfquery -- Bill. On 23 January 2013 07:57, Steve Milburn scmilb...@gmail.com wrote: 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 cfquery tags? On Tue, Jan 22, 2013 at 3:33 PM, Tom McNeer tmcn...@gmail.com wrote: 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: cfset sqlStatement = SELECT DISTINCT tableName.ID FROM tableName LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND tableName.otherID IN (SELECT otherID FROM thirdTable WHERE (firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / OR lastName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% /)) ) / And then do: cfquery name=foo #sqlStatement# /cfquery I get an error from the SQL Server driver that points to the first part of the client ID - Incorrect syntax near 'D35DAF11' and a nextException that says Incorrect syntax near ''. Yet if I paste the above statement directly into the cfquery tag, it runs perfectly. Can anyone suggest where I'm going wrong, please? I suppose it could be a single quotes problem of some sort. But certainly preserveSingleQuotes has no affect. And if I wrap the first varchar (the clientID) in a queryParam statement, it simply eliminates the first error and immediately shows the Incorrect syntax near ''. error. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354010 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
You need to use the preserveSingleQuotes () pseudo function ie: cfquery name=foo #preserveSingleQuotes(sqlStatement)# /cfquery ~| 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/message.cfm/messageid:354011 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
@ 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 cfqueryparam - inside of the cfquery block. On Tue, Jan 22, 2013 at 4:10 PM, wrote: You need to use the preserveSingleQuotes () pseudo function ie: cfquery name=foo #preserveSingleQuotes(sqlStatement)# /cfquery ~| 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/message.cfm/messageid:354012 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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 logic, and it's already almost unmaintainable. The current requirement means there will be literally hundreds of possible combinations of parameters and operators. I've built lots and lots of dynamic queries in the manner you suggest, and I understand your concern. It just won't work in this case. Please take my word for it. On Tue, Jan 22, 2013 at 4:47 PM, Bill Moniz hydro.b...@gmail.com wrote: Coldfusion is just going to see #sqlStatement# as a block of literal text, so it will pass WHERE firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / into the DB exactly as written. It appears you may well be correct. But that's exactly what I'm trying to determine for certain. Do you know that this is the case, or are you assuming it to be so? Believe me, I'm not trying to be argumentative. I've combed the docs and Googled extensively before asking my question here. I suppose that's the key question: if a query statement containing queryParam statements is output within a cfquery tag, does CF try to interpret the tags, or simply pass the plain text to the driver? It does seem as if Bill is correct, and CF simply passes the queryParam tag as text to the driver. Can anyone confirm for certain this is true, or point to anywhere in the docs this is dealt with? Thanks to everyone for their suggestions. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354013 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
It is a fact that using cfqueryparam outside of cfquery will result in it being seen by CF as purely string text. It will never be executed as an actual cfqueryparam. 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 separate methods/functionality. That is, of course, from the outside looking in. :-) On Tue, Jan 22, 2013 at 4:19 PM, Tom McNeer tmcn...@gmail.com wrote: 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 logic, and it's already almost unmaintainable. The current requirement means there will be literally hundreds of possible combinations of parameters and operators. I've built lots and lots of dynamic queries in the manner you suggest, and I understand your concern. It just won't work in this case. Please take my word for it. On Tue, Jan 22, 2013 at 4:47 PM, Bill Moniz hydro.b...@gmail.com wrote: Coldfusion is just going to see #sqlStatement# as a block of literal text, so it will pass WHERE firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / into the DB exactly as written. It appears you may well be correct. But that's exactly what I'm trying to determine for certain. Do you know that this is the case, or are you assuming it to be so? Believe me, I'm not trying to be argumentative. I've combed the docs and Googled extensively before asking my question here. I suppose that's the key question: if a query statement containing queryParam statements is output within a cfquery tag, does CF try to interpret the tags, or simply pass the plain text to the driver? It does seem as if Bill is correct, and CF simply passes the queryParam tag as text to the driver. Can anyone confirm for certain this is true, or point to anywhere in the docs this is dealt with? Thanks to everyone for their suggestions. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354014 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
Hi Matt, On Tue, Jan 22, 2013 at 5:22 PM, Matt Quackenbush quackfu...@gmail.comwrote: It is a fact that using cfqueryparam outside of cfquery will result in it being seen by CF as purely string text. It will never be executed as an actual cfqueryparam. Good. Or rather, not good, but thanks for the confirmation. 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 separate methods/functionality. That is, of course, from the outside looking in. :-) You're absolutely correct: it is a god query. Unfortunately, there's no way to split it up in this case. But thanks very much for your advice. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354015 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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/message.cfm/messageid:354016 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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 careful to ensure no invalid text is being put into the variables. Remove anything non-numeric from number fields with a regular expression, remove any html from text, eliminate any single quotes from text. That sort of thing. That said I'm not sure quite why it's faster/easier/more manageable to build a dynamic string then it is to build a dynamic query? Perhaps you could look into building views within your database or even content that is aggregated into a single table on a periodic basis to make your situation more straightforward and manageable. Regards, Nick Voss ncv...@gmail.com ~| 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/message.cfm/messageid:354017 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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 careful to ensure no invalid text is being put into the variables. Remove anything non-numeric from number fields with a regular expression, remove any html from text, eliminate any single quotes from text. That sort of thing. That said I'm not sure quite why it's faster/easier/more manageable to build a dynamic string then it is to build a dynamic query? Perhaps you could look into building views within your database or even content that is aggregated into a single table on a periodic basis to make your situation more straightforward and manageable. Regards, Nick Voss ncv...@gmail.com ~| 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/message.cfm/messageid:354018 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
What version of CF? If it's 9+, you can use placeholders in the SQL string for the parameters, rather than cfqueryparam tags, passing the param data to the query separately. You can't use cfquery for this approach, but can use Query.cfc instead. It's one of the few areas in which Query.cfc is superior to cfquery. That said, I'd like to echo the idea of creating these generic sort of queries isn't a great approach to things, so would strongly discourage this practice. -- Adam On 22 January 2013 20:33, Tom McNeer tmcn...@gmail.com wrote: 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: cfset sqlStatement = SELECT DISTINCT tableName.ID FROM tableName LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND tableName.otherID IN (SELECT otherID FROM thirdTable WHERE (firstName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% / OR lastName LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%bar% /)) ) / And then do: cfquery name=foo #sqlStatement# /cfquery I get an error from the SQL Server driver that points to the first part of the client ID - Incorrect syntax near 'D35DAF11' and a nextException that says Incorrect syntax near ''. Yet if I paste the above statement directly into the cfquery tag, it runs perfectly. Can anyone suggest where I'm going wrong, please? I suppose it could be a single quotes problem of some sort. But certainly preserveSingleQuotes has no affect. And if I wrap the first varchar (the clientID) in a queryParam statement, it simply eliminates the first error and immediately shows the Incorrect syntax near ''. error. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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/message.cfm/messageid:354019 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Difficulty in creating a dynamic query
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 encouraged (even with very thorough mitigating explanations such as the one you give here). -- Adam On 22 January 2013 22:57, Nick Voss ncv...@gmail.com wrote: 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 careful to ensure no invalid text is being put into the variables. Remove anything non-numeric from number fields with a regular expression, remove any html from text, eliminate any single quotes from text. That sort of thing. ~| 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/message.cfm/messageid:354020 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
