> Date: Sun, 24 Jun 2007 11:14:38 +0200 > From: [EMAIL PROTECTED] > > Rob Frohwein wrote: > > - query1.xml using SqlTransformer on table1 > > - cleansql.xsl rename > > - process1.xsl reorganize > > - query2.xsl query different table > > - cleansql.xsl rename > > - process2.xsl reorganize > > ... > > > > It works, but is this the "right" approach? > ... > > It works well, but it has some security and performance problems (more > on that in a minute) so I'd like to know if there's a better approach. > > One of the problems with the SQL Transformer, when used with XSLT in > this fashion, is that it lacks support for prepared statements and > parameters. Without parameters ( in XSP) you have the > same security problems you would have in badly written PHP: > > SELECT ... > FROM ... > WHERE name = '' > > You did think of this problem, didn't you? :) I can post my:addslashes() > if you want. In any case you will agree that composing queries this way > is... "antiquated" at best. > > The compiled query cannot be cached (as a prepared statement would), > because it changes with every request, and I fear the SQL Transformer > doesn't even try to cache it.
What about SELECT ... FROM ... WHERE name = ? ? The in-parameter may change, but the query itself doesn't so the prepared statement ought to cache it. Admittedly, as written above you've got to supply the in-parameter value as a map:parameter to the XSL transformer rather than the SQL transformer, since it's not valid XML to put elements inside attributes (so "/> isn't valid). To work around that, we customised the transformer so that in-parameter can take either a constant value (in the value attribute) or the name of a supplied parmeter (in a new "param" attribute) which is substituted in the same fashion as substitute-value. That shortens it to SELECT ... FROM ... WHERE name = ? Chaining together multiple SQL transformers (as Rob was doing above) does have one other drawback that should be considered - the number of database connections that are used. Each instance of the SQL transformer will look up a connection when it is initialised, but doesn't return it to the pool until the component is recycled; this doesn't happen until the entire pipeline has finished executing, at which point all the components involved are cleaned up. So if you chain together 4 or 5 SQL transformers, they will be using 4 or 5 separate database connections. If your pool size is set to a maximum of, say, 10 connections then you only need a few simultaneous requests and the pipelines all deadlock waiting for their third or fourth connections (which never become available). To avoid this, you can either 1) configure the pool with a much greater maximum, 2) configure it to be only a soft limit (so it will be exceeded, but the extra connections are closed instead of pooled), or 3) change the SQL transformer to release the connection in endDocument instead (which eliminates the deadlock as the later transformers in the pipeline are able to reuse the connection the previous transformer was using). At least, that was the case last time I had to look into it in detail, though things may have changed since then. Andrew. -- http://pseudoq.sourceforge.net/ Open source java Sudoku application. _________________________________________________________________ Feel like a local wherever you go with BackOfMyHand.com http://www.backofmyhand.com --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
