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?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie
Arehart
Sent: Monday, July 23, 2007 4:43 PM
To: [email protected]
Subject: RE: [ACFUG Discuss] cfqueryparam in a sort
OK, as I had surmised, you're using the CFQueryParam in the SORT
itself. I know this "works" (doesn't give an error, in 7 or
before), but it doesn't do what you want, right? I ran a test
against 7 and found, as you did, that it did not sort. But guess
what, it fails entirely in CF8 (complaining that there is a ? in
the SORT). That's what I'd suspect, really. CFQUERYPARAM is
intended for use in a WHERE clause value. From the CFML reference,
regarding the VALUE attribute, it should be a "value that
ColdFusion passes to the right of the comparison operator in a
where clause."
Indeed, there are two main purposes for this tag: one is to help
prevent SQL injection, and the other is to cause a bind variable
(or parameterized query) to be passed to the DB. That's what the ?
is about. I suppose some database drivers out there may accept a
bind variable for a column name, but I'm guessing it will be hit
and miss. I've found references that suggest that they're intended
just for literals, not DB objects (tables, columns, etc.) Again,
perhaps it's worked for some.
Anyone have more specific experience?
/charlie
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Small,
Lewis B.
Sent: Monday, July 23, 2007 2:45 PM
To: Charlie Arehart; [email protected]
Subject: RE: [ACFUG Discuss] cfqueryparam in a sort
<cfquery name="Recordset" datasource="db1">
SELECT * From Sheet1 where
number = number
<cfif myFname is not "">
AND fname like
<cfqueryparam value="%#myFname#%" cfsqltype="CF_SQL_VARCHAR"
maxlength="30">
</cfif>
<cfif myLname is not "">
AND lname like
<cfqueryparam value="%#Lname#%" cfsqltype="CF_SQL_VARCHAR"
maxlength="30">
</cfif>
<cfif myDepartment is not "">
AND dept like
<cfqueryparam value="#myDepartment#" cfsqltype="CF_SQL_VARCHAR"
maxlength="30">
</cfif>
<cfif myType is not "">
AND type like
<cfqueryparam value="#myType#" cfsqltype="CF_SQL_VARCHAR"
maxlength="30">
</cfif>
<cfif myOcbar is not "">
AND ocbar like
<cfqueryparam value="%#myOcbar#%" cfsqltype="CF_SQL_VARCHAR"
maxlength="30">
</cfif>
<cfif myRadio is not "">
ORDER BY
<cfqueryparam value="#myRadio#" cfsqltype="CF_SQL_VARCHAR"
maxlength="30"> ASC
</cfif>
</cfquery>
From: Charlie Arehart [mailto:[EMAIL PROTECTED]
Sent: Monday, July 23, 2007 1:42 PM
To: [email protected]
Cc: Small, Lewis B.
Subject: RE: [ACFUG Discuss] cfqueryparam in a sort
Seth, it may help to show your SQL so we know for sure what you're
asking. I can't see how using CFQUERYPARAM for the value of a WHERE
clause would affect in any way an ability to SORT. Now, are you
sorting in the same CFQUERY using the CFQUERYPARAM? Or might this
be a Query of Query sort of that previous CFQUERY?
/charlie
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tepfer,
Seth
Sent: Monday, July 23, 2007 12:28 PM
To: [email protected]
Cc: Small, Lewis B.
Subject: [ACFUG Discuss] cfqueryparam in a sort
We have an MSAccess db, and have a dynamic sort with cfqueryparam.
When I use the actual field (fname), the query sorts correctly.
When I use the cfqueryparam, it does not sort – even though the
debug says the field fname was sent correctly.
Does cfqueryparam make sort not work?
Thanks
seth
-------------------------------------------------------------
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
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
-------------------------------------------------------------
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
-------------------------------------------------------------
