> That's a nice list Isaac, but I'm curious about two things. Thanks Judah. :)
This should help me improve the wiki and the documentation. > 1) what is the "sort" versus "order by". I'm quite familiar with the order > by clause but I've never heard of a sort clause. Perhaps you mean something > else? Right. As far as I know, there's not a "sort" clause in SQL. The "sort" feature is a DataFaucet feature that allows you to safely use user-supplied data in the order-by actually in much the way you described in question 2. So when you're building a query with DataFaucet, you might have something like this: qry = ds.getSelect(table="mytable",orderby="a,b,c").sort(url.sort) Now what DataFaucet does with that is set a default sort order using the orderby property you supplied. It then will pre-pend the value of url.sort to that order, so if the user goes to index.cfm?sort=d,c/desc it will create the order by clause "order by d desc, c desc, a, b (it also eliminates the dup-c because duplicated columns cause errors with some databases) Before it prepends those columns it verifies that the columns specified exist in that table, so for example, if a malicious user were to enter the url index.cfm?sort=DECLARE... it would automatically remove the malicious sort string because it won't find it in the list of available columns in that table. The OrderBy property however is not protected. So you can have things in the orderby property that aren't allowed in sort, like concatenated columns or a coalesce(). So orderby="coalesce(a,b)" is allowed, but sort="coalesce(a,b)" will be stripped to protect you. Hence the reason why you should never put user data in orderby, because sort has been provided as a tool to protect you from SQL-injection attempts while still giving you the freedom to do some of those more advanced things you might want to do with orderby. There's a brief section on sorting in the docs for select statements here: http://www.datafaucet.com/sqlselect.cfm > 2) You say to not "put ANY user-supplied variables into the content property > of a dynamic filter (i.e. stmt.sqlFilter("column",form.x,"in"))" I can > understand the reasoning behind that, but that is a very common task. Actually "dynamic filters" in DataFaucet are pretty uncommon. I can't even think of any in my code offhand. There are a number of different types of filters in DataFaucet - there's a standard "filter" and then there are numeric, date, etc. filters which really are just "helpers" or "shortcuts" for doing more sophisticated kinds of filtering although they ultimately all use the same standard filters underneath, just with different syntax sugars applied. A dynamic filter in DataFaucet isn't your standard "where col = 3" kind of filter. A dynamic filter is a filter that's been declared with the dynamic property so that you can for example compare it against another column name, so it's things like "where col1 like '%' + col2 + '%'". They only exist to allow the flexibility to perform some advanced SQL tasks that honestly I can't recall needing with any of the apps I've built with DataFaucet. I think I added it originally because it was requested by Aaron Rouse. (Hi Aaron!) :) The reason why you don't want to put user supplied data in the content porperty of those filters is because when the filter is declared as "dynamic", the content value is just output directly into the query without any modification and without any query parameters... It can't use query parameters for that because that would turn the column reference "col2" (in the above example) into a literal value of "col2", so you'd get this "where col1 like '''%'' + col2 + ''%'''" which won't match the way you want if you're using a dynamic filter. There's a page on dynamic filters in the documentation here: http://www.datafaucet.com/sqldynamicfilter.cfm > There > are lots of reasons to let the user order the data by various columns and > that should be a standard feature of most any tabular data display. So how > would you suggest that developers implement the feature? I personally would > have a built in list of columns that could be sorted by and then double > check on the server side to make sure that the chosen sort field is in the > allowable list before adding the filter. Do you have other suggestions? Yep, DataFaucet uses the list of columns that are actually in the table to determine what columns are allowed in the sort property. Anything not found in the table is dropped from the query. So it's basically what you're describing here, though it automates the list so you don't have to write it out manually. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312514 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
