> 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
