Skip to Main Content
  • Questions
  • Unexpected results when using DBMS_CRYPTO to decrypt a CLOB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Paul .

Asked: September 20, 2021 - 7:04 am UTC

Last updated: October 04, 2021 - 4:50 am UTC

Version: 19c

Viewed 100+ times

You Asked

I have a requirement to store CLOBS both encrypted and in plain text in the same table and column. I have a wrapper function insert_clob() that handles the situation.

For the encryption and decryption I created two functions, which accepts a CLOB as input. The size of the CLOB could be small or huge>32k, which shouldn't be a problem.

I am able to get through the encryption function.
Whether that function is working properly is yet to be determined as I am having issues decrypting the CLOB to verify my results.

After the update step in my test case, I am calling dump() on the decrypted CLOB, which shows some unusual characters in the middle of the CLOB that weren't in the input and therein lies my problem that I can't seem to resolve. I was hoping you could explain the problem(s) (where I went wrong) and provide me with a code fix, which will rectify the issue(s) wherever they are in the test CASE.

Btw, I know there are some issues such as storing the keys in the DB and anyone with the DBA role can see that information. I intend on changing that once I'm satisfied I'm getting the correct results.

I'm testing this in livesql if you want to emulate my testing environment.

Thanks in advance for responding to my question and sharing your time and expertise.

alter session set nls_date_format = 'MMDDYYYY HH24:MI:SS';

create table encryption_values (
  name  varchar2 (100 byte),
  value nvarchar2 (100)
);
/
‚Äč
insert into encryption_values
  select 'key' name,
         rawtohex (
           '52AB32;^$!ER94988OPS3W21'
         ) value
  from dual
  union
  select 'iv' name,
         rawtohex (
           'TY54ABCX'
         ) value
  from dual;
/

create or replace function encrypt_clob (
  p_clob in clob
) return clob is

  l_clob            clob;
  lb_variable       blob;
  v_key             raw (320);
  v_encryption_type pls_integer := dbms_crypto.aes_cbc_pkcs5;
  v_iv              raw (320);
  l_dest_offset     pls_integer := 1;
  l_src_offset      pls_integer := 1;
  l_lang_context    pls_integer := dbms_lob.default_lang_ctx;
  l_warning         pls_integer;
  l_step            pls_integer := 1998;
begin
  select value
  into v_key
  from encryption_values
  where name = 'key';

  select value
  into v_iv
  from encryption_values
  where name = 'iv';

  dbms_lob.createtemporary (
     lb_variable,
     true
  );
  dbms_crypto.encrypt (
    dst => lb_variable,
    src => p_clob,
    typ => v_encryption_type,
    key => v_key,
    iv => v_iv
  );

  dbms_lob.createtemporary (
     lob_loc => l_clob,
     cache => true
  );
  for i in 0..trunc ((dbms_lob.getlength (lb_variable)) / l_step) loop
    l_clob := l_clob
              || utl_raw.cast_to_varchar2 (utl_encode.base64_encode (dbms_lob.substr (
                   lb_variable,
                   l_step,
                   i *
                   l_step +
                   1
                 )));
  end loop;

  return l_clob;
end encrypt_clob;
/

create or replace function decrypt_clob (
  p_clob in clob
) return clob is

  lb_variable       clob;
  l_clob            clob;
  l_blob            blob;
  v_key             raw (320);
  v_encryption_type pls_integer := dbms_crypto.aes_cbc_pkcs5;
  v_iv              raw (320);
  l_dest_offset     pls_integer := 1;
  l_src_offset      pls_integer := 1;
  l_lang_context    pls_integer := dbms_lob.default_lang_ctx;
  l_warning         pls_integer;
  l_raw             raw (32767);
  l_amt             number := 7700;
  l_offset          number := 1;
  l_temp            varchar2 (32767);
  l_step            pls_integer := 7700;
begin
  select value
  into v_key
  from encryption_values
  where name = 'key';

  select value
  into v_iv
  from encryption_values
  where name = 'iv';

  dbms_lob.createtemporary (
     l_blob,
     true
  );

  -- I saw that base-64 may have line feeds; those could give an inaccurate result 
  -- to the decode function below, so they are replaced with NULL.
  l_clob := replace (
              replace (
                p_clob,
                chr (13),
                null
              ),
              chr (10),
              null
            );

  for i in 0..trunc ((dbms_lob.getlength (l_clob)) / l_amt) loop
    dbms_lob.read (
      l_clob,
      l_amt,
      l_offset,
      l_temp
    );
    l_offset := l_offset + l_amt;
    l_raw := utl_encode.base64_decode (utl_raw.cast_to_raw (l_temp));
    dbms_lob.append (
      l_blob,
      to_blob (l_raw)
    );
  end loop;

  dbms_lob.createtemporary (
    lb_variable,
    true
  );
  dbms_crypto.decrypt (
    dst => lb_variable,
    src => l_blob,
    typ => v_encryption_type,--dbms_crypto.des_cbc_pkcs5, 
    key => v_key,
    iv => v_iv
  );

  return lb_variable;
end decrypt_clob;
/

