How I built my own captcha in Oracle APEX without using any third party service
Posted by Jefith
For a long time I was using a normal third party captcha in my APEX app just like everyone does. It worked fine but I never liked depending on an outside service for something this small. One day I thought why not just build my own small captcha inside APEX itself using PL/SQL. So I started trying it and it took me a good few days of testing and fixing errors before it finally worked properly. In this post I am writing down the whole process, the mistakes I made and how I solved them, so if you are trying the same thing you will not waste as much time as I did.
Why I did not want a normal image captcha
First I thought I will generate a distorted image captcha like the usual ones with random letters. But PL/SQL does not have any proper way to draw text on an image directly, there is no built in font rendering. I tried searching if there is any package for this but nothing simple came up, everything needed external libraries or Java stored procedures which felt too heavy for what I wanted. So I dropped that idea and went with something simpler, a small math question along with a hidden field and a timing check. This combination is actually what most self hosted bot protection uses behind the scenes anyway.
My first attempt and where it broke
My first plan was to store the correct answer directly in a page item and check it on submit. I tried this but quickly realised it is not safe at all, because the answer sits right there in the page source and anyone can open dev tools and read it. So I changed the approach, instead of storing the plain answer, I generate a salted hash of the answer and only send that hash to the browser. The real answer never leaves the server.
Here is the package I finally ended up with, this generates the question and creates the hash of the answer using a secret key stored as an application item.
CREATE OR REPLACE PACKAGE pkg_captcha AS
FUNCTION generate_challenge(
p_a OUT NUMBER,
p_b OUT NUMBER
) RETURN VARCHAR2;
FUNCTION hash_answer(
p_answer IN NUMBER,
p_salt IN VARCHAR2
) RETURN VARCHAR2;
FUNCTION verify_answer(
p_answer IN NUMBER,
p_salt IN VARCHAR2,
p_hash IN VARCHAR2
) RETURN BOOLEAN;
END pkg_captcha;
/
CREATE OR REPLACE PACKAGE BODY pkg_captcha AS
FUNCTION generate_challenge(
p_a OUT NUMBER,
p_b OUT NUMBER
) RETURN VARCHAR2 IS
BEGIN
p_a := TRUNC(DBMS_RANDOM.VALUE(1,10));
p_b := TRUNC(DBMS_RANDOM.VALUE(1,10));
RETURN p_a || ' + ' || p_b || ' = ?';
END generate_challenge;
FUNCTION hash_answer(
p_answer IN NUMBER,
p_salt IN VARCHAR2
) RETURN VARCHAR2 IS
v_hash VARCHAR2(64);
BEGIN
SELECT STANDARD_HASH(p_answer || p_salt || V('APP_CAPTCHA_SECRET'), 'SHA256')
INTO v_hash
FROM dual;
RETURN v_hash;
END hash_answer;
FUNCTION verify_answer(
p_answer IN NUMBER,
p_salt IN VARCHAR2,
p_hash IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN hash_answer(p_answer, p_salt) = p_hash;
END verify_answer;
END pkg_captcha;
/After the package compiled fine, I added a page process on Before Header to fill the question and hash into hidden items. I tried running it once and got a totally different error, ORA-01400 cannot insert NULL into captcha id. That was because I was inserting a log record into a table where the id column did not auto generate any value. Fixed it by making the id column an identity column so it fills itself.
Finally this approach worked properly once I matched everything together. Here is the process code.
DECLARE
v_a NUMBER;
v_b NUMBER;
v_salt VARCHAR2(40) := DBMS_RANDOM.STRING('X', 20);
BEGIN
:P18_CAPTCHA_QUESTION := pkg_captcha.generate_challenge(v_a, v_b);
:P18_CAPTCHA_SALT := v_salt;
:P18_CAPTCHA_HASH := pkg_captcha.hash_answer(v_a + v_b, v_salt);
:P18_FORM_TS := TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS');
END;And the validation that actually checks the answer along with the honeypot and the timing check.
DECLARE
v_elapsed NUMBER;
BEGIN
IF :P18_HONEYPOT IS NOT NULL THEN
RETURN FALSE;
END IF;
v_elapsed := (SYSDATE - TO_DATE(:P18_FORM_TS,'YYYYMMDDHH24MISS')) * 86400;
IF v_elapsed < 3 THEN
RETURN FALSE;
END IF;
IF NOT pkg_captcha.verify_answer(TO_NUMBER(:P18_CAPTCHA_INPUT), :P18_CAPTCHA_SALT, :P18_CAPTCHA_HASH) THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;Small issues I faced along the way
- I tried keeping the honeypot field with display none in css but some bots skip fields that are set to display none, so I moved it off screen with position absolute instead.
- One issue I faced was my page items had different prefix than what I used in my process code, since my page number was 18 and not 1, every item had to start with P18 not P1, small mistake but it broke everything.
- I tried checking the time gap using just SYSDATE minus a text field directly and it threw conversion errors, had to convert the stored timestamp properly using TO_DATE with the exact format model.
- At one point my package body would not compile at all and every process using it failed with ORA-04063, the real reason was hidden two errors above, so I learned to always scroll up and read the very first error, not just the last one.
After fixing all of this, the captcha finally started working the way I wanted, no third party script, no external request, everything running inside my own database and my own APEX session. It is not as fancy as the big captcha services but for a small app like mine this is more than enough and I like that I fully understand every part of how it works since I built it myself.
If you want to see it running live or check the full page setup and package code, I have added both links below.
Comments
Post a Comment