Charlie,
Good idea. Actually the numeric value can be used
to do this too in
what is called the pattern of indirection. Put all
of the fixed
values, i.e. the order by clauses, into an array.
When the user
selects a value it is the ordinal position of the
value in the
array. Before referencing the array you can
validate the value is
numeric and between 0 and arrayLen - 1. You can
safely reference the
value directly in the cfquery at this point.
I commonly use this pattern for things like account
numbers I am
presenting to a user, this is a good mechanism to
prevent the user
from attempting to put in a value other than the
ones presented to
him on the form.
-dhs
Dean H. Saxe, CISSP, CEH
[EMAIL PROTECTED]
"If liberty means anything at all, it means the
right to tell people
what they do not want to hear."
-- George Orwell, 1945
On Jul 26, 2007, at 8:57 PM, Charlie Arehart wrote:
Just wanted to add another thought on this thread
from earlier in
the week. The issue was that Seth wanted to make
his ORDER BY
clause to be driven by a user-entered variable (in
his case, a form
radio button selecting the field to sort by), and
he found that
CFQUERYPARAM wasn't working for that (because it's
designed for
substituting values in a WHERE clause, not
table.or column names.)
Dean's proposal of using a number to do the
sorting is indeed a
useful one, but I thought of something that none
of us mentioned.
Since you know that the list of columns is a
limited set, you could
also keep it the simpler way of passing in the
column names (if you
needed to for some reason), but always compare the
input field name
against the set of valid columns to sort by. That
way, any
nefarious attempt by a user to inject extra SQL
statements will be
detected and prevented. Hope that's helpful.
/charlie
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Dean H.
Saxe
Sent: Monday, July 23, 2007 4:52 PM
To: [email protected]
Subject: Re: [ACFUG Discuss] cfqueryparam in a
sort
Yes, Seth, technically you are correct. But there
is a better way
here. Instead of passing the sort column name
directly from the
user, send a proxy value for the order by clause.
In other words,
identify the different order by clauses by numeric
value, 1, 2, 3,
4, etc. In the query you would include a
<cfswitch> and the cases
would be the different numeric values. These
would then define
what the ORDER by value would be.
No more SQL injection and no need to much around
with
<cfqueryparam> here, either.
-dhs
Dean H. Saxe, CISSP, CEH
[EMAIL PROTECTED]
"Great spirits have often encountered violent
opposition from weak
minds."
--Einstein
On Jul 23, 2007, at 4:46 PM, Tepfer, Seth wrote:
Aren’t dynamic ORDER BY variables just as
susceptible to SQL
injection as WHERE clauses?
