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
