[Zope] Database Adaptors and security and query()

[Brian Lloyd]

> Couple of things came up trying to use the current PostgreSQL da
> and zope 2.2b3.  I am working on a ZClass that lets you define
> and create tables in a database, so I needed to execute some
> generated sql [1].  
> 
> 1) I couldn't access the query() method of the connection unless
>    I added our friend
> 
>    __allow_access_to_unprotected_subobjects__=1
>  
>   to the DB class defined in ZPyGreSQLDA/db.py
> 
>    Is this ok?

No - at least I highly doubt that your DBA will think so :) By
adding that assertion, you have now made the query() method 
accessible to anyone who can write DTML on your site and they 
can now run arbitrary SQL on your database. You may also have 
made it possible for random joes to call the query method of 
the connection through the web with whatever query they want :(


> 
> 2) Re calling query().  The query method only returns a tuple used
>    to create a Results object (which dtml-in would like).  So I
>    added a method to return a real live Results object 
> 
>    def query_result(self,query_string, max_rows=9999999):
>         return Results(self.query(query_string, max_rows))
> 
>    Which does what I want.  Is this ok?  Is this a good idea?

No, for the same reason as 1


> 
> 3) I went down several wrong paths until I realized that it seems
>    as if dtml-methods can't return anything but strings.  Is this
>    right?

If you are calling the dtml method, you will get a string (the 
rendered document) unless you use something like the 'return' 
tag:  <dtml-return "('foo', 'bar')">.  If you are going to this 
much trouble, you probably want to use an external method or 
something more suited to the task.

> 
> 
> 4) Grumble.  I had a lot of trouble with the interaction between
>    Results returning data as tuples, and dtml-in handling tuples
>    of 2 differently than everything else.  Create a table with
>    3 columns, everything works.  Create a table with two columns,
>    things dont work.  Is there access to list() anywhere?

Not in DTML.


> [1] I know, I can hear you saying "Use ZSQLMethods" but 1) 
> You can't subclass
>     them, and b) You can't put one inside a ZClass without a dummy
>     connection.

"Use ZSQLMethods" :^) Seriously, it would be better to 
deal with the two problems above directly than to use 
workarounds that put security at risk.



Brian Lloyd        brian@digicool.com
Software Engineer  540.371.6909              
Digital Creations  http://www.digicool.com