[Zope] howto sql-query directly from my products methods?

[Jim Penny]

On Mon, Mar 04, 2002 at 03:32:24PM -0500, Thomas B. Passin wrote:
> [hans]
> 
> > well, i could instantiate a bunch of zsql methods, each with
> > its own query and then call them from my products methods.
> > but how could i obviate the need to have these zsql methods
> > and query directly? Or is it just not worth it?
> > thx hans
> 
> Well, the extreme and simplest case is to have one zsql method that takes a
> single parameter called, say, "sql":
> 
> &dtml-sql;
> 
> The sql parameter would contain the entire query, that is, an entire select
> statement or insert statement or whatever.
> 
> This is easy and flexible, but has no security at all.  If someone posted
> you a DELETE statement, you might end up very unhappy, for example.  Still
> it is an easy approach to use when you are tuning up a query, as long as you
> don't let it loose outside your system.
> 
> The next level up from this is to have your page, or a method called by the
> page, construct a query statement using values from a form, then hand the
> query to your friendly general zsql method.  This is quite a bit more more
> secure, but you still need to make sure it's going to be safe.
> 
> A next step up in security is to make sure that any query that can be
> constructed and passed to the zsql method can only access non-updateable
> views.
> 
> There's a tradeoff between having highly specialized zsql methods, which
> could be the most secure but can lead to you having to maintain many
> individual methods, and the completely general approach I've outlined above
> where you only need one method but have to manage the security issues.

These parameterized ZSQL methods are generally a bad idea.  They occur
to everyone at some point early in their Zope career.  I know they did
to me, I wrote a howto on it.  But, they are almost always a bad idea.  

Mind you, not only is there a security tradeoff, but there are also
managability, testability, and correctness tradeoffs.

If you use concrete zsql methods, then you have only to examine the method
to determine what arguments are needed, and often you can directly read
what is being returned, modified, or deleted.  That is, understanding
the database interactions of the code in a folder is often as simple as
looking at the ZSQL methods in the folder.

If you use one of the "statement is a parameter" forms, then you have to
read (at least) all the code in the folder, everything, every dtml
method, pythonscript, external method, etc. to determine the
interactions with the database.

Worse, you learn about mistakes too late.  With concrete ZSQL methods
you can use the Test Tab to make sure that the method does what it is
supposed to do.  You are reasonably confident that the method does
what it should, no more and no less, before you ever let it loose.
With "statement is a parameter" code, you don't learn that there is a
problem until run time.

My current belief is that you should never use any ZSQL method with
anything more complicated than a sqlgroup in it, and that you want to be
sparing with sqlgroups.

One other thing -- keep your ZSQL methods close to the folder they are
accessed from.  Only very common queries should be closer to the root.
I suspect that in many, if not most, cases, the set of databases that
you need to work with in a single folder is pretty small.  I think that
this is due to programmer forethought, that is, that you naturally design
your databases and your folder structure to be compatible.  Now, if you
are operating with only one, two, or three tables per folder, then you
probably need no more than a few insert, delete, select, or updates per
table.  This translates to four to ten ZSQL methods per table used.  If
you have only three tables you are working with in a paritcular folder,
this is just not all that onerous.

Jim Penny

> 
> Cheers, 
> 
> Tom P