[Zope-DB] dynamic SQL
charlie at begeistert.org
Wed Oct 8 14:47:08 EDT 2003
Jason LeMonier wrote:
> 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)">
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
More information about the Zope-DB