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

[Ken Winter]

Thanks Charlie & Jim ~

SQL injection is a new one on me, and I'm glad to learn about it now
(painlessly) rather than later (painfully).

~ Ken


> -----Original Message-----
> From: JPenny at ykksnap-america.com [mailto:JPenny at ykksnap-america.com]
> Sent: Monday, June 18, 2007 10:03 AM
> To: Ken Winter
> Cc: 'Zope-DB List'
> Subject: RE: [Zope-DB] Using <dtml-var>s in ZSQL methods?
> 
> >
> > 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