On Mon, Mar 04, 2002 at 03:32:24PM -0500, Thomas B. Passin wrote:
[hans]
well, i could instantiate a bunch of zsql methods, each with its own query and then call them from my products methods. but how could i obviate the need to have these zsql methods and query directly? Or is it just not worth it? thx hans
Well, the extreme and simplest case is to have one zsql method that takes a single parameter called, say, "sql":
&dtml-sql;
The sql parameter would contain the entire query, that is, an entire select statement or insert statement or whatever.
This is easy and flexible, but has no security at all. If someone posted you a DELETE statement, you might end up very unhappy, for example. Still it is an easy approach to use when you are tuning up a query, as long as you don't let it loose outside your system.
The next level up from this is to have your page, or a method called by the page, construct a query statement using values from a form, then hand the query to your friendly general zsql method. This is quite a bit more more secure, but you still need to make sure it's going to be safe.
A next step up in security is to make sure that any query that can be constructed and passed to the zsql method can only access non-updateable views.
There's a tradeoff between having highly specialized zsql methods, which could be the most secure but can lead to you having to maintain many individual methods, and the completely general approach I've outlined above where you only need one method but have to manage the security issues.
These parameterized ZSQL methods are generally a bad idea. They occur to everyone at some point early in their Zope career. I know they did to me, I wrote a howto on it. But, they are almost always a bad idea. Mind you, not only is there a security tradeoff, but there are also managability, testability, and correctness tradeoffs. If you use concrete zsql methods, then you have only to examine the method to determine what arguments are needed, and often you can directly read what is being returned, modified, or deleted. That is, understanding the database interactions of the code in a folder is often as simple as looking at the ZSQL methods in the folder. If you use one of the "statement is a parameter" forms, then you have to read (at least) all the code in the folder, everything, every dtml method, pythonscript, external method, etc. to determine the interactions with the database. Worse, you learn about mistakes too late. With concrete ZSQL methods you can use the Test Tab to make sure that the method does what it is supposed to do. You are reasonably confident that the method does what it should, no more and no less, before you ever let it loose. With "statement is a parameter" code, you don't learn that there is a problem until run time. My current belief is that you should never use any ZSQL method with anything more complicated than a sqlgroup in it, and that you want to be sparing with sqlgroups. One other thing -- keep your ZSQL methods close to the folder they are accessed from. Only very common queries should be closer to the root. I suspect that in many, if not most, cases, the set of databases that you need to work with in a single folder is pretty small. I think that this is due to programmer forethought, that is, that you naturally design your databases and your folder structure to be compatible. Now, if you are operating with only one, two, or three tables per folder, then you probably need no more than a few insert, delete, select, or updates per table. This translates to four to ten ZSQL methods per table used. If you have only three tables you are working with in a paritcular folder, this is just not all that onerous. Jim Penny
Cheers,
Tom P