I'm not sure that you can do this with an applet only, the sandbox for the
applet would probaly stop it from working. YOu may need to make this service
available via a servlet and communicate with the servlet via sockets

chanoch

-----Original Message-----
From: Ryatt, Harry [mailto:Harry.Ryatt@n...]
Sent: 06 February 2001 09:42
To: Professional Java
Subject: [pro_java] JDBC Applet security issues


Can anyone help me with applet security issues that need to be addressed
when trying to connect to a database through a JDBC driver ? (I posted this
problem on the java_database digest prior to this and got no replies so I am
hoping that I can get some help from a pro - I am a beginner with only 6
months java experience stuck with a difficult problem). 

I have tried a simple applet (code below) to try to connect to our Reality
database via a JDBC driver that is specially written for the Reality
database. The applet seems to stall completely when I run in Internet
Explorer 5.5 or Netscape 4.0 when the getConnection(String) method is called
and any exceptions that might have been thrown do not show up on these
browsers. 

However, if I run this applet through the Kawa 5.0  debugger in
appletviewer, I do get the following exceptions passed to the Kawa's debug
screen:

Running applet with breakpoint on
Class.forName("com.northgateis.reality.realsql.RealSQLDriver"); (see code
below)

Fatal exception: java.net.SocketException: Connection reset by peer:
JVM_recv in socket input stream read


Running applet with no breakpoint gives the following error:
Security Exception access denied (java.util.PropertyPermission * read,write)

I have provided the code below.

Thank you in advance.

Harry Ryatt

Notes: 

(i) The applet and browsers are being run on the same machine, and the
database also resides on the same machine at the moment,  although, later I
will need to try these components on different machines to make the applet
worthwhile.

(ii) Reality is the name of the database product (like Oracle).

(iii) For the variable connect_string in the code below:

realsql   	= the subprotocol for the Reality JDBC driver
hryattpc2	= machine name
1203		= port number
jdbctestdb	= database name

CODE:

//<APPLET CODE="TestApplet.class" WIDTH=500 HEIGHT=450></APPLET>
// Import the JDBC classes. 
import com.northgateis.reality.realsql.*;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.lang.Class;

// Import the java classes used in applets
import java.awt.*;
import java.io.*;
import java.util.*;
import java.applet.*;

public class TestApplet extends Applet
{
  // The JDBC driver to load   
  String driver_class = "com.northgateis.reality.realsql.RealSQLDriver";  
  String user = "hryatt";
  String password = "sqa123";
  String account = "NWINDS";
  String accountpwd = "";
  String logLevel= "";
  String logOptions= "";
  String logModules= "";
  String minServerVer= "";
  
  String connect_string = "jdbc:realsql://hryattpc2:1203/jdbctestdb" 
  						  + ";user=" + user +
",password=" + password + ",account="  + account + ",accountpwd="  +
accountpwd; 
						  + ",logLevel=" + logLevel
+ ",logOptions=" + logOptions + ",logModules=" + logModules +
",minServerVer=" + minServerVer;
  
  // The SQL query string
  static final String query = "select * from EMP";
  
  // The button to push for executing the query
  Button execute_button;

  // The place where to dump the query result
  static TextArea output;

  // The connection to the database
  Connection conn;
  
  //Properties prop = new Properties();
  String[] arr 	= new 	String[500];
		
  
  //
**************************************************************************
  // *init
*
  // *Create the simple user interface during the initialization of the
applet*
  //
**************************************************************************
  public void init ()
  {
    this.setLayout (new BorderLayout());
    Panel p = new Panel();
    p.setLayout (new FlowLayout (FlowLayout.LEFT));
    // Change the name for the button
    execute_button = new Button("Run SQL");
    p.add(execute_button);
    this.add("North", p);
    output = new TextArea(10, 60);
    this.add("Center", output);
  }
  
  // ********************************** 
  // * action                         *
  // * If the User presses the button *
  // **********************************
  public boolean action (Event ev, Object arg)
  {
    if (ev.target == execute_button)
    {
      try
      {	  	
	  	 Properties prop = System.getProperties();
		 Enumeration pn = prop.propertyNames();
		 while (pn.hasMoreElements()) 
		 {
         	System.out.println((String)pn.nextElement());
     	 }
 		
		 
		 
		 
         output.appendText("Start.\n");
         // Load the JDBC driver
         output.appendText ("Loading JDBC driver " + driver_class + "\n");

		 		 	 
         Class.forName("com.northgateis.reality.realsql.RealSQLDriver");
		 output.appendText("Driver registration complete." + "\n");

         // Connect to the databse
         output.appendText("Connecting to " + connect_string + "\n");
         Connection conn = DriverManager.getConnection(connect_string);
		 
         output.appendText("Connected\n");
  
         // Create a statement
         Statement stmt = conn.createStatement();

         // Execute the query
         output.appendText ("Executing query " + query + "\n");
         ResultSet rset = stmt.executeQuery(query);

         // Write the result set
         dispResult(rset);

         // Write the final text
         output.appendText("done.\n");

         // Close the connection to the database and clean up memory
         rset.close();
         stmt.close();
         conn.close();
      }
	    
      // Catch the SQL errors
      catch(SQLException e) {
      	output.appendText("\n"+"**** SQL Exception caught ****"+"\n");
        while(e != null) {
                output.appendText ("SQL State :" + e.getSQLState() +"\n");
                output.appendText ("Message   :" + e.getMessage());
                output.appendText ("Error Code:" + e.getErrorCode() +"\n");
                output.appendText("\n");
                 
                // Goto next exception
                e = e.getNextException ();
        }
      }
	  
	  //Catch Applet Security Exceptions
	  catch(SecurityException e) 
	  {
	  		output.appendText("\n" + "Security Exception " +
e.getMessage());
	  }
	  
      // Catch the other Java errors
      catch(java.lang.Exception e) {
      	// Write the error message if it excist
        output.appendText(e.getMessage () + "\n");
      }
	  
      // Quit the procedure
      return true;
	  
    }   
    else
    	return false;
  }
  
  // ******************************************************
  // * dispResult                                         *
  // * Display all the columns and rows of the result set *
  // ******************************************************
  private static void dispResult(ResultSet rs) throws SQLException 
  {
          int i;

          // Get the ReusltSetMetaData. This is needed for the collumn desc.
          ResultSetMetaData rsmd = rs.getMetaData();

          // Get the number of collumns
          int numCols = rsmd.getColumnCount();

          // Display the column headings
          for (i=1; i<=numCols; i++) {
             if (i>1) output.appendText (", ");
             output.appendText(rsmd.getColumnLabel(i));
          }
          output.appendText("\n"+"\n");

          // Display data, fetching until end of result set
          boolean more = rs.next();
          while (more) {
            //Loop through each column, getting the data and display them
            for(i=1; i<=numCols; i++) {
               if (i>1) output.appendText(",");
               output.appendText(rs.getString(i));
            }
            output.appendText("\n");

            // Fetch next result set 
            more = rs.next();
          }
}
}