You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2008/05/26 12:05:39 UTC
svn commit: r660153 - in /db/derby/code/trunk/java:
engine/org/apache/derby/catalog/ engine/org/apache/derby/iapi/util/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/junit/
Author: kahatlen
Date: Mon May 26 03:05:38 2008
New Revision: 660153
URL: http://svn.apache.org/viewvc?rev=660153&view=rev
Log:
DERBY-3682: SYSCS_BULK_INSERT doesn't quote identifiers or strings properly
Ensure proper quoting of SQL identifiers and string literals by using
utility functions in StringUtil and IdUtil. VTITest has been updated
to test SYSCS_BULK_INSERT with a table containing single quotes and
double quotes in its name.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/IdUtil.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableVTI.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VTITest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java?rev=660153&r1=660152&r2=660153&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java Mon May 26 03:05:38 2008
@@ -723,8 +723,8 @@
throws SQLException
{
- String escapedSchema = escapeSQLIdentifier(schema);
- String escapedTableName = escapeSQLIdentifier(tablename);
+ String escapedSchema = IdUtil.normalToDelimited(schema);
+ String escapedTableName = IdUtil.normalToDelimited(tablename);
String query =
"alter table " + escapedSchema + "." + escapedTableName +
" compress" + (sequential != 0 ? " sequential" : "");
@@ -739,22 +739,6 @@
}
/**
- * Escape an SQL identifier to preserve mixed case and special characters.
- */
- private static String escapeSQLIdentifier(String identifier) {
- StringBuffer buffer = new StringBuffer(identifier.length() + 2);
- buffer.append('"');
- for (int i = 0; i < identifier.length(); i++) {
- char c = identifier.charAt(i);
- // if c is a double quote, escape it with an extra double quote
- if (c == '"') buffer.append('"');
- buffer.append(c);
- }
- buffer.append('"');
- return buffer.toString();
- }
-
- /**
* Freeze the database.
* <p>
* Call internal routine to freeze the database so that a backup
@@ -1101,8 +1085,8 @@
//Send all the other inplace compress requests to ALTER TABLE
//machinery
- String escapedSchema = escapeSQLIdentifier(schema);
- String escapedTableName = escapeSQLIdentifier(tablename);
+ String escapedSchema = IdUtil.normalToDelimited(schema);
+ String escapedTableName = IdUtil.normalToDelimited(tablename);
String query =
"alter table " + escapedSchema + "." + escapedTableName +
" compress inplace" + (purgeRows != 0 ? " purge" : "")
@@ -1585,16 +1569,26 @@
throws SQLException
{
Connection conn = getDefaultConn();
-
- String entityName = (schemaName == null ? tableName : schemaName + "." + tableName);
+
+ // Use default schema if schemaName is null. This isn't consistent
+ // with the other procedures, as they would fail if schema was null.
+ String entityName = IdUtil.normalToDelimited(tableName);
+ if (schemaName != null) {
+ entityName =
+ IdUtil.normalToDelimited(schemaName) + "." + entityName;
+ }
+
String binsertSql =
"insert into " + entityName +
" --DERBY-PROPERTIES insertMode=bulkInsert \n" +
- "select * from new " + vtiName +
+ "select * from new " + IdUtil.normalToDelimited(vtiName) +
"(" +
- "'" + schemaName + "'" + ", " +
- "'" + tableName + "'" + ", " +
- "'" + vtiArg + "'" + ")" +
+ // Ideally, we should have used parameter markers and setString(),
+ // but some of the VTIs need the parameter values when compiling
+ // the statement. Therefore, insert the strings into the SQL text.
+ StringUtil.quoteStringLiteral(schemaName) + ", " +
+ StringUtil.quoteStringLiteral(tableName) + ", " +
+ StringUtil.quoteStringLiteral(vtiArg) + ")" +
" as t";
PreparedStatement ps = conn.prepareStatement(binsertSql);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/IdUtil.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/IdUtil.java?rev=660153&r1=660152&r2=660153&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/IdUtil.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/IdUtil.java Mon May 26 03:05:38 2008
@@ -63,18 +63,7 @@
*/
public static String normalToDelimited(String id)
{
- StringBuffer quotedBuffer = new StringBuffer();
- quotedBuffer.append('\"');
-
- for (int ix = 0; ix < id.length(); ix++){
- char currentChar = id.charAt(ix);
- quotedBuffer.append(currentChar);
- if (currentChar == '\"')
- quotedBuffer.append('\"');
- }
- quotedBuffer.append('\"');
-
- return quotedBuffer.toString();
+ return StringUtil.quoteString(id, '"');
}
/**
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java?rev=660153&r1=660152&r2=660153&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/util/StringUtil.java Mon May 26 03:05:38 2008
@@ -421,7 +421,42 @@
return result;
}
+ /**
+ * Quote a string so that it can be used as an identifier or a string
+ * literal in SQL statements. Identifiers are surrounded by double quotes
+ * and string literals are surrounded by single quotes. If the string
+ * contains quote characters, they are escaped.
+ *
+ * @param source the string to quote
+ * @param quote the character to quote the string with (' or ")
+ * @return a string quoted with the specified quote character
+ * @see #quoteStringLiteral(String)
+ * @see IdUtil#normalToDelimited(String)
+ */
+ static String quoteString(String source, char quote) {
+ // Normally, the quoted string is two characters longer than the source
+ // string (because of start quote and end quote).
+ StringBuffer quoted = new StringBuffer(source.length() + 2);
+ quoted.append(quote);
+ for (int i = 0; i < source.length(); i++) {
+ char c = source.charAt(i);
+ // if the character is a quote, escape it with an extra quote
+ if (c == quote) quoted.append(quote);
+ quoted.append(c);
+ }
+ quoted.append(quote);
+ return quoted.toString();
+ }
-
+ /**
+ * Quote a string so that it can be used as a string literal in an
+ * SQL statement.
+ *
+ * @param string the string to quote
+ * @return the string surrounded by single quotes and with proper escaping
+ * of any single quotes inside the string
+ */
+ public static String quoteStringLiteral(String string) {
+ return quoteString(string, '\'');
+ }
}
-
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableVTI.java?rev=660153&r1=660152&r2=660153&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableVTI.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableVTI.java Mon May 26 03:05:38 2008
@@ -35,6 +35,7 @@
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Array;
+import org.apache.derbyTesting.junit.JDBC;
/**
This class has been adapted from org.apache.derby.vti.VTITemplate
@@ -73,7 +74,7 @@
Connection conn = DriverManager
.getConnection("jdbc:default:connection");
Statement s = conn.createStatement();
- ResultSet rs = s.executeQuery("select * from " + tableName
+ ResultSet rs = s.executeQuery("select * from " + JDBC.escape(tableName)
+ " where 1 = 0 ");
rsmd = rs.getMetaData();
rs.close();
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VTITest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VTITest.java?rev=660153&r1=660152&r2=660153&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VTITest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/VTITest.java Mon May 26 03:05:38 2008
@@ -20,6 +20,7 @@
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
@@ -33,21 +34,27 @@
* Add tests that use VTI
*/
public class VTITest extends BaseJDBCTestCase {
-
+
+ private final String tableName;
public static Test suite()
{
TestSuite suite = new TestSuite("VTITest");
// requires DriverManager support
- if (JDBC.vmSupportsJDBC2())
- suite.addTest(new VTITest("bulkInsertVtiTest"));
+ if (JDBC.vmSupportsJDBC2()) {
+ suite.addTest(new VTITest("bulkInsertVtiTest", "WAREHOUSE"));
+ // Run the same test again, but now insert into a table whose name
+ // contains single and double quote characters (DERBY-3682)
+ suite.addTest(new VTITest("bulkInsertVtiTest", "test\"'table"));
+ }
return suite;
}
- public VTITest(String name) {
+ private VTITest(String name, String tableName) {
super(name);
+ this.tableName = tableName;
}
@@ -56,7 +63,7 @@
*/
protected void setUp() throws SQLException {
Statement stmt = createStatement();
- stmt.execute("CREATE TABLE warehouse (id int)");
+ stmt.execute("CREATE TABLE " + JDBC.escape(tableName) + "(id int)");
stmt.close();
}
@@ -67,7 +74,7 @@
protected void tearDown()
throws Exception {
Statement stmt = createStatement();
- stmt.execute("DROP TABLE warehouse");
+ stmt.execute("DROP TABLE " + JDBC.escape(tableName));
stmt.close();
super.tearDown();
}
@@ -81,16 +88,17 @@
throws SQLException
{
int expectedRows = 10;
- Statement stmt = createStatement();
- stmt.execute("call SYSCS_UTIL.SYSCS_BULK_INSERT('APP','WAREHOUSE'," +
- "'org.apache.derbyTesting.functionTests.tests.lang.WarehouseVTI',"
- +"\'"+expectedRows+"')");
- stmt.close();
- stmt = createStatement();
- ResultSet rs = stmt.executeQuery("SELECT COUNT(*) from warehouse");
- rs.next();
- assertEquals(expectedRows,rs.getInt(1));
- rs.close();
- stmt.close();
+
+ CallableStatement cs =
+ prepareCall("CALL SYSCS_UTIL.SYSCS_BULK_INSERT(?, ?, ?, ?)");
+ cs.setString(1, "APP");
+ cs.setString(2, tableName);
+ cs.setString(3, WarehouseVTI.class.getName());
+ cs.setInt(4, expectedRows);
+ cs.execute();
+
+ ResultSet rs = createStatement().executeQuery(
+ "SELECT 1 FROM " + JDBC.escape(tableName));
+ JDBC.assertDrainResults(rs, expectedRows);
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=660153&r1=660152&r2=660153&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Mon May 26 03:05:38 2008
@@ -1259,7 +1259,16 @@
*/
public static String escape(String name)
{
- return "\"" + name + "\"";
+ StringBuffer buffer = new StringBuffer(name.length() + 2);
+ buffer.append('"');
+ for (int i = 0; i < name.length(); i++) {
+ char c = name.charAt(i);
+ // escape double quote characters with an extra double quote
+ if (c == '"') buffer.append('"');
+ buffer.append(c);
+ }
+ buffer.append('"');
+ return buffer.toString();
}
/**
* Escape a schama-qualified name so that it is suitable
@@ -1267,7 +1276,7 @@
*/
public static String escape(String schema, String name)
{
- return "\"" + schema + "\".\"" + name + "\"";
+ return escape(schema) + "." + escape(name);
}
/**