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
