views:

882

answers:

3

I am using Oracle 9 and JDBC and would like to encyrpt a clob as it is inserted into the DB. Ideally I'd like to be able to just insert the plaintext and have it encrypted by a stored procedure:

String SQL = "INSERT INTO table (ID, VALUE) values (?, encrypt(?))";
PreparedStatement ps = connection.prepareStatement(SQL);
ps.setInt(id);
ps.setString(plaintext);
ps.executeUpdate();

The plaintext is not expected to exceed 4000 characters but encrypting makes text longer. Our current approach to encryption uses dbms_obfuscation_toolkit.DESEncrypt() but we only process varchars. Will the following work?

FUNCTION encrypt(p_clob IN CLOB) RETURN CLOB
IS
    encrypted_string        CLOB;
    v_string                CLOB;
BEGIN
  dbms_lob.createtemporary(encrypted_string, TRUE);
  v_string := p_clob;
  dbms_obfuscation_toolkit.DESEncrypt(
    input_string => v_string,
    key_string => key_string,
    encrypted_string => encrypted_string );
  RETURN UTL_RAW.CAST_TO_RAW(encrypted_string);
END;

I'm confused about the temporary clob; do I need to close it? Or am I totally off-track?

Edit: The purpose of the obfuscation is to prevent trivial access to the data. My other purpose is to obfuscate clobs in the same way that we are already obfuscating the varchar columns. The oracle sample code does not deal with clobs which is where my specific problem lies; encrypting varchars (smaller than 2000 chars) is straightforward.

+2  A: 

There is an example in Oracle Documentation:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_obtoo2.htm

You do not need to close it

DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048); 
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING 
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

    -- 1. Begin testing raw data encryption and decryption
       BEGIN
   dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
   dbms_output.put_line('> Raw input                        : ' || 
             UTL_RAW.CAST_TO_VARCHAR2(raw_input));
   BEGIN 
      dbms_obfuscation_toolkit.DESEncrypt(input => raw_input, 
               key => raw_key, encrypted_data => encrypted_raw );
      dbms_output.put_line('> encrypted hex value              : ' || 
           rawtohex(encrypted_raw));
  dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw, 
           key => raw_key, decrypted_data => decrypted_raw);
  dbms_output.put_line('> Decrypted raw output             : ' || 
                UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
  dbms_output.put_line('>  ');      
  if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = 
                UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
     dbms_output.put_line('> Raw DES Encyption and Decryption successful');
  END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
borjab
This doesn't really answer the question; for one it doesn't use CLOBS and for two it doesn't have the same semantics as the original function:function encrypt (plaintext in clob) returning clob -- returns cypher text
Mr. Shiny and New
+1  A: 

Slightly off-topic: What's the point of the encryption/obfuscation in the first place? An attacker having access to your database will be able to obtain the plaintext -- finding the above stored procedure will enable the attacker to perform the decryption.

Alexander
The key should not be stored in your database, but passed in from the application.
Matthew Watson
I agree with the comment, but the original code isn't passing any keys to the encryption function.
Alexander
Having a backup with the unencripted data can be a security problem. Anyway, I agree that the document will travel unencripted over the network but for that purpose is better to encrypt the whole connection with https.
borjab
Decent sysadmins encrypt backups anyway -- you don't need to have the database data encrypted to have the backups of the database encypted. It appears here that the goal of encryption here is to prevent users having unauthorized access to the database from reading the CLOBs.
Alexander
The goal here is more about preventing users with authorized access from accidentally revealing sensitive data.
Mr. Shiny and New
A: 

I note you are on Oracle 9, but just for the record in Oracle 10g+ the dbms_obfuscation_toolkit was deprecated in favour of dbms_crypto.

dbms_crypto does include CLOB support:

DBMS_CRYPTO.ENCRYPT(
   dst IN OUT NOCOPY BLOB,
   src IN            CLOB         CHARACTER SET ANY_CS,
   typ IN            PLS_INTEGER,
   key IN            RAW,
       iv  IN            RAW          DEFAULT NULL);

DBMS_CRYPT.DECRYPT(
   dst IN OUT NOCOPY CLOB         CHARACTER SET ANY_CS,
   src IN            BLOB,
   typ IN            PLS_INTEGER,
   key IN            RAW,
   iv  IN            RAW          DEFAULT NULL);
tardate