Context

Wouldn't it be great to be able to determine whether a password has been compromised in a data breach? Well, the website Have I Been Pwned contains millions of de-duplicated (within the same breach) user IDs and passwords that appeared in public data breaches. The database can be queried through their website or using APIs to determine how many, if any, times a User ID and/or password occur. Obviously, the higher the number the less secure the User ID and/or password.

The query can be performed without providing the password to the database. How? Encrypt the password and send the first five encrypted key characters to the API. The API returns the encryption key (minus the first five characters that were passed) of all breached passwords whose first five encrypted key characters match those provided along with the number of breaches. Then compare the starting password's encryption key (minus the first five characters) to the list for a match and determine the number of breaches if one is found. The starting password never goes over the wire in clear text or encrypted.

A word of caution when notifying a user that their password appears in data breaches. In so many words you may want to make clear:

• The breach did not occur in your system, assuming that is correct
• Their password was not shared with anyone else when determining if it was found in breaches.
• If they use the password anywhere else, they should change it to something more secure

Objective

Create a PL/SQL function that accepts a password and returns the number of "Have I Been Pwned" public breach database occurrences.

Benefit

Check password security in application-based security models by determining if a password was compromised in a public data breach. The higher the count the less secure the password. Passwords can be checked during sign on and/or password entry/change.

Solution

Create the following network ACL for the schema User ID allowing access to the provided Have I Been Pwned API.

 begin
  dbms_network_acl_admin.append_host_ace(
    host => 'api.pwnedpasswords.com',
    ace => xs$ace_type(
    privilege_list => xs$name_list('connect','resolve'),
    principal_name => '[upper case schema name]',
    principal_type => xs_acl.ptype_db));
  commit;
end;

Create a function that accepts the full password to be tested and returns the number of occurrences in the Have I Been Pwned database. Functions called are described below as well.

(A status table managed through an APEX application for turning the password check on and off could be helpful. If the Have I Been Pwned service is unavailable or for some reason your application cannot call the API, it gives the option to allow user sign on to continue until the issue is resolved without having to modify source code. An error, logging, and alert handler would be useful for the same reasons. These measures are not detailed here because each organization often has their own standards for doing so.)

  --
  -- Accept a password and check to see if it is in the "Have I been pawned" list
  -- Return the number of times the password was found in public breaches, zero
  -- if there are not any matches.
  --
  Function Have_I_Been_Pawned( p_CR_Password     IN  User_Creds.CR_Password%Type)
    Return Number
    Is
    v_Password_SHA1         Varchar2(50);
    v_Password_SHA1_5       Varchar2(50);
    v_Pwn_Return            Varchar2(32767);
    v_Num_Occur             Number(20);
    v_Message               App_Error.Ae_Message%Type;
    e_Error                Exception;
    Begin
    If p_CR_Password Is Null  Then
      v_Message                := 'Error: Password required to test but missing.';
      Raise e_Error;
    End If;

    -- Hash p_CR_Password using SHA1 to match has of Pawned data
    -- for comparison
    --
    v_Password_SHA1 := Convert_Pwd_to_SHA1(p_CR_Password => p_CR_Password);

    -- Parse first 5 characters of Password Hash for use in Pawned API range value
    v_Password_SHA1_5 := Substr(v_Password_SHA1,1,5);

    -- Retrieve text string of potential matchs from Pawned API.  Values exclude the 
    -- first five characters provided for the range search.  In order to match the 
    -- SHA1 value of the password provided use the value of the hashed password 
    -- without the first five characters.
    v_Pwn_Return    := Qry_Pawned_Similar_Passwords(p_SHA1_First_Five=> v_Password_SHA1_5);

    -- 
    -- Accept SHA1 version of password for comparison, and the return result for 
    -- the given 5 character SHA1 range from Have I been pawned, parse the return
    -- result comparing each SHA1 hex string to the provided SHA1 password and
    -- determine if there is a match.
    -- Return the number of occurrences if there is a match, else zero for no matches
    --
    v_Num_Occur     := Qry_How_Many_Occurences( v_Password_SHA1, v_Pwn_Return);
    Return v_Num_Occur;
    Exception
        When e_Error Then
      -- Your logging and error handling here
        When Others Then
      -- Your logging and error handling here
  End Have_I_Been_Pawned;

