problem solved--Re: [Zope] question about forms and zsql

[Jim Penny]

On Tue, Feb 12, 2002 at 11:26:54AM -0600, Robert Hood, Ph.D. wrote:
> Thanks for the help!
> 
> Tom P wrote:
> >You don't need to go to a python script. Instead, include a input element
> >in your form to hold the search phrase. Say you name that input field
> >"searchphrase". Then in your zsql method, you write something like
> >select * from table where name like '%&dtml-searchphrase;%'
> 
> YES--all I had to do was make some minor changes to my zsql and it now 
> works, and thankfully, no scripting, like so:
> 
> SELECT * FROM Courses
> WHERE (CourseName LIKE '%&dtml-CourseName;%' OR
> SemesterTaught LIKE '%&dtml-SemesterTaught;%' OR
> CourseType = <dtml-sqlvar CourseType type=string>);

An aside to newbies:  

Make sure that you do some validation here.  You need to be sure
that CourseName and SemesterTaught do not contain an unquoted '
and possibly a sql-delimiter (usually ;).  It might be a very
good idea to simply strip all single quotes from these fields
before you call you zsql method.

Suppose you had a course
Charlotte Bronte's Role in the Development of the Gender Awareness,
and your user entered 
Charlotte Bronte's Role
For CourseName.

The ZSQL method will expand to
SELECT * FROM Courses WHERE (CourseName LIKE 'Charlotte Bronte's Role' OR ...

This will lead to a failure (ungrammatical SQL).

On the other hand, if you have someone with a grudge,
who enters 
Charlotte Bronte'); delete from Course; delete from course where CourseName='' 
well, you either better have a backup, or you have a nice little 
denial of service attack.

Recap:

If you cannot use a dtml-sqlvar form, you are responsible for making sure
that sqlquoting is done (check sql_quote of dtml-var in appendix A in the
Zope Book), or that input strings with single quotes in them never reach 
your zsql method.  Always validate such input carefully.

Jim Penny


> 
> Robert
> 
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
> http://lists.zope.org/mailman/listinfo/zope-announce
> http://lists.zope.org/mailman/listinfo/zope-dev )
>