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