Adding a CAPTCHA to your Oracle APEX application

6. September 2007

Oracle APEX, Integration, Oracle APEX, Security

Haven’t had the requirement to add one in my applications until now, but after reading Dan’s posting about integrating reCAPTCHA with Oracle SSO, I thought that shouldn’t be to hard to integrate into an Oracle Application Express (APEX) application too! A nice challenge for the evening ;-)

I know that there are some similar solutions out there on the OTN forum and on the unOfficial Wiki page, but the SVG solution isn’t very secure in my opinion and the other solution uses an unsupported non-public package of Oracle APEX. So how can we integrate the reCAPTCHA widget instead?

Just follow my step by step instruction and you are done in minutes!

  1. Sign up at reCAPTCHA to get the private and public key.
  2. Load the stored function into your application schema which you can find at the end of the article.
  3. Go to the page where you want to integrate the CAPTCHA
  4. Create three page items
    • Name: P<your page>_RECAPTCHA_CHALLENGE_FIELD, P<your page>_RECAPTCHA_RESPONSE_FIELD and P<your page>_RECAPTCHA_ERROR
    • Type: Hidden
    • Sequence: Should be before all other items
    • Item Source: Always Null
  5. Put the following JavaScript code into the “Post Element Text” property of an existing page item or create a new region of type “HTML”. Replace P7 by your own page number and also replace “your_public_key” with your public key you got during the registration. 
    <script type="text/javascript"
   src="http://api.recaptcha.net/challenge?k=your_public_key&error=&P7_RECAPTCHA_ERROR.">
</script>
<script type="text/javascript">
function renameRecaptchaField(pRecaptchaFieldName, pApexFieldName)
{
  var vRecaptchaField = $x(pRecaptchaFieldName);
  var vApexField      = $x(pApexFieldName);
  // Set for the dynamically generated Recaptcha field the same name as for
  // our APEX field (eg. t02), so that wwv_flow.accept puts the value into the APEX field.
  // After that remove the APEX field, because wwv_flow.accept can't handle
  // two fields with the same name.
  vRecaptchaField.name = vApexField.name;
  vApexField.parentNode.removeChild(vApexField);
};
renameRecaptchaField('recaptcha_challenge_field', 'P7_RECAPTCHA_CHALLENGE_FIELD');
renameRecaptchaField('recaptcha_response_field',  'P7_RECAPTCHA_RESPONSE_FIELD' );
</script>
  6. Create a page level validation
    • Type: PL/SQL – Function Returning Boolean
    • Sequence: 1
    • Validation Name: Recaptcha Check
    • Error Display Location: Inline in Notification
    • Validation: 
      BEGIN
    :P7_RECAPTCHA_ERROR := checkRecaptcha
                             ( pPrivateKey => 'your_private_key'
                             , pChallenge  => :P7_RECAPTCHA_CHALLENGE_FIELD
                             , pResponse   => :P7_RECAPTCHA_RESPONSE_FIELD
                             );
    RETURN (:P7_RECAPTCHA_ERROR IS NULL);
END;
    • Error Message: Recapture verification failed!

That’s all, you are done!

So what was the challenge?

The JavaScript code provided by reCAPTURE creates two new fields on the fly. But because they do not comply with the names of the wwv_flow.accept package, which is called when a page is submitted, you will get an error during submit. So I had to use a trick. The nice side effect was that I can use the regular referencing in Oracle APEX, because they are just normal page items. See the comment text of the JavaScript code for details.

And here comes the stored function:

CREATE OR REPLACE FUNCTION checkRecaptcha
  ( pPrivateKey IN VARCHAR2
  , pRemoteIP   IN VARCHAR2 := OWA_Util.get_cgi_env('REMOTE_ADDR')
  , pChallenge  IN VARCHAR2
  , pResponse   IN VARCHAR2
  )
  RETURN VARCHAR2
IS
    --
    vRequest      Utl_Http.req;
    vPostText     VARCHAR2(500);
    vResponse     Utl_Http.resp;
    vResponseText VARCHAR2(2000);
    vError        VARCHAR2(200);
