Forum Stats

  • 3,826,400 Users
  • 2,260,641 Discussions
  • 7,896,931 Comments

Discussions

Getting unreadable/special characters after encrypting using dbms_crypto and returning lob

AllenS.
AllenS. Member Posts: 73 Blue Ribbon

Hi,


I have a requirement to send an xml payload with some parts being encrypted. Here's a sample format.


<xml>

<events>

 <create>

  <person_id/>

  <name/>

  <encrypted_data>64b6fcb391380a0a67b07844b46f9544

e7def8df78c6d7e68b79de62fe5627f9

cb30b3d15b0905f94538475bd681b113

  </encrypted_data>

 </create>

</events>


For reference, the decrypted format of the encrypted_data is:


<content>

 <date_of_birth/>

 <sex/>

 <address/>

 <email/>

 <phone/>

... etc

</content>


So I created the following wrapper package for encryption. 


 create or replace package body APPS.xxhr_crypto

 is

  function encrypt ( src in clob

      , typ in binary_integer

      , key in raw

      , iv in raw default null ) return blob

  is

  l_enc blob;

  begin

  dbms_lob.createtemporary ( l_enc, true );

  dbms_crypto.encrypt (

   dst => l_enc

   , src => src

   , typ => typ

   , key => key

   , iv => iv

  );

  return ( l_enc );

  end;


  function decrypt ( src in blob

      , typ in binary_integer

      , key in raw

      , iv in raw default null ) return clob

  is

  l_dec clob;

  begin

  dbms_lob.createtemporary ( l_dec, true );

  dbms_crypto.decrypt (

   dst => l_dec

   , src => src

   , typ => typ

   , key => key

   , iv => iv

  );

  return l_dec;

  end;


 end;


Then I also created a package for generating the payload using dbms_xmlgen. Then a function that basically generates a query into an xml clob which I then encrypt in the main procedure that generates the main payload.


 create or replace package body APPS.xxhr_publisher

 as

  l_nls_date_format2 nls_session_parameters.parameter%type := 'yyyy-mm-dd"T"hh24:mi:ss"Z"';


  function get_emp_sensitive_data_as_xml ( p_person_id in number ) return xmltype

  is

  l_xml xmltype;

  l_ctx dbms_xmlgen.ctxhandle;

  begin

  l_ctx := dbms_xmlgen.newcontext('select * from xxhr_emp_sensitive_pub_v where "personId" = :person_id');

  dbms_xmlgen.setbindvalue(l_ctx, 'person_id', p_person_id);

  dbms_xmlgen.setrowsettag(l_ctx, 'content');

  dbms_xmlgen.setrowtag(l_ctx, '');

  l_xml := dbms_xmlgen.getxmltype (l_ctx);

  dbms_xmlgen.closecontext(l_ctx);

  return l_xml;

  end;


  procedure publish_emp_delta ( errbuff   out varchar2

         , retcode   out number

         , p_event_date in  varchar2

         , p_from_lastname in  varchar2

         , p_to_lastname in  varchar2 )

  is

  l_create xmltype;

  l_payload clob;

  l_create_ctx  dbms_xmlgen.ctxhandle;

  l_response clob;

  begin

  execute immediate 'alter session set nls_date_format = ''' || l_nls_date_format2 || '''';


  l_create_ctx := dbms_xmlgen.newcontext(

 q'~select a.*

   , xxhr_crypto.encrypt ( ( xxhr_publisher.get_emp_sensitive_data_as_xml (a."personId") ), 4356, 'SECRET_KEY' ) as "_encryptedData"

   from xxhr_employees_pub_v a

  where ( ( fnd_date.canonical_to_date(:event_date) is not null and a."_eventType" = 'create' ) or fnd_date.canonical_to_date(:event_date) is null )

   and lower(a."lastName") between lower(nvl(:from_last_name,a."lastName")) and lower(nvl(:to_last_name,a."lastName"))

   and trunc(a."_eventDate") = nvl(fnd_date.canonical_to_date(:event_date),trunc(a."_eventDate"))~');


  dbms_xmlgen.setbindvalue(l_create_ctx,'from_last_name',p_from_lastname );

  dbms_xmlgen.setbindvalue(l_create_ctx,'to_last_name',p_to_lastname );

  dbms_xmlgen.setbindvalue(l_create_ctx,'event_date',p_event_date);


  dbms_xmlgen.setrowsettag(l_create_ctx, '');

  dbms_xmlgen.setrowtag(l_create_ctx, 'create');

   

  select xmlserialize ( content ( deletexml ( xmlelement ( "payload", xmlelement ( "correlationId", fnd_global.conc_request_id ), xmlelement ( "initialLoad", nvl2(p_event_date,'false','true') )


                , xmlelement ( "events", ( select dbms_xmlgen.getxmltype(l_create_ctx) from dual ) )

             )

          , '/payload/events/*/_eventType' ) ) as clob indent size = 4

       )

   into l_payload

   from dual;  


  dbms_xmlgen.closecontext(l_create_ctx);   

  end;  


 end;


When we generate xml payload for many employees, somewhere in the middle, an record will have when some special characters when decrypting the encrypted data. Something like:


<xml>

<events>

 <create>

  <person_id>1</person_id>

  <name>Name</name>

  <encrypted_data>

   <content>

    <date_of_birth>1967-09-14T00:00:00Z</date_of_birth>

    <address>ADDRESS LINE 1 ADDRESS LINE 2 ADDRESS LINE 3</address>

    ¿D(¿¿¿4¿M¿¿)M¿¿*¿¿~MZ¿y.œ¿¿¿¿¿¿

   </content>

  </encrypted_data>

 </create>

</events>

  

However, when we try to generate xml payload for this particular employee, it work's fine.


I don't know yet what's causing this but it seems to happen only when generating payload for may records. My hunch is that the encryption procedure xxhr_crypto.encrypt is the culprit since I am not closing/freeing the temporary lob? How do I do this btw, since it is used in a SQL statement, I can't close or free it before returning it.


Appreciate any help.

Tagged: