[Zope] Generic SQL insert

[jpenny at ykksnap-america.com]

Great idea.  Not to be recommended in general.

This works because every field is textual, and you are
sql-quoting by using type=string.

Here are the problems:
1)  if someone reads this and does not use the type=string
tag, or equivalent, they will be wide open to sql injection.
2)  OR, they can pass a list of type with each variable.
3)  If you have to handle casts, then you will have to pass
a list of cast-types, as well.

So, you have essentially moved the problem from making at
least one insertion call per table to a single insertion method
that requires the creation of two, three, or four lists.  This does
not self-evidently require less work.

You can no longer inspect the method to see if it is correct.
You have to look to each call-point to determine what is actually
being used.  Just as bad, your application goes happily on its way if you
are missing (non-key) variables.

Keep zsql methods a simple as possible.  Use as few tricks as
possible.  Your goal is self-evident correctness, not the minimization
of typing.

jim penny




zope-bounces at zope.org wrote on 04/13/2006 02:23:22 PM:

> Whenever I'm using SQL databases in zope, I always seem to have to make
> a ZSQL instance for inserting into every table in my database, and they
> are all nearly the same - they just have a list of all the fields in the
> database in the parameters, then they say:
> 
> insert into [table] ([list of fields]) values ([list of <dtml-sqlvar>s])
> 
> I'd much rather have a dictionary of fields and values, and just throw
> it at the DB, not having to make those queries for every table. I have
> acheived it like so:
> 
> mydict = {"field1":"value1" , "field2":"value2" ,...}
> (fields,values)=zip(*myDict.items())
> context.genericInsert(table='table name',fields=fields,values=values)
> 
> Where generic insert is the following ZSQL method:
> insert into "<dtml-var table>"
>  (<dtml-in expr="fields">"<dtml-var sequence-item>"<dtml-if
> sequence-end><dtml-else>,</dtml-if></dtml-in>)
>  values (<dtml-in expr="values"><dtml-sqlvar sequence-item
> type=string><dtml-if sequence-end><dtml-else>,</dtml-if></dtml-in>);
> 
> with parameters:
> * table - table name
> * fields - list of fieldnames
> * values - list of values in the same order
> 
> What do other people think of this? Is it a really bad idea?
> 
> Robert Munro
> _______________________________________________
> 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 )
>