db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r739830 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/load/ExportResultSetForObject.java engine/org/apache/derby/impl/load/Import.java testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportTest.java
Date Sun, 01 Feb 2009 20:44:07 GMT
Author: kahatlen
Date: Sun Feb  1 20:44:06 2009
New Revision: 739830

URL: http://svn.apache.org/viewvc?rev=739830&view=rev
Log:
DERBY-4042: org.apache.derby.impl.load.Import needs to escape single quotes

Made sure that the export and import procedures properly quoted string
literals and SQL identifiers when constructing internal SQL
statements. Achieved by using the helper methods in StringUtil and
IdUtil.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java?rev=739830&r1=739829&r2=739830&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/ExportResultSetForObject.java
Sun Feb  1 20:44:06 2009
@@ -27,6 +27,7 @@
 import java.sql.ResultSetMetaData;
 import java.sql.DatabaseMetaData;
 import java.sql.SQLException;
+import org.apache.derby.iapi.util.IdUtil;
 
 //uses the passed connection and table/view name to make the resultset on
 //that entity. If the entity to be exported has non-sql types in it, an
@@ -64,9 +65,8 @@
 			// undelimited names are passed in upper case, because that is
 			// the form database stores them. 
 			
-			this.selectQuery = "select * from " + 
-				(schemaName == null ? "\"" + tableName + "\"" : 
-				 "\"" + schemaName + "\"" + "." + "\"" + tableName + "\""); 
+			this.selectQuery = "select * from " +
+                    IdUtil.mkQualifiedName(schemaName, tableName);
 		}
         else
 		{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java?rev=739830&r1=739829&r2=739830&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/load/Import.java Sun Feb  1 20:44:06
2009
@@ -21,19 +21,17 @@
 
 package org.apache.derby.impl.load;
 
-import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.sql.SQLWarning;
 import java.sql.Statement;
 import java.sql.PreparedStatement;
 import java.sql.Connection;
-import java.sql.ResultSetMetaData;
-import java.sql.DatabaseMetaData;
 import java.util.*;
 
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.error.PublicAPI;
+import org.apache.derby.iapi.util.IdUtil;
+import org.apache.derby.iapi.util.StringUtil;
 
 /**
  * This class implements import of data from a URL into a table.
@@ -206,32 +204,22 @@
             ColumnInfo columnInfo = new ColumnInfo(connection , schemaName ,
                                                    tableName, insertColumnList, 
                                                    columnIndexes, COLUMNNAMEPREFIX);
-            
-            /* special handling of single quote delimiters
-             * Single quote should be writeen with an extra quote otherwise sql will
-             * throw syntac error.
-             * i.e  to recognize a quote  it has to be appended with extra  quote ('')
-             */
-            if(characterDelimiter!=null && characterDelimiter.equals("'"))
-                characterDelimiter = "''";
-            if(columnDelimiter !=null && columnDelimiter.equals("'"))
-                columnDelimiter = "''";
-            
-            
+
             StringBuffer sb = new StringBuffer("new ");
             sb.append("org.apache.derby.impl.load.Import");
-            sb.append("(") ; 
-            sb.append(	(inputFileName !=null ? "'" + inputFileName + "'" : null));
+            sb.append("(") ;
+            sb.append(quoteStringArgument(inputFileName));
             sb.append(",") ;
-            sb.append(	(columnDelimiter !=null ? "'" + columnDelimiter + "'" : null));
+            sb.append(quoteStringArgument(columnDelimiter));
             sb.append(",") ;
-            sb.append(	(characterDelimiter !=null ? "'" + characterDelimiter + "'" : null));
+            sb.append(quoteStringArgument(characterDelimiter));
             sb.append(",") ;
-            sb.append(	(codeset !=null ? "'" + codeset + "'" : null));
+            sb.append(quoteStringArgument(codeset));
             sb.append(", ");
             sb.append( columnInfo.getExpectedNumberOfColumnsInFile());
             sb.append(", ");
-            sb.append( "'" + columnInfo.getExpectedVtiColumnTypesAsString() + "'");
+            sb.append(quoteStringArgument(
+                    columnInfo.getExpectedVtiColumnTypesAsString()));
             sb.append(", ");
             sb.append(lobsInExtFile);
             sb.append(", ");
@@ -250,8 +238,7 @@
             // case, because all undelimited names are stored in the upper case 
             // in the database. 
             
-            String entityName = (schemaName == null ? "\""+ tableName + "\"" : 
-                                 "\"" + schemaName + "\"" + "." + "\"" + tableName + "\"");

+            String entityName = IdUtil.mkQualifiedName(schemaName, tableName);
             
             String insertModeValue;
             if(replace > 0)
@@ -334,5 +321,20 @@
 
         return PublicAPI.wrapStandardException(se);
     }
