[Zope] safe sql queries

[Graham Chiu]

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