Re: [Zope-DB] Using dtml-vars in ZSQL methods?
I tried to invoke the query method of my adapter (which is named dhatabase) with this code: p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) t = delete from person where person_id in (%s); % s context.dhatabase.query(query_string=t) ...and got this error: AttributeError: query (full traceback at end of message). What's wrong with my method call? Try: context.dhatabase().query(query_string=t) Assuming that 'dhatabase' is your DatabaseConnection object. I also have a broader question: When one is composing dynamic SQL in Python scripts, what are the pros and cons of executing them by going directly to the database adapter (as suggested above) vs passing it in as the sole argument of an empty ZSQL method? By empty ZQL method I mean something like: I think in this case there is no difference. Both ways may be harmful unless you're sure that it is not possible to do sql injection. -- Maciej Wisniowski ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] Using dtml-vars in ZSQL methods?
P.S. I know that dtml-var... rather than dtml-sqlvar... is an unorthodox and apparently undocumented construct to use within a ZSQL definition. But I need it because dtml-sqlvar... malformats the string. What do you mean by 'malformats the string'? Have you tried dtml-sqltest clause? It may be used to do 'where colname in ' statements. -- Maciej Wisniowski ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
I also have a broader question: When one is composing dynamic SQL in Python scripts, what are the pros and cons of executing them by going directly to the database adapter (as suggested above) vs passing it in as the sole argument of an empty ZSQL method? By empty ZQL method I mean something like: There are no pros and cons. Only cons. There is a good argument to be made that ZSQL methods are entirely a bad idea -- that only prepared statements should be supported, as it is far harder to break security. But, every use of dtml-var ... in a ZSQL method requires that the argument be examined and correctly SQL-Quoted. For example, what is to keep someone from entering 13225, 12337; delete from person in your web form? Further, you have greatly complicated verification and maintenance. It no longer is enough to test the ZSQL method to be sure that it operates as expected. You have to examine every call-point to determine what the SQL method is doing. And you have to examine every argument to be sure that it has been quoted properly and you aren't open to SQL injection. Charlie has already given the best answer -- use a really simple method like: delete from person where person_id = dtml-sqlvar foo type=int, and call it once for each person you have to delete. SQL injection is impossible, since foo is verified to be an int just before it is used. Now, there are times where dtml-var ... is unavoidable; IN clauses and LIKE clauses are the principal ones. In either case, you really need to verify the arguments. At the bare minimum, look at dtml-var ... sql_quote. jim penny ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
-Original Message- From: Maciej Wisniowski [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 1:34 AM To: Ken Winter Cc: 'Zope-DB List' Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods? I tried to invoke the query method of my adapter (which is named dhatabase) with this code: p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) t = delete from person where person_id in (%s); % s context.dhatabase.query(query_string=t) ...and got this error: AttributeError: query (full traceback at end of message). What's wrong with my method call? Try: context.dhatabase().query(query_string=t) Assuming that 'dhatabase' is your DatabaseConnection object. Thanks, Maciej. That works fine! ~ Ken ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
Thanks Charlie Jim ~ SQL injection is a new one on me, and I'm glad to learn about it now (painlessly) rather than later (painfully). ~ Ken -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 10:03 AM To: Ken Winter Cc: 'Zope-DB List' Subject: RE: [Zope-DB] Using dtml-vars in ZSQL methods? I also have a broader question: When one is composing dynamic SQL in Python scripts, what are the pros and cons of executing them by going directly to the database adapter (as suggested above) vs passing it in as the sole argument of an empty ZSQL method? By empty ZQL method I mean something like: There are no pros and cons. Only cons. There is a good argument to be made that ZSQL methods are entirely a bad idea -- that only prepared statements should be supported, as it is far harder to break security. But, every use of dtml-var ... in a ZSQL method requires that the argument be examined and correctly SQL-Quoted. For example, what is to keep someone from entering 13225, 12337; delete from person in your web form? Further, you have greatly complicated verification and maintenance. It no longer is enough to test the ZSQL method to be sure that it operates as expected. You have to examine every call-point to determine what the SQL method is doing. And you have to examine every argument to be sure that it has been quoted properly and you aren't open to SQL injection. Charlie has already given the best answer -- use a really simple method like: delete from person where person_id = dtml-sqlvar foo type=int, and call it once for each person you have to delete. SQL injection is impossible, since foo is verified to be an int just before it is used. Now, there are times where dtml-var ... is unavoidable; IN clauses and LIKE clauses are the principal ones. In either case, you really need to verify the arguments. At the bare minimum, look at dtml-var ... sql_quote. jim penny ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
-Original Message- From: Charlie Clark [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 6:24 PM To: Ken Winter; 'Zope-DB List' Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods? ... FWIW you might want to call the query or execute methods directly on the DA connection object if they are supported. ZPsycopgDA is my database adapter. I haven't found any documentation of ZPsycopgDA's methods except its code. In the code, I found a class DB with a query method (which in turn uses execute and fetchall methods, whose definitions I can't locate). I tried to invoke the query method of my adapter (which is named dhatabase) with this code: p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) t = delete from person where person_id in (%s); % s context.dhatabase.query(query_string=t) ...and got this error: AttributeError: query (full traceback at end of message). What's wrong with my method call? I also have a broader question: When one is composing dynamic SQL in Python scripts, what are the pros and cons of executing them by going directly to the database adapter (as suggested above) vs passing it in as the sole argument of an empty ZSQL method? By empty ZQL method I mean something like: dtml-comment title: Query whose whole body is passed in as an argument connection_id: dhatabase arguments: foo /dtml-comment dtml-var foo ~ Thanks ~ Ken P.S. Full details of error: Time 2007-06-17 13:59 User Name admin (admin) Request URL http://localhost/DAgroups/portal_factory/People/people.2007-06-17.6691253086 /people_edit Exception Type AttributeError Exception Value query Traceback (innermost last): * Module ZPublisher.Publish, line 115, in publish * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFPlone.FactoryTool, line 369, in __call__ * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFFormController.FSControllerPageTemplate, line 90, in __call__ * Module Products.CMFFormController.BaseControllerPageTemplate, line 28, in _call * Module Products.CMFFormController.ControllerBase, line 232, in getNext __traceback_info__: ['id = people_edit', 'status = success', 'button=delete', 'errors={}', 'context=People at people.2007-06-17.6691253086', kwargs={'portal_status_message': 'People allegedly deleted: 69394, 77084'}, 'next_action=None', ''] * Module Products.CMFFormController.Actions.TraverseTo, line 38, in __call__ * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFFormController.FSControllerPythonScript, line 104, in __call__ * Module Products.CMFFormController.Script, line 145, in __call__ * Module Products.CMFCore.FSPythonScript, line 108, in __call__ * Module Shared.DC.Scripts.Bindings, line 311, in __call__ * Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec * Module Products.CMFCore.FSPythonScript, line 164, in _exec * Module None, line 6, in people_delete_control FSControllerPythonScript at /DAgroups/people_delete_control used for /DAgroups/portal_factory/People/people.2007-06-17.6691253086 Line 6 AttributeError: query REQUEST form delete_this [69394, 77084] form.button.delete 'Delete Selected People' cookies tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt' __ac'YWRtaW46cHAyMTA3' lazy items SESSION bound method SessionDataManager.getSessionData of SessionDataManager at /session_data_manager other tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt' __factory__info__ {'stack': ['People', 'people.2007-06-17.6691253086', 'people_edit'], 'People': TempFolder at /DAgroups/portal_factory/People} URL5'http://localhost' URL4'http://localhost/DAgroups' __ac'YWRtaW46cHAyMTA3' URL0 'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308 6/people_edit' URL3'http://localhost/DAgroups/portal_factory' URL2'http://localhost/DAgroups/portal_factory/People' AUTHENTICATION_PATH '' AUTHENTICATED_USER PropertiedUser 'admin' SERVER_URL 'http://localhost' delete_this [69394, 77084] form.button.delete 'Delete Selected People' ACTUAL_URL 'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308 6/people_edit' portal_status_message 'People allegedly deleted: 69394, 77084' URL 'http://localhost/DAgroups/portal_factory/People/people.2007-06-17.669125308 6/people_edit' PUBLISHED FactoryTool at /DAgroups/portal_factory controller_state Products.CMFFormController.ControllerState.ControllerState object at 0x0681DB10 TraversalRequestNameStack [] BASE0 'http://localhost' BASE1 'http://localhost/DAgroups' BASE2 'http://localhost/DAgroups/portal_factory' BASE3
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
Ken Winter wrote at 2007-6-16 16:03 -0400: ... Exception Type KeyError Exception Value 'id_list' Traceback (innermost last): ... * Module Products.CMFCore.FSPythonScript, line 164, in _exec * Module None, line 4, in people_delete_control FSControllerPythonScript at /DAgroups/people_delete_control used for /DAgroups/portal_factory/People/people.2007-06-16.3625931808 Line 4 * Module Shared.DC.ZRDB.DA, line 481, in __call__ FSZSQLMethod at /DAgroups/people_delete_by_id used for /DAgroups/portal_factory/People/people.2007-06-16.3625931808 * Module DocumentTemplate.DT_String, line 476, in __call__ KeyError: 'id_list' The traceback tells you that it comes from the FSControllerPythonScript people_delete_control and not from a DMTL object (as you have shown us in a previous message). Almost surely, you did not call the Z SQL Method with an id_list keyword parameter there. -- Dieter ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
Jaroslav ~ I'm pretty sure that 'delete_this' is a list (of integers), because the lines p = context.REQUEST.get('delete_this') s = ', '.join([str(x) for x in p]) process it correctly into the string 's'. In other words, I think it is the same as if p had been assigned a list in this way: p = [9765, 10058, 11333] s = ', '.join([str(x) for x in p]) In either case, s ends up as the string 9765, 10058, 11333. That's what gets passed to the ZSQL method (below), and that's where the trouble seems to be. I will put in another message to this thread documenting some further experiments I have done. ~ Thanks ~ Ken -Original Message- From: Jaroslav Lukesh [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 1:41 AM To: Ken Winter; 'Zope-DB List' Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods? It is just because 'delete_this' is a string, not the list, or vice versa? - Original Message - From: Ken Winter [EMAIL PROTECTED] To: 'Zope-DB List' zope-db@zope.org Sent: Saturday, June 16, 2007 5:15 AM Subject: [Zope-DB] Using dtml-vars in ZSQL methods? Here's the definition of a ZSQL method, people_delete_by_id.zsql: dtml-comment title: Method to delete People by ids given in a comma-separated list connection_id: my_database arguments: id_list /dtml-comment delete from person where person_id in (dtml-var id_list) Here's the puzzle: When I define and test this ZSQL method through the ZMI, passing it a string such as 9765, 10058, 11333, it indeed deletes the rows with those values on person_id. BUT when I invoke the same ZSQL method from a Python script with the following code: p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) context.people_delete_by_id(id_list=s) ...and with a REQUEST variable 'delete_this' that comes from this snippet of a form in a page template: form method=post name=form2 tal:attributes=action string:${here/absolute_url}/${template/id}; input type=hidden name=form.submitted value=1 / p class=error_message tal:define=err errors/n|nothing tal:condition=errb tal:content=err //p table tr tal:repeat=person options/dataa name=id id=id tal:attributes=name person/person_id/a td tal:condition=not:options/selectall | nothing input type=checkbox name=delete_this:list:int value= tal:attributes=value person/person_id/ /td ... /tr /table ... /form ...and with the same three person_ids (9765, 10058, 11333) the 4th line of the Python script evokes an error: KeyError: 'id_list' The core idea is that the string id_list is passed into the ZSQL method, where it is spliced into the delete...where clause via the dtml-var id_list) to provide the list of person_ids to delete. Here's the question: WHY? ~ TIA ~ Ken P.S. I know that dtml-var... rather than dtml-sqlvar... is an unorthodox and apparently undocumented construct to use within a ZSQL definition. But I need it because dtml-sqlvar... malformats the string. And if doing this trick is a no-no, how come it works in the ZMI test but not when called from Python? More importantly, how can I get it to work from Python? ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
First, Jaroslav is right. One time you're passing a string and next your passing a list. Make your choice how the API should looks like. Second..it would be *really* helpful to see the full traceback instead of just *Keyerror*. It's somewhat boring to ask every time for it :-) -aj --On 16. Juni 2007 15:19:10 -0400 Ken Winter [EMAIL PROTECTED] wrote: Jaroslav ~ I'm pretty sure that 'delete_this' is a list (of integers), because the lines p = context.REQUEST.get('delete_this') s = ', '.join([str(x) for x in p]) process it correctly into the string 's'. In other words, I think it is the same as if p had been assigned a list in this way: p = [9765, 10058, 11333] s = ', '.join([str(x) for x in p]) In either case, s ends up as the string 9765, 10058, 11333. That's what gets passed to the ZSQL method (below), and that's where the trouble seems to be. I will put in another message to this thread documenting some further experiments I have done. ~ Thanks ~ Ken -Original Message- From: Jaroslav Lukesh [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 1:41 AM To: Ken Winter; 'Zope-DB List' Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods? It is just because 'delete_this' is a string, not the list, or vice versa? - Original Message - From: Ken Winter [EMAIL PROTECTED] To: 'Zope-DB List' zope-db@zope.org Sent: Saturday, June 16, 2007 5:15 AM Subject: [Zope-DB] Using dtml-vars in ZSQL methods? Here's the definition of a ZSQL method, people_delete_by_id.zsql: dtml-comment title: Method to delete People by ids given in a comma-separated list connection_id: my_database arguments: id_list /dtml-comment delete from person where person_id in (dtml-var id_list) Here's the puzzle: When I define and test this ZSQL method through the ZMI, passing it a string such as 9765, 10058, 11333, it indeed deletes the rows with those values on person_id. BUT when I invoke the same ZSQL method from a Python script with the following code: p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) context.people_delete_by_id(id_list=s) ...and with a REQUEST variable 'delete_this' that comes from this snippet of a form in a page template: form method=post name=form2 tal:attributes=action string:${here/absolute_url}/${template/id}; input type=hidden name=form.submitted value=1 / p class=error_message tal:define=err errors/n|nothing tal:condition=errb tal:content=err //p table tr tal:repeat=person options/dataa name=id id=id tal:attributes=name person/person_id/a td tal:condition=not:options/selectall | nothing input type=checkbox name=delete_this:list:int value= tal:attributes=value person/person_id/ /td ... /tr /table ... /form ...and with the same three person_ids (9765, 10058, 11333) the 4th line of the Python script evokes an error: KeyError: 'id_list' The core idea is that the string id_list is passed into the ZSQL method, where it is spliced into the delete...where clause via the dtml-var id_list) to provide the list of person_ids to delete. Here's the question: WHY? ~ TIA ~ Ken P.S. I know that dtml-var... rather than dtml-sqlvar... is an unorthodox and apparently undocumented construct to use within a ZSQL definition. But I need it because dtml-sqlvar... malformats the string. And if doing this trick is a no-no, how come it works in the ZMI test but not when called from Python? More importantly, how can I get it to work from Python? ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db -- ZOPYX Ltd. Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376 Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535 Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK E-Publishing, Python, Zope Plone development, Consulting pgpX6f3WaiFKL.pgp Description: PGP signature ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
2.9.6-final, python 2.4.3, win32) ZServer/1.1 Plone/2.5.2' SCRIPT_NAME '' REQUEST_METHOD 'POST' HTTP_KEEP_ALIVE '300' SERVER_PROTOCOL 'HTTP/1.1' channel.creation_time 1182022394 CONNECTION_TYPE 'keep-alive' HTTP_ACCEPT_CHARSET 'ISO-8859-1,utf-8;q=0.7,*;q=0.7' HTTP_USER_AGENT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.1) Gecko/20061204 Firefox/2.0.0.1' HTTP_REFERER 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' SERVER_NAME 'KenIBM' REMOTE_ADDR '127.0.0.1' PATH_TRANSLATED '\\DAgroups\\portal_factory\\People\\people.2007-06-16.3625931808\\people_ed it' SERVER_PORT '80' CONTENT_LENGTH '89' HTTP_HOST 'localhost' HTTP_ACCEPT 'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q =0.8,image/png,*/*;q=0.5' GATEWAY_INTERFACE 'CGI/1.1' HTTP_ACCEPT_LANGUAGE'en-us,en;q=0.5' CONTENT_TYPE'application/x-www-form-urlencoded' HTTP_ACCEPT_ENCODING'gzip,deflate' PATH_INFO '/DAgroups/portal_factory/People/people.2007-06-16.3625931808/people_edit' Let me add that I get the same error (down to all the details) if I simply put a single string or integer constant into the call to the ZSQL Method - that is, if instead of this context.people_delete_by_id(id_list=s) the call looks like this: context.people_delete_by_id(id_list=str(68775)) or context.people_delete_by_id(id_list=68775) That's why I think the problem has nothing to do with the prior processing of the list into a string. ~ Thanks ~ Ken -Original Message- From: Andreas Jung [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 3:27 PM To: Ken Winter; 'Zope-DB List' Subject: RE: [Zope-DB] Using dtml-vars in ZSQL methods? ... Second..it would be *really* helpful to see the full traceback instead of just *Keyerror*. It's somewhat boring to ask every time for it :-) --On 16. Juni 2007 15:19:10 -0400 Ken Winter [EMAIL PROTECTED] wrote: Jaroslav ~ I'm pretty sure that 'delete_this' is a list (of integers), because the lines p = context.REQUEST.get('delete_this') s = ', '.join([str(x) for x in p]) process it correctly into the string 's'. In other words, I think it is the same as if p had been assigned a list in this way: p = [9765, 10058, 11333] s = ', '.join([str(x) for x in p]) In either case, s ends up as the string 9765, 10058, 11333. That's what gets passed to the ZSQL method (below), and that's where the trouble seems to be. I will put in another message to this thread documenting some further experiments I have done. ~ Thanks ~ Ken -Original Message- From: Jaroslav Lukesh [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 1:41 AM To: Ken Winter; 'Zope-DB List' Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods? It is just because 'delete_this' is a string, not the list, or vice versa? - Original Message - From: Ken Winter [EMAIL PROTECTED] To: 'Zope-DB List' zope-db@zope.org Sent: Saturday, June 16, 2007 5:15 AM Subject: [Zope-DB] Using dtml-vars in ZSQL methods? Here's the definition of a ZSQL method, people_delete_by_id.zsql: dtml-comment title: Method to delete People by ids given in a comma-separated list connection_id: my_database arguments: id_list /dtml-comment delete from person where person_id in (dtml-var id_list) Here's the puzzle: When I define and test this ZSQL method through the ZMI, passing it a string such as 9765, 10058, 11333, it indeed deletes the rows with those values on person_id. BUT when I invoke the same ZSQL method from a Python script with the following code: p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) context.people_delete_by_id(id_list=s) ...and with a REQUEST variable 'delete_this' that comes from this snippet of a form in a page template: form method=post name=form2 tal:attributes=action string:${here/absolute_url}/${template/id}; input type=hidden name=form.submitted value=1 / p class=error_message tal:define=err errors/n|nothing tal:condition=errb tal:content=err //p table tr tal:repeat=person options/dataa name=id id=id tal:attributes=name person/person_id/a td tal:condition=not:options/selectall | nothing input type=checkbox name=delete_this:list:int value= tal:attributes=value person/person_id/ /td ... /tr /table ... /form ...and with the same three person_ids (9765, 10058, 11333) the 4th line of the Python script evokes an error: KeyError: 'id_list' The core idea is that the string id_list is passed into the ZSQL method, where it is spliced into the delete...where clause via the dtml-var id_list) to provide the list of person_ids to delete. Here's the question: WHY? ~ TIA ~ Ken P.S. I know that dtml-var... rather than dtml-sqlvar
RE: [Zope-DB] Using dtml-vars in ZSQL methods?
Charlie ~ I tried several experiments based on your suggestion. See results inserted below. ~ Thanks, Ken -Original Message- From: Charlie Clark [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 3:54 PM To: Ken Winter; 'Zope-DB List' Subject: Re: [Zope-DB] Using dtml-vars in ZSQL methods? Am 16.06.2007, 21:19 Uhr, schrieb Ken Winter [EMAIL PROTECTED]: p = [9765, 10058, 11333] s = ', '.join([str(x) for x in p]) s gets passed to the ZSQL method, and that's where the trouble seems to be. I find this code a bit convoluted and somewhat dangerous if you are passing data from a web form. What's wrong with repeatedly calling a delete_person() method that just accepts a single id as a dtml-sqlvar? This stuff is coming from a web form so it probably won't be a huge list so the speed won't matter. EXPERIMENT #1: I tried this out by rewriting this part of the .cpy script to: p = context.REQUEST.get('delete_this') for id in p: context.person_delete_by_id(person_id=id) where person_delete_by_id is defined as follows (note that again I'm using a dtml-var... rather than a dtml-sqlvar here): dtml-comment title: Method to delete one Person by id connection_id: dhatabase arguments: person_id /dtml-comment delete from person where person_id = dtml-var person_id When I tried to delete a couple of People using this I got the error message: ProgrammingError: column none does not exist Here are the details of the error: Request URL http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808 /people_edit Exception Type ProgrammingError Exception Value column none does not exist Traceback (innermost last): * Module ZPublisher.Publish, line 115, in publish * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFPlone.FactoryTool, line 369, in __call__ * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFFormController.FSControllerPageTemplate, line 90, in __call__ * Module Products.CMFFormController.BaseControllerPageTemplate, line 28, in _call * Module Products.CMFFormController.ControllerBase, line 232, in getNext __traceback_info__: ['id = people_edit', 'status = success', 'button=delete', 'errors={}', 'context=People at people.2007-06-16.3625931808', kwargs={'portal_status_message': 'People allegedly deleted: 68775, 69390'}, 'next_action=None', ''] * Module Products.CMFFormController.Actions.TraverseTo, line 38, in __call__ * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFFormController.FSControllerPythonScript, line 104, in __call__ * Module Products.CMFFormController.Script, line 145, in __call__ * Module Products.CMFCore.FSPythonScript, line 108, in __call__ * Module Shared.DC.Scripts.Bindings, line 311, in __call__ * Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec * Module Products.CMFCore.FSPythonScript, line 164, in _exec * Module None, line 4, in people_delete_control FSControllerPythonScript at /DAgroups/people_delete_control used for /DAgroups/portal_factory/People/people.2007-06-16.3625931808 Line 4 * Module Shared.DC.ZRDB.DA, line 495, in __call__ FSZSQLMethod at /DAgroups/person_delete_by_id used for /DAgroups/portal_factory/People/people.2007-06-16.3625931808 * Module Products.ZPsycopgDA.db, line 204, in query ProgrammingError: column none does not exist REQUEST form delete_this [68775, 69390] form.button.delete 'Delete Selected People' cookies tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt' __ac'YWRtaW46cHAyMTA3' lazy items SESSION bound method SessionDataManager.getSessionData of SessionDataManager at /session_data_manager other tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt' __factory__info__ {'stack': ['People', 'people.2007-06-16.3625931808', 'people_edit'], 'People': TempFolder at /DAgroups/portal_factory/People} URL5'http://localhost' URL4'http://localhost/DAgroups' __ac'YWRtaW46cHAyMTA3' URL0 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' URL3'http://localhost/DAgroups/portal_factory' URL2'http://localhost/DAgroups/portal_factory/People' AUTHENTICATION_PATH '' AUTHENTICATED_USER PropertiedUser 'admin' SERVER_URL 'http://localhost' delete_this [68775, 69390] form.button.delete 'Delete Selected People' ACTUAL_URL 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' portal_status_message 'People allegedly deleted: 68775, 69390' URL 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' PUBLISHED FactoryTool at /DAgroups
Re: [Zope-DB] Using dtml-vars in ZSQL methods?
Am 17.06.2007, 00:14 Uhr, schrieb Ken Winter [EMAIL PROTECTED]: dtml-comment title: Method to delete one Person by id connection_id: dhatabase arguments: person_id /dtml-comment delete from person where person_id = dtml-var person_id When I tried to delete a couple of People using this I got the error message: ProgrammingError: column none does not exist You should be using dtml-sqlvar person_id type=int here but in any case no argument is being passed in: the error is coming from the DTML rendering of a missing key argument. I don't know about your file system product for this as all my ZSQL's live in the ZODB but I assume all the meta-data is contained in the dtml-comment section. Any chance you can test your stuff on a standard Zope (non-filesystem) setup? I think that's where the problem is. FWIW you might want to call the query or execute methods directly on the DA connection object if they are supported. Charlie -- Charlie Clark eGenix.com Professional Python Services directly from the Source Python/Zope Consulting and Support ...http://www.egenix.com/ mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ mxODBC, mxDateTime, mxTextTools ...http://python.egenix.com/ Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db