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 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 https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
