christian.muel...@nvoe.at ha scritto: > Puuh, a long mail, I will try to keep it short. > > 1) Each db engine has its own optimizer, DB2 as an example has a cost > based optimizer (not rule based) using indices based on distribution > statistics. Creating an and index for a char(1) field having values "Y" > and "N" will have no sense, DB2 will never use this index. If the > statistics is up to date, prepared statements outperform non ps > statements clearly in most situations. But I think PostGIS and Oracle > are mainstream, it is ok to focus on these systems.
So you're saying that the DB2 optimizer does not make a plan for a prepared statement, but actually recomputes it every time it receives the actual values? Because it's the only way it can recognize the actual bbox filter is so large that using the index is counter productive. If so, that's quite cool! > 2) A superb solution is to parametrize the use of a VT, either to use > the select statement as a prepared statement or replace the parameters > markers with literals. I did this for a DB2 implementation some times > ago , but for all our supported db engines this would be a heavy job. I > only mention this as an idea. Eh yeah, indeed a heavy one. > 3) Using named parameters raises some problems. (For ps or non ps ). At > the end of the day, you will need to parse the sql statement, because > there could be a parameter marker within a string constant. During the > Oracle Georaster stuff, I need to create queries which where composed > of a lot of statements delimited by ";" (more like a procedure). A > simple search and replace will work in 99.9 %, but not in 100%. I trust the user not to do too fancy things there. In GeoServer I will also design the GUI so that a regexp based matcher will fill the parameters table by pure inspection of the query, so if there is some odd situation the user should recognize it by the code not guessing properly the param names. Ok, this is a bit of a long shot, but just today I found out a very cool usage of regular expressions that allowed me to tokenize a string so that the quoted terms were recognized as a single block despite having spaces inside, see: http://stackoverflow.com/questions/366202/regex-for-splitting-a-string-using-space-when-not-surrounded-by-single-or-double > To bring it to an end, if we offer regexp patterns for the user and > warn them about sql injection attacks , I am +0 here. (I cant give a +1 > here, my stomach has not the best feeling, I fear most of the users wont > even know what sql injection is, but I may be wrong here). No, I fear you're right, most people do not know what sql inject is at all. So we have to warn people with a long red angry explanation that either: - they are working in a walled garden where no one will perform attacks (does such heaven exist?) or - if they don't think out fully the protection regexp, well, they can kiss goodbye their data (if they are so smart to use a account that can also write) or at least their privacy (though desinign an attack that will return extra data in Geotools is very hard, we just return the data we know is in the feature type) Cheers Andrea -- Andrea Aime OpenGeo - http://opengeo.org Expert service straight from the developers. ------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
