[Zope] sql-statements in DTML-Methods....

[jpenny@universal-fasteners.com]

On Mon, Jun 19, 2000 at 08:50:14AM -0500, Jim Sanford wrote:
> For the most part all a ZSQL method does is used some specialized DTML
> syntax to contruct a string that is your SQL query. You can create a ZQL
> method, say "GenericSQL", that has 1 parameter, let's call it
> "SQLStatement", whose sole DTML statement is "<dtml-var SQLStatement>".
> 
> To use it you would do this:
> 
> <dtml-in "GeneicSQL(SQLStatement='select * from the_table where the_var =
> \'var_value\'')>
> ...
> </dtml-in>
> 
> You can replace the literal string with a string variable like from a filed
> on a form where you entered you SQL statement.
> 
> I use this method extensively.
> 
> Jim Sanford

I have also used Jim S.'s method.  But, there is a danger here.  Using ZSQL
methods, you can pretty much insure that users cannot fill in a form such
such that when zope triggers the execution the sql server is crashed or
an inappropriate command is executed.  ZSQL methods will handle quoting
for you, the literal string method will not.  So, in the literal string
method, you may have to worry about input like:
hello';delete from the_table;'select * from that_table
which should run and probably does not have the intended effect.

Further, if your database backend has memory leaks when unexecutable
SQL requests are submitted (some postgresql version have had), you have
opened up a dandy denial of service.  

It is better to use normal ZSQL Methods, unless you are forced not to
(arguments that depend on the data in the form, for example).
And then you need to be very careful with data validation.

Also, note that there are not normally all that many calls to the
database.  You can usually get by with a insert into, a delete from,
a select *, and a few updates per database.  And, they can be shared
by sub-folders.  

Jim Penny 

> 
> 
> ----- Original Message -----
> From: "Marc LUDWIG" <lutschi72@gmx.net>
> To: <zope@zope.org>; <zope-dev@zope.org>
> Sent: Sunday, June 18, 2000 3:08 PM
> Subject: [Zope] sql-statements in DTML-Methods....
> 
> 
> Hi.
> 
> This is one of the first times I'm working with Zope and databases.
> 
> Until now - if I wanted to work with a databse, i always created a Z SQL
> method that I called from my DTML-method via
> 
>     <dtml-call "add_item(param1 = ..., param2 = ..., param3 = ...,
> ...">    (for example)
> or <dtml-in get_items> ... </dtml-in>              (for example)
> 
> In the Z SQL method 'add_item' or 'get_items', my SQL statement are defined.
> 
> My question is now:
> 
> Do I have to define one Z SQL Method for every SQL statement I want to
> perform on my database or is it possible to define SQL-statements and
> database requests in my DTML-method?
> 
> Am I missing something? Any idea? Thanks a lot.
> 
> Regards, Marc
> mailto:lutschi72@gmx.net
> 
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
> 
> 
> 
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
>