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
