[Zope-DB] dynamic SQL

[Charlie Clark]

Jason LeMonier wrote:

> Hey,
> 
> the simplest case where it was nice to have control
> was when i needed:
> 
> select * from table
> select * from table where type = 6
> select * from table where id = 238
> 
> setting up type and id as required parameters takes away
> being able to have no where clause at all.
> (w/o even more labor in the zpt and now 3 separate SQL methods).
> 
> I figure if I'm going to allow "select * from table"
> then I don't really care how any code builds a where clause!

SELECT * is a bit too _implicit_ but not fatal.

I have some queries on a site where four tables with exactly the same 
structure are queried in exactly the same way. I pass the table name 
into the method which generates the query dynamically. No it would be 
nice if I could actually use <dtml-sqlvar> to check this for me but that 
invalidates they query so I live without this extra security.

my SQL looks a bit like this

SELECT id, value FROM <dtml-var table>

and I call this in my ZPT like this:

<tal repeat:"results python:here.SQL.mySQL(table=shoes)">
</tal>

Apart from having to switch between "path" and normal Python syntax in 
the ZPR I find this an acceptable compromise. I'd be interested in what 
Jim Penny and others have to say on security, of course. I think this is 
okay for queries. I have virtually no instances of this connected with 
INSERT, UPDATE or DELETE

Don't complain about bloating the ZPT or your SQL-Folder, just think 
about how much work you don't have because some malicious SQL code 
sneaking in.

Charlie