On Fri, 8 Aug 2003 10:16:26 +0200 "Elena Schulz" <elena.schulz@gmx.net> wrote:
Hi Jim,
thanks for your answer. Yes, I know about the quoting problem and what dtml-var does. But acording to my findings dtml-var plus sql_quote doesn't do the same thing. I couldn't find that it was doing anything with the quoting. That's why I'm asking. Also the different way dtml-var plus fmt=sql-quote. But I will check again.
I just tested. My ZSQL method says this: select * from foo where color='<dtml-var color sql_quote>' with variable color set to: black;'delete from foo'; select * where color='white it renders and executes this: select * from foo where color='black;''delete from foo''; select * where color=''white' Note the doubling of the quotes. There is no doubt that sql_quote is doing what it is supposed to.
But another question: what should be done in the following construct:
select <dtml-var myCol> from <dtml-sqlvar myTable>
Well, lets try it! ZSQL method: select * from <dtml-var table sql_quote> where color='<dtml-var color sql_quote>' variable table set to: foo; delete from foo; select * from foo color set to '' renders and executes this: select * from foo; delete from foo; select * from foo where color='' Result: poof, goodbye data! What should you do about this? Realistically the answer is simple. Never use this kind of query. Create two different queries and call the appropriate one. Why loose sleep when five minutes of effort can prevent insecurity? Alternatively, you can write your own quote routine and call it, like select from <dtml-var expr="my_acceptable_tables(table)"> where ... my_acceptable_tables checks to see if table is in a list of acceptable tables, and returns it is it is. If it is not, you need to throw an exception. I don't like this as much as separate methods because you have to know more to figure out what it is doing, in particular, you have to look at my_acceptable_tables as well as the method source; but that is a matter of your taste. In general, sql_quote will give you no protection if the variable is numeric, boolean, or anything else that is not quoted in SQL syntax. The rules are very simple. If it will work, ALWAYS use the appropriate dtml-sqlvar syntax. Use dtml-var ONLY if there is no way to make dtml-sqlvar work. Don't get cute trying to build all-singing, all-dancing queries. The simpler the query is, the easier it is to audit, the easier it is to maintain, and the easier it is to modify. In my experience, there are two places where you often end up needing to use dtml-var: like clauses, and in clauses. like clauses work very well with sql_quote. The column being selected on has to be textual, you have to supply surrounding quotes yourself, etc. So methods such as: select * from foo where color like '<dtml-var color sql_quote>%' is correct and safe. in clauses are NOT safe. If you want to make them safe, call your own canonicalization routine, as: select * from foo where color in <dtml-var expr="my_careful_quote_routine(color)"> You will probably need this for type-checking, anyway. Jim Penny
Here another kind of quoting would be needed or the following could be done: myCols = "a_table_col from a_table; delete * from a_table; select * " Thats the same problem, isn't it? What would help here? Just a double quoting like: select "<dtml-var myCol>" from <dtml-sqlvar myTable>?
-- Elena
_______________________________________________ Zope maillist - Zope@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 )