[Zope-DB] ZSQL Method security concern

Charlie Reiman creiman@kefta.com
Fri, 12 Apr 2002 09:57:36 -0700

I have a bunch of queries I need to make for my product. These look like:

select sum(thing1) from the_big_table where something...
select sum(thing2) from the_big_table where something...
select sum(thing3) from the_big_table where something...
select sum(thing4) from the_big_table where something...

I have this set up as a single ZSQL Method with a template like this:

select sum(<dtml-var field>) from the_big_table where <dtml-var expr>

This is insecure since I should be using dtml-sqlvar to escape suspect
strings. Fine and dandy, except dtml-sqlvar is used for inserting SQL field

select yadda from the_big_table where afield=<dtml-sqlvar key type="string">

As far as I can tell, dtml-sqlvar isn't useful for inserting field names or
fragments of an expression. I guess this make sense since you can't really
know what kind of fragments would be malicious.

Unfortunately, the "where" clause in my system can get complicated and can
vary for each report style. How can I secure this? My ZSQL Methods are
callable from any old browser, so anyone who can read the code will see a
back door into the database. Would it be reasonable to subclass ZSQL Method
and add extra checks somewhere?