Re: Sanitize input data for SQL
plenty of ideas, but you unfortunately forgot to mention the condition(s)/validation rule(s) when you want your trap/filter to spring/run... what does the value of your 'start' url var have to be to 'pass'? i.e. a positive integer? an numeric value within a certain range? something else? assuming url.start must be a positive integer, something like this should work: cfparam name=url.start default=0 cfset url.start = int(val(url.start)) cfif url.start lte 0cfabort/cfif Azadi On 09/11/2010 01:00 , Paul Smith wrote: I need a cffunction similar to PHP's mysql_escape_string that sanitizes input data, that is - escapes invalid code so as to prevent SQL injection and the like. I've have been unable to trap/filter the following: www.MyWebsite.com\index.cfm?start=-1%27 to keep it from emailing me an error message, and instead CFABORT or CFLOCATION to Home Page or some such. Any ideas? ~| 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:338983 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Sanitize input data for SQL
cfset city = model(Cities).findOne(where=id=#id# AND some_other_param=#param#) If you know it should only accept a numeric value, you can just throw in a Val() function and that will ensure nothing but numbers get passed ( e.g. id=#Val(id)# ). If you need to allow strings, and can restrict to just alphanumeric, you can do something similar with a Regex or other custom function. It's not ideal, but it's certainly better than leaving it unsanitized! --- Mary Jo ~| 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:339028 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Sanitize input data for SQL
If you know it should only accept a numeric value, you can just throw in a Val() function and that will ensure nothing but numbers get passed ( e.g. id=#Val(id)# ). For numeric inputs that has been my standard for years: cfparam name=url.id default=0 / cfset url.id = abs(val(trim(url.id))) / This guarantees a positive integer or zero, safe to pass to most queries (through CFQUERYPARAM, of course, or directly if you're feeling adventurous). The query results can then be checked to see if anything came back. Alternately, you can kick the user off the page if you don't want the query to run if anything other than a positive integer or zero was passed to begin with: cfif url.id neq abs(val(trim(url.id))) cflocation url=/ addtoken=no / /cfif -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:339030 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Sanitize input data for SQL
I need a cffunction similar to PHP's mysql_escape_string that sanitizes input data, that is - escapes invalid code so as to prevent SQL injection and the like. I've have been unable to trap/filter the following: www.MyWebsite.com\index.cfm?start=-1%27 to keep it from emailing me an error message, and instead CFABORT or CFLOCATION to Home Page or some such. Any ideas? ~| 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:338951 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Sanitize input data for SQL
+1. Don't rely on stripping, regular expressions or any of that (although feel free to do those too); use cfqueryparam in every query and SQL injection is no longer a problem, if your DB genuinely supports bound parameters. The problem is that I've started using Coldfusion On Wheels (http://cfwheels.com/) which has constructs like these: cfset city = model(Cities).findOne(where=id=#id# AND some_other_param=#param#) So it is these constructs that I need sanitation for :) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248378 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sanitize input data for SQL
Well, best of luck with that. On 8/1/06, Dmitrii Dimandt [EMAIL PROTECTED] wrote: +1. Don't rely on stripping, regular expressions or any of that (although feel free to do those too); use cfqueryparam in every query and SQL injection is no longer a problem, if your DB genuinely supports bound parameters. The problem is that I've started using Coldfusion On Wheels (http://cfwheels.com/) which has constructs like these: cfset city = model(Cities).findOne(where=id=#id# AND some_other_param=#param#) So it is these constructs that I need sanitation for :) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248379 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Sanitize input data for SQL
I need a cffunction similar to PHP's mysql_escape_string that sanitizes input data, that is - escapes invalid code so as to prevent SQL injection and the like. Thank you in advance :) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248212 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Sanitize input data for SQL
cfqueryparam -Original Message- From: Dmitrii Dimandt [mailto:[EMAIL PROTECTED] Sent: 31 July 2006 10:35 To: CF-Talk Subject: Sanitize input data for SQL I need a cffunction similar to PHP's mysql_escape_string that sanitizes input data, that is - escapes invalid code so as to prevent SQL injection and the like. Thank you in advance :) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248213 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sanitize input data for SQL
+1. Don't rely on stripping, regular expressions or any of that (although feel free to do those too); use cfqueryparam in every query and SQL injection is no longer a problem, if your DB genuinely supports bound parameters. On 7/31/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: cfqueryparam -Original Message- From: Dmitrii Dimandt [mailto:[EMAIL PROTECTED] Sent: 31 July 2006 10:35 To: CF-Talk Subject: Sanitize input data for SQL I need a cffunction similar to PHP's mysql_escape_string that sanitizes input data, that is - escapes invalid code so as to prevent SQL injection and the like. Thank you in advance :) -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248214 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sanitize input data for SQL
Correct, you can't. The query can instead be cached using one of the custom tags that people have created. On 8/1/06, Denny Valliant [EMAIL PROTECTED] wrote: Doh. Sometimes gmail don't thread quite right. Only down side is you can't use them in cached queries. I think. :D On 7/31/06, James Holmes [EMAIL PROTECTED] wrote: +1. Don't rely on stripping, regular expressions or any of that (although feel free to do those too); use cfqueryparam in every query and SQL injection is no longer a problem, if your DB genuinely supports bound parameters. On 7/31/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: cfqueryparam -Original Message- From: Dmitrii Dimandt [mailto:[EMAIL PROTECTED] Sent: 31 July 2006 10:35 To: CF-Talk Subject: Sanitize input data for SQL I need a cffunction similar to PHP's mysql_escape_string that sanitizes input data, that is - escapes invalid code so as to prevent SQL injection and the like. Thank you in advance :) -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248364 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sanitize input data for SQL
On 7/31/06, James Holmes [EMAIL PROTECTED] wrote: Correct, you can't. The query can instead be cached using one of the custom tags that people have created. Do any of those custom tags re-create the cache in the background? Seems like a good use of cfthread, but my custom caches aren't that imaginative, there is probably some easier way to achieve the effect. I resorted to updating the cache (just the cached record) whenever a record was updated, to avoid re-creation of the whole thing on the next request. Not elegant, I don't think. :-) :denny ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:248372 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
