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