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