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
