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: discussion@acfug.org
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?
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
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 FusionLink
-------------------------------------------------------------
