Photo by Thanos Pal on Unsplash
Easily and securely check user passwords for compromise
Easily check password security in application-based security models by determining if a password was compromised in a public data breach.
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 !