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_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 > ------------------------------------------------------------- > > > > -- Douglas Knudsen http://www.cubicleman.com this is my signature, like it? ------------------------------------------------------------- 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 -------------------------------------------------------------
