This discussion is archived
4 Replies Latest reply: Aug 19, 2013 5:23 AM by Recx Ltd RSS

Calling stored procedure from Apex

84e45717-06df-499e-a789-5312b7434a74 Newbie
Currently Being Moderated

I am a total newbie at Apex so any help would be appreciated. My situation:

 

I created Apex app with few forms and reports. Since I want to implement security for some tables, example:

Table Employee has columns email and credit_card_num. These 2 columns are encrypted using DBMS_CRYPTO.

My Oracle Database version in Oracle XE 11g Release 2.

So I have stored procedure and package for encryption and decryption using dbms_crypto.

When I add new employee in APEX form email and credit card number are passed as plain text and saved on that way in database.

 

1. How can I make it to be saved as encrypted value using package and procedures I've created in the database (to parse the inserted value to function)?

 

2. Since I am a total dummy and newbie at Apex can anyone explain me how does Apex really work? Is the inserted data sent in plain text or encrypted over network?

 

Thank you

  • 1. Re: Calling stored procedure from Apex
    jrimblas Expert
    Currently Being Moderated

    84e45717-06df-499e-a789-5312b7434a74 wrote:

    1. How can I make it to be saved as encrypted value using package and procedures I've created in the database (to parse the inserted value to function)?

     

    You have a few options.  You can create a Process (after submit process) that calls your code.

    For example, if your form is on Page 2 and you're saving the EMP table your process could look like this:

     

    begin
      app_util.emp_save_secure(p_EMPNO => :P2_EMPNO
    , p_ename => :P2_ENAME
    , p_deptno => :P2_DEPTNO);
    end;

    Here of course app_util is your own package (with any name you want) and emp_save_secure is the procedure that will receive the params to save.

     

    You could also use the APEX wizard to create a form on the Procedure then edit the code as you need. It would be almost the same result, but probably less typing and less typos.

     

    2. Since I am a total dummy and newbie at Apex can anyone explain me how does Apex really work? Is the inserted data sent in plain text or encrypted over network?

     

    Thank you

    Like any web page, if your connection is done with HTTPS then the data from the browser to the webserver will be encrypted and that's all completely transparent to you.  If your call is HTTP then it will be done in clear text.

    To setup an SSL connection that uses HTTPS you'll need to create a certificate on the server and configure HTTPS. This is better done by a DBA that knows what they are doing. 

     

    Thanks

    -Jorge

    http://rimblas.com/blog/

  • 2. Re: Calling stored procedure from Apex
    84e45717-06df-499e-a789-5312b7434a74 Newbie
    Currently Being Moderated

    Thank you very much. I am developing app for my Master thesis so everything is done on local machine. And I found a round way for the 1st question, just parsed the value to function in trigger before insert.

  • 3. Re: Calling stored procedure from Apex
    jrimblas Expert
    Currently Being Moderated

    That will work.

    You're welcome.

  • 4. Re: Calling stored procedure from Apex
    Recx Ltd Explorer
    Currently Being Moderated

    In addition you should encrypt sensitive items in APEX session state (such as credit card). This ensures that you don't end up with a situation where you have encrypted the item value in the database tables, but they are stored in plain text within APEX session tables.

     

    [APEX Item] -> Security -> Store value encrypted in session state

     

    regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points