[Zope-DB] Dynamically generate sql-query in ZSQL Method

[Jim Penny]

On Tue, 06 Apr 2004 14:58:58 -0500
Ian Bicking <ianb at colorstudy.com> wrote:

> hawelek_tlen wrote:
> > Hi,
> > I'm beginner in Zope,
> > I want to generate dynamically sql-query in ZSQL Method,
> > exactly i want to give whole sql-query like parameter to ZSQL Method
> > for example in DTML Method,
> 
> I think you want a Z SQL method (we'll call it raw_sql) with a body
> like:
> 
> <dtml-var sql>
> 
> Then you call raw_sql(sql='select * from person').  That's all there
> is to it!  (Of course if you use this, be cautious of SQL injection 
> security holes)
> 
>    Ian

Ian has told you how to do it.  Now, I will tell you, don't do it.

Why?

Two reasons:
1)  you cannot prevent SQL injection problems, short of having a
complete and correct parser from your SQL dialect.  Writing that parser
will be a LOT more work than the typical three ZSQL methods per table.

2)  you have made debugging much more difficult.

The beauty of ZSQL methods, correctly used, is that they can be debugged
pretty statically.  Although you can contruct impossibly complex ZSQL
methods, restrict yourself to no more than <dtml-sqlvar ...>, '<dtml-var
... sql_quote>%' (for wildcarding, there are obvious variations), and
<dtml-if>...</dtml-if>. You can get really wild and use <dtml-and> or
<dtml-or>.  Write an insert method, an update method, and a delete
method.  Test them.  Once working, leave them alone!

Consider ZSQL methods to be correctness assertions.  If a database
interaction is blowing up, the ZSQL method pretty much automatically
isolates you to a single piece that can be failing.  With variable SQL,
you no longer have this.  A failure may be the result of any call, which
can be in pretty much any piece of code.  This is not good from a
debugging or a maintenance point of view.

Every newbie goes through this stage. I did.  To my shame, I wrote a
howto, long ago, about this very subject.  My only defense is that it
was before SQL injection was widely published, but even at that, the
idea was bad!

Jim Penny