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

Thomas B. Passin tpassin@mitretek.org
Mon, 4 Mar 2002 16:59:52 -0500


[Jim Penny]

> On Mon, Mar 04, 2002 at 03:32:24PM -0500, Thomas B. Passin wrote:

> > 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.
> >

> 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.
>

I find I spend most of my effort developing queries and browsing tables as I
develop code.  I like to use zsql methods that make this easy and flexible.
So I tend to write my own SQL statements by hand, even in a textarea.  You
can still test them using the test tab.  When you go to a production query,
I go along with everything you said.  Same for using  Cold Fusion.

Cheers,

Tom P