[Zope] safe sql queries

[Alexander Staubo]

This should work (untested):

<!--#sqlvar "'%' + _.string.upper(_['sequence-item']) + '%'"
type=string-->

--
Alexander Staubo             http://www.mop.no/~alex/
"He could open a tin of sardines with his teeth, strike a Swan Vestas
on his chin, rope steers, drive a steam locomotive and hum all the
works of Gilbert and Sullivan without becoming confused or breaking
down in tears."
--Robert Rankin, _The Book of Ultimate Truths_

>-----Original Message-----
>From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of
>Graham Chiu
>Sent: 13. juli 1999 22:33
>To: zope@zope.org
>Subject: [Zope] safe sql queries
>
>
>
>I would like to construct a crash proof case insensitive query that
>searches on a text field, and has a variable number of parameters.
>
>This effort works
>
>
>param: keyword:tokens
>
>select * from main where
>
><!--#in keyword-->
>  upper(description) like '%<!--#var sequence-item upper-->%'
>
>  <!--#if "(_['sequence-end'])"-->
>  <!--#else-->
>     and
>  <!--#/if-->
>
><!--#/in-->
>
>but dies if anyone inputs ' or other such chars as a parameter.
>
>This doesn't work
>
>select * from main where
><!--#in keyword-->
>  upper(description) like '%<!--#sqlvar sequence-item type=string-->%'
>
>  <!--#if "(_['sequence-end'])"-->
>  <!--#else-->
>     and
>  <!--#/if-->
>
><!--#/in-->
>
>as #sqlvar also puts ' marks around the variable, and there is
>no way to
>convert it to upper case.
>
>I've tried preprocessing the parameters to upper case them and
>add the %
>marks before feeding to the ZSQL query but get error messages like:
>
>'Strings are not allowed as input to the in tag.'
>
>Suggestions?
>
>-------
>Regards,        Graham Chiu
>gchiu<at>compkarori.co.nz
>
>_______________________________________________
>Zope maillist  -  Zope@zope.org
>http://www.zope.org/mailman/listinfo/zope
>
>(For developer-specific issues, use the companion list,
>zope-dev@zope.org - http://www.zope.org/mailman/listinfo/zope-dev )
>