Java: Read and Write Encrypted Data to a Database

I am taking another break from my RCP application to focus on an issue that was asked in one of the BIRT newsgroups. The basic question was, how can a user retrieve an encrypted value from a database, decrypt it, and display in a report. While many database packages provide encryption packages, I prefer to use client side decryption routines so that decrypted values are not transmitted over the wire. First part of this is to get an encrypted value into the database using Oracle JDBC driver by using prepared statements, and retrieve those values and decrypt. The following example will do just that. I am using a TripleDES class as demonstrated in David Flanagans Java Examples in a Nutshell. The below example will simply read a key from an already generated key located at C:\Eclipse\Workspace\TripleDES, encrypt the string “This is a test String”, insert into a database, read from a database, decrypt and output to the console. I have also already added the Oracle JDBC driver to my classpath.

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.spec.InvalidKeySpecException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.SecretKey;

public class WriteValueToDatabase
{
  public static void main(final String[] args)
  {
      try
      {
          //variable declarations, file where the key is located, and the input and output streams
          final File f = new File("/eclipse/workspace/TripleDES/myKeyFile.txt");
          final ByteArrayOutputStream out = new ByteArrayOutputStream();
          ByteArrayInputStream in = new ByteArrayInputStream("This is a test String".getBytes());

          //handle the database settings
          Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

          //Instantiate the OracleDriver
          final Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.110:1521:birt", "digiassn", "r00s7er");
          //Statement for reading from the database
          final Statement stmt = con.createStatement();
          //Prepared statement for writing to the database. We need this since the field is a raw field
          final PreparedStatement pset = con.prepareStatement("insert into encryptTest (encrypted) values (?)");

          //Read the secret key from the file and encrypt the string created as a stream above
          final SecretKey key = TripleDES.readKey(f);
          TripleDES.encrypt(key, in, out);

          //Here, insert the encrypted value into the database
          pset.setBytes(1, out.toByteArray());
          pset.executeUpdate();

          //Now, read the result back from the database
          final ResultSet set = stmt.executeQuery("select * from encryptTest");

          //Cycle through the resultset
          while (set.next())
          {
              //REuse my input stream by casting the stream read from the resultset
              in = (ByteArrayInputStream) set.getBinaryStream(1);

              //Output the value read from the database
              System.out.print("Value read from database: ");
              TripleDES.decrypt(key, in, System.out);
              System.out.println();
          }
        
          pset.close();
          set.close();
          stmt.close();
          con.close();
          //Auto generated catch from Eclipse
      } catch (final InvalidKeyException e)
      {
          e.printStackTrace();
      } catch (final NoSuchAlgorithmException e)
      {
          e.printStackTrace();
      } catch (final InvalidKeySpecException e)
      {
          e.printStackTrace();
      } catch (final NoSuchPaddingException e)
      {
          e.printStackTrace();
      } catch (final InstantiationException e)
      {
          e.printStackTrace();
      } catch (final IllegalAccessException e)
      {
          e.printStackTrace();
      } catch (final ClassNotFoundException e)
      {
          e.printStackTrace();
      } catch (final SQLException e)
      {
          e.printStackTrace();
      } catch (final IOException e)
      {
          e.printStackTrace();
      } catch (final BadPaddingException e)
      {
          e.printStackTrace();
      } catch (final IllegalBlockSizeException e)
      {
          e.printStackTrace();
      }
  }

}