[Zope] Re: sql_quote in dtml-var in zsql-Methods

[Jim Penny]

On Fri, 8 Aug 2003 10:16:26 +0200
"Elena Schulz" <elena.schulz at 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 at 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 )
> 
>