Re: Double Quote issue
thanks for all your replies and guidance... i think you guys have helped us understand that building the queries in strings is indeed craziness! We previously had functions that retrieved user defined filters from a table and then built the 'where clause' in a string to run on the main query. We are now changing the functions to return metadata about the filters and can then loop through them within the query; that way we can use cfqueryparams etc... thanks again for your help ~| 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:345733 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Double Quote issue
Hi, we are having to build a complex query in a string using parameters passed through the URL and then run it within a cfquery. Here is a basic example: cfset param1 = url.param1 / cfset sqlString = 'select value from table1 where id = #param1#' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery This all works fine until the url.param1 includes a double quote, then of course it conflicts with the double quotes it is surrounded in and throws an error. Even if we encode the string then of course mysql wont be able to retrieve the correct results Do you have any suggestions on how to get around this? thanks ~| 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:345662 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
try reversing the quotes, using double quotes in a query shouldn't actually work anyway. cfset sqlString = select value from table1 where id = '#param1#' / On Sat, Jun 25, 2011 at 1:12 PM, Richard White rich...@j7is.co.uk wrote: Hi, we are having to build a complex query in a string using parameters passed through the URL and then run it within a cfquery. Here is a basic example: cfset param1 = url.param1 / cfset sqlString = 'select value from table1 where id = #param1#' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery This all works fine until the url.param1 includes a double quote, then of course it conflicts with the double quotes it is surrounded in and throws an error. Even if we encode the string then of course mysql wont be able to retrieve the correct results Do you have any suggestions on how to get around this? thanks ~| 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:345663 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
try reversing the quotes, using double quotes in a query shouldn't actually work anyway. Exact. But then the problem may arise with single quotes in the parameter. Allowing single or double quotes in an id field in a database is looking for trouble. If quotes are not supposed to be used, may be some validation before the query is made could solve the problem. ~| 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:345664 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
Hi Claude, thanks for your reply. we want to allow users to be able to enter either single or double quotes in the fields thanks try reversing the quotes, using double quotes in a query shouldn't actually work anyway. Exact. But then the problem may arise with single quotes in the parameter. Allowing single or double quotes in an id field in a database is looking for trouble. If quotes are not supposed to be used, may be some validation before the query is made could solve the problem. ~| 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:345665 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
Don't built dynamic queries with user-supplied data, unless you like exposing yourself to SQL injection. cfquery name=myQuery datasource=myDatasource SELECT value FROM table1 WHERE id = cfqueryparam value=#url.param1# / /cfquery And url.param1 can contain as many single or double quotes as you like without causing any SQL issues at all. ~| 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:345666 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Double Quote issue
Why aren't you doing this? cfquery... Select value from table1 where id = cfqueryparam cfsqltype=cf_sql_int value=#url.param1# / /cfquery What you have now is quite dangerous. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Saturday, June 25, 2011 8:13 AM To: cf-talk Subject: Double Quote issue Hi, we are having to build a complex query in a string using parameters passed through the URL and then run it within a cfquery. Here is a basic example: cfset param1 = url.param1 / cfset sqlString = 'select value from table1 where id = #param1#' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery This all works fine until the url.param1 includes a double quote, then of course it conflicts with the double quotes it is surrounded in and throws an error. Even if we encode the string then of course mysql wont be able to retrieve the correct results Do you have any suggestions on how to get around this? thanks ~| 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:345667 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Double Quote issue
Exactly. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Peter Boughton [mailto:bought...@gmail.com] Sent: Saturday, June 25, 2011 9:24 AM To: cf-talk Subject: Re: Double Quote issue Don't built dynamic queries with user-supplied data, unless you like exposing yourself to SQL injection. cfquery name=myQuery datasource=myDatasource SELECT value FROM table1 WHERE id = cfqueryparam value=#url.param1# / /cfquery And url.param1 can contain as many single or double quotes as you like without causing any SQL issues at all. ~| 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:345669 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Double Quote issue
Richard you could always use the cfsavecontent to build the SQL, and then use the variable in the cfquery. But you should also be using cfqueryparam with anything from forms and url, essentially anything that is or could be from user input. Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Saturday, 25 June 2011 10:13 PM To: cf-talk Subject: Double Quote issue Hi, we are having to build a complex query in a string using parameters passed through the URL and then run it within a cfquery. Here is a basic example: cfset param1 = url.param1 / cfset sqlString = 'select value from table1 where id = #param1#' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery This all works fine until the url.param1 includes a double quote, then of course it conflicts with the double quotes it is surrounded in and throws an error. Even if we encode the string then of course mysql wont be able to retrieve the correct results Do you have any suggestions on how to get around this? thanks ~| 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:345671 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
thanks for the pointers, we understand the problem here is down to not using the cfquery param thanks Richard you could always use the cfsavecontent to build the SQL, and then use the variable in the cfquery. But you should also be using cfqueryparam with anything from forms and url, essentially anything that is or could be from user input. Regards, Andrew Scott http://www.andyscott.id.au/ an ~| 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:345674 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
the issue here is that there are various filters being built up from different functions which is why we are having to do it as a string and not directly inside a cfquery tag. is there anyway to put the cfqueryparam tag inside a string and have it output that? again a basic example: cfset sqlString = 'select value from table1 where id = cfqueryparam cfsqltype=cf_sql_int value=#url.param1# /' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery thanks Why aren't you doing this? cfquery... Select value from table1 where id = cfqueryparam cfsqltype=cf_sql_int value=#url.param1# / /cfquery What you have now is quite dangerous. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com Hi, we are having to build a complex query in a string using parameters passed through the URL and then run it within a cfquery. Here is a basic example: cfset param1 = url.param1 / cfset sqlString = 'select value from table1 where id = #param1#' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery This all works fine until the url.param1 includes a double quote, then of course it conflicts with the double quotes it is surrounded in and throws an error. Even if we encode the string then of course mysql wont be able to retrieve the correct results Do you have any suggestions on how to get around this? thanks ~| 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:345676 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Double Quote issue
Ok. Your example didn't show any filters, it just took it directly from the URL and stuck it right into a cfquery. I've heard of people writing their cfquery to files so they could generate the proper cfqueryparams and then just cfinclude that file to run the query. Obviously that's going to add file I/O overhead. Depending on your app, it could be way too much. The other option might be to create a stored proc that executes the dynamic SQL then pass the entire sql block into the SP call within a single CFQueryparam tag cfquery... Exec dbo.mySP @mySQl = cfqueryparam cfsqltype=cf_sql_varchar value=Select value form table where value = #url.id# / /cfquery I've never found the need to do either and if there was a more detailed example of what you are trying to do, I'm sure one of the many experienced people on this list might be able to offer some better ideas. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Saturday, June 25, 2011 12:34 PM To: cf-talk Subject: Re: Double Quote issue the issue here is that there are various filters being built up from different functions which is why we are having to do it as a string and not directly inside a cfquery tag. is there anyway to put the cfqueryparam tag inside a string and have it output that? again a basic example: cfset sqlString = 'select value from table1 where id = cfqueryparam cfsqltype=cf_sql_int value=#url.param1# /' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery thanks Why aren't you doing this? cfquery... Select value from table1 where id = cfqueryparam cfsqltype=cf_sql_int value=#url.param1# / /cfquery What you have now is quite dangerous. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com Hi, we are having to build a complex query in a string using parameters passed through the URL and then run it within a cfquery. Here is a basic example: cfset param1 = url.param1 / cfset sqlString = 'select value from table1 where id = #param1#' / cfquery name=myQuery datasource=myDatasource cfoutput#sqlString#/cfoutput /cfquery This all works fine until the url.param1 includes a double quote, then of course it conflicts with the double quotes it is surrounded in and throws an error. Even if we encode the string then of course mysql wont be able to retrieve the correct results Do you have any suggestions on how to get around this? thanks ~| 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:345688 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Double Quote issue
Richard wrote: the issue here is that there are various filters being built up from different functions which is why we are having to do it as a string and not directly inside a cfquery tag. The issue here is that you are trying to use cfquery in a way it wasn't designed to be used, which is why you're having trouble trying the get the language to support your craziness. :) If you want to work with SQL-based databases, you have to work with queries - which in CF means using cfquery and cfqueryparam with individual queries, not trying to build up the statement from parts of strings scattered all over the place. If you want a more filter-based approach, you want to use something that is built around filtering, which (I think) is one of the main benefits of CouchDB and similar. Depending on exactly what you're doing, I'd probably just put the entire query in a single function, and use suitable arguments and switch/if statements to control what parts of the where clause is used, which is very likely to be more maintainable than having filters in different functions. ~| 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:345689 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
