[Zope-DB] How can I dynamically generate a Z Search Interface?

[Jim Penny]

> }def foo (Columns, table):
> }
> }                                         # Columns contain names of columns
> }                                         # table is the name of the table ;)
> }    finalresult = ""                     # what this method returns as table in
> html
> }    qry_str = "select "                  # query string
> }    results = []                         # result of query
> }
> }                                         # open Oracle connection
> }    dCon = dco.connect("someone/password@aninstance")      # gaping security
> hole!
> }    dCur = dCon.cursor()
> }                                         # build query string
> }    qry_str = something_like_ "select bla1, bla2, bla3 from `table`"
> }
> }    dCur.execute(qry_str)
> }    results = dCur.fetchall()            # fetch results generated by the query
> string
> }                                         # start assembly of the table in html
> }    finalresult = "<table border>"
> }                                         # generate column headings
> }    finalresult = finalresult + "`each entry in Columns`"
> }
> }                                         # build rows of real data
> }    for each_row in results:
> }            finalresult = finalresult +  "some_real_data </td>"
> }
> }    finalresult = finalresult + "</table>"
> }
> }                                         # all done, just return what got
> created
> }    return finalresult
> }
> 
> 

OK - here goes:

1)  it is terribly inefficient.  As I understand it, Oracle is one of
the worst DB's as far as connection setup time goes.  (postgresql is
also quite bad).  I would be surprised if it takes less than .2 seconds
to do the connection.  (this is what I thought Dieter was telling you to
do, but is not at all what he had in mind!)

1B)  You didn't close the cursor or connection, this may cause memory 
leakage, but I am not sure.

2)  The gaping security hole may not be.  You are depending on A) that
the External Method not be world readable (under you control), B) that
anyone who can log into whatever userid zope is running under can be
trusted (under your control), and C) that the network connection not
expose it to the world by packet sniffing.  This last can be solved by
putting zope and oracle on the same machine or by using some sort of
encryption layer (DA provided, or stunnel).

3)  You may be exposed to SQL Injection.  Neither the column names nor
the table name have been sql_quoted.  So, if there is any way that the
end user can enter a string, either by an input box, or by a hand
modified REQUEST, your database is toast.  The latter may make even
choosing the tables by checkbox unsafe.

Other than that, it is OK.  ;-)

Jim Penny

As to the HOWTO, you have already seen the gist of it...

http://www.zope.org/Members/jpenny/Variable_SQL_statements

Note all the warnings, I wrote this as a quick note, and then found that
people really, really wanted to use it in inapropriate ways.

Jim