[Zope] MySQL LIKE operator

Phill Hugo phill@fortune-cookie.com
Thu, 13 Jul 2000 08:43:15 +0000


> > -----Original Message-----
> > From: aaronw@c.ict.om.org [mailto:aaronw@c.ict.om.org]
> > Sent: Wednesday, July 12, 2000 11:03 AM
> > To: zope@zope.org
> > Subject: [Zope] MySQL LIKE operator
> >
> >
> > Hello,
> >
> >     I'm writing a search query to a MySQL database.  I want to keep
> > people from screwing around with my database by running searches like ";
> > delete from ... yada yada.  So I should use <dtml-sqlvar>, right?  But
> > what if I want to use LIKE?
> >   If I say:  WHERE goo LIKE "%<dtml-sqlvar name=bar type=string>%"  then
> > effectively I am saying: WHERE goo LIKE "%'somestring'%".  In other
> > words, it will match only the string with the single quotes.  I hope
> > this makes sense.  Has anyone faced a similar problem?
> >   Thanks for any help
> >
> > --Aaron

Here's a sample of some larger scale SQL with just the thing your
looking for in it.

SELECT *
FROM users
<dtml-sqlgroup where>
 <dtml-sqltest user_name_exact column=user_name type=nb optional>
 <dtml-and>
 <dtml-if user_name>
 user_name LIKE <dtml-sqlvar "'%'+user_name+'%'" type=nb optional>
 </dtml-if>
<dtml-and>
 <dtml-if user_firstname>
 user_firstname LIKE <dtml-sqlvar "'%'+user_firstname+'%'" type=nb
optional>
 </dtml-if>
<dtml-and>
 <dtml-if user_lastname>
 user_lastname LIKE <dtml-sqlvar "'%'+user_lastname+'%'" type=nb
optional>
 </dtml-if>
<dtml-and>
 <dtml-if user_age>
 user_age LIKE <dtml-sqlvar user_age type=nb optional>
 </dtml-if>
<dtml-and>
 <dtml-if user_town>
 user_town LIKE <dtml-sqlvar "'%'+user_town+'%'" type=nb optional>
 </dtml-if>
<dtml-and>
 <dtml-if user_email>
 user_email LIKE <dtml-sqlvar "'%'+user_email+'%'" type=nb optional>
 </dtml-if>
</dtml-sqlgroup>
ORDER BY
user_name

Hope this helps

Phill