Re: [Zope-DB] Using dtml-vars in ZSQL methods?

2007-06-18 Thread Maciej Wisniowski

 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?

2007-06-18 Thread Maciej Wisniowski

 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?

2007-06-18 Thread JPenny
 
 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?

2007-06-18 Thread Ken Winter
 -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?

2007-06-18 Thread Ken Winter
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?

2007-06-17 Thread Ken Winter
 -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?

2007-06-17 Thread Dieter Maurer
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?

2007-06-16 Thread Ken Winter
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?

2007-06-16 Thread Andreas Jung

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?

2007-06-16 Thread Ken Winter
 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?

2007-06-16 Thread Ken Winter
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?

2007-06-16 Thread Charlie Clark

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