BEGIN
    ----------------------------------------------------------------------------
    -- Build text for the post action.
    -- For a field description, see
    -- http://recaptcha.net/apidocs/captcha/
    ----------------------------------------------------------------------------
    vPostText :=
      'privatekey='||Utl_Url.escape(pPrivateKey, TRUE)||CHR(38)||
      'remoteip='  ||Utl_Url.escape(pRemoteIP,   TRUE)||CHR(38)||
      'challenge=' ||Utl_Url.escape(pChallenge,  TRUE)||CHR(38)||
      'response='  ||Utl_Url.escape(pResponse,   TRUE)||CHR(38)
    ;
    ----------------------------------------------------------------------------
    -- if you need to set a proxy, uncomment next line.
    ----------------------------------------------------------------------------
    /* Utl_Http.set_proxy('proxy.it.my-company.com', 'my-company.com'); */
    ----------------------------------------------------------------------------
    -- Send data to reCAPTCHA.
    ----------------------------------------------------------------------------
    vRequest := Utl_Http.begin_request
                  ( url    => 'http://api-verify.recaptcha.net/verify'
                  , method => 'POST'
                  );
    Utl_Http.set_header
      ( r     => vRequest
      , name  => 'Content-Type'
      , value => 'application/x-www-form-urlencoded'
      );
    Utl_Http.set_header
      ( r     => vRequest
      , name  => 'Content-Length'
      , value => LENGTH(vPostText)
      );
    Utl_Http.write_text
      ( r    => vRequest
      , data => vPostText
      );
    vResponse := Utl_Http.get_response(vRequest);
    IF vResponse.status_code = '200' -- the HTTP call was successful
    THEN
        Utl_Http.read_text(vResponse, vResponseText);
        -- Has the user entered a correct solution?
        IF vResponseText LIKE 'false%'
        THEN
            vError := SUBSTR(vResponseText, 7);
        END IF;
    ELSE
        vError := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
    END IF;
    --
    Utl_Http.end_response(vResponse);
    --
    RETURN vError;
END checkRecaptcha;

