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> 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>> 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> 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>
> > 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
>
------------------------------------------------------------------------------
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
