On 13.01.2016 17:19, Nicolas Ribot wrote: > Ouch ;) > > With DButils approach though, we still have to build queries manually to add > schema and table names for some databases.
true.. but we use prepared statements and utilize it's magic to 1. escape identifiers properly, 2. usually only allow one statement, no appending w/ semicolon possible > SQLBuilder on the other side, encapsulates the FROM/JOIN clause in a object. > It seems safer (though not tested against SQL injection), but needs more code > to write. if i had the choice i'd try dbutils or plain prepared statements first > For me, the 2 doors for SQL injections are custom WHERE clause in Datasource > queries and adhoc queries. well, wrong! that is plain user input, that the user sees and puts in in the first place. real issue here are eg. shapefiles with weird column names or row content that when simply put into a sql string and run against a db dorp tables or such. see http://imgs.xkcd.com/comics/exploits_of_a_mom.png > It can't be easily fixed except if we could find a good java SQL parser that > can tell if query seems valid before executing it (with all OJ db supported, > I doubt such a parser exists). Here, other GIS I know of are also sensitive > to SQL injection as soon as user can enter free parameters. no need to fix anything on that front.. see above. > > DBUtil and SQLBuilder are useless here. > > I would say that using SQBuilder is a bit heavy to use just to avoid building > SQL strings. > What do you think ? using dbutils or prepared statement should suffice. ..ede > Nicolas > > On 13 January 2016 at 16:26, <[email protected] > <mailto:[email protected]>> wrote: > > am looking forward to your implementation ;).. ede > > On 13.01.2016 16:18, Nicolas Ribot wrote: > > Yes, really worth the size (also integrates well conceptually with > other commons libs used). > > > > Very few impact indeed: > > • A new JDBCUtil.query method to mimic existing execute > > • A ResultSetHandler interface offered by the lib instead of > ResultSetBlock: same convenient usage in anonymous blocks. > > • Query parameters are passed directly by SpatialDatabasesMetadata > methods to JDBCUtils.query instead of building a final SQL query with > formatted parameters. > > • SQL queries parameters are replaced by ? instead of %s. > > > > Nicolas > > > > On 13 January 2016 at 16:10, <[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>> wrote: > > > > if you say that it's worth a 77k jar, i won't object. i'd suggest > you try out one code path and see how it goes and come back w/ a result. > > > > ..ede > > > > On 13.01.2016 16:03, Nicolas Ribot wrote: > > > It provides varargs query method taking a SQL query and the list > of parameters to bind. > > > No need to call ps.setXXX to bind parameters. > > > > > > On 13 January 2016 at 15:42, <[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>>> wrote: > > > > > > how do you mean? ..ede > > > > > > On 13.01.2016 15:40, Nicolas Ribot wrote: > > > > It could be useful to encapsulate parameters binding, > though. > > > > > > > > On 13 January 2016 at 15:21, Nicolas Ribot > <[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>>>> wrote: > > > > > > > > Yes, indeed, as we trap exceptions ourselves for JDBC > stuff, simple PreparedStatement should be enough. > > > > I will give it a test. > > > > > > > > Nicolas > > > > > > > > On 13 January 2016 at 15:17, <[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>>>> wrote: > > > > > > > > hmm.. don't see what the advantage is besides as > they call it "abstract out all of the cleanup tasks" . > > > > > > > > you might as well work w/ plain prepared statements > before using it. > > > > > > > > Mike: wanna pitch in :) ? > > > > > > > > ..ede > > > > > > > > On 13.01.2016 14:57, Nicolas Ribot wrote: > > > > > Maybe the lightweight Apache commons DB Utils > would be enough ? > > > > > > https://commons.apache.org/proper/commons-dbutils/examples.html > > > > > > > > > > It provides preparedStatement and a very thin > interface for resultSets. It seems to integrate well with current code. > > > > > > > > > > Nicolas > > > > > > > > > > On 13 January 2016 at 14:35, <[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>>>>> wrote: > > > > > > > > > > there are probably other alternatives out > there, don't feel obliged to use the first one i found ;) . > > > > > > > > > > ..ede > > > > > > > > > > On 13.01.2016 14:28, Nicolas Ribot wrote: > > > > > > Yes, definitely agree it would be far > better than current code. > > > > > > Will look at sqlbuilder. > > > > > > > > > > > > Nicolas > > > > > > > > > > > > On 13 January 2016 at 14:25, > <[email protected] <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>> <mailto:[email protected] > <mailto:[email protected]> <mailto:[email protected] > <mailto:[email protected]>>>>> <mailto:edga [email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>> > <mailto:[email protected] <mailto:[email protected]> > <mailto:[email protected] <mailto:[email protected]>>>>>>> wrote: > > > > > > > > > > > > hey Nico, > > > > > > > > > > > > good start. but before escaping every > little speciality consider using prepared statements again ;). > > > > > > > > > > > > or how about > > > > > > > http://openhms.sourceforge.net/sqlbuilder/ > > > > > > looks fairly simple and w/ 200k size > affordable. > > > > > > > > > > > > ..ede > > > > > > > > > > > > > > > > > > On 13.01.2016 14:02, > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> <mailto:jump-pilot-svn@lis ts.sourceforge.net > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] <mailto:[email protected]> > rceforge.net <http://rceforge.net> > > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>>>> wrote: > > > > > > > Revision: 4784 > > > > > > > > http://sourceforge.net/p/jump-pilot/code/4784 > > > > > > > Author: elnico > > > > > > > Date: 2016-01-13 13:02:12 +0000 > (Wed, 13 Jan 2016) > > > > > > > Log Message: > > > > > > > ----------- > > > > > > > Escape of single quotes in SQL > identifiers names, as most database identifiers can contain quotes. > > > > > > > > > > > > > > Modified Paths: > > > > > > > -------------- > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesSQLBuilder.java > > > > > > > > core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -3,6 +3,7 @@ > > > > > > > import > com.vividsolutions.jump.datastore.DataStoreConnection; > > > > > > > import > com.vividsolutions.jump.datastore.GeometryColumn; > > > > > > > import > com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSMetadata; > > > > > > > +import > com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder; > > > > > > > > > > > > > > import java.util.List; > > > > > > > > > > > > > > @@ -18,7 +19,7 @@ > > > > > > > defaultSchemaName = "PUBLIC"; > > > > > > > spatialDbName = "H2"; > > > > > > > //spatialExtentQuery1 = > "SELECT ST_AsBinary(ST_Estimated_Extent( '%s', '%s', '%s' ))"; > > > > > > > - spatialExtentQuery1 = > "SELECT ST_AsBinary(ST_Envelope(ST_Extent(%s))) FROM %s.%s"; > > > > > > > + spatialExtentQuery1 = > "SELECT ST_AsBinary(ST_Envelope(ST_Extent(%s))) FROM \"%s\".\"%s\""; > > > > > > > geoColumnsQuery = "SELECT > f_geometry_column, srid, type FROM geometry_columns where f_table_schema = > '%s' and f_table_name = '%s'"; > > > > > > > sridQuery = "SELECT srid > FROM geometry_columns where f_table_schema = '%s' and f_table_name = '%s' and > f_geometry_column = '%s'"; > > > > > > > } > > > > > > > @@ -35,13 +36,18 @@ > > > > > > > > > > > > > > @Override > > > > > > > public String > getGeoColumnsQuery(String datasetName) { > > > > > > > - return > String.format(this.geoColumnsQuery, getSchemaName(datasetName), > getTableName(datasetName)); > > > > > > > + // escape single quotes > > > > > > > + return > String.format(this.geoColumnsQuery, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(getSchemaName(datasetName)), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(getTableName(datasetName))); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > public String > getSridQuery(String schemaName, String tableName, String colName) { > > > > > > > - // TODO > > > > > > > - return > String.format(this.sridQuery, schemaName, tableName, colName); > > > > > > > + // escape single quotes > > > > > > > + return > String.format(this.sridQuery, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(schemaName), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(tableName), colName); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -3,12 +3,8 @@ > > > > > > > import > com.vividsolutions.jump.datastore.DataStoreConnection; > > > > > > > import > com.vividsolutions.jump.datastore.spatialdatabases.*; > > > > > > > import > com.vividsolutions.jump.datastore.GeometryColumn; > > > > > > > -import > com.vividsolutions.jump.datastore.jdbc.JDBCUtil; > > > > > > > -import > com.vividsolutions.jump.datastore.jdbc.ResultSetBlock; > > > > > > > import java.sql.DatabaseMetaData; > > > > > > > import java.sql.ResultSet; > > > > > > > -import java.sql.SQLException; > > > > > > > -import java.util.ArrayList; > > > > > > > import java.util.List; > > > > > > > > > > > > > > public class MariadbDSMetadata > extends SpatialDatabasesDSMetadata { > > > > > > > @@ -73,6 +69,7 @@ > > > > > > > > > > > > > > // query according to detected > layout: > > > > > > > geoColumnsQuery = "SELECT > f_geometry_column, srid, type FROM geometry_columns where f_table_name = > '%s'"; > > > > > > > + // TODO: not the same number of > param to replace... > > > > > > > if (geometryColumnsLayout == > GeometryColumnsLayout.NO_LAYOUT) { > > > > > > > geoColumnsQuery = "select > c.COLUMN_NAME, 0, 'geometry' \n" > > > > > > > + "from > information_schema.TABLES t join information_schema.COLUMNS C \n" > > > > > > > @@ -86,8 +83,9 @@ > > > > > > > // query according to detected > layout: > > > > > > > sridQuery = "SELECT srid FROM > geometry_columns where f_table_name = '%s' and f_geometry_column = '%s'"; > > > > > > > if (geometryColumnsLayout == > GeometryColumnsLayout.NO_LAYOUT) { > > > > > > > - sridQuery = "select case when > min(st_srid(%s)) <> max(st_srid(%s)) then 0 else min(st_srid(%s)) end as > srid\n" > > > > > > > - + "from %s.%s"; > > > > > > > + // quote identifiers > > > > > > > + sridQuery2 = "select case when > min(st_srid(%s)) <> max(st_srid(%s)) then 0 else min(st_srid(%s)) end as > srid\n" > > > > > > > + + "from `%s`.`%s`"; > > > > > > > } > > > > > > > > > > > > > > } > > > > > > > @@ -104,7 +102,9 @@ > > > > > > > > > > > > > > @Override > > > > > > > public String > getGeoColumnsQuery(String datasetName) { > > > > > > > - return > String.format(this.geoColumnsQuery, getTableName(datasetName)); > > > > > > > + // escape single quotes in > identifier > > > > > > > + return > String.format(this.geoColumnsQuery, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(getTableName(datasetName))); > > > > > > > } > > > > > > > > > > > > > > public String > getGeoColumnsQuery2(String datasetName) { > > > > > > > @@ -113,11 +113,16 @@ > > > > > > > > > > > > > > @Override > > > > > > > public String getSridQuery(String > schemaName, String tableName, String colName) { > > > > > > > - return > String.format(this.sridQuery, tableName, colName); > > > > > > > + // escape single quotes in > identifier > > > > > > > + // TODO: geom ? > > > > > > > + return > String.format(this.sridQuery, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(tableName), colName); > > > > > > > } > > > > > > > > > > > > > > public String getSridQuery2(String > schemaName, String tableName, String colName) { > > > > > > > - return > String.format(this.sridQuery2, colName, colName, colName, schemaName, > tableName); > > > > > > > + return > String.format(this.sridQuery2, colName, colName, colName, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(schemaName), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(tableName)); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -45,9 +45,9 @@ > > > > > > > " > (select sdo_lb from tmp where sdo_dimname = 'Y'))\n" + > > > > > > > " )) as geom \n" + > > > > > > > "from dual"; > > > > > > > + // double quotes identifiers > > > > > > > + spatialExtentQuery2 = > "select sdo_util.to_wktgeometry(sdo_aggr_mbr(%s)) as geom from \"%s\".\"%s\""; > > > > > > > > > > > > > > - spatialExtentQuery2 = > "select sdo_util.to_wktgeometry(sdo_aggr_mbr(%s)) as geom from %s.%s"; > > > > > > > - > > > > > > > geoColumnsQuery = "select > t.column_name, t.srid, 'SDO_GEOMETRY' as type from ALL_SDO_GEOM_METADATA t " > > > > > > > + "where t.owner = '%s' > and t.table_name = '%s'"; > > > > > > > sridQuery = "select t.srid > from ALL_SDO_GEOM_METADATA t " > > > > > > > @@ -56,7 +56,10 @@ > > > > > > > > > > > > > > @Override > > > > > > > public String > getSpatialExtentQuery1(String schema, String table, String attributeName) { > > > > > > > - return > String.format(this.spatialExtentQuery1, schema, table, attributeName); > > > > > > > + // escape single quote for > table name: > > > > > > > + // TODO: do it for > schema/user name ? > > > > > > > + return > String.format(this.spatialExtentQuery1, schema, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(table), attributeName); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > @@ -66,13 +69,18 @@ > > > > > > > > > > > > > > @Override > > > > > > > public String > getGeoColumnsQuery(String datasetName) { > > > > > > > - return > String.format(this.geoColumnsQuery, getSchemaName(datasetName), > getTableName(datasetName)); > > > > > > > + // escape single quote for > table name: > > > > > > > + // TODO: do it for > schema/user name ? > > > > > > > + return > String.format(this.geoColumnsQuery, getSchemaName(datasetName), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(getTableName(datasetName))); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > public String > getSridQuery(String schemaName, String tableName, String colName) { > > > > > > > - // TODO > > > > > > > - return > String.format(this.sridQuery, schemaName, tableName, colName); > > > > > > > + // escape single quote for > table name: > > > > > > > + // TODO: do it for > schema/user name ? > > > > > > > + return > String.format(this.sridQuery, schemaName, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(tableName), colName); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -14,14 +14,19 @@ > > > > > > > defaultSchemaName = "public"; > > > > > > > spatialDbName = "PostGIS"; > > > > > > > spatialExtentQuery1 = > "SELECT ST_AsBinary(ST_Estimated_Extent( '%s', '%s', '%s' ))"; > > > > > > > - spatialExtentQuery2 = > "SELECT ST_AsBinary(ST_Envelope(ST_Extent(\"%s\"))) FROM %s.%s"; > > > > > > > + // Nicolas Ribot: add double > quotes for identifiers > > > > > > > + spatialExtentQuery2 = > "SELECT ST_AsBinary(ST_Envelope(ST_Extent(\"%s\"))) FROM \"%s\".\"%s\""; > > > > > > > geoColumnsQuery = "SELECT > f_geometry_column, srid, type FROM geometry_columns where f_table_schema='%s' > and f_table_name = '%s'"; > > > > > > > sridQuery = "SELECT srid > FROM geometry_columns where f_table_schema = '%s' and f_table_name = '%s' and > f_geometry_column = '%s'"; > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > public String > getSpatialExtentQuery1(String schema, String table, String attributeName) { > > > > > > > - return > String.format(this.spatialExtentQuery1, schema, table, attributeName); > > > > > > > + //must escape single quote > in idenfifiers before formatting query > > > > > > > + return > String.format(this.spatialExtentQuery1, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(schema), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(table), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(attributeName)); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > @@ -31,13 +36,19 @@ > > > > > > > > > > > > > > @Override > > > > > > > public String > getGeoColumnsQuery(String datasetName) { > > > > > > > - return > String.format(this.geoColumnsQuery, getSchemaName(datasetName), > getTableName(datasetName)); > > > > > > > + //must escape single quote > in idenfifiers before formatting query > > > > > > > + return > String.format(this.geoColumnsQuery, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(getSchemaName(datasetName)), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(getTableName(datasetName))); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > public String > getSridQuery(String schemaName, String tableName, String colName) { > > > > > > > - // TODO > > > > > > > - return > String.format(this.sridQuery, schemaName, tableName, colName); > > > > > > > + //must escape single quote > in idenfifiers before formatting query > > > > > > > + return > String.format(this.sridQuery, > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(schemaName), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(tableName), > > > > > > > + > SpatialDatabasesSQLBuilder.escapeSingleQuote(colName)); > > > > > > > } > > > > > > > > > > > > > > @Override > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -93,9 +93,6 @@ > > > > > > > } > > > > > > > > > > > > > > public > SpatialDatabasesDSMetadata(DataStoreConnection conn) { > > > > > > > - > JUMPWorkbench.getInstance().getFrame().log("creating a > SpatialDatabasesDSMetadata (class:" + this.getClass() > > > > > > > - + " ) (con: " + > conn.toString() + ") id" > > > > > > > - + this.hashCode(), > this.getClass()); > > > > > > > this.conn = conn; > > > > > > > // TODO: use bind parameters to > avoid SQL injection > > > > > > > this.datasetNameQuery = ""; > > > > > > > @@ -289,6 +286,7 @@ > > > > > > > new ResultSetBlock() { > > > > > > > public void > yield(ResultSet resultSet) throws SQLException { > > > > > > > while (resultSet.next()) > { > > > > > > > + // TODO: escape single > quotes in geo column name ? > > > > > > > > geometryAttributes.add(new GeometryColumn( > > > > > > > > resultSet.getString(1), > > > > > > > > resultSet.getInt(2), > > > > > > > @@ -353,6 +351,7 @@ > > > > > > > DatabaseMetaData dbMd = > this.conn.getJdbcConnection().getMetaData(); > > > > > > > rs = dbMd.getColumns(null, > getSchemaName(datasetName), getTableName(datasetName), null); > > > > > > > while (rs.next()) { > > > > > > > + // TODO: escape quotes in > column names ? > > > > > > > cols.add(rs.getString(4)); > > > > > > > } > > > > > > > } catch (SQLException sqle) { > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesSQLBuilder.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesSQLBuilder.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesSQLBuilder.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -69,4 +69,14 @@ > > > > > > > else > > > > > > > return srid.getString(); > > > > > > > } > > > > > > > + > > > > > > > + /** > > > > > > > + * Utility method to escape single > quotes in given identifier. > > > > > > > + * Replace all single quotes ("'") > by double single quotes ("''") > > > > > > > + * @param identifier > > > > > > > + * @return the identifier with > single quotes escaped, or identifier if no string found > > > > > > > + */ > > > > > > > + public static String > escapeSingleQuote(String identifier) { > > > > > > > + return identifier == null ? null > : identifier.replaceAll("'", "''"); > > > > > > > + } > > > > > > > } > > > > > > > > > > > > > > Modified: > core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java > > > > > > > > =================================================================== > > > > > > > --- > core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java > 2016-01-13 10:49:14 UTC (rev 4783) > > > > > > > +++ > core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java > 2016-01-13 13:02:12 UTC (rev 4784) > > > > > > > @@ -84,7 +84,7 @@ > > > > > > > > > > > > > > defaultSchemaName = ""; > > > > > > > spatialDbName = > isSpatialiteLoaded() ? "Spatialite" : "SQLite"; > > > > > > > - spatialExtentQuery1 = "SELECT %s > from %s"; > > > > > > > + spatialExtentQuery1 = "SELECT %s > from \"%s\""; > > > > > > > // no second query for spatialite > > > > > > > spatialExtentQuery2 = null; > > > > > > > if (this.geometryColumnsLayout > == GeometryColumnsLayout.OGC_GEOPACKAGE_LAYOUT) { > > > > > > > @@ -129,11 +129,12 @@ > > > > > > > // TODO: switch case > > > > > > > if (this.isSpatialiteLoaded()) { > > > > > > > if (gcType == > GeometricColumnType.WKB) { > > > > > > > - ret = String.format("select > st_asBinary(extent(st_geomFromWkb(%s))) from %s", attributeName, table); > > > > > > > + // quotes identifier. > > > > > > > + ret = String.format("select > st_asBinary(extent(st_geomFromWkb(%s))) from \"%s\"", attributeName, table); > > > > > > > } else if (gcType == > GeometricColumnType.WKT) { > > > > > > > - ret = String.format("select > st_asBinary(extent(st_geomFromText(%s))) from %s", attributeName, table); > > > > > > > + ret = String.format("select > st_asBinary(extent(st_geomFromText(%s))) from \"%s\"", attributeName, table); > > > > > > > } else if (gcType == > GeometricColumnType.SPATIALITE) { > > > > > > > - ret = String.format("select > st_asBinary(extent(CastAutomagic(%s))) from %s", attributeName, table); > > > > > > > + ret = String.format("select > st_asBinary(extent(CastAutomagic(%s))) from \"%s\"", attributeName, table); > > > > > > > } else { > > > > > > > // unknown geom type > > > > > > > // TODO: log > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > > > > Site24x7 APM Insight: Get Deep > Visibility into Application Performance > > > > > > APM + Mobile APM + RUM: Monitor 3 App > instances at just $35/Month > > > > > > Monitor end-to-end web transactions and > take corrective actions now > > > > > > Troubleshoot faster and improve > end-user experience. Signup Now! > > > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > > _______________________________________________ > > > > > > Jump-pilot-devel mailing list > > > > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>>>> > > > > > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > > > > Site24x7 APM Insight: Get Deep Visibility > into Application Performance > > > > > > APM + Mobile APM + RUM: Monitor 3 App > instances at just $35/Month > > > > > > Monitor end-to-end web transactions and > take corrective actions now > > > > > > Troubleshoot faster and improve end-user > experience. Signup Now! > > > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > > > Jump-pilot-devel mailing list > > > > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>>> > > > > > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > > > Site24x7 APM Insight: Get Deep Visibility > into Application Performance > > > > > APM + Mobile APM + RUM: Monitor 3 App > instances at just $35/Month > > > > > Monitor end-to-end web transactions and take > corrective actions now > > > > > Troubleshoot faster and improve end-user > experience. Signup Now! > > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > _______________________________________________ > > > > > Jump-pilot-devel mailing list > > > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>>> > > > > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > > > Site24x7 APM Insight: Get Deep Visibility into > Application Performance > > > > > APM + Mobile APM + RUM: Monitor 3 App instances > at just $35/Month > > > > > Monitor end-to-end web transactions and take > corrective actions now > > > > > Troubleshoot faster and improve end-user > experience. Signup Now! > > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > > Jump-pilot-devel mailing list > > > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > > > > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > > Site24x7 APM Insight: Get Deep Visibility into > Application Performance > > > > APM + Mobile APM + RUM: Monitor 3 App instances at > just $35/Month > > > > Monitor end-to-end web transactions and take > corrective actions now > > > > Troubleshoot faster and improve end-user > experience. Signup Now! > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > _______________________________________________ > > > > Jump-pilot-devel mailing list > > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>>> > > > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > > Site24x7 APM Insight: Get Deep Visibility into Application > Performance > > > > APM + Mobile APM + RUM: Monitor 3 App instances at just > $35/Month > > > > Monitor end-to-end web transactions and take corrective > actions now > > > > Troubleshoot faster and improve end-user experience. Signup > Now! > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > > > > > > > > > > > _______________________________________________ > > > > Jump-pilot-devel mailing list > > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > > > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > ------------------------------------------------------------------------------ > > > Site24x7 APM Insight: Get Deep Visibility into Application > Performance > > > APM + Mobile APM + RUM: Monitor 3 App instances at just > $35/Month > > > Monitor end-to-end web transactions and take corrective > actions now > > > Troubleshoot faster and improve end-user experience. Signup > Now! > > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > _______________________________________________ > > > Jump-pilot-devel mailing list > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > <mailto:[email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>>> > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > > > > > > ------------------------------------------------------------------------------ > > > Site24x7 APM Insight: Get Deep Visibility into Application > Performance > > > APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month > > > Monitor end-to-end web transactions and take corrective actions > now > > > Troubleshoot faster and improve end-user experience. Signup Now! > > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > > > > > > > _______________________________________________ > > > Jump-pilot-devel mailing list > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > ------------------------------------------------------------------------------ > > Site24x7 APM Insight: Get Deep Visibility into Application > Performance > > APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month > > Monitor end-to-end web transactions and take corrective actions now > > Troubleshoot faster and improve end-user experience. Signup Now! > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > _______________________________________________ > > Jump-pilot-devel mailing list > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > > > > > > > ------------------------------------------------------------------------------ > > Site24x7 APM Insight: Get Deep Visibility into Application Performance > > APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month > > Monitor end-to-end web transactions and take corrective actions now > > Troubleshoot faster and improve end-user experience. Signup Now! > > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > > > > > _______________________________________________ > > Jump-pilot-devel mailing list > > [email protected] > <mailto:[email protected]> > > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > ------------------------------------------------------------------------------ > Site24x7 APM Insight: Get Deep Visibility into Application Performance > APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month > Monitor end-to-end web transactions and take corrective actions now > Troubleshoot faster and improve end-user experience. Signup Now! > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > _______________________________________________ > Jump-pilot-devel mailing list > [email protected] > <mailto:[email protected]> > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > > > > > ------------------------------------------------------------------------------ > Site24x7 APM Insight: Get Deep Visibility into Application Performance > APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month > Monitor end-to-end web transactions and take corrective actions now > Troubleshoot faster and improve end-user experience. Signup Now! > http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 > > > > _______________________________________________ > Jump-pilot-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel > ------------------------------------------------------------------------------ Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 _______________________________________________ Jump-pilot-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
