db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1005362 - in /db/derby/code/trunk/java: testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/ testing/org/a...
Date Thu, 07 Oct 2010 08:43:49 GMT
Author: kahatlen
Date: Thu Oct  7 08:43:49 2010
New Revision: 1005362

URL: http://svn.apache.org/viewvc?rev=1005362&view=rev
Log:
DERBY-4829: dblook fails if there is a column privilege on a table
whose name contains a quote

Properly quote strings and identifiers. Also add missing clause in the
generated GRANT statement to prevent syntax errors when copying
statements from the dblook output.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
    db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net.out
Thu Oct  7 08:43:49 2010
@@ -229,6 +229,16 @@ null
 null
 null
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+-----
 MULTI WORD NAME
 C
 1
@@ -928,6 +938,15 @@ null
 false
 <systemid>
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+-----
 APP
 "tquoteTwo
 <systemnumber>
@@ -1728,6 +1747,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+-----
 APP
 APP
 APP
@@ -1812,6 +1835,12 @@ T
 APP
 R
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+-----
 MULTI WORD NAME
 MULTI WORD NAME
 T

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/dblook_test_net_territory.out
Thu Oct  7 08:43:49 2010
@@ -229,6 +229,16 @@ null
 null
 null
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+-----
 MULTI WORD NAME
 C
 1
@@ -928,6 +938,15 @@ null
 false
 <systemid>
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+-----
 APP
 "tquoteTwo
 <systemnumber>
@@ -1728,6 +1747,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+-----
 APP
 APP
 APP
@@ -1812,6 +1835,12 @@ T
 APP
 R
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+-----
 MULTI WORD NAME
 MULTI WORD NAME
 T

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net.out
Thu Oct  7 08:43:49 2010
@@ -229,6 +229,16 @@ null
 null
 null
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+-----
 MULTI WORD NAME
 C
 1
@@ -928,6 +938,15 @@ null
 false
 <systemid>
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+-----
 APP
 "tquoteTwo
 <systemnumber>
@@ -1728,6 +1747,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+-----
 APP
 APP
 APP
@@ -1812,6 +1835,12 @@ T
 APP
 R
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+-----
 MULTI WORD NAME
 MULTI WORD NAME
 T

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/dblook_test_net_territory.out
Thu Oct  7 08:43:49 2010
@@ -229,6 +229,16 @@ null
 null
 null
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+-----
 MULTI WORD NAME
 C
 1
@@ -928,6 +938,15 @@ null
 false
 <systemid>
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+-----
 APP
 "tquoteTwo
 <systemnumber>
@@ -1728,6 +1747,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 -----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+-----
 APP
 APP
 APP
@@ -1812,6 +1835,12 @@ T
 APP
 R
 -----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+-----
 MULTI WORD NAME
 MULTI WORD NAME
 T

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out
Thu Oct  7 08:43:49 2010
@@ -222,6 +222,16 @@ null
 null
 null
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+----
 MULTI WORD NAME
 C
 1
@@ -921,6 +931,15 @@ null
 false
 <systemid>
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+----
 APP
 "tquoteTwo
 <systemnumber>
@@ -1721,6 +1740,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+----
 APP
 APP
 APP
@@ -1805,6 +1828,12 @@ T
 APP
 R
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+----
 MULTI WORD NAME
 MULTI WORD NAME
 T
@@ -2404,6 +2433,16 @@ null
 null
 null
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+----
 MULTI WORD NAME
 C
 1
@@ -3103,6 +3142,15 @@ null
 false
 <systemid>
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+----
 APP
 "tquoteTwo
 <systemnumber>
@@ -3903,6 +3951,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+----
 APP
 APP
 APP
@@ -3987,6 +4039,12 @@ T
 APP
 R
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+----
 MULTI WORD NAME
 MULTI WORD NAME
 T
@@ -5272,6 +5330,9 @@ Creating database 'wombat_new' from ddl 
 FAILED: to execute cmd from DDL script:
 create view v1 (dum, dee, dokie) as select a.d, a.c, b.p1 from t1 as a, bar.t3 as b
 Table/View 'BAR.T3' does not exist.
