[Zope-DB] Using <dtml-var>s in ZSQL methods?

[JPenny at ykksnap-america.com]

> 
> I also have a broader question:  When one is composing dynamic SQL in 
Python
> scripts, what are the pros and cons of executing them by going directly 
to
> the database adapter (as suggested above) vs passing it in as the sole
> argument of an "empty" ZSQL method?  By "empty" ZQL method I mean 
something
> like:
> 

There are no pros and cons.  Only cons.

There is a good argument to be made that ZSQL methods are entirely
a bad idea -- that only prepared statements should be supported, as it
is far harder to break security.

But, every use of <dtml-var ...> in a ZSQL method requires that the 
argument be examined and correctly SQL-Quoted.  For example, what is
to keep someone from entering "13225, 12337; delete from person" in 
your web form?

Further, you have greatly complicated verification and maintenance.  It
no longer is enough to test the ZSQL method to be sure that it operates
as expected.  You have to examine every call-point to determine what the
SQL method is doing.  And you have to examine every argument to be sure
that it has been quoted properly and you aren't open to SQL injection.

Charlie has already given the best answer -- use a really simple method 
like:
delete from person where person_id = <dtml-sqlvar foo type=int>, and
call it once for each person you have to delete.  SQL injection is
impossible, since foo is verified to be an int just before it is used.

Now, there are times where <dtml-var ... > is unavoidable;  IN clauses and
LIKE clauses are the principal ones.  In either case, you really need to
verify the arguments.  At the bare minimum, look at <dtml-var ... 
sql_quote>.


jim penny