[Zope-DB] How can I dynamically generate a Z Search Interface?

[Luis Gregorio Muniz Rodriguez]

On Fri, Aug 30, 2002 at 12:00:03PM -0400, zope-db-request@zope.org wrote:
> From: "Tena Sakai" <tena.sakai.b@bayer.com>
> Date: Thu, 29 Aug 2002 19:13:53 -0700
> Subject: [Zope-DB] How can I dynamically generate a Z Search Interface?
> 
> So far what I have been doing is to generate Z Search Interface
> statically [...] But now I want to do this dynamically.  Namely, I have gotten
> a dtml method that creates a Z SQL method.  The output might
> look something like:
>       select  emp_id, phone, zip
>         from  mytable
I have found myself the same problem many times.  As always, there is
more than one way to do it:

a) If you only need to "select", then simply put:
	select * from mytable

   And, in the DMTL or ZPT code that iterates through the results of 
   the search, choose what fields to show.  It should be easier and
   safer than the next method.  And if you have been using a form
   to select which data to retrieve, you have half the work done.


b) You are not forced to use <dmtl-sqlvar> inside Z SQL Methods, you can
   use plain <dtml-var>, and this allows you to construct fully dynamic
   methods.  [It took me a couple months to realize about this :-(  ]

   For instance, I'm just now using this generic Z SQL Method, called
   "list":

   <params>table research name teacher colab login</params>
   select * from <dtml-var table url_quote_plus>
   <dtml-sqlgroup where>
     <dtml-sqltest research type=nb optional>
   </dtml-sqlgroup>
   <dtml-sqlgroup where>
     <dtml-sqltest login type=nb optional>
   </dtml-sqlgroup>

   Then, from within DTML or ZPT, you can use:
   	* list( table='teacher')
   	* list( table='research')
   	* list( table='teacher', login='ast')
   	* list( table='research', research='fund 34')
   
   Just now I think that the method is safe.  Note that if you are going 
   to allow users to enter their own search data, then someone could try 
   to feed something like this:
   	
	"teacher;select * from secret_table;drop *"

   But, because of the "url_quote_plus", it expands  to:

   	"select * from teacher%3bselect * from secret_table%3bdrop"

   and that causes a syntax error but poses no security risks.  
   I have tried to crack the method using other various tricks and I
   have been unable to do so.  So, I suppose the method is safe.
   But one never knows; if someone find this method useful, please
   check it by yourself and please warn me if you see any problem.


   The main problem I had is that sometimes I was backfired by
   acquisition ;)  Suppose that you want to list all entries from
   table "teachers". You could write something like:

        <dtml-in expr="list ( table='teacher')">

   But what if your method has already a property called "research"?
   Then, you are not aware that the previous search really is being
   invoked as:
   	<dtml-in expr="list ( table='teacher', research='xyz')">
   
   As Murphy's said, "teacher" has no such field "research", and you
   get a pretty unexpected (and difficult to trace) error.  It forces
   you to write "<dtml-in expr="list (table='teacher', research=''">.

   The other problem is that it is not easy to use the "ORDER BY"
   clause.  You can instead user <dtml-in ... sort="...">.  
   (Anyone knows how to do the same with ZPT???)

   Or, if the attributes you are going to supply are muthual exclusive,
   then you can sort _always_:

	   <dtml-sqlgroup where>
	     <dtml-sqltest research type=nb optional> ORDER BY (research)
	   </dtml-sqlgroup>
	   <dtml-sqlgroup where>
	     <dtml-sqltest login type=nb optional> ORDER BY (login)
	   </dtml-sqlgroup>
   

Finally, I also agree that it is best to have a couple small, fixed 
Z SQL methods than using a more complex solution, like the generic
"list" method.  However, as the number of small methods increase I've 
found this dynamic solution more elegant and less confusing.

HTH,
--
	- kreator@lmunix.net