[Zope] Re: dynamic sql query

[Ben Avery]

Eugen,

you can't easily do what you're trying to, safely. the <dtml-sqlvar ...> 
was created so that the variable substitution methods couldn't be 
maliciously used by people passing in a paramter of e.g. "3;drop 
database mydb", which would terminate the first sql statement, then make 
a new arbitrary one.

personally, I have a created a method for each table, e.g.
delete from employee where <dtml-sqltest emp_id multiple>

It is a pain to do this, but it's the only way without opening up your 
system to major risks. I believe there are products which will generate 
a batch of zsql methods for you - someone help point the way here please?
Or you could write a python script which does its own checking of 
parameters sent for safety, and against "safe" lists of tables, etc, and 
which then calls a zsql method with "unsafe" dtml-var substitutions.
Or if your really sure it couldn't be called maliciously, just go for it!
delete from <dtml-var database>.<dtml-var table>
where <dtml-var id_col> in
( <dtml-in id_list>
    <dtml-var sequence-item>,
   </dtml-in> )

Ben

Eugen Nedelcu wrote:
> On Thu, Jan 30, 2003 at 12:51:39PM +0100, martin f krafft wrote:
> 
>>first, shouldn't you be using dmtl-sqlvar? and second, i think the
>>problem is that dtml-var returns a string, which is single-quoted.
>>you'll need to fine a type="" argument for dtml-sqlvar that renders
>>unquoted characters.
> 
> 
> I don't think you understand my problem...
> I need to pass this arguments to my zsql method:
> 
> 1. database - string - the name of the database
> 2. table - string - the name of the table
> 3. pri - string - the name of the primary_key column
> 4. tuple_pri - tuple - a tuple with primary_keys of the rows
> 	which will be deleted.
> 
> In sql the query would be (i.e): 
> 
> delete from test.test where id in (1,3,7,9)
> 
> A zsql method like this:
> 
> delete from <dtml-var database>.<dtml-var table> where
> <dtml-sqltest id type=nb op=eq multiple>
> 
> will work if the name of the primary_key is id, but I
> want to pass that column name myself.
> 
> Hope that this make things more clear.
> Best regards.
>