Fair enough, but I wonder if the coffee has yet kicked in. :-) I don't mean to start anything, but I really don't think I said something to convey that "parameterization actually escapes anything". I just said:
> I'm pretty sure the validation of CFQUERYPARAM for cfsqltype="CF_SQL_CHAR" does more than just ensure "it's a string" And that extra info you offer is indeed what I had in mind, so thanks for repeating it. I did go on to explain how CFQP causes "still has other benefits in causing CF to send a "prepared statement", which some use as another way to refer to parameterization, along with "bind variables". I know you know, that Dean. Just being clear. This is what we get when two people strive to be very clear all the time! :-) /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. Saxe Sent: Friday, July 27, 2007 10:23 AM To: [email protected] Subject: Re: [ACFUG Discuss] cfqueryparam in a sort Good catch Charlie... guess my morning coffee hadn't kicked in yet! Parameterization doesn't actually escape anything. It forces the DB to parse the query independently of the data, produce an execution plan and then plug the data in at runtime. Since the execution plan exists it cannot be changed by the data. This gets back to a discussion we just had elsewhere... the context below is using queries w/o cfqueryparam: <snip> Unless you use preserveSingleQuotes() for string values in a query CF will automatically handle quote escaping for you. So that mitigates SQL injection in this instance. Numeric values are subject to SQL injection and should always be parameterized or simply validated as numeric. Queries should never be built outside of <cfquery> to ensure automatic string escaping. In other words, don't build the query as a string and then do <cfquery>#myQuery#</cfquery>, this will most likely be subject to injection. Additionally, string values from the user should never be placed directly into the query when they are not surrounded by quotes (e.g. [...] WHERE #form.where_clause#) since these will obviously not be escaped by CF. Use the pattern of indirection instead. All in all, CF is much more secure from a SQL Injection perspective than most programming languages when programmed well. That doesn't mean you should stop using <cfqueryparam>, however, since it does confer some performance benefits. </snip> Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "I have always strenuously supported the right of every man to his own opinion, however different that opinion might be to mine. He who denies another this right makes a slave of himself to his present opinion, because he precludes himself the right of changing it." -- Thomas Paine, 1783 On Jul 27, 2007, at 10:14 AM, Charlie Arehart wrote: > Yes, this is an unfortunate misunderstanding, but I do think I know > where Vivek may have been going, and it's worth discussing. > > If the value being used in the CFQUERYPARAM is other than a string > (like a number), then it could make sense to think that if one is > doing the query in a CFFUNCTION and sets a datatype for the variable > coming in as a CFARGUMENT, then that will provide the protection > against non-numeric data trying to be appended in the value. > > I wouldn't go so far, though, as to say then that "you don't need > CFQUERYPARAM", since it still has other benefits in causing CF to send > a "prepared statement" which should perform better if you send more > requests that would reuse it. > > But this thinking does not apply for strings: I'm pretty sure the > validation of CFQUERYPARAM for cfsqltype="CF_SQL_CHAR" does more than > just ensure "it's a string". I think it escapes single quotes and > more, which I don't think the CFARGUMENT datatype protection will do. > Anyone know more? > > /charlie > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. > Saxe > Sent: Friday, July 27, 2007 8:40 AM > To: [email protected] > Subject: Re: [ACFUG Discuss] cfqueryparam in a sort > > How does that provide any security? Unless you validate the data, you > are subject to SQL injection. > > > Dean H. Saxe, CISSP, CEH > [EMAIL PROTECTED] > "Great spirits have often encountered violent opposition from weak > minds." > --Einstein > > > On Jul 27, 2007, at 8:20 AM, vivek khosla wrote: > >> The other way is to use cffunction in a cfc and passing the order by >> as varchar/string in cfargument, this will provide security as well >> performance. >> >> <cfargument name="parameterName" type="dataType" >> required="true/false" default="defaultValue"> >> >> Rgds >> >> Vivek Khosla > > > > ------------------------------------------------------------- > Annual Sponsor FigLeaf Software - http://www.figleaf.com > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists Archive @ > http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