Convert Password to SHA1:

  --
  -- Accept a varchar2 p_Cr_Password and return the raw hased SHA1 equivalent
  --
  Function Convert_Pwd_to_SHA1( p_CR_Password   IN User_Creds.CR_Password%Type)
    Return Varchar2
    Is
    v_USER_CREDS    USER_CREDS%RowType;
    v_Result            Varchar2(1)                       := 'N';
    v_CR_Password_SHA1  Varchar2(100);
    v_CR_Password_Raw   Raw(20);
    v_Password_Request  Varchar2(70);
    v_Req               utl_http.req;    -- Var to open session with v_Password_Request URL
    v_Ret               utl_http.resp;
    v_Data              varchar2(32767); -- Data returned from Api including SHA1 value ':' # of occurrences
    v_Pwd_SHA1_Return   Varchar2(32767);  -- Password portion of return value (Left side of colon)
    v_Occurences_Return Number(10);  -- Number of occurrences of compromised password in breaches
    v_Message                 App_Error.Ae_Message%Type;        
    e_Error                     Exception;

    Begin
    If p_CR_Password Is Null  Then
        v_Message                := 'Error: Password required to test, but missing.';
        Raise e_Error;
    End If;

    -- Hash provided password with SHA1 for comparison to the SHA1 hashed 
    -- passwords returned from the compromised password api
    --
    Begin
      v_CR_Password_Raw   := utl_i18n.string_to_raw (p_CR_Password, 'AL32UTF8');
      v_CR_Password_SHA1  := DBMS_CRYPTO.HASH (src => v_CR_Password_Raw, typ => DBMS_CRYPTO.hash_sh1);
    End;
    Return v_CR_Password_SHA1;
    Exception
        When e_Error Then
      -- Your error handling and logging here
        When Others Then
      -- Your error handling and logging here  
  End Convert_Pwd_to_SHA1;

Query for similar pawned passwords:

  --
  -- Accept the first five SHA1 hash characters from a p_CR_Password User_Creds.CR_Password%Type 
  -- variable and return 
  -- a concatenated string of compromised passwords from https://api.pwnedpasswords.com/range/
  -- that could be a match.  Each string segment is the SHA1 password || ':' || # of occurences
  -- 
  Function Qry_Pawned_Similar_Passwords( p_SHA1_First_Five  IN Varchar2)
    Return Varchar2
    Is
    USER_CREDS    USER_CREDS%RowType;
    v_Result            Varchar2(32767);
    v_CR_Password_SHA1  Raw(20);
    v_CR_Password_Raw   Raw(20);
    v_Password_Request  Varchar2(70);
    v_SHA1_First_Five   Varchar2(5);
    v_Req               utl_http.req;    -- Var to open session with v_Password_Request URL
    v_Ret               utl_http.resp;
    v_Data              varchar2(32767);  -- Data returned from Api including SHA1 value ':' # of occurences
    v_Pwd_SHA1_Return   Varchar2(32767);    -- Password portion of return value (Left side of colon)
    v_Occurences_Return Number(10);  -- Number of occurences of compromised password in breaches
    v_Message                      App_Error.Ae_Message%Type;        
    e_Error                          Exception;

    Begin
    If p_SHA1_First_Five Is Null  Then
        v_Message                := 'Error: Password portion required to test, but missing.';
        Raise e_Error;
    End If;

    -- The range option accepts the first five characters of the hashed string
    -- and returns all values that match to save search time in the api, and 
    -- avoid sharing the entire hash string with the site so even if the 
    -- passed parameter were intercepted or reviewed on the api server it 
    -- would not reveal the password that is being checked.
    --
    v_SHA1_First_Five  := Substr(p_SHA1_First_Five,1,5);
    v_Password_Request := 'https://api.pwnedpasswords.com/range/' || v_SHA1_First_Five;

    -- Retrieve compromised passwords with the same first 5 characters and compare
    -- the full hex string of the provided password to each of the compromised
    -- passwords returned from the api to see if there is a match.
    --
    begin
      utl_http.set_wallet('');
      v_Req := utl_http.begin_request(v_Password_Request );
      v_Ret := utl_http.get_response(v_Req);
      begin
        loop
          -- Get next line of data from list of compromised passwords
          utl_http.read_text(v_Ret,v_data,32766);
          -- Concatenate strings retrieved
          v_Result    := Substr(v_Result || v_data,1,32767);
        End Loop;
      Exception
        when utl_http.end_of_body then
          -- Review complete, close session
          utl_http.end_response(v_Ret);
      End;
    Exception
      When others then
        -- Close session
        utl_http.end_response(v_Ret);
        -- Your logging and error handling here
    End;
        Return v_Result;
    Exception
        When e_Error Then
      -- Your logging and error handling here
        When Others Then
      -- Close session
      utl_http.end_response(v_Ret);
      -- Your logging and error handling here
  End Qry_Pawned_Similar_Passwords;

