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

JPenny at ykksnap-america.com JPenny at ykksnap-america.com
Mon Jun 18 10:03:02 EDT 2007

> I also have a broader question:  When one is composing dynamic SQL in 
> scripts, what are the pros and cons of executing them by going directly 
> 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 
> 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 
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 ... 

jim penny

More information about the Zope-DB mailing list