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, <edgar.sol...@web.de > <mailto:edgar.sol...@web.de>> 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, <edgar.sol...@web.de > <mailto:edgar.sol...@web.de> <mailto:edgar.sol...@web.de > <mailto:edgar.sol...@web.de>>> 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, jump-pilot-...@lists.sourceforge.net > <mailto:jump-pilot-...@lists.sourceforge.net> > <mailto:jump-pilot-...@lists.sourceforge.net > <mailto:jump-pilot-...@lists.sourceforge.net>> 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 > > Jump-pilot-devel@lists.sourceforge.net > <mailto:Jump-pilot-devel@lists.sourceforge.net> > <mailto:Jump-pilot-devel@lists.sourceforge.net > <mailto:Jump-pilot-devel@lists.sourceforge.net>> > > 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 > > Jump-pilot-devel@lists.sourceforge.net > <mailto:Jump-pilot-devel@lists.sourceforge.net> > > 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 > Jump-pilot-devel@lists.sourceforge.net > <mailto:Jump-pilot-devel@lists.sourceforge.net> > 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 > Jump-pilot-devel@lists.sourceforge.net > 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 Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
