This is an automated email from the ASF dual-hosted git repository.
abhishek pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 41fdf6eafb Quote and escape literals in JDBC lookup to allow reserved
identifiers. (#13632)
41fdf6eafb is described below
commit 41fdf6eafbf3ff4bb67909ba15a2eaeb648dd036
Author: Abhishek Radhakrishnan <[email protected]>
AuthorDate: Tue Jan 10 12:11:54 2023 +0530
Quote and escape literals in JDBC lookup to allow reserved identifiers.
(#13632)
* Quote and escape table, key and column names.
* fix typo.
* More select statements.
* Derby lookup tests create quoted identifiers so it's compatible.
* Use Stringutils.replace() utility.
* quote the filter string.
* Squish doubly quote usage into a single function.
* Add parameterized test with reserved identifiers.
* few changes.
---
.../lookup/namespace/JdbcExtractionNamespace.java | 2 +-
.../lookup/namespace/JdbcCacheGenerator.java | 25 ++++---
.../cache/JdbcExtractionNamespaceTest.java | 78 +++++++++++++---------
3 files changed, 64 insertions(+), 41 deletions(-)
diff --git
a/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/query/lookup/namespace/JdbcExtractionNamespace.java
b/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/query/lookup/namespace/JdbcExtractionNamespace.java
index 5e0a8851fd..1495370a45 100644
---
a/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/query/lookup/namespace/JdbcExtractionNamespace.java
+++
b/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/query/lookup/namespace/JdbcExtractionNamespace.java
@@ -88,7 +88,7 @@ public class JdbcExtractionNamespace implements
ExtractionNamespace
this.filter = filter;
if (pollPeriod == null) {
// Warning because if JdbcExtractionNamespace is being used for lookups,
any updates to the database will not
- // be picked up after the node starts. So for use casses where nodes
start at different times (like streaming
+ // be picked up after the node starts. So for use cases where nodes
start at different times (like streaming
// ingestion with peons) there can be data inconsistencies across the
cluster.
LOG.warn("No pollPeriod configured for JdbcExtractionNamespace - entries
will be loaded only once at startup");
this.pollPeriod = new Period(0L);
diff --git
a/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/server/lookup/namespace/JdbcCacheGenerator.java
b/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/server/lookup/namespace/JdbcCacheGenerator.java
index f05ff70250..b9d1a4cda6 100644
---
a/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/server/lookup/namespace/JdbcCacheGenerator.java
+++
b/extensions-core/lookups-cached-global/src/main/java/org/apache/druid/server/lookup/namespace/JdbcCacheGenerator.java
@@ -19,6 +19,7 @@
package org.apache.druid.server.lookup.namespace;
+import com.google.common.annotations.VisibleForTesting;
import com.google.common.base.Strings;
import org.apache.druid.data.input.MapPopulator;
import org.apache.druid.java.util.common.ISE;
@@ -160,23 +161,29 @@ public final class JdbcCacheGenerator implements
CacheGenerator<JdbcExtractionNa
if (Strings.isNullOrEmpty(filter)) {
return StringUtils.format(
"SELECT %s, %s FROM %s WHERE %s IS NOT NULL",
- keyColumn,
- valueColumn,
- table,
- valueColumn
+ toDoublyQuotedEscapedIdentifier(keyColumn),
+ toDoublyQuotedEscapedIdentifier(valueColumn),
+ toDoublyQuotedEscapedIdentifier(table),
+ toDoublyQuotedEscapedIdentifier(valueColumn)
);
}
return StringUtils.format(
"SELECT %s, %s FROM %s WHERE %s AND %s IS NOT NULL",
- keyColumn,
- valueColumn,
- table,
+ toDoublyQuotedEscapedIdentifier(keyColumn),
+ toDoublyQuotedEscapedIdentifier(valueColumn),
+ toDoublyQuotedEscapedIdentifier(table),
filter,
- valueColumn
+ toDoublyQuotedEscapedIdentifier(valueColumn)
);
}
+ @VisibleForTesting
+ public static String toDoublyQuotedEscapedIdentifier(String identifier)
+ {
+ return "\"" + StringUtils.replace(identifier, "\"", "\"\"") + "\"";
+ }
+
private DBI ensureDBI(CacheScheduler.EntryImpl<JdbcExtractionNamespace> key,
JdbcExtractionNamespace namespace)
{
DBI dbi = null;
@@ -208,7 +215,7 @@ public final class JdbcCacheGenerator implements
CacheGenerator<JdbcExtractionNa
handle -> {
final String query = StringUtils.format(
"SELECT MAX(%s) FROM %s",
- tsColumn, table
+ toDoublyQuotedEscapedIdentifier(tsColumn),
toDoublyQuotedEscapedIdentifier(table)
);
return handle
.createQuery(query)
diff --git
a/extensions-core/lookups-cached-global/src/test/java/org/apache/druid/server/lookup/namespace/cache/JdbcExtractionNamespaceTest.java
b/extensions-core/lookups-cached-global/src/test/java/org/apache/druid/server/lookup/namespace/cache/JdbcExtractionNamespaceTest.java
index b6a37240ce..3e219fce07 100644
---
a/extensions-core/lookups-cached-global/src/test/java/org/apache/druid/server/lookup/namespace/cache/JdbcExtractionNamespaceTest.java
+++
b/extensions-core/lookups-cached-global/src/test/java/org/apache/druid/server/lookup/namespace/cache/JdbcExtractionNamespaceTest.java
@@ -77,9 +77,7 @@ public class JdbcExtractionNamespaceTest
public final TestDerbyConnector.DerbyConnectorRule derbyConnectorRule = new
TestDerbyConnector.DerbyConnectorRule();
private static final Logger log = new
Logger(JdbcExtractionNamespaceTest.class);
- private static final String TABLE_NAME = "abstractDbRenameTest";
- private static final String KEY_NAME = "keyName";
- private static final String VAL_NAME = "valName";
+
private static final String TS_COLUMN = "tsColumn";
private static final String FILTER_COLUMN = "filterColumn";
private static final Map<String, String[]> RENAMES = ImmutableMap.of(
@@ -90,22 +88,32 @@ public class JdbcExtractionNamespaceTest
);
- @Parameterized.Parameters(name = "{0}")
+ @Parameterized.Parameters(name = "tableName={0}, keyName={1}, valName={2},
tsColumn={3}")
public static Collection<Object[]> getParameters()
{
return ImmutableList.of(
- new Object[]{"tsColumn"},
- new Object[]{null}
+ new Object[]{"table", "select", "foo \" column;", "tsColumn"}, //
reserved identifiers as table, key and value columns.
+ new Object[]{"abstractDbRenameTest", "keyName", "valName", "tsColumn"},
+ new Object[]{"abstractDbRenameTest", "keyName", "valName", null}
);
}
public JdbcExtractionNamespaceTest(
+ String tableName,
+ String keyName,
+ String valName,
String tsColumn
)
{
+ this.tableName = tableName;
+ this.keyName = keyName;
+ this.valName = valName;
this.tsColumn = tsColumn;
}
+ private final String tableName;
+ private final String keyName;
+ private final String valName;
private final String tsColumn;
private CacheScheduler scheduler;
private Lifecycle lifecycle;
@@ -132,18 +140,20 @@ public class JdbcExtractionNamespaceTest
handle.createStatement(
StringUtils.format(
"CREATE TABLE %s (%s TIMESTAMP, %s VARCHAR(64), %s
VARCHAR(64), %s VARCHAR(64))",
- TABLE_NAME,
- TS_COLUMN,
- FILTER_COLUMN,
- KEY_NAME,
- VAL_NAME
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tableName),
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(TS_COLUMN),
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(FILTER_COLUMN),
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(keyName),
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(valName)
)
).setQueryTimeout(1).execute()
);
- handle.createStatement(StringUtils.format("TRUNCATE TABLE %s",
TABLE_NAME)).setQueryTimeout(1).execute();
+ handle.createStatement(StringUtils.format("TRUNCATE TABLE %s",
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tableName))).setQueryTimeout(1).execute();
handle.commit();
closer.register(() -> {
- handle.createStatement("DROP TABLE " +
TABLE_NAME).setQueryTimeout(1).execute();
+ handle.createStatement(StringUtils.format("DROP TABLE %s",
+
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tableName))).setQueryTimeout(1).execute();
final ListenableFuture future = setupTeardownService.submit(new
Runnable()
{
@Override
@@ -292,19 +302,25 @@ public class JdbcExtractionNamespaceTest
final String statementVal = val != null ? "'%s'" : "%s";
if (tsColumn == null) {
handle.createStatement(
- StringUtils.format("DELETE FROM %s WHERE %s='%s'", TABLE_NAME,
KEY_NAME, key)
+ StringUtils.format("DELETE FROM %s WHERE %s='%s'",
JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tableName),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(keyName), key)
).setQueryTimeout(1).execute();
query = StringUtils.format(
"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', " + statementVal +
")",
- TABLE_NAME,
- FILTER_COLUMN, KEY_NAME, VAL_NAME,
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tableName),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(FILTER_COLUMN),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(keyName),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(valName),
filter, key, val
);
} else {
query = StringUtils.format(
"INSERT INTO %s (%s, %s, %s, %s) VALUES ('%s', '%s', '%s', " +
statementVal + ")",
- TABLE_NAME,
- tsColumn, FILTER_COLUMN, KEY_NAME, VAL_NAME,
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tableName),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(tsColumn),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(FILTER_COLUMN),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(keyName),
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(valName),
updateTs, filter, key, val
);
}
@@ -321,9 +337,9 @@ public class JdbcExtractionNamespaceTest
{
final JdbcExtractionNamespace extractionNamespace = new
JdbcExtractionNamespace(
derbyConnectorRule.getMetadataConnectorConfig(),
- TABLE_NAME,
- KEY_NAME,
- VAL_NAME,
+ tableName,
+ keyName,
+ valName,
tsColumn,
null,
new Period(0),
@@ -354,11 +370,11 @@ public class JdbcExtractionNamespaceTest
{
final JdbcExtractionNamespace extractionNamespace = new
JdbcExtractionNamespace(
derbyConnectorRule.getMetadataConnectorConfig(),
- TABLE_NAME,
- KEY_NAME,
- VAL_NAME,
+ tableName,
+ keyName,
+ valName,
tsColumn,
- FILTER_COLUMN + "='1'",
+ JdbcCacheGenerator.toDoublyQuotedEscapedIdentifier(FILTER_COLUMN) +
"='1'",
new Period(0),
null,
new JdbcAccessSecurityConfig()
@@ -429,9 +445,9 @@ public class JdbcExtractionNamespaceTest
final JdbcAccessSecurityConfig securityConfig = new
JdbcAccessSecurityConfig();
final JdbcExtractionNamespace extractionNamespace = new
JdbcExtractionNamespace(
derbyConnectorRule.getMetadataConnectorConfig(),
- TABLE_NAME,
- KEY_NAME,
- VAL_NAME,
+ tableName,
+ keyName,
+ valName,
tsColumn,
"some filter",
new Period(10),
@@ -454,9 +470,9 @@ public class JdbcExtractionNamespaceTest
{
final JdbcExtractionNamespace extractionNamespace = new
JdbcExtractionNamespace(
derbyConnectorRule.getMetadataConnectorConfig(),
- TABLE_NAME,
- KEY_NAME,
- VAL_NAME,
+ tableName,
+ keyName,
+ valName,
tsColumn,
null,
new Period(10),
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
