[Zope-DB] Re: ZSQL Method security concern (Charlie Reiman)

[Koos Blaauw]

> From: "Charlie Reiman" <creiman@kefta.com>
> To: <Zope-DB@zope.org>
> Date: Fri, 12 Apr 2002 09:57:36 -0700
> Subject: [Zope-DB] ZSQL Method security concern
> 
> 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
> values:
> 
> 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?

Charlie,

I've solved a similar problem by creating a Python-script wich composes
the complete SQL-statement.  I call this script from a ZSQL-method, so
you still have the advantages of that object.

  <dtml-in expr="Name_of_ZSQL_method()" size=10 start=query_start>

In the ZSQL-method I call the Python-script and pass a bunch of
arguments.

  <dtml-var expr="Name_of_Python_script(arg_1, ..., arg_x)">

Within the Python script I call another Python script to do a
comparible security-check as SQLVAR.

  arg_x = SQLVar(arg_x)

The SQLVar Python-script looks like this:

  sqlvar = var

  for i in range(len(var)):
    if var[:i] + ";" + var[i+1:] == var:
      sqlvar = var[:i]
      break

  return sqlvar

I hope this helps you!

Koos Blaauw.