[Zope] Re: Zope SQL injection

[Jim Penny]

>Andy Yates wrote:
>> Could somebody either point me to an article or explain what
>> precautions  should be taken to prevent SQL injection in Zope.  If
>> user entered form  data is passed to a ZSQL method does something
>> automajically db escape  the data or is the programmer responsible
>>for
>> doing this.  If the  programmer is responsible, how is it done in
>> Zope?  Thanks!

>Don't use <dtml-var> in ZSQL-Methods, use only <dtml-sqlvar>. 
><dtml-sqlvar> is escaping the parameter automagically, so nobody can 
>inject malicious code... at least I hope so...;)

>Cheers, Maik

OK, this is pretty close.  Use dtml-sqlvar whenever possible. 
dtml-sqlvar will sql_quote things declared string for you, and will
through an exception if something declared float or integer can't be
evaluated.

Unfortunately, there are some fairly common cases where dtml-sqlvar
won't work.  The best example is when using a LIKE clause.

select * from foo where field like <dtml-sqlvar stuff type=string>% 
will render as
select * from foo where field like 'sql_quoted_form_of_stuff'%
which is syntactically incorrect.

You have to use 
select * from foo where field like '<dtml-var stuff sql_quote>%'

Do not sql_quote numeric items, always use dtml-sqlvar, as sql_quote
really just affects quoting, and does not escape semi-colon.