Re: RE: Preventing SQL injection attacks...?
cfqueryparam does in fact prevent that code from running. cfqueryparam creates a prepared statement with parameters. It then compares what you've entered as a value with the datatype you've specified and, if successful, binds the parameters with what you've entered. So, if you entered: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_DECIMAL and then in your url entered: id=12;drop table yourtable It would through you an error. As well, if you had: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_VARCHAR It would create the equivalent SQL statement of: select * from table where id = '12;drop table yourtable' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:00 am Subject: RE: Preventing SQL injection attacks...? let's say you have a text field that is 100 characters long. you can still get a drop table tablename appended to the sql statement or write an entire sql statment. Cfqueryparam was meant to speed up cfquery, not be to a cure all. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:06 PM To: CF-Talk Subject: Re: Preventing SQL injection attacks...? [EMAIL PROTECTED] wrote: you can't forget that form fields also play a part in this. after readingthe informaiton provided in jeff's link, it did shine a light. although i have been taught from the beginning to always use val() around numbericvalues (thank Adam) and to use regex to validate text input (props Raymond). if your anal and take the time to make sure that the information that people are passing you is in the extact fomrat you want, you shouldn't have a problem. also, don't rely on javascript, i always do server-side validationeven after client side, just to make certain. i even go as far as putting as much validation as i can into my stored procedures and triggers. althoughSQL server doesn't support regular expressions , which sucks! anyone know a way it could? why not just use cfqueryparam, it validates and it makes your sql code run faster??? __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: RE: Preventing SQL injection attacks...?
still. if i had the value tony ' drop table tablename-- and a cfqueryparam with a type of CF_SQL_VARCHAR it would still pass in the value as such select * from mytable where username = 'tony ' drop table tablename--' which would cause the table to be dropped. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:45 PM To: CF-Talk Subject: Re: RE: Preventing SQL injection attacks...? cfqueryparam does in fact prevent that code from running. cfqueryparam creates a prepared statement with parameters. It then compares what you've entered as a value with the datatype you've specified and, if successful, binds the parameters with what you've entered. So, if you entered: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_DECIMAL and then in your url entered: id=12;drop table yourtable It would through you an error. As well, if you had: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_VARCHAR It would create the equivalent SQL statement of: select * from table where id = '12;drop table yourtable' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:00 am Subject: RE: Preventing SQL injection attacks...? let's say you have a text field that is 100 characters long. you can still get a drop table tablename appended to the sql statement or write an entire sql statment. Cfqueryparam was meant to speed up cfquery, not be to a cure all. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:06 PM To: CF-Talk Subject: Re: Preventing SQL injection attacks...? [EMAIL PROTECTED] wrote: you can't forget that form fields also play a part in this. after readingthe informaiton provided in jeff's link, it did shine a light. although i have been taught from the beginning to always use val() around numbericvalues (thank Adam) and to use regex to validate text input (props Raymond). if your anal and take the time to make sure that the information that people are passing you is in the extact fomrat you want, you shouldn't have a problem. also, don't rely on javascript, i always do server-side validationeven after client side, just to make certain. i even go as far as putting as much validation as i can into my stored procedures and triggers. althoughSQL server doesn't support regular expressions , which sucks! anyone know a way it could? why not just use cfqueryparam, it validates and it makes your sql code run faster??? __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: RE: Preventing SQL injection attacks...?
What if you added a replace statement, doubling up on all single quotes?... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 10:47 AM To: CF-Talk Subject: RE: RE: Preventing SQL injection attacks...? still. if i had the value tony ' drop table tablename-- and a cfqueryparam with a type of CF_SQL_VARCHAR it would still pass in the value as such select * from mytable where username = 'tony ' drop table tablename--' which would cause the table to be dropped. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:45 PM To: CF-Talk Subject: Re: RE: Preventing SQL injection attacks...? cfqueryparam does in fact prevent that code from running. cfqueryparam creates a prepared statement with parameters. It then compares what you've entered as a value with the datatype you've specified and, if successful, binds the parameters with what you've entered. So, if you entered: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_DECIMAL and then in your url entered: id=12;drop table yourtable It would through you an error. As well, if you had: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_VARCHAR It would create the equivalent SQL statement of: select * from table where id = '12;drop table yourtable' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:00 am Subject: RE: Preventing SQL injection attacks...? let's say you have a text field that is 100 characters long. you can still get a drop table tablename appended to the sql statement or write an entire sql statment. Cfqueryparam was meant to speed up cfquery, not be to a cure all. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:06 PM To: CF-Talk Subject: Re: Preventing SQL injection attacks...? [EMAIL PROTECTED] wrote: you can't forget that form fields also play a part in this. after readingthe informaiton provided in jeff's link, it did shine a light. although i have been taught from the beginning to always use val() around numbericvalues (thank Adam) and to use regex to validate text input (props Raymond). if your anal and take the time to make sure that the information that people are passing you is in the extact fomrat you want, you shouldn't have a problem. also, don't rely on javascript, i always do server-side validationeven after client side, just to make certain. i even go as far as putting as much validation as i can into my stored procedures and triggers. althoughSQL server doesn't support regular expressions , which sucks! anyone know a way it could? why not just use cfqueryparam, it validates and it makes your sql code run faster??? __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: RE: Preventing SQL injection attacks...?
This code would fail my own code review. I don't allow any url. or form. variables inside CFQUERY statements. This, however, might pass. !--- Validate ID --- cfif #isdefined(id)# is true cfset theId=#val(htmleditformat(REreplacenocase(id, [*,;^:?|\], , ALL )))# cfelse cfset theId=0 /cfif !--- update time of last visit for this user --- cfquery name=queryit datasource=#dsn# update people set updated = '#dateformat(now(), MM/DD/)#' where peopleid = cfqueryparam value=#theid# cfsqltype=cf_sql_integer /cfquery (Note: I am not using CFQueryParam here as a validation tool. I already know the variable is an integer. This query is from a header and will fire every page load so I want successive queries to run from cache on the Database server.) Good Fortune, Richard Walters, Webmaster, Davita Laboratory Services [EMAIL PROTECTED] (800) 604-5227 x 3525 [EMAIL PROTECTED] 04/12/02 01:44PM cfqueryparam does in fact prevent that code from running. cfqueryparam creates a prepared statement with parameters. It then compares what you've entered as a value with the datatype you've specified and, if successful, binds the parameters with what you've entered. So, if you entered: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_DECIMAL and then in your url entered: id=12;drop table yourtable It would through you an error. As well, if you had: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_VARCHAR It would create the equivalent SQL statement of: select * from table where id = '12;drop table yourtable' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:00 am Subject: RE: Preventing SQL injection attacks...? let's say you have a text field that is 100 characters long. you can still get a drop table tablename appended to the sql statement or write an entire sql statment. Cfqueryparam was meant to speed up cfquery, not be to a cure all. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:06 PM To: CF-Talk Subject: Re: Preventing SQL injection attacks...? [EMAIL PROTECTED] wrote: you can't forget that form fields also play a part in this. after readingthe informaiton provided in jeff's link, it did shine a light. although i have been taught from the beginning to always use val() around numbericvalues (thank Adam) and to use regex to validate text input (props Raymond). if your anal and take the time to make sure that the information that people are passing you is in the extact fomrat you want, you shouldn't have a problem. also, don't rely on javascript, i always do server-side validationeven after client side, just to make certain. i even go as far as putting as much validation as i can into my stored procedures and triggers. althoughSQL server doesn't support regular expressions , which sucks! anyone know a way it could? why not just use cfqueryparam, it validates and it makes your sql code run faster??? __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: RE: RE: Preventing SQL injection attacks...?
No, it won't. It'll produce the SQL equivalent of: select * from mytable where username = 'tony ''drop table tablename --' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:46 am Subject: RE: RE: Preventing SQL injection attacks...? still. if i had the value tony ' drop table tablename-- and a cfqueryparam with a type of CF_SQL_VARCHAR it would still pass in the value as such select * from mytable where username = 'tony ' drop table tablename--' which would cause the table to be dropped. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:45 PM To: CF-Talk Subject: Re: RE: Preventing SQL injection attacks...? cfqueryparam does in fact prevent that code from running. cfqueryparam creates a prepared statement with parameters. It then compares what you've entered as a value with the datatype you've specified and, if successful, binds the parameters with what you've entered. So, if you entered: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_DECIMAL and then in your url entered: id=12;drop table yourtable It would through you an error. As well, if you had: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_VARCHAR It would create the equivalent SQL statement of: select * from table where id = '12;drop table yourtable' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:00 am Subject: RE: Preventing SQL injection attacks...? let's say you have a text field that is 100 characters long. you can still get a drop table tablename appended to the sql statement or write an entire sql statment. Cfqueryparam was meant to speed up cfquery, not be to a cure all. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:06 PM To: CF-Talk Subject: Re: Preventing SQL injection attacks...? [EMAIL PROTECTED] wrote: you can't forget that form fields also play a part in this. after readingthe informaiton provided in jeff's link, it did shine a light. although i have been taught from the beginning to always use val() around numbericvalues (thank Adam) and to use regex to validate text input (props Raymond). if your anal and take the time to make sure that the information that people are passing you is in the extact fomrat you want, you shouldn't have a problem. also, don't rely on javascript, i always do server- side validationeven after client side, just to make certain. i even go as far as putting as much validation as i can into my stored procedures and triggers. althoughSQL server doesn't support regular expressions , which sucks! anyone know a way it could? why not just use cfqueryparam, it validates and it makes your sql code run faster??? __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: RE: RE: RE: Preventing SQL injection attacks...?
I myself use SP's whenever possible (there are times when this isn't possible). However, it's beneficial to correctly outline what cfqueryparam does, for those people on the list that should be using cfqueryparam. - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 2:03 pm Subject: RE: RE: RE: Preventing SQL injection attacks...? i will have to take your word for it since i have probably never use cfquery param, nor intend to. i personally always use stored procedures and i do all of my validation myself. if cfqueryparam works for you, then use it and god bless. i have been taught differently and personally i don't think i would trust it. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 3:24 PM To: CF-Talk Subject: Re: RE: RE: Preventing SQL injection attacks...? No, it won't. It'll produce the SQL equivalent of: select * from mytable where username = 'tony ''drop table tablename --' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:46 am Subject: RE: RE: Preventing SQL injection attacks...? still. if i had the value tony ' drop table tablename-- and a cfqueryparam with a type of CF_SQL_VARCHAR it would still pass in the value as such select * from mytable where username = 'tony ' drop table tablename--' which would cause the table to be dropped. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:45 PM To: CF-Talk Subject: Re: RE: Preventing SQL injection attacks...? cfqueryparam does in fact prevent that code from running. cfqueryparam creates a prepared statement with parameters. It then compares what you've entered as a value with the datatype you've specified and, if successful, binds the parameters with what you've entered. So, if you entered: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_DECIMAL and then in your url entered: id=12;drop table yourtable It would through you an error. As well, if you had: select * from table where id = cfqueryparam value=#url.id# cfsqltype=CF_SQL_VARCHAR It would create the equivalent SQL statement of: select * from table where id = '12;drop table yourtable' - Original Message - From: [EMAIL PROTECTED] Date: Friday, April 12, 2002 11:00 am Subject: RE: Preventing SQL injection attacks...? let's say you have a text field that is 100 characters long. you can still get a drop table tablename appended to the sql statement or write an entire sql statment. Cfqueryparam was meant to speed up cfquery, not be to a cure all. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:06 PM To: CF-Talk Subject: Re: Preventing SQL injection attacks...? [EMAIL PROTECTED] wrote: you can't forget that form fields also play a part in this. after readingthe informaiton provided in jeff's link, it did shine a light. although i have been taught from the beginning to always use val() around numbericvalues (thank Adam) and to use regex to validate text input (props Raymond). if your anal and take the time to make sure that the information that people are passing you is in the extact fomrat you want, you shouldn't have a problem. also, don't rely on javascript, i always do server- side validationeven after client side, just to make certain. i even go as far as putting as much validation as i can into my stored procedures and triggers. althoughSQL server doesn't support regular expressions , which sucks! anyone know a way it could? why not just use cfqueryparam, it validates and it makes your sql code run faster??? __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: RE: RE: Preventing SQL injection attacks...?
i will have to take your word for it since i have probably never use cfquery param, nor intend to. i personally always use stored procedures and i do all of my validation myself. if cfqueryparam works for you, then use it and god bless. i have been taught differently and personally i don't think i would trust it. The validation done by creating a prepared statement is, as far as I can tell, identical to that done within a stored procedure. When you use CFQUERYPARAM, you're creating a prepared statement; the CFQUERYPARAM itself is what you might call a bound parameter to that statement. In any case, never say never, as you may not be able to guarantee that you'll always have the freedom to dictate whether you'll use stored procedures. You may, for example, work against a database in which you don't have rights to create stored procedures, or you may work against a database that doesn't support the use of stored procedures. At that time, you'll need to deal with validation elsewhere. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: RE: Preventing SQL injection attacks...?
This code would fail my own code review. I don't allow any url. or form. variables inside CFQUERY statements. This, however, might pass. !--- Validate ID --- cfif #isdefined(id)# is true cfset theId=#val(htmleditformat(REreplacenocase(id, [*,;^:?|\], , ALL )))# cfelse cfset theId=0 /cfif !--- update time of last visit for this user --- cfquery name=queryit datasource=#dsn# update people set updated = '#dateformat(now(), MM/DD/)#' where peopleid = cfqueryparam value=#theid# cfsqltype=cf_sql_integer /cfquery (Note: I am not using CFQueryParam here as a validation tool. I already know the variable is an integer. This query is from a header and will fire every page load so I want successive queries to run from cache on the Database server.) While it's better in this case to be too cautious, rather than not cautious enough, you're really doing more work here than is necessary. If you want to ensure that something is an integer, you can just use IsNumeric or the CFPARAM tag, if you want to do that before you attempt to execute the query. I agree with you that the validation should take place before the query, since that way you can respond appropriately without having to put exception handlers throughout your code. Also, you'd be better off not running the query if the variable isn't defined beforehand, rather than initializing the variable and running the query even though you won't return any results. Also, there's nothing wrong with referencing the URL or Form scope within a CFQUERY or CFSTOREDPROC, as long as you've validated the appropriate variables within that scope. Remember, these scopes are writable, they're not just read-only. Personally, I'd prefer sticking with the original variables, if for no other reason than general readability. Finally, you can simplify and clean up your code slightly by removing the unnecessary hashes and comparisons: cfif IsDefined(id) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
