Re: writing protected CF with CFStoredProc
Excellent points! Thanks Dave, and everyone who took the time to reply to / read this thread. Moral lessons learned: 1) Don't go crazy with tightening security around SQL statements. Only secure the vulnerable; 2) Whenever possible, think of using native CF functions to simplify code. :-) BTW, Dave, do you have a Reader's Digest version of the CFTalk threads? There are so many good ideas flying around here that it is hard to keep up sometimes. It would be awesome if we could have the common problem discussions abstracted, condensed and posted for all to share. On Tue, Jul 22, 2008 at 8:15 PM, Dave Watts [EMAIL PROTECTED] wrote: Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery Well, first of all, in this case the stored procedure itself is handling validation. It's going to make sure that @aid and @bid are integers, and fail if they're not. In addition, in the above case, the parameters don't even contain CF variables! So, you don't really need to go any farther, as your current code is safe. As Mark just pointed out, if you did have actual CF variables in your statement, those would be vulnerable. The stored procedure itself isn't vulnerable, of course, but the CFQUERY tag would be unless you'd configured your database login so that it could only execute stored procedures. 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! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309526 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
writing protected CF with CFStoredProc
Hello folks: The discussion yesterday regarding using CFqueryparam to protect sites from SQL Injection attacks got me thinking. Well, it is easy enough to use CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed to the SQL query. However, how do you do that with CFStoredProc? If I understand correctly, if you want to protect calls to stored procs (from SQL injection and the like), you have to use cfstoredproc and cfprocparam instead of cfquery and cfqueryparam. But apparently, you can't indicate what parameters you're actually passing. Am I missing something? Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery If I was using CFSTOREDPROC, I might write this: cfstoredproc procedure=sps_testproc... cfprocparam type=in cfsqltype=cf_sql_integer value=123 . /cfstoredproc See my problem? In my proc example, we don't need to know which of the two params is going to be passed to it. In the CFQUERY, I use that to pass one param or the other depending on something else (the value of whichvar). But as far as I can tell, CFSTOREDPROC doesn't let me tell it which parameter I'm passing -- presumably it wants all parameters, in order. So maybe I need something like this: cfstoredproc procedure=sps_testproc... cfif whichvar=A cfprocparam type=in cfsqltype=cf_sql_integer value=123 cfprocparam type=in cfsqltype=cf_sql_integer value=null cfelse cfprocparam type=in cfsqltype=cf_sql_integer value=null cfprocparam type=in cfsqltype=cf_sql_integer value=456 /cfstoredproc That kind of sucks, right? Am I making any sense? Any thoughts and/or suggestions? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309460 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: writing protected CF with CFStoredProc
Why not pass both to the proc, then rewrite the proc so that rather than testing for it's existence, you're testing for whether or not it's blank? -Original Message- From: Qing Xia [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2008 9:21 AM To: CF-Talk Subject: writing protected CF with CFStoredProc Hello folks: The discussion yesterday regarding using CFqueryparam to protect sites from SQL Injection attacks got me thinking. Well, it is easy enough to use CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed to the SQL query. However, how do you do that with CFStoredProc? If I understand correctly, if you want to protect calls to stored procs (from SQL injection and the like), you have to use cfstoredproc and cfprocparam instead of cfquery and cfqueryparam. But apparently, you can't indicate what parameters you're actually passing. Am I missing something? Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery If I was using CFSTOREDPROC, I might write this: cfstoredproc procedure=sps_testproc... cfprocparam type=in cfsqltype=cf_sql_integer value=123 .. /cfstoredproc See my problem? In my proc example, we don't need to know which of the two params is going to be passed to it. In the CFQUERY, I use that to pass one param or the other depending on something else (the value of whichvar). But as far as I can tell, CFSTOREDPROC doesn't let me tell it which parameter I'm passing -- presumably it wants all parameters, in order. So maybe I need something like this: cfstoredproc procedure=sps_testproc... cfif whichvar=A cfprocparam type=in cfsqltype=cf_sql_integer value=123 cfprocparam type=in cfsqltype=cf_sql_integer value=null cfelse cfprocparam type=in cfsqltype=cf_sql_integer value=null cfprocparam type=in cfsqltype=cf_sql_integer value=456 /cfstoredproc That kind of sucks, right? Am I making any sense? Any thoughts and/or suggestions? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309461 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: writing protected CF with CFStoredProc
Yup, you're making sense. The way around it is to pass NULL in using: cfprocparam null=true Adrian -Original Message- From: Qing Xia [mailto:[EMAIL PROTECTED] Sent: 22 July 2008 15:21 To: CF-Talk Subject: writing protected CF with CFStoredProc Hello folks: The discussion yesterday regarding using CFqueryparam to protect sites from SQL Injection attacks got me thinking. Well, it is easy enough to use CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed to the SQL query. However, how do you do that with CFStoredProc? If I understand correctly, if you want to protect calls to stored procs (from SQL injection and the like), you have to use cfstoredproc and cfprocparam instead of cfquery and cfqueryparam. But apparently, you can't indicate what parameters you're actually passing. Am I missing something? Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery If I was using CFSTOREDPROC, I might write this: cfstoredproc procedure=sps_testproc... cfprocparam type=in cfsqltype=cf_sql_integer value=123 .. /cfstoredproc See my problem? In my proc example, we don't need to know which of the two params is going to be passed to it. In the CFQUERY, I use that to pass one param or the other depending on something else (the value of whichvar). But as far as I can tell, CFSTOREDPROC doesn't let me tell it which parameter I'm passing -- presumably it wants all parameters, in order. So maybe I need something like this: cfstoredproc procedure=sps_testproc... cfif whichvar=A cfprocparam type=in cfsqltype=cf_sql_integer value=123 cfprocparam type=in cfsqltype=cf_sql_integer value=null cfelse cfprocparam type=in cfsqltype=cf_sql_integer value=null cfprocparam type=in cfsqltype=cf_sql_integer value=456 /cfstoredproc That kind of sucks, right? Am I making any sense? Any thoughts and/or suggestions? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309462 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing protected CF with CFStoredProc
What's wrong with using: cfquery ... exec sps_testproc cfif whichvar = A @aid=cfqueryparam value=123 cfsqltype=cf_sql_integer cfelse @bid=cfqueryparam value=456 cfsqltype=cf_sql_integer /cfif /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309464 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: writing protected CF with CFStoredProc
Oh yeah, you are right, of course. There is no NULL in CF so if I do a Value=NULL that will only confuse SQL. Cool, thanks! On Tue, Jul 22, 2008 at 10:27 AM, Adrian Lynch [EMAIL PROTECTED] wrote: Yup, you're making sense. The way around it is to pass NULL in using: cfprocparam null=true Adrian -Original Message- From: Qing Xia [mailto:[EMAIL PROTECTED] Sent: 22 July 2008 15:21 To: CF-Talk Subject: writing protected CF with CFStoredProc Hello folks: The discussion yesterday regarding using CFqueryparam to protect sites from SQL Injection attacks got me thinking. Well, it is easy enough to use CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed to the SQL query. However, how do you do that with CFStoredProc? If I understand correctly, if you want to protect calls to stored procs (from SQL injection and the like), you have to use cfstoredproc and cfprocparam instead of cfquery and cfqueryparam. But apparently, you can't indicate what parameters you're actually passing. Am I missing something? Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery If I was using CFSTOREDPROC, I might write this: cfstoredproc procedure=sps_testproc... cfprocparam type=in cfsqltype=cf_sql_integer value=123 .. /cfstoredproc See my problem? In my proc example, we don't need to know which of the two params is going to be passed to it. In the CFQUERY, I use that to pass one param or the other depending on something else (the value of whichvar). But as far as I can tell, CFSTOREDPROC doesn't let me tell it which parameter I'm passing -- presumably it wants all parameters, in order. So maybe I need something like this: cfstoredproc procedure=sps_testproc... cfif whichvar=A cfprocparam type=in cfsqltype=cf_sql_integer value=123 cfprocparam type=in cfsqltype=cf_sql_integer value=null cfelse cfprocparam type=in cfsqltype=cf_sql_integer value=null cfprocparam type=in cfsqltype=cf_sql_integer value=456 /cfstoredproc That kind of sucks, right? Am I making any sense? Any thoughts and/or suggestions? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309465 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: writing protected CF with CFStoredProc
True! I can certainly do this as well. On Tue, Jul 22, 2008 at 10:40 AM, morgan l [EMAIL PROTECTED] wrote: What's wrong with using: cfquery ... exec sps_testproc cfif whichvar = A @aid=cfqueryparam value=123 cfsqltype=cf_sql_integer cfelse @bid=cfqueryparam value=456 cfsqltype=cf_sql_integer /cfif /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309466 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing protected CF with CFStoredProc
In your example you are altering the behavior of the query based upon input which does not affect injection attacks. The idea of protecting against injection attacks is to stop invalid values from being executed within the query/SP. Take for example this query: delete from customer where customerId = 1 if this query were parameterized from CF without cfqueryparam you would have: delete from customer where customerId = #customerId# If someone were trying to inject sql they could inject 1;drop customers; as the parameter and without the queryparam, it would be executed literally as the following and drop the customers table: delete from customer where lastname = 1; drop customers; To prevent this we utilize cfqueryparam which parameterizes the query that is passed. As I understand it, this informs the database that the value being passed is of a specific datatype. So in the previous example: delete from customer where customerId = cfqueryparam value=#customerId# cfsqltype=cf_sql_integer null=false / In essense, the database sees this as: declare @custId int set @custId = 1 delete from customer where customerId = @custId This has the benefit of not allowing the additional SQL to be injected, and I just learned recently, it also creates a parameterized query which on SQL server creates a cached query execution plan, minimially increasing performance. HTH, Rich ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309467 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: writing protected CF with CFStoredProc
The discussion yesterday regarding using CFqueryparam to protect sites from SQL Injection attacks got me thinking. Well, it is easy enough to use CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed to the SQL query. However, how do you do that with CFStoredProc? If I understand correctly, if you want to protect calls to stored procs (from SQL injection and the like), you have to use cfstoredproc and cfprocparam instead of cfquery and cfqueryparam. But apparently, you can't indicate what parameters you're actually passing. Am I missing something? Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery Well, first of all, in this case the stored procedure itself is handling validation. It's going to make sure that @aid and @bid are integers, and fail if they're not. In addition, in the above case, the parameters don't even contain CF variables! So, you don't really need to go any farther, as your current code is safe. If I was using CFSTOREDPROC, I might write this: cfstoredproc procedure=sps_testproc... cfprocparam type=in cfsqltype=cf_sql_integer value=123 . /cfstoredproc See my problem? In my proc example, we don't need to know which of the two params is going to be passed to it. In the CFQUERY, I use that to pass one param or the other depending on something else (the value of whichvar). But as far as I can tell, CFSTOREDPROC doesn't let me tell it which parameter I'm passing -- presumably it wants all parameters, in order. So maybe I need something like this: cfstoredproc procedure=sps_testproc... cfif whichvar=A cfprocparam type=in cfsqltype=cf_sql_integer value=123 cfprocparam type=in cfsqltype=cf_sql_integer value=null cfelse cfprocparam type=in cfsqltype=cf_sql_integer value=null cfprocparam type=in cfsqltype=cf_sql_integer value=456 /cfstoredproc That kind of sucks, right? Am I making any sense? CF 5 and earlier used the DBVARNAME attribute to specify which one is which. My understanding is that JDBC doesn't support this, so CF no longer supports this either. However, I'm not knowledgeable enough about JDBC to confirm this, so maybe it's a DataDirect-specific issue. In any case, you need to send parameters in the order that they're expected by the stored procedure. That said, you can send NULLs to each parameter that allows it, and you could simplify the above code: cfstoredproc ... cfprocparam type=in cfsqltype=cf_sql_integer value=123 null=#YesNoFormat(whichvar neq A)# cfprocparam type=in cfsqltype=cf_sql_integer value=456 null=#YesNoFormat(whichvar eq A)# /cfstoredproc 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! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309496 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: writing protected CF with CFStoredProc
Say you had a proc that looked like this: CREATE PROC sps_testproc @AID int = null, @BID int = null as IF @AID is not null SELECT @AID IF @AID is not NULL SELECT @BID If I was using CFQUERY, unprotected-style, I might write this: cfquery ... sps_testproc cfif whichvar = A @aid=123 cfelse @bid=456 /cfif /cfquery Well, first of all, in this case the stored procedure itself is handling validation. It's going to make sure that @aid and @bid are integers, and fail if they're not. In addition, in the above case, the parameters don't even contain CF variables! So, you don't really need to go any farther, as your current code is safe. As Mark just pointed out, if you did have actual CF variables in your statement, those would be vulnerable. The stored procedure itself isn't vulnerable, of course, but the CFQUERY tag would be unless you'd configured your database login so that it could only execute stored procedures. 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! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309500 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