16 Responses to “Adding a CAPTCHA to your Oracle APEX application”

  1. Paul Says:
    September 7th, 2007 at 03:17

    Very cool Patrick.

    Glad you enjoyed your weekend like I did mine!

  2. Dan Says:
    September 7th, 2007 at 04:59

    Hi Patrick,

    Just so no one is confused (you wouldn’t be if you followed the link), I didn’t actually write anything about actually implementing SSO with reCAPTCHA. My contribution was to link to Paul Gallagher’s great article on how to configure everything. Thanks for the link!

  3. Patrick Wolf Says:
    September 7th, 2007 at 05:20

    Looks like that I was a little bit confused yesterday evening :-)

    Patrick

  4. josep Says:
    February 10th, 2009 at 19:07

    Hi Patrick,

    We had an APEX application working with captcha and after a fresh install in a new server we get the following error:

    The requested URL /apex/wwv_flow.accept was not found on this server

    We reveiwed proper GRANTS on UTL_HTTP to our server, but still does not work… any other ideas?

    Many thanks!

    Josep

  5. Patrick Wolf Says:
    February 16th, 2009 at 14:50

    Hi Josep,

    have a look at my posting Oracle APEX: Got a 404 Not Found? to determine what is the root cause of the wwwv_flow.accept error.

    Hope that helps
    Patrick

  6. Tom Nagle Says:
    March 10th, 2009 at 20:35

    Patrick, thanks for this post, it got me going in the right direction in very short order.

    I ran into is a 404 error when attempting to login after the “get a new challenge” button is clicked. Apparently clicking that button re-creates recaptcha_challenge_field and recaptcha_response_field, confusing wwv_flow.accept.

    To work around this, I did the following:

    1. Placed the following code in HTML header of the login page:

    function renameRecaptchaField(pRecaptchaFieldName, pApexFieldName)
    {
    var vRecaptchaField = document.getElementById(pRecaptchaFieldName);
    var vApexField = document.getElementById(pApexFieldName);

    vRecaptchaField.name = vApexField.name;
    vApexField.parentNode.removeChild(vApexField);
    };

    function doApexSubmit() {
    renameRecaptchaField(’recaptcha_challenge_field’, ‘P101_RECAPTCHA_CHALLENGE_FIELD’);
    renameRecaptchaField(’recaptcha_response_field’, ‘P101_RECAPTCHA_RESPONSE_FIELD’ );
    doSubmit(”LOGIN”);
    }

    2. Created a new HTML region below the Login region, using no template, with the following source (note that the login page is accessed via SSL, so I used api-secure.recaptcha.net):

    // Makes the recaptcha area blend a bit better with the template
    var RecaptchaOptions = {
    theme : ‘clean’
    };

    3. Set display condition of “Never” for original Login button.

    That was about it. The formatting can stand some messing around with, but it works.

    Also, to get your PL/SQL to work in 11g with an OHS front end, I had to set up the ACL correctly in the database:

    declare

    –works for testing, but is not secure to leave in place…
    –lc_principal constant varchar2(30) := ‘PUBLIC’;
    –lc_host constant varchar2(100) := ‘*’;

    lc_principal constant varchar2(30) := ‘your-application-schema-name-here’;
    lc_host constant varchar2(100) := ‘api-verify.recaptcha.net’;

    begin

    –Uncomment if recreating…
    –dbms_network_acl_admin.drop_acl(’recaptcha.xml’);

    dbms_network_acl_admin.create_acl(acl => ‘recaptcha.xml’,
    description => ‘Recaptcha Validation ACL’,
    principal => lc_principal,
    is_grant => true,
    privilege => ‘connect’);

    dbms_network_acl_admin.add_privilege(acl => ‘recaptcha.xml’,
    principal => lc_principal,
    is_grant => true,
    privilege => ‘resolve’);

    dbms_network_acl_admin.assign_acl(acl => ‘recaptcha.xml’,
    host => lc_host,
    lower_port => 80,
    upper_port => 80);
    commit;

    end;

    One nice (and unplanned) side effect of this approach is that I can easily disable the recaptcha / new login button region and re-enable the old login button if (a) we have network issues accessing the outside world or (b) ReCaptcha is unavailable for some reason.

    This was a lot of sorting out and I hope others find it helpful. Please let me know if you note some opportunities for improvement or see any potential security issues.

    -Tom

  7. Tom Nagle Says:
    March 10th, 2009 at 20:53

    Drat, it looks like my HTML tags were stripped out. I thought it would automatically convert to plain text, my mistake. If you would like me to repost the above, please provide some guidance on proper formatting…I don’t want to experiment and clutter things up!

    -Tom

  8. ApEx Developer Says:
    March 19th, 2009 at 12:02

    Tom,

    you workaround either not complete, not correct. It does’nt work on ApEx 3.2 with 10gR2, but:

    You absolutely sure in this function correctness:

    function doApexSubmit() {
    renameRecaptchaField(’recaptcha_challenge_field’, ‘P101_RECAPTCHA_CHALLENGE_FIELD’);
    renameRecaptchaField(’recaptcha_response_field’, ‘P101_RECAPTCHA_RESPONSE_FIELD’ );
    doSubmit(”LOGIN”);
    }

    ??? I ever think than JS functions calls anything else rather than “doSubmit(”LOGIN”);”. Hm-m-m-m?

    Also – how does it work your login page without login button? I.e, how and where you calls function above?

    Summary, your workaround explains nothing.

    Patrick, could you comment this and, may be, fix your first solution? An 404 error occurs regularry in ANY variant of implementation your solution.

  9. Tom Nagle Says:
    March 19th, 2009 at 14:02

    You are absolutely correct, the solution I posted is not complete.

    However, this should not be too surprising since I explained in the post IMMEDIATELY following it that the HTML code and some of the JS was stripped out in error and asked for guidance from Patrick on reposting. I have not heard back yet, but will post the correctly formatted solution as soon as I do.

    If you would like to post an e-mail address, I will be happy to send you the complete, correct solution…or, perhaps I should say, the solution that works for me.

    -Tom

  10. ApEx Developer Says:
    March 19th, 2009 at 14:25

    Please, Tom, send it now – I krack head ;) this time (may be I stupid, but can’t solve problem myself). Please send it to this mail: http://mailhide.recaptcha.net/d?k=01obJA5zxpRd4VwDVQ-N051g==&c=LZZYU9L6zOqdrqs7xW2aDoYw4MRbB09G-Aj1BbgMjgs=

    Thank you. (Will try to complete my own solution – not only for login pages).

  11. ApEx Developer Says:
    March 19th, 2009 at 18:43

    Tom,

    please, drop me all code to e-mail here:

    http://mailhide.recaptcha.net/d?k=014LjJEsQRR7egSByeiHs5Mg==&c=OEEYJsia_qm8fj1vdVF45fUxvzF0Jt-28EdTo-UlOU0=

    I’ve cracked my head now to solve this. I need the solution not only login page, but also in registration wizard and forum posting code.

    I can’t solve it myself. SOmething wrong or I am a bit stupid. Thank you.

  12. Tom Nagle Says:
    March 19th, 2009 at 23:39

    ApEx Developer, it should be in your inbox now.

    If you have not already, you might consider installing Firebug and using that to help you work through these issues.

    Also, you can read Patrick’s useful post on diagnosing 404 errors:

    http://www.inside-oracle-apex.com/oracle-apex-got-404-not-found-2/

    -Tom

  13. AbhinaV Rai Says:
    April 17th, 2009 at 18:45

    Hey Patrick

    I believe you should update your checkrecaptcha function. I found the following bug.

    The check reCaptcha was returning NULL response sometimes. I added an exception and found that in some cases the challenge field (specially when we deal with audio captcha) is larger than 500 characters(vPostText VARCHAR2(500)). I increased this to 1500 for my application and it resolved the issue.

    I suggest you add an exception that returns the SQL error message.

    Great work though.

    Cheers,
    – Abhinav

  14. Abhinav Rai Says:
    April 17th, 2009 at 18:47

    Oh and I implemented the AJAX API for ReCaptcha in APEX. This way I did not have to implement your renameRecaptchaField function. I have an application process that validates the captcha. I did use your checkrecaptcha function though.

    Email me abhinavxrai at gmail dot com if anyone wants an example of how I implemented it in ApEx.

  15. vvidov Says:
    October 20th, 2009 at 09:08

    Hi Tom
    Please send me solution for captcha on
    http://mailhide.recaptcha.net/d?k=01sqXWnmpBldB9UCMnFeuMJg==&c=7VjPPcIhWnUApP2aDWMd4IxQBuPnDQ9RQ34sfOm5PdA=

    rds
    vvidov

  16. steve montgomerie Says:
    November 2nd, 2009 at 05:19

    Just curious if this sort of thing would work in the Oracle Apex cloud at apex.oracle.com as it may require a proxy.

    Best regards,

    Steve

Leave a Reply