Re: CFDocument PDF text duplication
Hello CF-Talk, I've been a long-time reader of your mailing list and this is my first post :) I've been working with PDF generation (CF 8.0.1 / Windows Linux) and have gotten most of the known kinks fixed but there's still one problem that I can't get around: Sometimes when (bold?) text that is supposed to wrap to the next line get duplicated (is shown twice)... once on the first line outside the margins of the pdf and once again on the next line. crude oil prices, meanwhile, rebounded gets repeated. see: https://www.advantagedata.com/page_toPDF.gif I haven't found the source, but usually unbolding the problematic string or the entire paragraph fixes this duplicating bug. Adjusting the scale from 100 to 99.6 sometimes helps too. Has anyone else seen this behavior? My CFDOCUMENT margins are set to zero, and the paragraph content is in a div. I'd appreciate any feedback. Thanks, Minh ~| 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:353997 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
sql injection attempt
I saw some request errors but what were they trying to do? This is what the onRequest error email showed declare @q varchar(8000) select @q = 0x57414954464F522044454C4159202730303A30303A313527 exec(@q) ~| 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:353998 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
That's hex for, ?WAITFOR DELAY '00:00:15' On Tue, Jan 22, 2013 at 11:12 AM, Greg Morphis gmorp...@gmail.com wrote: 0x57414954464F522044454C4159202730303A30303A313527 -- John Bliss - http://about.me/jbliss ~| 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:353999 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Ah so they were just checking to see if they could get something to work before possibly trying anything real. Thanks! On Tue, Jan 22, 2013 at 11:15 AM, John M Bliss bliss.j...@gmail.com wrote: That's hex for, ?WAITFOR DELAY '00:00:15' On Tue, Jan 22, 2013 at 11:12 AM, Greg Morphis gmorp...@gmail.com wrote: 0x57414954464F522044454C4159202730303A30303A313527 -- John Bliss - http://about.me/jbliss ~| 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:354000 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Ah so they were just checking to see if they could get something to work before possibly trying anything real. That's a pretty standard approach. If they can get the response to delay then they can mark that URL as a potential entry point to come back and explore more later. -Justin ~| 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:354001 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SOT: Custom 404
I created a custom 404 page so visitors would see the CF error page. Now I can't see what errors are being produced when developing new dynamic content. Are there any recommendation on how to resolve this? Thanks, Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| 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:354002 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SOT: Custom 404
You only need the custom error page on the live site, simply disable it on your development machine. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 22, 2013 7:37 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: I created a custom 404 page so visitors would see the CF error page. Now I can't see what errors are being produced when developing new dynamic content. Are there any recommendation on how to resolve this? Thanks, Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| 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:354003 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SOT: Custom 404
Unfortunately there is no development machine. Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edu -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Tuesday, January 22, 2013 2:51 PM To: cf-talk Subject: Re: SOT: Custom 404 You only need the custom error page on the live site, simply disable it on your development machine. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 22, 2013 7:37 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: I created a custom 404 page so visitors would see the CF error page. Now I can't see what errors are being produced when developing new dynamic content. Are there any recommendation on how to resolve this? Thanks, Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| 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:354004 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SOT: Custom 404
In situations like yours I would say the opposite. If you are developing on a live server, you have no production machine, only a development one, deployed in a production environment. Really though, if you have a computer that you use every day, you should make an effort to make that your development environment. CF will run fine on a desktop or laptop. If this is not possible in your case, I would look into emailing yourself the errors, or perhaps writing them to a log (maybe they already are logged). If you choose to email them, you might consider creating a mailbox just for the errors so that a major mistake doesn't destroy your inbox. Lastly, look into Hoth: http://aarongreenlee.com/share/hoth-coldfusion-errors-tracking-reporting -Cameron On Tue, Jan 22, 2013 at 2:54 PM, Steve LaBadie slaba...@po-box.esu.eduwrote: Unfortunately there is no development machine. Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edu -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Tuesday, January 22, 2013 2:51 PM To: cf-talk Subject: Re: SOT: Custom 404 You only need the custom error page on the live site, simply disable it on your development machine. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com- CF search engine On Jan 22, 2013 7:37 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: I created a custom 404 page so visitors would see the CF error page. Now I can't see what errors are being produced when developing new dynamic content. Are there any recommendation on how to resolve this? Thanks, Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| 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:354005 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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: SOT: Custom 404
Cameron, I am still using MX7. I didn't see any examples of sending via email except for CF 8 and 9 Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edu -Original Message- From: Cameron Childress [mailto:camer...@gmail.com] Sent: Tuesday, January 22, 2013 3:05 PM To: cf-talk Subject: Re: SOT: Custom 404 In situations like yours I would say the opposite. If you are developing on a live server, you have no production machine, only a development one, deployed in a production environment. Really though, if you have a computer that you use every day, you should make an effort to make that your development environment. CF will run fine on a desktop or laptop. If this is not possible in your case, I would look into emailing yourself the errors, or perhaps writing them to a log (maybe they already are logged). If you choose to email them, you might consider creating a mailbox just for the errors so that a major mistake doesn't destroy your inbox. Lastly, look into Hoth: http://aarongreenlee.com/share/hoth-coldfusion-errors-tracking-reporting -Cameron On Tue, Jan 22, 2013 at 2:54 PM, Steve LaBadie slaba...@po-box.esu.eduwrote: Unfortunately there is no development machine. Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edu -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Tuesday, January 22, 2013 2:51 PM To: cf-talk Subject: Re: SOT: Custom 404 You only need the custom error page on the live site, simply disable it on your development machine. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com- CF search engine On Jan 22, 2013 7:37 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: I created a custom 404 page so visitors would see the CF error page. Now I can't see what errors are being produced when developing new dynamic content. Are there any recommendation on how to resolve this? Thanks, Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| 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:354008 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SOT: Custom 404
On Tue, Jan 22, 2013 at 4:09 PM, Steve LaBadie slaba...@po-box.esu.eduwrote: I am still using MX7. I didn't see any examples of sending via email except for CF 8 and 9 http://www.bennadel.com/blog/932-Ask-Ben-Handling-Errors-With-ColdFusion-CFError.htm -Cameron -- Cameron Childress -- p: 678.637.5072 im: cameroncf facebook http://www.facebook.com/cameroncf | twitterhttp://twitter.com/cameronc | google+ https://profiles.google.com/u/0/117829379451708140985 ~| 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:354009 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