0 Replies Latest reply on Mar 7, 2016 1:02 AM by Rabbit (user528481)

    Google Authenticator (TOTP)

    Rabbit (user528481)

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