tero.co.uk

DES in Oracle's dbms_obfuscation_toolkit

This is a description of how to use DES in Oracle's dbms_obfuscation_toolkit. Many thanks to Tim Scott (see the acknowledgements for contact details) for this information.

  1. Encrypting in JavaScript
  2. Decrypting in PL/SQL
  3. NLS considerations
  4. An example
  5. Additional notes
  6. Acknowledgements

1. Encrypting in JavaScript
Oracle uses 3DES in CBC mode with an Input Vector documented as: '0123456789ABCDEF'. This is an 8 byte string, 0x01, 0x23, 0x45, etc. Therefore encrypt using:

iv = hexToString("0x0123456789ABCDEF");
result = des(key, value, 1, 1, iv);

This example uses 24 byte key. This has not been tested with a key of other lengths.

2. Decrypting in PL/SQL
There are several 3DES decryption functions and procedures that are supplied with the dbms_obfuscation_toolkit to cater for various scenarios and application or host programming requirements.

Using a 24 byte key Oracle expects the parameter "which" set to '1' so that it carries out encryption/decryption in ThreeKeyMode (as opposed to the default TwoKeyMode, also known as 2DES (ref: Metalink Note:228636.1).

Therefore decrypt using either:

rawstring := dbms_obfuscation_toolkit.des3decrypt
   (encrypted, key=>rawkey, which=>1);
varchar2string := dbms_obfuscation_toolkit.des3decrypt 
   (encrypted, key_string=>varchar2key, which=>1);

3. NLS considerations
Oracle transparently converts character data from the session character set to the database character set as it sends data to the database and vice versa. Because the encrypted strings are binary data they should be treated as RAW or BLOB when dealing with the potential for a variety of character sets so that this conversion does not occur.

It is recommended that the data is converted to a concatenated list of two-digit hex values before sending from the browser to the web server so that the cgi or asp process can then pass this directly to Oracle decryption functions that accept RAW parameters. stringToHex() comes in handy here, but remember to remove the leading 0x before passing into Oracle.

No testing or consideration has been made for strings that include non 7-bit ASCII characters prior to encryption. Such characters will encrypt, transfer and decode correctly resulting in the correct sequence of bits, but it is unknown and untested as to whether Oracle will treat the resulting string correctly for multi byte character sets.

4. An example
Oracle procedure:-

create or replace procedure decrypt
   (key in varchar2,
    encstr in raw,
    string out varchar2,
    opt varchar2 := null)
return varchar2 is
  -- arbitrary size limits of 240 bytes, can increase to 32k if required
  rv     raw(240);
  rawkey raw(240) := '';
begin
  for i in 1..length(key) loop
    rawkey := rawkey||hextoraw(to_char(ascii(substr(key, i, 1)), 'FMXX'));
  end loop;
  rv := dbms_obfuscation_toolkit.des3decrypt(encstr, key=>rawkey, which=>1);
  str := utl_raw.cast_to_varchar2(rv);
  str := rtrim(str);
  if upper(opt) like 'U%' then
    str := upper(str);
  elsif upper(opt) like 'L%' then
    str := lower(str);
  end if;
end;

Server side form processing (in no particular language) :-

var decryptedString(240);
Connect("Oracle:...");
SQL("decrypt(:1, :2, :3, 'UPPER')",
    generatedKey, HTTPPost_Data("pw"), decryptedString);
delete generatedKey;

HTML page:-

<HTML>
 <HEAD>
  <TITLE>Encrypt example</TITLE>
  <SCRIPT LANGUAGE='javascript' SRC='des.js'><!--
    // des.js from shopable.co.uk
    function enc(fld)
    {
      var key = "a 24byte generated key:)"; //generated dynamically by asp? cgi?
      while (fld.value.length % 8 != 0) { fld.value += " "; };
      iv = hexToString("0x0123456789ABCDEF");
      fld.value = des(key, fld.value, 1, 1, iv);
      fld.value = stringToHex(fld.value);
      return true;
    };
    // -->
  </SCRIPT>
 </HEAD>
 <BODY>
  <FORM ACTION='form processor' NAME='encForm' onSubmit='enc(this.pw);'>
    <P>Password: <INPUT TYPE='password' SIZE='480' /></P>
    <!-- make password field 2x required limit to allow for 'hexification' -->
  </FORM>
 </BODY>
</HTML>

It is suggested that 'des.js' be supplied by a cgi or asp page that generates the appropriate encryption key, stores it server side to process the response and sends it as part of the enc() function. Once the response is processed this key should be discarded.

5. Additional notes
Oracle now allows usage of the dbms_obfuscation_toolkit in all of it's 10g editions - including their free edition - Oracle XE.

There are various ways to generate keys, the easiest of which is possibly:-
dbms_random.string('a', 24);
(See bug: 1541060, and pay attention to the need to escape quotes and '\'s etc, or use dbms_random.string('u', 24); or dbms_random.string('l', 24);)

There may be a need to have this decryption written in PL/SQL (for Standard Edition Oracle customers), and if so this would be part of a commercial product (as is Oracle's dbms_obfuscation_toolkit) and will be licensed accordingly. If anyone wants to write and support the decryption in PL/SQL then we could negotiate licensing and costs appropriately (see contact details below).

6. Acknowledgements
This information was provided by Tim Scott, Senior Technical Consultant, FDI, www.oclc.org. Feel free to email him at if you have any queries.