[Zope] User-controlled report sorting with Z SQL Methods

Andy Dustman andy@dustman.net
Mon, 13 Aug 2001 22:59:22 -0400 (EDT)


Suppose you have a Z SQL Method which is used for generating a tabular
report, and you want to add some buttons to the headers so that clicking
on a button will resort the report based on that column, either ascending
or descending. You've probably seen something like this. My first impulse
is to do something like this:

SELECT ...
<dtml-if OrderBy>ORDER BY <dtml-var OrderBy></dtml-if>

This has a couple of problems: You don't want to accept just any old value
of OrderBy that someone manages to submit. You probably only want to allow
sorting on columns that actually have an index. Worse, a malicious user
might put other bad things in there. (Since we are passing in a column
name, rather than a literal value, we cannot use sql_quote.)

One solution to this could be to have a method (a Python Script is
probably the best match) which returns the ORDER BY clause (or an empty
string) after validating the ordering column. So let's say that doOrderBy
is our method that does this, and our query looks like this:

SELECT ...
<dtml-var doOrderBy>

However, nothing stops a malicious user from passing in doOrderBy on the
form, which may do Very Bad Things. But, due to the DTML namespace, the
client object is always on top of REQUEST, so any passed-in doOrderBy will
not be seen (unless using <dtml-with REQUEST only>).

Does this sound like a reasonable solution?

-- 
Andy Dustman         PGP: 0xC72F3F1D
    @       .net     http://dustman.net/andy
I'll give spammers one bite of the apple, but they'll
have to guess which bite has the razor blade in it.