Hi, I have this query: "delete from database.table where pri in tuple_pri", where database,table,pri and tuple_pri are parameters which are passed from python script to a zsql method. I've tried to implement zsql method like this: delete from <dtml-var database>.<dtml-var table> where <dtml-sqltest <dtml-var pri> op=eq type=nb multiple>, but it fails... Could someone help me? TNX.
also sprach Eugen Nedelcu <eugen@sifolt.ro> [2003.01.30.1242 +0100]:
delete from <dtml-var database>.<dtml-var table> where <dtml-sqltest <dtml-var pri> op=eq type=nb multiple>,
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. -- martin; (greetings from the heart of the sun.) \____ echo mailto: !#^."<*>"|tr "<*> mailto:" net@madduck NOTE: The pgp.net keyservers and their mirrors are broken! Get my key here: http://people.debian.org/~madduck/gpg/330c4a75.asc "to get back my youth i would do anything in the world, except take exercise, get up early, or be respectable." -- oscar wilde
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. -- ICQ: 165549179
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.
On Thu, Jan 30, 2003 at 12:21:55PM +0000, Ben Avery wrote:
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.
I am aware of that.
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.
My application is one like Webmin or PHPMyAdmin, so it must be generic! -- ICQ: 165549179
then to do it safely you would have to either to modify the source of sqlvar ([zope_base]/lib/python/Shared/DC/ZRDB/sqlvar.py) to give you a non-quoted type to use for table etc names [slight case of overkill], or call a generic python method with your values, which can do any checks for safety you need, e.g. make sure there are no ';'s, then that script calls your zsql method with the checked parameters. But make sure your zsql method can only be called by the python script - give it a local contextual role, and only let this role call the zsql method. Eugen Nedelcu wrote:
On Thu, Jan 30, 2003 at 12:21:55PM +0000, Ben Avery wrote:
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.
I am aware of that.
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.
My application is one like Webmin or PHPMyAdmin, so it must be generic!
also sprach Eugen Nedelcu <eugen@sifolt.ro> [2003.01.30.1304 +0100]:
I don't think you understand my problem...
I don't think you understand my analysis. The difference is that you want: delete from test.test where id in (1,3,7,9) and the <dtml-vars> render: delete from test.'test' where 'id' in (1,3,7,9) I don't think this will work. -- martin; (greetings from the heart of the sun.) \____ echo mailto: !#^."<*>"|tr "<*> mailto:" net@madduck NOTE: The pgp.net keyservers and their mirrors are broken! Get my key here: http://people.debian.org/~madduck/gpg/330c4a75.asc "love is a grave mental disease." -- platon
Hello This is an example of exUserFolder, it should also work for your problem: Arguments: table=adressen usernameColumn=dbusername username:string SQL: DELETE FROM <dtml-var table> where <dtml-var usernameColumn>=<dtml-sqlvar username type=string> --- Mit freundlichen Grüssen - Meilleures salutations - Kind regards - Cordiali saluti Dieter Fischer Grid IT GmbH Im Niederholzboden 24 CH-4125 Riehen Tel: +41 61 601 29 62 mailto:dieter.fischer@grid-it.ch http://:www.grid-it.ch
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Eugen Nedelcu Sent: Thursday, January 30, 2003 1:05 PM To: martin f krafft Cc: zope@zope.org Subject: Re: [Zope] Re: dynamic sql query
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.
-- ICQ: 165549179
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Thu, Jan 30, 2003 at 01:33:15PM +0100, Dieter Fischer wrote:
Hello
This is an example of exUserFolder, it should also work for your problem:
Arguments:
table=adressen usernameColumn=dbusername username:string
SQL:
DELETE FROM <dtml-var table> where <dtml-var usernameColumn>=<dtml-sqlvar username type=string>
Better would be, in that it still prevent s SQL injection: DELETE FROM <dtml-var table sql_quote> where <dtml-var usernameColumn sql_quote>=<dtml-sqlvar username type=string> But, I agree with everyone else in this thread. This is a horrid design; suitable for use only inside a firewall with a highly trusted audience. It is far better to write many controllable and auditable SQL methods than a few generalized ones that cannot be trusted. And I do not really buy the "it must be general" idea. That would be true only if you are allowing end users to create new tables, and then you would not know enough of the structure to present the results of a select, anyway. Jim Penny
participants (5)
-
Ben Avery -
Dieter Fischer -
Eugen Nedelcu -
Jim Penny -
martin f krafft