Re: [Zope-DB] [Zope] Stored Procedures Versus ZSQL Methods
Yes, with a stored procedure the DB does not have to reparse and prepare a new plan for every query. This can be a major win. Esp. on Oracle. Remy Pinsonnault remypinsonna...@gmail.com Sent by: zope-boun...@zope.org 02/17/2009 06:37 PM To zope-db@zope.org, z...@zope.org cc Subject [Zope] Stored Procedures Versus ZSQL Methods Hello, We have a Zope application with thousands of Z SQL methods connected to an Oracle Database. Our DBA want us to develop our new applications using stored procedures called through external methods, instead of using directly Z SQL methods, for performance issues and memory usage. Do stored procedures will allow better performance? Thanks in advance Rémy___ Zope maillist - z...@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev ) ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] [Zope] Stored Procedures Versus ZSQL Methods
No, ZSQL really predates bind variables. That is, they we available on a few systems, but were rare. If the Oracle specialist has a reason for going to external methods, like his server is seriously loaded, I would pay attention to him. If he is just following some set of best practices, well, that is a political problem for Remy. Using external methods will be more work for the zope writer. I don't know enough to comment seriously on security issues, but I think that using procedures, like using bind variables, will make SQL Injection much harder. Cynthia Kiser cnk+z...@caltech.edu 02/17/2009 06:44 PM To jpe...@ykksnap-america.com cc Remy Pinsonnault remypinsonna...@gmail.com, zope-db@zope.org Subject Re: [Zope-DB] [Zope] Stored Procedures Versus ZSQL Methods Quoting jpe...@ykksnap-america.com jpe...@ykksnap-america.com: Yes, with a stored procedure the DB does not have to reparse and prepare a new plan for every query. This can be a major win. Esp. on Oracle. Does ZSQL allow the use of bind variables? If so and the database has a correctly sized query cache, there shouldn't be much reparsing for repeated queries. ___ 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] Please help me about driver adapter
An alternative is: select * from students where surname like '%dtml-var surname sql_quote%' This is a bit more succinct, but if you use it, be sure not to forget the sql_quote, or you will be open to sql injection problems. jim penny [EMAIL PROTECTED] wrote on 02/16/2007 02:57:37 AM: On Fri, 2007-02-16 at 07:38 +0100, robert rottermann wrote: I do not think any one of us can help you unless you tell us what exactly your problem with installing pymssql ist. by the way: do you mean MySQLdb? there is very good documentation on using Z SQL in the zope book: http://www.plope.com/Books/2_7Edition/RelationalDatabases.stx there you find examples how to use a query with like. robert Here's an example of how to use like and wildcards in sqlvar's select * from students where surname ilike dtml-sqlvar expr='%'+surname+'%' type=string Regards Garry ___ 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
