Adding a CAPTCHA to your Oracle APEX application

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 ;-)

Update on 08-Nov-2011: This is now also available as an easy to use item type plug-in for Oracle APEX 4.0. Have a look at the Google reCaptcha plug-in at http://apex.oracle.com/plugins.

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;

23 thoughts on “Adding a CAPTCHA to your Oracle APEX application

  1. 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!

  2. 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

  3. 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

  4. 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

  5. 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.

  6. 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

  7. 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

  8. 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.

  9. 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

  10. Hi Patrick,
    while signing up for APEX 4.0 (http://tryapexnow.com/) another
    captcha feature is shown that is more comfortable than this one you described above. Can you show me a way to integrate this feature?

    Thanks and Regards,
    Juergen

  11. Patrick,

    could this be implemented as an authentication scheme? I have an APEX application that is a public application (no username/password required) but we would like to keep the bots out! Could a CAPTCHA be used as an authentication scheme?

    Thanks!
    John

  12. Hi John,

    I have implemented the above captcha as an item type plug-in for Oracle APEX 4.0. See http://apex.oracle.com/plugins for details. I wouldn’t be aware that you can use a CAPTCHA in an authentication scheme, but you should easily be able to add the new item at all the places where you can enter data.

    Regards
    Patrick

  13. Nice plug-in, but it can be broken with Firebug. Just delete the ‘td’ tag which contains the plug-in. Hope it will be fixed :)

  14. Thanks for your feedback, Patrick (just now seeing your feedback). I will try it out in 4.0. We are not up to 4.0 in that application but I can try it out online.

    Thanks,
    John

  15. Patrick,

    Great article. Unless you are communicating from the future, the update s/b 08-Nov-2010 not 08-Nov-2011.

    Best Regards,
    Robert

Comments are closed.