Query How Many Occurrences

  -- 
  -- Accept SHA1 version of password for comparison, and the return result for 
  -- the given 5 character SHA1 range from Have I been pawned, parse the return
  -- result comparing each SHA1 hex string to the provided SHA1 password and
  -- determine if there is a match.
  -- Return the number of occurrences if there is a match, else zero for no matches
  --
  Function Qry_How_Many_Occurences( p_CR_Pwd_Raw_SHA1     IN  Varchar2,
                                    p_Pwnd_Return_String  IN  Varchar2)
    Return Number
    Is

    v_CR_Pwd_SHA1_Minus_5 Varchar2(50);
    v_Match_Start         Number(20);
    v_Num_Occur_Segment Number(20);    -- Total number of breaches the current SHA1 segment appears in
    v_Num_Begin                    Number(20);
    v_Num_End                        Number(20);
    v_Message                      App_Error.Ae_Message%Type;        
    e_Error                          Exception;

    Begin
    -- Take the first five characters used as range in the api call off of the front
    -- so it can be used for comparison purpose to the return string.
    --
    v_CR_Pwd_SHA1_Minus_5 := Substr(p_CR_Pwd_Raw_SHA1,6,40);

    -- Check to see if the provided SHA1 string occurs in the return string, if so
    -- first position of the string.
    v_Match_Start     := Instr(p_Pwnd_Return_String, v_CR_Pwd_SHA1_Minus_5);

    If v_Match_Start > 0 Then 
      -- A Match was found
      -- Determine how many times the match occurred in public breaches
      v_Num_Begin       := Instr(p_Pwnd_Return_String,':',v_Match_Start+1) + 1;  -- Position number of occurences starts in
      v_Num_End         := Instr(p_Pwnd_Return_String,':',v_Match_Start,2)-37; -- Position number of occurences ends 
      If v_Num_End = 0 Or v_Num_End < v_Num_Begin Then
        -- Either the match was the last or first string
        v_Num_End       := v_Num_Begin + 20;
      End If;
      v_Num_Occur_Segment := Nvl(Substr(p_Pwnd_Return_String, v_Num_Begin, v_Num_End - v_Num_Begin ),1);
    Else
      -- Did not occur in breaches
      v_Num_Occur_Segment := 0;
    End If;
    Return v_Num_Occur_Segment;
    Exception
        When e_Error Then
      -- Your logging and error handling here
        When Others Then
      -- Your logging and error handling here
  End Qry_How_Many_Occurences;

Hopefully you found this helpful. Comments and likes welcome !