[Zope-DB] dynamic SQL

Jim Penny jpenny@universal-fasteners.com
Tue, 8 Apr 2003 19:25:45 -0400


On Wed, Apr 09, 2003 at 12:36:23AM +0200, Fernando Martins wrote:
> Hi,
> 
> I'm creating a form which can be used to build queries by the user. It's not
> a general purpose query designer, but it involves a few tables and I'll have
> to create several quite different queries.
> 
> I don't want to go through the trouble of manually preparing all the
> possible queries (ZSQL methods) in antecipation. I would like the form to
> call a PythonScript which will build the SQL statement and then _somehow_
> run it and _somehow_ return a second web page, a ZPT page which can use the
> results sequence to show it to the user.
> 
> So, two questions:
> 
> - how can I execute the SQL statement to get a sequence?
> 
> - how can I send the second page built with the results from the
> PythonScript to the user?
> (I believe I can use 'options' in zpt to receive parameters from a Script,
> but I'm not sure this is the best option for this case).
> 
> Another possibility would be to pass the SQL statement to the second ZPT
> page and run it there _somehow_. Hmm, using 'options' I could pass the SQL
> statement and in the ZPT page I can call a python script which executes the
> SQL statement and returns a sequence. But how can I have a Script executing
> a SQL statement?
> 
> TIA,
> Fernando

It is quite easy.  But you really, really, really don't want to do it.

zsql method
variable_sql
parameter
command
body
<dtml-var command>

Now, what is wrong with this?

Well, you have no security, whatsoever.  Anyone who can access method
variable_sql can do anything that they want to our database.  Even if
you somehow limit access to the method, you can't stop SQL injection.
And you can't debug the SQL, since you have no idea of what will be
executed.

Go to the trouble now.  It will reduce your trouble later.

Jim Penny

> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>