Database Access Using Lightweight Applets
Pages: 1, 2, 3, 4
Adding Security to SqlServlet
|
Related Reading 
Java Programming with Oracle JDBC |
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.
