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. cfargument name=parameterName type=dataType 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 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 - __ __ Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mailp=graduation+giftscs=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 cfquery name=ins datasource=artists maxrows=10 Select * from Artwork order by cfqueryparam cfsqltype=cf_sql_varchar value=costcode asc / /cfquery 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. cfargument name=parameterName type=dataType 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 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 - Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mailp=graduation+giftscs=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
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. cfargument name=parameterName type=dataType 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 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: 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_mailp=graduation+giftscs=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: snip 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 cfquery to ensure automatic string escaping. In other words, don't build the query as a string and then do cfquery#myQuery#/cfquery, 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 cfqueryparam, however, since it does confer some performance benefits. /snip 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. cfargument name=parameterName type=dataType 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 - - 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. cfargument name=parameterName type=dataType 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: snip 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 cfquery to ensure automatic string escaping. In other words, don't build the query as a string and then do cfquery#myQuery#/cfquery, 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 cfqueryparam, however, since it does confer some performance benefits. /snip 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. cfargument name=parameterName type=dataType
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: snip 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 cfquery to ensure automatic string escaping. In other words, don't build the query as a string and then do cfquery#myQuery#/cfquery, 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 cfqueryparam, however, since it does confer some performance benefits. /snip 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. cfargument name=parameterName type=dataType 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
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 - 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
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: 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 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: 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
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? 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 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: 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
