[Zope-DB] How can I dynamically generate a Z Search Interface?
Luis Gregorio Muniz Rodriguez
Mon, 9 Sep 2002 21:45:30 +0200
On Fri, Aug 30, 2002 at 12:00:03PM -0400, email@example.com wrote:
> From: "Tena Sakai" <firstname.lastname@example.org>
> 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
<params>table research name teacher colab login</params>
select * from <dtml-var table url_quote_plus>
<dtml-sqltest research type=nb optional>
<dtml-sqltest login type=nb optional>
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
<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-sqltest research type=nb optional> ORDER BY (research)
<dtml-sqltest login type=nb optional> ORDER BY (login)
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.