create table t (
  seq_num     integer
    generated by default as identity (start with 1)
  not null,
  hash_pk     varchar2 (1000) not null primary key,
  c           clob,
  encrypted   varchar2 (1) default 'N',
  create_date date default sysdate
);
/

create or replace function hash_sha512 (
  psinput in varchar2
) return varchar2 as
  rhash raw (512);
begin
  rhash := dbms_crypto.hash (
             to_clob (psinput),
             dbms_crypto.hash_sh512
           );
  return (lower (rawtohex (rhash)));
end hash_sha512;
/

create or replace procedure insert_clob (
  p_clob      in clob,
  p_encrypted varchar2
) as
  l_hash_pk varchar2 (1000);
begin
  select hash_sha512 (p_clob)
  into l_hash_pk
  from dual;

  if (p_encrypted = 'N') then
    insert into t (
      hash_pk,
      c,
      encrypted
    )
      select l_hash_pk,
             p_clob,
             p_encrypted
      from dual
      where not exists (
        select 1
        from t
        where (hash_pk = l_hash_pk)
      );

  else
    insert into t (
      hash_pk,
      c,
      encrypted
    )
      select l_hash_pk,
             encrypt_clob (p_clob),
             p_encrypted
      from dual
      where not exists (
        select 1
        from t
        where (hash_pk = l_hash_pk)
      );

  end if;

end;
/

declare
  c1 clob := empty_clob ();
  c2 varchar2 (32000);
begin
  for i in 1..3 loop
    c2 := 'abcdefghijklmnopqrstuvwxyz ' || i;
    dbms_output.put_line (c2);
    select concat ( c1, c2 )
    into c1
    from dual;

    insert_clob (
      c1,
      'Y'
    );
    c1 := empty_clob ();
    c2 := null;
  end loop;
end;
/

update t 
set    c = decrypt_clob(c),encrypted = 'N' 
where  encrypted ='Y';

-- As you can see from the dump() the decrypted CLOB doesn't match the original CLOB.
select  c, dump(to_char(c)) from t;
/

and Connor said...

I see similar issues - for example encrypting and decrypting the same value twice can give different results:

with rws as (
  select 'abcdefghijklmnopqrstuvwxyz 1' plaintext from dual
  connect by level <= 2
), enc as (
  select plaintext, encrypt_clob ( plaintext ) cypher_text 
  from   rws
)
  select plaintext, decrypt_clob ( cypher_text ) decyphered_text
  from   enc e;
  
PLAINTEXT                    DECYPHERED_TEXT                              
---------------------------- ---------------------------------------------
abcdefghijklmnopqrstuvwxyz 1 abcdefgh?klmnopqrstuvwxyz 1                 
abcdefghijklmnopqrstuvwxyz 1 abcdefghijklmnopqrstuvwxyz 1 


I'm not sure why. Contact support to get to the bottom of this.

That said, there are several issues with the current approach:

Storing plaintext and ciphertext values in the same column

This is a recipe for disaster! Due to bugs or mistakes, the encrypted flag is bound to get out-of-sync with the column for some rows at some point. Which could lead to double or triple encrypting them. Cleaning this up could be a major effort.

Storing plaintext at all!

If the values are important enough that you want to encrypt them, you should minimize the places you store the unencrypted data. Anytime you store the plaintext in a table, these values will be recorded in redo logs, backups, etc. This greatly increases the attack surface and chance of the data being leaked.

For example, if anyone manages to get a hold of a backup storing plaintext values and restore it they've got the unencrypted data!

Converting the encrypted value back to clob


The dbms_crypto routines return raw/blob data - why bother converting it back to a clob? Just store it as a blob. This is less code, less processing (= faster), and removes a possible reason for the problems you're seeing.

If you're stuck with the method you're currently using and/or making the changes above doesn't resolve the issue, contact support.

Rating

  (2 ratings)

Comments

key/IV wrong length

Jonathan Taylor, October 01, 2021 - 2:49 pm UTC

The problem is that the key/IV should be 256 bytes (32 chars converted to binary).

Use this, and it works (I've padded your keys/iv to 32 with X - but better to use a longer key).

insert into encryption_values
select 'key' name,
rawtohex (
rpad ('52AB32;^$!ER94988OPS3W21',32,'X')
) value
from dual
union
select 'iv' name,
rawtohex (
rpad ('TY54ABCX',32,'X')
) value
from dual;

P.S. It would be better if "value" was a RAW (instead of varchar2), and then use UTL_RAW.cast_to_raw()

Chris Saxon
October 01, 2021 - 4:37 pm UTC

Good catch, thanks for explaining this

P.S. It would be better if "value" was a RAW (instead of varchar2), and then use UTL_RAW.cast_to_raw()

Indeed!

A reader, October 01, 2021 - 6:30 pm UTC

Thank you both works perfectly!! A follow up, can the KEY/IV be any length or does it need to be a multitude of 32 (or any other number) for the encryption and decryption to work properly.

I'm going to do more detailed testing as I fell into this trap once I like to know what to avoid before I tell my application team what they need to do

Connor McDonald
October 04, 2021 - 4:50 am UTC

glad we could help - but we reiterate

- Storing plaintext and ciphertext values in the same column
- Storing plaintext at all!

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here