[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 http://www.fusionlink.com -
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: discussion@acfug.org 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 http://www.fusionlink.com -
RE: [ACFUG Discuss] cfqueryparam in a sort
SELECT * From Sheet1 where number = number AND fname like AND lname like AND dept like AND type like AND ocbar like ORDER BY ASC From: Charlie Arehart [mailto:[EMAIL PROTECTED] Sent: Monday, July 23, 2007 1:42 PM To: discussion@acfug.org 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: discussion@acfug.org 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 http://www.fusionlink.com -
RE: [ACFUG Discuss] cfqueryparam in a sort
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: discussion@acfug.org 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; discussion@acfug.org Subject: RE: [ACFUG Discuss] cfqueryparam in a sort SELECT * From Sheet1 where number = number AND fname like AND lname like AND dept like AND type like AND ocbar like ORDER BY ASC From: Charlie Arehart [mailto:[EMAIL PROTECTED] Sent: Monday, July 23, 2007 1:42 PM To: discussion@acfug.org 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: discussion@acfug.org 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://w
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 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
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: discussion@acfug.org
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; discussion@acfug.org
Subject: RE: [ACFUG Discuss] cfqueryparam in a sort
SELECT * From Sheet1 where
number = number
AND fname like
maxlength="30">
AND lname like
maxlength="30">
AND dept like
maxlength="30">
AND type like
maxlength="30">
AND ocbar like
maxlength="30">
ORDER BY
maxlength="30"> ASC
From: Charlie Arehart [mailto:[EMAIL PROTECTED]
Sent: Monday, July 23, 2007 1:42 PM
To: discussion@acfug.org
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: discussion@acfug.org
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
-
-
RE: [ACFUG Discuss] cfqueryparam in a sort
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 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 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 - 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 -
Re: [ACFUG Discuss] cfqueryparam in a sort
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 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 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 -
RE: [ACFUG Discuss] cfqueryparam in a sort
Yep, more and more refinement. :-) /charlie _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. Saxe Sent: Thursday, July 26, 2007 10:13 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] cfqueryparam in a sort 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 - 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 -
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. Rgds Vivek Khosla --- "Dean H. Saxe" <[EMAIL PROTECTED]> wrote: 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 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 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 - __ __ Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz - 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 -
Re: [ACFUG Discuss] cfqueryparam in a sort
to add a kink to the whole thing Select * from Artwork order by works just fine against a SQL Server DB. I'm about 98% certain this works against Oracle too, but alas I have no big phat Oracle DB to play with anymore. That said, Dean's approach is safe, simple to implement, and more portable in this case, eh? DK On 7/27/07, vivek khosla <[EMAIL PROTECTED]> 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. > > required="true/false" default="defaultValue"> > > Rgds > > Vivek Khosla > > > --- "Dean H. Saxe" <[EMAIL PROTECTED]> > wrote: > > > 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 > > 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 > > > 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? > > >> > > >> > > > > > >
Re: [ACFUG Discuss] cfqueryparam in a sort
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. Rgds Vivek Khosla --- "Dean H. Saxe" <[EMAIL PROTECTED]> wrote: > 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 > 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 > > 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: > > > >> Arent 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 > > > - > > Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz - 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 -
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: 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 to ensure automatic string escaping. In other words, don't build the query as a string and then do #myQuery#, 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 , however, since it does confer some performance benefits. 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: discussion@acfug.org 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. 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 -
RE: [ACFUG Discuss] cfqueryparam in a sort
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: discussion@acfug.org 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. > > 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 -
Re: [ACFUG Discuss] cfqueryparam in a sort
Wanna fight over it? ;-) And no, there isn't enough coffee in the world this morning to wake me up. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "To announce that there must be no criticism of the president, or that we are to stand by the president right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public." -- Theodore Roosevelt On Jul 27, 2007, at 12:06 PM, Charlie Arehart wrote: 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: discussion@acfug.org 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: 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 to ensure automatic string escaping. In other words, don't build the query as a string and then do #myQuery#, 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 , however, since it does confer some performance benefits. 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: discussion@acfug.org 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:2
RE: [ACFUG Discuss] cfqueryparam in a sort
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: discussion@acfug.org 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: 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 to ensure automatic string escaping. In other words, don't build the query as a string and then do #myQuery#, 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 , however, since it does confer some performance benefits. 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: discussion@acfug.org > 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. >> >> > required="true/false" default="defaultValue"> >> >> Rgds >> >> Vivek Khosla &g
