[Zope] Searching for a generalized key in ZSQL...

Rob Page rob.page@digicool.com
Mon, 2 Aug 1999 13:25:01 -0400


> 	I'm trying to search an SQL table for a key=value where 
> the user gets to 
> select the key and the value.  I have a select method on a 
> DTML document to set 
> the key and a text input to set the value, but I cannot 
> successfully use them in 
> the SQL without getting single quotes around them.
> 	select *
>           from Users
>           where <!--#sqlvar searchkey type=string--> =     

make this 

           where <!--#var searchkey--> =     

when you say sqlvar Zope wants to wrap it so it's safe.  Now, another
point entirely is the wisdom of doing this in this exact way.  You don't
want Zope to escape the fieldname (becuause it breaks).  However, I
could easily hijack the form and submit
http://foo.com/SQLMethod?searchkey=;DROP+TABLE+Users  which would, on
some databases, cause a Bad Thing.	

I might suggest adding some logic in the SQL Method to ensure that the
searchkey you are getting is one you intended.
(By example):

<!--#if searchkey in ['firstname','lastname','favorite_color']-->

    SELECT * FROM Users WHERE <!--#var searchkey--> =   
    <!--#sqlvar searchvalue type=string-->
    ORDER BY <!--#var searchkey--> 

<!--#else-->

<!--#comment-->
If the user didn't input something nice, give them generic results.  I
might even log this somewhere as hijacking a form is a decidedly
antisocial thing to do...
<!--#/comment-->

    SELECT * FROM Users ORDER BY lastname

<!--#/if-->