+FAILED: to execute cmd from DDL script:
+GRANT SELECT("'Single' and ""double"" quoted column name") ON "'Single' and ""double"" quoted
schema name"."'Single' and ""double"" quoted table name" TO "SOMEONE"
+Schema ''Single' and "double" quoted schema name' does not exist
 Dumping system tables for 'wombat_new'
 ----------------=================---------------
 System Tables for: wombat_new

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
Thu Oct  7 08:43:49 2010
@@ -222,6 +222,16 @@ null
 null
 null
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+----
 MULTI WORD NAME
 C
 1
@@ -921,6 +931,15 @@ null
 false
 <systemid>
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+----
 APP
 "tquoteTwo
 <systemnumber>
@@ -1721,6 +1740,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+----
 APP
 APP
 APP
@@ -1805,6 +1828,12 @@ T
 APP
 R
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+----
 MULTI WORD NAME
 MULTI WORD NAME
 T
@@ -2404,6 +2433,16 @@ null
 null
 null
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted column name
+1
+INTEGER
+null
+null
+<autoincval>
+null
+null
+----
 MULTI WORD NAME
 C
 1
@@ -3103,6 +3142,15 @@ null
 false
 <systemid>
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted table name
+<systemnumber>
+'Single' and "double" quoted table name
+false
+null
+false
+<systemid>
+----
 APP
 "tquoteTwo
 <systemnumber>
@@ -3903,6 +3951,10 @@ unqkay1"
 "Quoted"Schema"
 APP
 ----
+'Single' and "double" quoted schema name
+'Single' and "double" quoted schema name
+APP
+----
 APP
 APP
 APP
@@ -3987,6 +4039,12 @@ T
 APP
 R
 ----
+'Single' and "double" quoted table name
+'Single' and "double" quoted table name
+T
+'Single' and "double" quoted schema name
+R
+----
 MULTI WORD NAME
 MULTI WORD NAME
 T
@@ -5272,6 +5330,9 @@ Creating database 'wombat_new' from ddl 
 FAILED: to execute cmd from DDL script:
 create view v1 (dum, dee, dokie) as select a.d, a.c, b.p1 from t1 as a, bar.t3 as b
 Table/View 'BAR.T3' does not exist.
