Home » Developer & Programmer » Application Express & MOD_PLSQL » Encryption help (APEX 4.1)
Encryption help [message #610256] Tue, 18 March 2014 14:57 Go to next message
JD Dreamflight
Messages: 2
Registered: March 2014
Location: Glasgow
Junior Member
Hi Guy's
Looking for some advice on encryption within APEX
I have a form for users to fill out that contains some personal data, I'm encrypting that data using dbms_crypto.encrypt
I'm able to fill out the form & submit without any issues, I can see the data is encrypted fine in the table
on reloading the form, by calling the function (INITCAP(CRYPTIT.DECRYPT_DATA(:P2_SURNAME))) in the post calculation computation I can decrypt the data & display it without any issues, however, if then go to submit the form again & any of the item validations trigger an error, I'm getting the following error

Error in item post calculation for page item P2_SURNAME.
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06510: PL/SQL: unhandled user-defined exception
Technical Info (only visible for developers)
is_internal_error: true
apex_error_code: WWV_FLOW_FORMS.ITEM_POST_CALC_ERR
ora_sqlcode: -6502
ora_sqlerrm: ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06510: PL/SQL: unhandled user-defined exception
component.type: APEX_APPLICATION_PAGE_ITEMS
component.id: 58791010438634389
component.name: P2_SURNAME
error_backtrace:
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926
ORA-06512: at "SYS.WWV_DBMS_SQL", line 1065
ORA-06512: at "SYS.WWV_DBMS_SQL", line 1091
ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 600
ORA-06512: at "APEX_040200.WWV_FLOW_FORMS", line 708

I think it's down to the post calculation trying to fire against the item but as it's already been loaded & decrypted the function is rightly failing.

I'm not 100% I'm approaching this encryption method the right way, although I can't seem to find much help on Google when it comes to encrypting & decrypting data contained within items on APEX

THe Item P2_SURNAME source is set to:

Source used: Always, replace existing value in session state
Source type: Database Column
session state: Per Session
Source Value: SURNAME
Post Calc : INITCAP(CRYPTIT.DECRYPT_DATA(:P2_SURNAME))

Any help or pointers would be greatly appreciated

Re: Encryption help [message #610258 is a reply to message #610256] Tue, 18 March 2014 16:47 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I tried to replicate what you described on my Apex 4.1 & Oracle 10.2.0.3; I'm successfully encrypting data, fetching it back (decrypted), modifying it and submitting again - no problem at all.

Here's what I did (took most of encrypting/decrypting info from this page).

CREATE TABLE a1_test
  (id NUMBER, col VARCHAR2(40)
  );

CREATE OR REPLACE
  FUNCTION A1_ENC_VARCHAR
    (
      V_VARCHAR IN VARCHAR2
    )
    RETURN RAW
  IS
    V_ENC_VARCHAR RAW(500);
    V_KEY RAW(24) := 'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A';
    V_MOD NUMBER  := SYS.DBMS_CRYPTO.ENCRYPT_AES + SYS.DBMS_CRYPTO.CHAIN_CBC + SYS.DBMS_CRYPTO.PAD_PKCS5;
  BEGIN
    V_ENC_VARCHAR := SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
    RETURN V_ENC_VARCHAR;
  END;
  /

CREATE OR REPLACE
FUNCTION A1_DEC_VARCHAR
  (
    V_RAW IN RAW,
    V_KEY IN RAW
  )
  RETURN VARCHAR2
IS
  V_RET VARCHAR2 (500);
  V_DEC_RAW RAW (500);
  V_MOD NUMBER := DBMS_CRYPTO.ENCRYPT_AES + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
  V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
  V_RET     := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
  RETURN V_RET;
END;
/


I created a form with a report. I didn't modify the report (so it displays encrypted data), but in the form I created an after submit computation on P146_COL item (146 is my page number) whose type is PL/SQL Function body, and the computation is
return A1_ENC_VARCHAR(:P146_COL);

I also modified P146_COL item's properties and created Post Calculation Computation as
A1_DEC_VARCHAR(:P146_COL, 'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A');


Now, try to compare what I did with your code and see if there are any differences. As I said, it works just fine for me.
Re: Encryption help [message #610657 is a reply to message #610258] Sat, 22 March 2014 10:01 Go to previous message
JD Dreamflight
Messages: 2
Registered: March 2014
Location: Glasgow
Junior Member
Hi, thanks for the pointers, i had most of page setup the same but the only difference was that i was doing the encryption via DB trigger, by moving the encryption into a submit computation like you suggested has resolved the errors
Thanks again Smile
Previous Topic: How can I configure Virtual host for mod_plsql in Oracle HTTP Server
Next Topic: Apex installation
Goto Forum:
  


Current Time: Sat Nov 22 19:24:15 CST 2014

Total time taken to generate the page: 0.07922 seconds