Re: avoiding direct SQL command injection
Hi Les, How about removing single quotes in application.cfm for the short term and submitting information from Adobe on the value of cfqueryparam for preventing sql injection in the hopes that helps for the future? David CFQUERYPARAM creates a prepared statement Just because some of you might find this fun and slightly related to the above... I'm working on a Congressional site at the moment, and Coldfusion is *very* poorly supported to start with. The official house supported version is CF5, so I'm having to rewrite most of my carefully crafted snippets and reusable code chunks. Anyway, once you're logged onto the house network and posted your files, they have to go through a security scan before they can go live. The scan doesn't take cfqueryparam into consideration at all, so my first scan of a dynamic page generated about 5 pages worth of failed reports that looked like: ScanDo included the ' (apostrophe) character in the parameter value. The server's reply indicates that the parameter is vulnerable to manipulation using SQL symbols. 1)High Severity URL: mypage-detail.cfm?id=97' Solution: Make sure parameter values sent by remote users do not include SQL commands or symbols. Validate and sanitize every user variable passed to the database. Check that the input has the expected data type. Never pass unchecked user-input to database-queries. Basically, the scan program tries about every SQL Injection trick known to man, and if you fail one of them, you don't go live. All my carefully written queries using cfqueryparam meant nothing. P! So, for every single variable passed to a query, about the only way to pass the security scan was to do this: cfif IsDefined(fname) and (findoneof(![^]+%!=,fname,1) NEQ 0) cfabort Sheesh. But, this presents a HUGE problem. The client has requested an admin section to update content on certain pages. So, a simple p tag which needs to get passed will trigger the cfabort above. ACK! I'm glad I'm getting paid well for this More later once I've got it all figured out on how to give the client what they want but pass all the required BS needed to get stuff approved, including the Secret Squirrel Security Code Generator that we've now got that display a security code to login into the house private network that changes every 60 seconds. This is fun stuff... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230695 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: avoiding direct SQL command injection
Nice. thank you all.. -- Sebastian Mork [EMAIL PROTECTED] -- On Fri, 27 Jan 2006 11:32:17 -0500 Dave Watts [EMAIL PROTECTED] wrote: Anyway, I don't wanna get this working, I wanted to see if e.g. a cfqueryparam with type=cf_sql_varchar avoids this from execution or not. As Mr. Holmes pointed out, CFQUERYPARAM creates a prepared statement containing bound parameters. This means that pieces of data are explicitly identified as being data, instead of SQL commands. So, it doesn't matter what's in the data; the database will know it's not executable code. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230681 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: avoiding direct SQL command injection
CFQUERYPARAM creates a prepared statement containing bound parameters. Just because some of you might find this fun and slightly related to the above... I'm working on a Congressional site at the moment, and Coldfusion is *very* poorly supported to start with. The official house supported version is CF5, so I'm having to rewrite most of my carefully crafted snippets and reusable code chunks. Anyway, once you're logged onto the house network and posted your files, they have to go through a security scan before they can go live. The scan doesn't take cfqueryparam into consideration at all, so my first scan of a dynamic page generated about 5 pages worth of failed reports that looked like: ScanDo included the ' (apostrophe) character in the parameter value. The server's reply indicates that the parameter is vulnerable to manipulation using SQL symbols. 1)High Severity URL: mypage-detail.cfm?id=97' Solution: Make sure parameter values sent by remote users do not include SQL commands or symbols. Validate and sanitize every user variable passed to the database. Check that the input has the expected data type. Never pass unchecked user-input to database-queries. Basically, the scan program tries about every SQL Injection trick known to man, and if you fail one of them, you don't go live. All my carefully written queries using cfqueryparam meant nothing. P! So, for every single variable passed to a query, about the only way to pass the security scan was to do this: cfif IsDefined(fname) and (findoneof(![^]+%!=,fname,1) NEQ 0) cfabort Sheesh. But, this presents a HUGE problem. The client has requested an admin section to update content on certain pages. So, a simple p tag which needs to get passed will trigger the cfabort above. ACK! I'm glad I'm getting paid well for this More later once I've got it all figured out on how to give the client what they want but pass all the required BS needed to get stuff approved, including the Secret Squirrel Security Code Generator that we've now got that display a security code to login into the house private network that changes every 60 seconds. This is fun stuff... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230686 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: avoiding direct SQL command injection
Anyway, I don't wanna get this working, I wanted to see if e.g. a cfqueryparam with type=cf_sql_varchar avoids this from execution or not. As Mr. Holmes pointed out, CFQUERYPARAM creates a prepared statement containing bound parameters. This means that pieces of data are explicitly identified as being data, instead of SQL commands. So, it doesn't matter what's in the data; the database will know it's not executable code. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230599 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: avoiding direct SQL command injection
CFQUERYPARAM avoids SQL injection by binding the parameter into the statement, not just by checking types. It is impossible to have a bound parameter executed as literal SQL (if the DB actually supports binding). On 1/27/06, Sebastian Mork [EMAIL PROTECTED] wrote: Hi, In my apps I use nearly everytime selecting data from a database the cfqueryparam tag.. Now I tried (using mysql-db) to execute some sql-commands via url-param.. The case is I wanna avoid direct sql injections. Therefore I made some tests to see how manipulation works and how to avoid it. An example, I've a query like this: select tbl_users.* from tbl_users where fld_username='#url.test#' All test, e.g. trying so call file.cfm?test=anything';update tbl_users set fld_password='abc' failed. Anyway, I don't wanna get this working, I wanted to see if e.g. a cfqueryparam with type=cf_sql_varchar avoids this from execution or not. When using numeric values as queryparams an error is thrown, (eg where fld_id=cfqueryparam cfsqltype=cf_sql_bigint value=#url.v# when v=abc) but what about other types like text,varchar..? A way to avoid could be replacing the ; from e.g. search-strings when searching for anything in db. But what if a semikolon is needed to search for eg in a product teaser or something like that.. -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230550 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: avoiding direct SQL command injection
When using numeric values as queryparams an error is thrown, (eg where fld_id=cfqueryparam cfsqltype=cf_sql_bigint value=#url.v# when v=abc) This is easy to get around by wrapping the url variable in a val() function. That will guarantee that whole number will be passed in. -Justin ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230558 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
