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

Joel Burton joel@joelburton.com
Mon, 4 Mar 2002 15:49:50 -0500 (EST)


On Mon, 4 Mar 2002, 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.

I also wrote back with this idea, Tom, but forgot to mention that you
need to be really careful about the security, especially as some of us get
lazy about simply refer to things as <dtml-var foo> without being specific
about where foo comes from. Can someone post form variables with

script?sql=DELETE FROM tblImportant

?

Thanks for being on top of things to include this warning!

-- 

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant