[Zope-DB] dynamic SQL

[Jim Penny]

On Wed, Apr 09, 2003 at 01:10:25PM +0200, Fernando Martins wrote:
> Thanks for replying,
> >
> > It is quite easy.  But you really, really, really don't want to do it.
> >
> > zsql method
> > variable_sql
> > parameter
> > command
> > body
> > <dtml-var command>
> >
> 
> It took me a while to understand what you mean with this list of items. So,
> for the record, the idea is to create a zsql method called variable_sql with
> a parameter called command and a body having only <dtml-var command>. The
> zsql is called with a complete SQL statement from wherever you want.

Yes, exactly.

> 
> > Now, what is wrong with this?
> >
> > Well, you have no security, whatsoever.  Anyone who can access method
> > variable_sql can do anything that they want to our database.  Even if
> > you somehow limit access to the method, you can't stop SQL injection.
> > And you can't debug the SQL, since you have no idea of what will be
> > executed.
> >
> 
> Well, in my case is for an Intranet and it's essentially a prototype.
> 
> > Go to the trouble now.  It will reduce your trouble later.
> >

I wrote a howto on doing this.  This is a common idea _many_ new users
have.  I now deeply wish that I had not done so.  This is very bad
magic.  It makes your life unpleasant for an unforseeable time in the
future.  And when you are prototyping is exactly the wrong time to do
it!  The problem is that you then have an unauditable mess that can be
fixed only by throwing everything away.

I recommend that you do things in little steps.  Start writing your
application in ZPT (or DTML) and pythonscripts.  Every time you need a
SQL query, go off to the side, and write it.  Test it from the SQL test
tab.  That way you are reasonably confident that it works before you put
it in the ZPT.  Put it in the ZPT.  test again.  Flesh out the ZPT.
Test again.

One of the most pleasant parts of zope is the ease of continuous,
informal, samll testing.  Oh, things will get past you, but if you test
as you develop, a lot less gets past you.  I often test every time I
create a new table row, or paragraph, or whatever.

Jim Penny
> 
> Appreciated,
> 
> Fernando
> 
> PS: thanks also to Michal.
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>