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
-------------------------------------------------------------
