APEX

Should anyone be searching for a TOTP authentication method which works with the Google Authenticator here is a not so pretty PL/SQL block which should do the trick. Needs to be wrapped in a function and some user/key management added.

DECLARE
  cBASE32 CONSTANT VARCHAR2(32) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
  cSecret CONSTANT VARCHAR2(20) := 'JBSWY3DPEHPK3PXP';
  szBits VARCHAR2(500) := '';
  szTmp VARCHAR2(500) := '';
  szTmp2 VARCHAR2(500) := '';
  nPos NUMBER;
  nEpoch NUMBER(38);
  szEpoch VARCHAR2(16);
  rHMAC RAW(100);
  nOffSet NUMBER;
  nPart1 NUMBER;
  nPart2 NUMBER := 2147483647;


  FUNCTION to_binary(inNum NUMBER) RETURN VARCHAR2
  IS
  szBin VARCHAR2(8);
  nRem NUMBER := inNum;
  BEGIN
  IF inNum = 0 THEN
  RETURN '0';
  END IF;
  WHILE nRem > 0
  LOOP
  szBin := MOD(nRem, 2) || szBin;
  nRem  := TRUNC(nRem / 2 );
  END LOOP;
  RETURN szBin;
  END to_binary;
BEGIN


  FOR c IN 1..LENGTH(cSecret)
  LOOP
  nPos := INSTR( cBASE32, SUBSTR(cSecret, c, 1))-1;
  szBits := szBits || LPAD( to_binary(nPos), 5, '0');
  END LOOP;

  nPos := 1;
  WHILE nPos < LENGTH(szBits)
  LOOP
  SELECT LTRIM(TO_CHAR(BIN_TO_NUM( TO_NUMBER(SUBSTR(szBits, nPos, 1)), TO_NUMBER(SUBSTR(szBits, nPos+1, 1)), TO_NUMBER(SUBSTR(szBits, nPos+2, 1)), TO_NUMBER(SUBSTR(szBits, nPos+3, 1)) ), 'x'))
  INTO szTmp2
  FROM dual;
  szTmp := szTmp || szTmp2;
  nPos := nPos + 4;
  END LOOP;

  SELECT EXTRACT(DAY FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*86400+
    EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*3600+
    EXTRACT(MINUTE FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*60+
    EXTRACT(SECOND FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00')) n
  INTO nEpoch
  FROM dual;

  SELECT LPAD(LTRIM(TO_CHAR( FLOOR(nEpoch/30), 'xxxxxxxxxxxxxxxx' )), 16, '0')
  INTO szEpoch
  FROM dual;

  rHMAC := DBMS_CRYPTO.MAC( src => hextoraw(szEpoch), typ => DBMS_CRYPTO.HMAC_SH1, key => hextoraw(szTmp) );

  nOffSet := TO_NUMBER( SUBSTR( RAWTOHEX(rHMAC), -1, 1), 'x');

  nPart1 := TO_NUMBER( SUBSTR( RAWTOHEX(rHMAC), nOffSet*2+1, 8), 'xxxxxxxx');

  DBMS_OUTPUT.PUT_LINE( SUBSTR(BITAND( nPart1, nPart2), -6, 6) );
END;
/