SQOOP-2863 Properly escape column names for generated INSERT statements
(Jarek Jarcec Cecho via Venkat Ranganathan)
diff --git a/src/java/org/apache/sqoop/manager/ConnManager.java b/src/java/org/apache/sqoop/manager/ConnManager.java
index f98feb3..1811ce0 100644
--- a/src/java/org/apache/sqoop/manager/ConnManager.java
+++ b/src/java/org/apache/sqoop/manager/ConnManager.java
@@ -604,6 +604,21 @@
}
/**
+ * Variant of escapeColName() method that will escape whole column name array.
+ *
+ * @param colNames Column names as provided by the user, etc.
+ * @return
+ */
+ public String [] escapeColNames(String ...colNames) {
+ String [] escaped = new String[colNames.length];
+ int i = 0;
+ for(String colName : colNames) {
+ escaped[i++] = escapeColName(colName);
+ }
+ return escaped;
+ }
+
+ /**
* When using a table name in a generated SQL query, how (if at all)
* should we escape that column name? e.g., a table named "table"
* may need to be quoted with backtiks: "`table`".
diff --git a/src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java b/src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
index 2459698..f8594c7 100644
--- a/src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
+++ b/src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
@@ -91,7 +91,7 @@
DBOutputFormat.setOutput(
job,
mgr.escapeTableName(procedureName),
- colNames);
+ mgr.escapeColNames(colNames));
job.setOutputFormatClass(getOutputFormatClass());
job.getConfiguration().set(SQOOP_EXPORT_TABLE_CLASS_KEY, tableClassName);
diff --git a/src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java b/src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
index 78df33c..626119b 100644
--- a/src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
+++ b/src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
@@ -172,9 +172,9 @@
}
if (mgr.escapeTableNameOnExport()) {
- DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), colNames);
+ DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), mgr.escapeColNames(colNames));
} else {
- DBOutputFormat.setOutput(job, tableName, colNames);
+ DBOutputFormat.setOutput(job, tableName, mgr.escapeColNames(colNames));
}
job.setOutputFormatClass(getOutputFormatClass());
diff --git a/src/java/org/apache/sqoop/mapreduce/JdbcUpdateExportJob.java b/src/java/org/apache/sqoop/mapreduce/JdbcUpdateExportJob.java
index 8fa420e..c85602c 100644
--- a/src/java/org/apache/sqoop/mapreduce/JdbcUpdateExportJob.java
+++ b/src/java/org/apache/sqoop/mapreduce/JdbcUpdateExportJob.java
@@ -134,8 +134,7 @@
outColNames[j++] = colNames[i];
}
}
- DBOutputFormat.setOutput(job,
- mgr.escapeTableName(tableName), outColNames);
+ DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), mgr.escapeColNames(outColNames));
job.setOutputFormatClass(getOutputFormatClass());
job.getConfiguration().set(SQOOP_EXPORT_TABLE_CLASS_KEY, tableClassName);
diff --git a/src/java/org/apache/sqoop/mapreduce/JdbcUpsertExportJob.java b/src/java/org/apache/sqoop/mapreduce/JdbcUpsertExportJob.java
index 0a9bf7f..8e9d1b5 100644
--- a/src/java/org/apache/sqoop/mapreduce/JdbcUpsertExportJob.java
+++ b/src/java/org/apache/sqoop/mapreduce/JdbcUpsertExportJob.java
@@ -74,7 +74,7 @@
throw new IOException(
"Export column names could not be determined for " + tableName);
}
- DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), colNames);
+ DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), mgr.escapeColNames(colNames));
String updateKeyColumns = options.getUpdateKeyCol();
if (null == updateKeyColumns) {
diff --git a/src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java b/src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java
index 117cc3f..b035d86 100644
--- a/src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java
+++ b/src/java/org/apache/sqoop/mapreduce/netezza/NetezzaExternalTableExportJob.java
@@ -129,9 +129,9 @@
}
if (mgr.escapeTableNameOnExport()) {
- DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), colNames);
+ DBOutputFormat.setOutput(job, mgr.escapeTableName(tableName), mgr.escapeColNames(colNames));
} else {
- DBOutputFormat.setOutput(job, tableName, colNames);
+ DBOutputFormat.setOutput(job, tableName, mgr.escapeColNames(colNames));
}
job.setOutputFormatClass(getOutputFormatClass());
diff --git a/src/test/com/cloudera/sqoop/TestAvroExport.java b/src/test/com/cloudera/sqoop/TestAvroExport.java
index 137a6e1..f91cd48 100644
--- a/src/test/com/cloudera/sqoop/TestAvroExport.java
+++ b/src/test/com/cloudera/sqoop/TestAvroExport.java
@@ -234,11 +234,11 @@
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ");
sb.append(getTableName());
- sb.append(" (id INT NOT NULL PRIMARY KEY, msg VARCHAR(64)");
+ sb.append(" (\"id\" INT NOT NULL PRIMARY KEY, \"msg\" VARCHAR(64)");
int colNum = 0;
for (ColumnGenerator gen : extraColumns) {
if (gen.getColumnType() != null) {
- sb.append(", " + forIdx(colNum++) + " " + gen.getColumnType());
+ sb.append(", \"" + forIdx(colNum++) + "\" " + gen.getColumnType());
}
}
sb.append(")");
@@ -262,7 +262,7 @@
LOG.info("Verifying column " + colName + " has value " + expectedVal);
PreparedStatement statement = conn.prepareStatement(
- "SELECT " + colName + " FROM " + getTableName() + " WHERE id = " + id,
+ "SELECT \"" + colName + "\" FROM " + getTableName() + " WHERE \"id\" = " + id,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
Object actualVal = null;
try {
diff --git a/src/test/com/cloudera/sqoop/TestExport.java b/src/test/com/cloudera/sqoop/TestExport.java
index 0b650af..c4c264d 100644
--- a/src/test/com/cloudera/sqoop/TestExport.java
+++ b/src/test/com/cloudera/sqoop/TestExport.java
@@ -1,4 +1,5 @@
/**
+/**
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
@@ -279,10 +280,10 @@
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ");
sb.append(getTableName());
- sb.append(" (id INT NOT NULL PRIMARY KEY, msg VARCHAR(64)");
+ sb.append(" (\"id\" INT NOT NULL PRIMARY KEY, \"msg\" VARCHAR(64)");
int colNum = 0;
for (ColumnGenerator gen : extraColumns) {
- sb.append(", " + forIdx(colNum++) + " " + gen.getType());
+ sb.append(", \"" + forIdx(colNum++) + "\" " + gen.getType());
}
sb.append(")");
@@ -325,10 +326,10 @@
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ");
sb.append(stageTableName);
- sb.append(" (id INT NOT NULL PRIMARY KEY, msg VARCHAR(64)");
+ sb.append(" (\"id\" INT NOT NULL PRIMARY KEY, \"msg\" VARCHAR(64)");
int colNum = 0;
for (ColumnGenerator gen : extraColumns) {
- sb.append(", " + forIdx(colNum++) + " " + gen.getType());
+ sb.append(", \"" + forIdx(colNum++) + "\" " + gen.getType());
}
sb.append(")");
@@ -362,7 +363,7 @@
LOG.info("Verifying column " + colName + " has value " + expectedVal);
PreparedStatement statement = conn.prepareStatement(
- "SELECT " + colName + " FROM " + getTableName() + " WHERE id = " + id,
+ "SELECT \"" + colName + "\" FROM " + getTableName() + " WHERE \"id\" = " + id,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
String actualVal = null;
try {
diff --git a/src/test/com/cloudera/sqoop/TestParquetExport.java b/src/test/com/cloudera/sqoop/TestParquetExport.java
index 86b40fb..b938bf8 100644
--- a/src/test/com/cloudera/sqoop/TestParquetExport.java
+++ b/src/test/com/cloudera/sqoop/TestParquetExport.java
@@ -213,11 +213,11 @@
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ");
sb.append(getTableName());
- sb.append(" (id INT NOT NULL PRIMARY KEY, msg VARCHAR(64)");
+ sb.append(" (\"id\" INT NOT NULL PRIMARY KEY, \"msg\" VARCHAR(64)");
int colNum = 0;
for (ColumnGenerator gen : extraColumns) {
if (gen.getColumnType() != null) {
- sb.append(", " + forIdx(colNum++) + " " + gen.getColumnType());
+ sb.append(", \"" + forIdx(colNum++) + "\" " + gen.getColumnType());
}
}
sb.append(")");
@@ -241,7 +241,7 @@
LOG.info("Verifying column " + colName + " has value " + expectedVal);
PreparedStatement statement = conn.prepareStatement(
- "SELECT " + colName + " FROM " + getTableName() + " WHERE id = " + id,
+ "SELECT \"" + colName + "\" FROM " + getTableName() + " WHERE \"id\" = " + id,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
Object actualVal = null;
try {
diff --git a/src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java b/src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
index 9a6e8da..ed0dc31 100644
--- a/src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
+++ b/src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
@@ -173,7 +173,7 @@
/** @return the minimum 'id' value in the table */
protected int getMinRowId(Connection conn) throws SQLException {
PreparedStatement statement = conn.prepareStatement(
- "SELECT MIN(id) FROM " + getTableName(),
+ "SELECT MIN(\"id\") FROM " + getTableName(),
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
int minVal = 0;
try {
@@ -194,7 +194,7 @@
/** @return the maximum 'id' value in the table */
protected int getMaxRowId(Connection conn) throws SQLException {
PreparedStatement statement = conn.prepareStatement(
- "SELECT MAX(id) FROM " + getTableName(),
+ "SELECT MAX(\"id\") FROM " + getTableName(),
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
int maxVal = 0;
try {
@@ -266,8 +266,8 @@
assertEquals("Maximum row had invalid id", expectedNumRecords - 1, maxVal);
// Check that the string values associated with these points match up.
- statement = conn.prepareStatement("SELECT msg FROM " + getTableName()
- + " WHERE id = " + minVal,
+ statement = conn.prepareStatement("SELECT \"msg\" FROM " + getTableName()
+ + " WHERE \"id\" = " + minVal,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
String minMsg = "";
try {
@@ -285,8 +285,8 @@
assertEquals("Invalid msg field for min value", getMsgPrefix() + minVal,
minMsg);
- statement = conn.prepareStatement("SELECT msg FROM " + getTableName()
- + " WHERE id = " + maxVal,
+ statement = conn.prepareStatement("SELECT \"msg\" FROM " + getTableName()
+ + " WHERE \"id\" = " + maxVal,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
String maxMsg = "";
try {
diff --git a/src/test/org/apache/sqoop/TestExportUsingProcedure.java b/src/test/org/apache/sqoop/TestExportUsingProcedure.java
index 98ebf3c..6509124 100644
--- a/src/test/org/apache/sqoop/TestExportUsingProcedure.java
+++ b/src/test/org/apache/sqoop/TestExportUsingProcedure.java
@@ -235,10 +235,11 @@
StringBuilder sql = new StringBuilder("insert into ");
sql.append(instanceForProcedure.getTableName());
- sql.append("(id, msg");
+ sql.append("(\"id\", \"msg\"");
for (int i = 0; i < instanceForProcedure.names.length; ++i) {
- sql.append(", ");
+ sql.append(", \"");
sql.append(instanceForProcedure.names[i]);
+ sql.append("\"");
}
sql.append(") values (");
for (int i = 0; i < instanceForProcedure.names.length + 2; ++i) {