[Zope] Deleting data using ZSQL

[jpenny@universal-fasteners.com]

On Thu, Sep 28, 2000 at 11:17:03PM -0400, technews@egsx.com wrote:
> Hi,
> 
> I am writing a dtml method that deletes data from a database, and I found
> myself unable to do this.  It seems that ZSQL is used to insert, update
> and query a database. but can not use
> "delete from table where var=<dtml-var foo>"
> 
 Two things, both previusly said, but both worth expanding on:

1)  What you are doing is not type-safe and not data-safe.  
If var is a string, for example,
that contains "this; delete from table;" You would probably not like
the result.  (Most SQLs would delete everything in the table!).

At least, you have to do
"delete from table where var='<dtml-var foo>'"`  (if var is string).

This is still not data-safe.   A string like 
"this'; delete from table; update table where var=NAME set var='"
is just as nasty as the previous one, although it is a bit harder
for a drooling idiot to think of.

delete from table where var=<dtml-sqlvar foo type=string> is perfectly
data-safe.  Dtml-sqlvar does two things for you:  It embeds the item
in the type of quotes appropriate for the TYPE= construct, AND it 
quotes (escapes) any characters passed to it.  This makes sure that
any strings are strings, and cannot contain hidden live commands.

(It would make the second example expand to something like

delete from table where 
var='this\'; delete from table; update table where var=NAME set var=\''

And this is a perfectly legal SQL statement that probably does no
deletion, but does no harm either!

2)  You may be seeing a DB-admin problem, rather than a ZSQL problem,
make sure your Zope DB-user account has delete permission.

Jim Penny