[Jim Penny]
On Mon, Mar 04, 2002 at 03:32:24PM -0500, Thomas B. Passin wrote:
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.
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.
I find I spend most of my effort developing queries and browsing tables as I develop code. I like to use zsql methods that make this easy and flexible. So I tend to write my own SQL statements by hand, even in a textarea. You can still test them using the test tab. When you go to a production query, I go along with everything you said. Same for using Cold Fusion. Cheers, Tom P