ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Database Access Using Lightweight Applets
Pages: 1, 2, 3, 4

Adding Security to SqlServlet

Related Reading

Java Programming with Oracle JDBC
By Donald Bales

There are several tactics you can use to add security to SqlServlet. For example, you can have it check headers for Basic authorization, like a Web server does when security is enabled. You would then have to add functionality to SqlApplet so that it would send a properly-formatted response to the authorization header from SqlServlet. There is a good example of this in Java Servlet Programming. The problem with this method is that a Base-64-encoded userid and password are passed clear over the wire, so it's fairly easy for a malicious user to discover what userid and password are being used by using a sniffer. You could always use your own custom encoding, but there's a better approach.



You can authenticate a user with a separate login screen, and store an authentication flag in their HttpSession object. You can then check for the authentication flag before executing a SQL statement in SqlServlet. That's what I have done in the beginning of the SqlServlet's execute() method. First, I get the current session object. Next, I attempt to retrieve a String by the name of authenticated from the session object. If the authentication flag authenticated does not exist, SqlServlet returns a 503 error (forbidden) to SqlApplet. This tactic requires you to add authentication to your Web application, but prevents unauthorized access to the database through SqlServlet. It works transparently for both the generic and sub-classed use of SqlServlet.


Example 1. SqlServlet

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class SqlServlet extends HttpServlet {
  public void doGet(
   HttpServletRequest request,
   HttpServletResponse response)
   throws IOException, ServletException {
    // Moved the code to execute a SQL statement
    // to a private method: execute(...).
    // You can disable its use without being
    // sub-classed by sending a forbidden error.
    /*
    response.sendError(
     HttpServletResponse.SC_FORBIDDEN);
    return;
    */
    // Process the SQL statement
    String sql = request.getParameter("sql");
    if (sql != null) {
      execute(request, response, sql);
    }
    else {
      response.sendError(
       HttpServletResponse.SC_BAD_REQUEST);
    }
  }
  public void doPost(
   HttpServletRequest request,
   HttpServletResponse response)
   throws IOException, ServletException {
    doGet(request, response);
  }
  protected void execute(
   HttpServletRequest request,
   HttpServletResponse response,
   String sql)
   throws IOException, ServletException {
    // Check authentication.  If the session variable
    // "authenticated" does not exist, send a 401 error
    HttpSession session       = request.getSession();
    String      authenticated =
     (String)session.getAttribute("authenticated");
    if (authenticated == null) {
      response.sendError(
       HttpServletResponse.SC_FORBIDDEN);
      return;
    }
    // Normally, I'd never get a connection
    // for a servlet this way, but it's OK
    // for an example.
    // Load the JDBC driver
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    }
    catch (ClassNotFoundException e) {
      System.err.print(e.getMessage());
      response.sendError(
       HttpServletResponse.SC_INTERNAL_SERVER_ERROR,
       "Unable to load class " +
       "oracle.jdbc.driver.OracleDriver");
      return;
    }
    // Get a database connection
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@dssw2k01:1521:orcl",
       "scott",
       "tiger");
    }
    catch (SQLException e) {
      System.err.print(e.getMessage());
      response.sendError(
       HttpServletResponse.SC_INTERNAL_SERVER_ERROR,
       e.getMessage());
      return;
    }
    int               cols = 0;
    int               stat = 0;
    int               rows = 0;
    ResultSet         rset = null;
    ResultSetMetaData rsmd = null;
    Statement         stmt = null;
    // This StringBuffer will hold the output until
    // we're ready to send it.
    StringBuffer      data = new StringBuffer(8192);
    try {
      // Create a Statemetn object from the
      // Connection object
      stmt = conn.createStatement();
      // Execute the SQL statement.
      // The execute() method will return
      // a true if a result set is avaiable.
      if (stmt.execute(sql)) {
        // Get the result set
        rset = stmt.getResultSet();
        // Get meta data (data about the data)
        // from the result set.
        rsmd = rset.getMetaData();
        // Get the number of columns
        cols = rsmd.getColumnCount();
        // Walk the result set
        // tab delimiting the column
        // data as you go into the
        // StringBuffer, data.
        while(rset.next()) {
          rows++;
          if (rows > 1) {
            data.append("\n");
          }
          for(int col = 1;col <= cols;col++) {
            if (col > 1) {
              data.append("\t");
            }
            data.append(rset.getString(col));
          }
        }
        // Let go of the meta data object
        rsmd = null;
        // Close and let go of the result set
        rset.close();
        rset = null;
      }
      else {
        // If there's no result set
        // then the execute() method
        // returns the number of rows
        // affected by the SQL statement.
        rows = stmt.getUpdateCount();
      }
      // Close a let go of the statement
      stmt.close();
      stmt = null;
    }
    catch (SQLException e) {
      System.out.println(
       "Can't execute query: " + sql + ".");
      System.out.println(e.getMessage());
      stat = e.getErrorCode();
    }
    finally {
      // Make sure the result set
      // and statement objects
      // are close if there is a
      // SQLException.
      if (rset != null) {
        try {
          rset.close();
        }
        catch (SQLException ignore) {
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        }
        catch (SQLException ignore) {
        }
      }
    }
    // Close the connection
    try {
      conn.close();
    }
    catch (SQLException ignore) {
    }
    // Get the output stream
    PrintWriter out = response.getWriter();
    // Set the content type
    response.setContentType("text/plain");
    // Set the "custom" headers:
    // Sql-Stat returns any SQLException
    // error code.
    response.setHeader(
     "Sql-Stat",Integer.toString(stat));
    // Sql-Rows returns the number of rows
    response.setHeader(
     "Sql-Rows",Integer.toString(rows));
    // Sql-Cols returns the number of columns
    response.setHeader(
     "Sql-Cols",Integer.toString(cols));
    // Send the data
    out.print(data.toString());
    out.flush();
  }
}

Rewriting SqlApplet to Enable Sub-classing

Our dynamic query applet, SqlApplet, has two possible modifications. The first is to remove the console code from it. This is the code that existed in its init() method, which displayed the number of columns and rows, and the error code after each call to execute(). In Example 2, I've removed the code from init() and instead added a formatted call to System.out.println() to display that data in the Java console of the browser.

Pages: 1, 2, 3, 4

Next Pagearrow