+FAILED: to execute cmd from DDL script:
+GRANT SELECT("'Single' and ""double"" quoted column name") ON "'Single' and ""double"" quoted
schema name"."'Single' and ""double"" quoted table name" TO "SOMEONE"
+Schema ''Single' and "double" quoted schema name' does not exist
 Dumping system tables for 'wombat_new'
 ----------------=================---------------
 System Tables for: wombat_new

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB.sql
Thu Oct  7 08:43:49 2010
@@ -133,6 +133,7 @@ create table "tquote""One" ("i""q1" int 
 create table """tquoteTwo" ("""iq1" int constraint """effkay1" references "tquote""One" ("i""q1"),
"""iq2" int constraint """ck2" check ("""iq2" > 0));
 create table "tquoteThree""" ("iq1""" int not null constraint "unqkay1""" unique, "iq2"""
int constraint "ck2""" check ("iq2""" > 4));
 create table """Quoted""Schema"""."tee""""Hee" (n char not null primary key);
+create table "'Single' and ""double"" quoted schema name"."'Single' and ""double"" quoted
table name" ("'Single' and ""double"" quoted column name" int);
 
 -- ----------------------------------------------
 -- Indexes.
@@ -196,3 +197,10 @@ grant a to b;
 grant """eve""" to b;
 grant b to whomever;
 
+-- ----------------------------------------------
+-- Column privileges
+-- ----------------------------------------------
+
+-- Test column privileges when table/schema contains special characters.
+-- (DERBY-4829)
+grant select ("'Single' and ""double"" quoted column name") on "'Single' and ""double"" quoted
schema name"."'Single' and ""double"" quoted table name" to someone;

Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java?rev=1005362&r1=1005361&r2=1005362&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
(original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java
Thu Oct  7 08:43:49 2010
@@ -22,6 +22,7 @@
 package org.apache.derby.impl.tools.dblook;
 
 import java.sql.Connection;
+import java.sql.PreparedStatement;
 import java.sql.Statement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
@@ -188,6 +189,14 @@ public class DB_GrantRevoke {
 	private static void generateColumnPrivs(ResultSet rs, Connection conn)
 		throws SQLException
 	{
+        // Statement that gets the names of the columns in a given table.
+        PreparedStatement columnStmt = conn.prepareStatement(
+            "SELECT COLUMNNUMBER, COLUMNNAME " +
+            "FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S " +
+            "WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID " +
+            "AND S.SCHEMANAME = ? AND T.TABLENAME = ? " +
+            "ORDER BY COLUMNNUMBER");
+
 		boolean firstTime = true;
 		while (rs.next()) {
 			if (firstTime) {
@@ -196,30 +205,43 @@ public class DB_GrantRevoke {
 				Logs.reportString("----------------------------------------------\n");
 			}
 
+            // Auth name will added directly to the generated DDL, so we need
+            // to quote it.
 			String authName = dblook.addQuotes
 				(dblook.expandDoubleQuotes(rs.getString(1)));
-			String schemaName = dblook.expandDoubleQuotes(rs.getString(2));
-			String tableName = dblook.expandDoubleQuotes(rs.getString(3));
 
-			if (dblook.isIgnorableSchema(schemaName))
+            // Schema name and table name are parameters to a prepared
+            // statement, so quoting is not needed.
+            String schemaName = rs.getString(2);
+            String tableName = rs.getString(3);
+
+            // isIgnorableSchema, on the other hand, expects the schema name
+            // to be quoted.
+            String schemaNameQuoted =
+                    dblook.addQuotes(dblook.expandDoubleQuotes(schemaName));
+            if (dblook.isIgnorableSchema(schemaNameQuoted)) {
 				continue;
+            }
 
 			// Create another resultSet to get column names
-			Statement stmtCols = conn.createStatement();
-			String queryCols = "SELECT COLUMNNUMBER, COLUMNNAME " +
-				"FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S " +
-				"WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID "+
-				"and T.TABLENAME = '"+tableName+"' AND SCHEMANAME = '"+schemaName +
-				"' ORDER BY COLUMNNUMBER";
-
-			ResultSet rsCols= stmtCols.executeQuery(queryCols);
-			String fullName = dblook.addQuotes(schemaName) + "." + dblook.addQuotes(tableName);
+            columnStmt.setString(1, schemaName);
+            columnStmt.setString(2, tableName);
+            ResultSet rsCols = columnStmt.executeQuery();
+
+            // The full name will be added directly to the generated GRANT
+            // statement, so it needs to be quoted.
+            String fullName = schemaNameQuoted + "." +
+                    dblook.addQuotes(dblook.expandDoubleQuotes(tableName));
 
 			Logs.writeToNewDDL(columnPrivStatement(rs, fullName, authName, rsCols));
 			Logs.writeStmtEndToNewDDL();
 			Logs.writeNewlineToNewDDL();
 			firstTime = false;
+
+            rsCols.close();
 		}
+
+        columnStmt.close();
 	}
 
 	private static String privTypeToString(String privType)
@@ -261,8 +283,11 @@ public class DB_GrantRevoke {
 				curColumn = rsCols.getInt(1);
 			}
 			colNames.append(separatorStr(addSeparator));
-			colNames.append(rsCols.getString(2));
 			addSeparator = true;
+
+            String colName = dblook.addQuotes(
+                    dblook.expandDoubleQuotes(rsCols.getString(2)));
+            colNames.append(colName);
 		}
 
 		return colNames.toString();
@@ -286,7 +311,8 @@ public class DB_GrantRevoke {
 		grantStmt.append(privTypeToString(privType));
 		grantStmt.append("(");
 		grantStmt.append(mapColumnsToNames(columns, rsCols));
-		grantStmt.append(")");
+        grantStmt.append(") ON ");
+        grantStmt.append(fullName);
 		grantStmt.append(" TO ");
 		grantStmt.append(authName);
 



Mime
View raw message