-    
+
+    /**
+     * Quote a string argument so that it can be used as a literal in an
+     * SQL statement. If the string argument is {@code null} an SQL NULL token
+     * is returned.
+     *
+     * @param string a string or {@code null}
+     * @return the string in quotes and with proper escape sequences for
+     * special characters, or "NULL" if the string is {@code null}
+     */
+    private static String quoteStringArgument(String string) {
+        if (string == null) {
+            return "NULL";
+        }
+        return StringUtil.quoteStringLiteral(string);
+    }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportTest.java?rev=739830&r1=739829&r2=739830&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportTest.java
Sun Feb  1 20:44:06 2009
@@ -21,8 +21,6 @@
 
 package org.apache.derbyTesting.functionTests.tests.tools;
 
-import java.io.File;
-import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.Statement;
@@ -90,8 +88,7 @@
 	
 	public void testImportFromNonExistantFile() {
 		try {
-			Connection c = getConnection();
-			doImport(c, "Z" , "T1" , null , null , null, 0);
+            doImport("Z", null, "T1", null, null, null, 0);
             fail();
 		} catch (SQLException e) {
 			assertSQLState("XIE04", e);
@@ -100,8 +97,7 @@
 	
 	public void testNullDataFile() {
 		try {
-			Connection c = getConnection();
-			doImport(c, null, "T1" , null , null, null, 0);
+            doImport(null, null, "T1", null, null, null, 0);
             fail();
 		} catch (SQLException e) {
 			assertSQLState("XIE05", e);
@@ -109,55 +105,46 @@
 	}
 	
 	public void testEmptyTable() throws SQLException {
-		Connection c = getConnection();
-		doImportAndExport(c, "T1", null, null , null);
+        doImportAndExport(null, "T1", null, null, null);
 	}
 
 	public void testEmptyTableWithDelimitedFormat() throws SQLException {
-		Connection c = getConnection();
-		doImportAndExport(c, "T1", null, null , "8859_1");
+        doImportAndExport(null, "T1", null, null, "8859_1");
 	}
 
 	public void testEmptyTableWithFieldCharDelimiters() throws SQLException {
-		Connection c = getConnection();
-		doImportAndExport(c, "T1", "\t", "|" , "8859_1");
+        doImportAndExport(null, "T1", "\t", "|", "8859_1");
 	}
 	
 	public void testWithDefaultOptions() throws Exception {
-		Connection c = getConnection();
 		resetTables();
-		doImportAndExport(c, "T1", null, null, null);
+        doImportAndExport(null, "T1", null, null, null);
 	}
 	
 	public void testWithCodeset() throws Exception {
-		Connection c = getConnection();
 		resetTables();
-		doImportAndExport(c, "T1", null, null , "8859_1");
+        doImportAndExport(null, "T1", null, null, "8859_1");
 	}
 
 	public void testDelimiterAndCodeset() throws Exception {
-		Connection c = getConnection();
 		resetTables();
-		doImportAndExport(c, "T1", "\t", "|", "8859_1");
+        doImportAndExport(null, "T1", "\t", "|", "8859_1");
 	}
 	
 	public void testSpecialDelimitersAndCodeset() throws Exception {
-		Connection c = getConnection();
 		resetTables();
-		doImportAndExport(c, "T1", "%", "&", "Cp1252");
+        doImportAndExport(null, "T1", "%", "&", "Cp1252");
 	}
 
 	public void testSpecialDelimitersAndUTF16() throws Exception {
-		Connection c = getConnection();
 		resetTables();
-		doImportAndExport(c, "T1", "%", "&", "UTF-16");
+        doImportAndExport(null, "T1", "%", "&", "UTF-16");
 	}
 	
 	public void testInvalidEncoding() throws Exception {
-		Connection c = getConnection();
 		resetTables();
 		try {
-		    doImportAndExport(c, "T1", "^", "#", "INAVALID ENCODING");
+            doImportAndExport(null, "T1", "^", "#", "INAVALID ENCODING");
             fail();
 		} catch (SQLException e) {
 			assertSQLState("XIE0I", e);
@@ -165,22 +152,49 @@
 	}
 	
 	public void testEarlyEndOfFile() throws Exception {
-		Connection c = getConnection();
 		try {
-			doImportFromFile(c, "extin/TwoLineBadEOF.dat" , "T4" , null , null , "US-ASCII", 0);
+            doImportFromFile("extin/TwoLineBadEOF.dat", null, "T4",
+                             null, null, "US-ASCII", 0);
             fail();
 		} catch (SQLException e) {
 			assertSQLState("XIE0E", e);
 		}
 	}
-	
-	private void doImport(Connection c, String fromTable, String toTable, 
+
+    /**
+     * Test that quotes in the arguments to the export and import procedures
+     * are handled properly (DERBY-4042).
+     */
+    public void testQuotesInArguments() throws Exception {
+        resetTables();
+
+        // Create schema names and table names containing both single quotes
+        // and double quotes to expose bugs both for internally generated
+        // string literals (enclosed in single quotes) and SQL identifiers
+        // (enclosed in double quotes). This will also indirectly test that
+        // the export/import system procedures handle those characters in
+        // file names (which they didn't do very well before the fix for
+        // DERBY-4042), as doExport() and doImportAndVerify() use a file name
+        // derived from the table name.
+        final String schema = "s'\"";
+        final String table = "t'\"";
+        final String escapedName = JDBC.escape(schema, table);
+
+        Statement s = createStatement();
+        s.execute("create table " + escapedName +
+                  " as select * from T1 with no data");
+        s.execute("insert into " + escapedName + " select * from t1");
+
+        doImportAndExport(schema, table, "'", "\"", "US-ASCII");
+    }
+
+    private void doImport(String fromTable, String toSchema, String toTable,
 			 String colDel, String charDel , 
 			 String codeset, int replace) throws SQLException 
     {
 		String impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)";
-		PreparedStatement ps = c.prepareStatement(impsql);
-		ps.setString(1 , "APP");
+        PreparedStatement ps = prepareStatement(impsql);
+        ps.setString(1, toSchema);
 		ps.setString(2, toTable);
 		ps.setString(3, (fromTable==null ?  fromTable : "extinout/" + fromTable + ".dat" ));
 		ps.setString(4 , colDel);
@@ -191,13 +205,14 @@
 		ps.close();
     }
 	
-	private void doImportFromFile(Connection c, String fileName, String toTable, 
+    private void doImportFromFile(
+             String fileName, String toSchema, String toTable,
 			 String colDel, String charDel , 
 			 String codeset, int replace) throws Exception 
     {
 		String impsql = "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (? , ? , ? , ?, ? , ?, ?)";
-		PreparedStatement ps = c.prepareStatement(impsql);
-		ps.setString(1 , "APP");
+        PreparedStatement ps = prepareStatement(impsql);
+        ps.setString(1, toSchema);
 		ps.setString(2, toTable);
 		ps.setString(3, fileName);
 		ps.setString(4 , colDel);
@@ -209,25 +224,26 @@
 
     }
 
-	private void doImportAndExport(Connection c, String fromTable, String colDel , 
+	private void doImportAndExport(
+              String fromSchema, String fromTable, String colDel,
 			  String charDel, 
 			  String codeset) throws SQLException 
     {
-		doExport(c, fromTable , colDel , charDel , codeset);
-		doImportAndVerify(c, fromTable, colDel , charDel, codeset,  0);
+        doExport(fromSchema, fromTable, colDel, charDel, codeset);
+        doImportAndVerify(fromSchema, fromTable, colDel, charDel, codeset, 0);
         // also test with replace
-		doImportAndVerify(c, fromTable, colDel , charDel, codeset,  1);
+        doImportAndVerify(fromSchema, fromTable, colDel, charDel, codeset, 1);
     }
 	
-	private void doExport(Connection c, String fromTable, String colDel , 
+    private void doExport(String fromSchema, String fromTable, String colDel,
 			 String charDel, 
 			 String codeset) throws SQLException 
 	{
 		 //DERBY-2925: need to delete existing files first.
         	 SupportFilesSetup.deleteFile("extinout/" + fromTable + ".dat");
 		 String expsql = "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)";
-		 PreparedStatement ps = c.prepareStatement(expsql);
-		 ps.setString(1 , "APP");
+         PreparedStatement ps = prepareStatement(expsql);
+         ps.setString(1, fromSchema);
 		 ps.setString(2, fromTable);
 		 ps.setString(3, (fromTable==null ?  fromTable : "extinout/" + fromTable + ".dat" ));
 		 ps.setString(4 , colDel);
@@ -245,15 +261,19 @@
 	 * of the two files is then made to verify that the data has been
 	 * gone through the import/export process intact.
 	 */
-	private void doImportAndVerify(Connection c, String fromTable,  String colDel, 
+	private void doImportAndVerify(
+              String fromSchema, String fromTable, String colDel,
 			  String charDel , String codeset, 
 			  int replace) throws SQLException 
     {
 
-		doImport(c, fromTable , "T2" , colDel , charDel , codeset , replace);
+        doImport(fromTable, null, "T2", colDel, charDel, codeset, replace);
 
-		Statement stmt = c.createStatement();
-		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + fromTable);
+        Statement stmt = createStatement();
+        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " +
+                ((fromSchema == null) ?
+                    JDBC.escape(fromTable) :
+                    JDBC.escape(fromSchema, fromTable)));
 		rs.next();
 		int numberOfRowsInT1 = rs.getInt(1);
 		rs.close();
@@ -264,7 +284,7 @@
 		stmt.close();
 		assertEquals(numberOfRowsInT1, numberOfRowsInT2);
 
-		doExport(c, "T2" , colDel , charDel , codeset);
+		doExport(null, "T2" , colDel , charDel , codeset);
 
         //check whether the  exported files from T1 and T2  are same now.
 		assertEquals(SupportFilesSetup.getReadWrite(fromTable + ".dat"),



Mime
View raw message