[Zope-DB] Safe way to insert variable column names for order by?

[Jim Penny]

On Tue, Nov 12, 2002 at 11:05:51AM -0500, Jeff Kowalczyk wrote:
> I'm just getting started with ZSQL methods, and I wondered if there was a safe or
> recommended way to handle dynamic queries that parameterize their sorting and other
> 'non-test'. If I use dtml-sqlvar, sort and order will be quoted, which is incorrect SQL.
> 
> <params>ShipmentDate=11/06/2002
> sort=ShipmentID
> order=asc</params>
> select ShipmentID, ShipmentDate, LocationAddress
> from Shipments
> where <dtml-sqltest ShipmentDate op=eq type=string>
> and ShipperID = 'MyShipper'
> and DeliveredTo is not null
> order by <dtml-var sort> <dtml-var order>
> 

The best answer is that to be fully safe you should use
order by <dtml-var sort sql_quote> <dtml-var order sql_quote>

However, if sort and order are being generated from the form by a Script
Python, or the like, you have fairly small exposure, anyway.  The
evil-doer would have to know the exact name of the method, the names of
any weakly protected parameters, and URL quoting to directly use the
method.

Note sql_quote does not quote the argument.  It merely 'escapes' it.
So, you might use something like
'<dtml-var "_.str(_.SecurityGetUser()) sql_quote">'
as a correct clause.

Jim Penny
> 
> 
> 
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db
>