Skip to Main Content
  • Questions
  • Oracle Text Substring Search on Encrypted String

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ananth.

Asked: January 22, 2019 - 12:50 pm UTC

Last updated: January 22, 2019 - 2:45 pm UTC

Version: 12

Viewed 1000+ times

You Asked

Hi,

Do Oracle Text have capability to sub string search on encrypted string.
The Data resides in table as encrypted string (using DBMS_CRYPTO).

The sub string passed from UI is non encrypted and data resides on DB is encrypted.

Thanks & Regards
Ananth


and Chris said...

If you're encrypting the data then storing it, then no. Oracle Text can only "see" the encrypted string. Not the original plaintext:

create table t (
  c1 varchar2(1000),
  c2 raw(1000)
);

declare
  text varchar2(19) := 'red car';
  text_raw raw(128) := utl_raw.cast_to_raw(text);
  key raw(128)      := utl_raw.cast_to_raw('thekey');
  ciphertext raw(2048);
begin

  ciphertext := sys.dbms_crypto.encrypt (
    text_raw, dbms_crypto.des_cbc_pkcs5, key
  );

  insert into t values ( text, ciphertext );

end;
/
commit;

create index ti on t ( c1 ) indextype is ctxsys.context;
create index tir on t ( c2 ) indextype is ctxsys.context;


select * from t
where  contains ( c1, 'red' ) > 0;

C1        C2                 
red car   317D79EB8F4FDC38 

select * from t
where  contains ( c2, 'red' ) > 0;

no rows selected


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.