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