Creating a One Way Hash for Data Lookup

With the push to further secure and protect the data stored, there are a bunch of instances where we need to be able to compare a data field's value without ever needing to actually retrieve that data in a usable format. In the design implementation we show here, the data is encrypted using a good encryption algorithm in SHA2-512, but in this instance we use a randomly generated Salt with the intent of making this essentially a one way encryption. The result is a value that can really ONLY be reversed by brute forcing the data set. In this case, it would require running every possible value through the crypt comparison process and then use that to reverse to the associated data.

What we are presenting is a pretty typical way of storing passwords as well. That is a prime example where having the system not know, nor be able to resolve the contents of the data is a great security practice.

Create the Functions to Encrypt the Data

In order to generate a truly random salt, we need a view where every query on it generates a new and unique value.

create or alter view dbo.random_id as   
	select newid() as value  
go

With that random, let is generate a salt that we can use on every encryption so that the resulting values are always unique, making them far more difficult to reverse. In this particular function, we are also allowing the user to selected a hashing algorithm for the salt. This hash could be randomly selected, and it would not impact the usability, only the speed.

create or alter function dbo.gen_salt ( @type varchar(8) )  
    returns varchar(24)  
as  
begin  
    DECLARE @salt varchar(25);  
    set @salt = ''  
    DECLARE @id varchar(64);  
    select @id = (select top 1 value from dbo.random_id)  
    set @salt = SUBSTRING(master.dbo.fn_varbintohexstr(hashbytes(@type, @id)), 3, 24)  
    return @salt  
end  
go

Now it is time to implement an actual encryption call built upon the prior two bits. This in turn assembles the resulting string that we can store, but is nigh impossible to decrypt because we have no way to resolve what the salt was at the time the encryption process was run.

create or alter function dbo.crypt (  
    @value varchar(2048),  
    @salt varchar(3092) ) returns varchar(3092) as begin  
    declare @result varchar(3092)  
    declare @vh varchar(2048)  
    set @vh = SUBSTRING(master.dbo.fn_varbintohexstr(hashbytes('SHA2_512', @value)), 3, 2048)  
    declare @sh varchar(24)  
    set @sh = substring(@salt, 0, 24)  
    declare @h varchar(3092)  
    set @h = SUBSTRING(master.dbo.fn_varbintohexstr(hashbytes('SHA2_512', rtrim(@sh) + @vh)), 3, 3092)  
    set @result = SUBSTRING(rtrim(@sh) + @h, 0, 3092)  
  
    return @result  
end  
go

With the above in place, we can test generation of encrypting a value. You run the following sql 10,000 without ever duplicating the resulting value, however, if you know the original value, you can get 10,000 matches.

select dbo.crypt('value_to_compare', dbo.gen_salt('MD5'))

A more complete example. Here we create a table and we store values and encrypted versions for demonstration purposes. In a live system we would ONLY store the encrypted versions.

drop table if exists pii_hash_test;  
create table pii_hash_test (  
    id int identity(1,1),  
    value varchar(2048),  
    ssn_hash varchar(3096)  
);  
  
insert into pii_hash_test (value, ssn_hash)  
select '123-45-6789', dbo.crypt('123-45-6789', dbo.gen_salt('SHA2_512'));  
insert into pii_hash_test (value, ssn_hash)  
select '123-45-6789', dbo.crypt('123-45-6789', dbo.gen_salt('SHA2_512'));  
insert into pii_hash_test (value, ssn_hash)  
select '123-45-6789', dbo.crypt('123-45-6789', dbo.gen_salt('MD5'));  
insert into pii_hash_test (value, ssn_hash)  
select '123-45-6789', dbo.crypt('123-45-6789', dbo.gen_salt('SHA2_256'));  
  
insert into pii_hash_test (value, ssn_hash)  
select '123-45-6788', dbo.crypt('123-45-6788', dbo.gen_salt('SHA2_512'));  
insert into pii_hash_test (value, ssn_hash)  
select '123-45-6788', dbo.crypt('123-45-6787', dbo.gen_salt('SHA2_512'));  
  
select * from pii_hash_test;  

Once run, we get a data set that looks like this, where the same value has wildly different encrypted versions.

	value	hash
1	123-45-6789	5917385cdc1ca82f965d31b08...8a84e42d8add1d1f3c26106f5
2	123-45-6789	5ac31f9a39ddfe17801508be6d...bc0c0a9015e5a2cb76aa38fe5e
3	123-45-6789	bec53de10d17a4987df80ed64...c793258d9b75b351ac52048bc
4	123-45-6789	c601d7d96271504744a38ae11...91b49285abc79dd0d6474ac
5	123-45-6788	ee4a38b6400a1b4f6062f26c1f...68027404197fd4191653057a
6	123-45-6788	061385869a165a99e097c5a5f...4dd8b750904a85c38f5cbde

With that data set, if we where to run a SQL Query against where we have the PII Value, we could validate that what we have in hand indeed matches what we once entered, without storing the value in a readily reversed format, or in this case, get all 4 records that match that entered value.

select * from pii_hash_test where ssn_hash = dbo.crypt('123-45-6789', ssn_hash);

All of this while storing only an unencryptable data element. That sounds great, and super secure right? If this is the case why is it so important to use strong passwords?

In full disclosure, though this is very difficult to reverse, nothing it truly impossible, so in our opinion this would be the 'next best thing to not storing the data in ANY form'.

Much like a password, the longer and more complex the data we are storing, the harder it is to brute force. In this example, we are using a format that matches a Social. Due to the relative weakness that a Social represents at just 9 significant digits, a brute force attack with the knowledge of how to do this comparison could take as little as 2 hours. What exactly does that type of brute force attack look like? Using the following SQL against the above sample data set of just 6 records, we are brute forcing using 1/10th the possible values against a value we KNOW to be in that range.

DECLARE @bruteNumber bigint  
SET @bruteNumber=100000000  
WHILE ( @bruteNumber <= 200000000)  
BEGIN  
    declare @n varchar(9)  
    declare @v varchar(11)  
    declare @c int  
    set @n = CONVERT(VARCHAR,@bruteNumber)  
    -- format the number to a social security number ( ###-##-#### )  
    set @v = SUBSTRING(@n,1,3)+'-'+SUBSTRING(@n,4,2)+'-'+SUBSTRING(@n,6,4)  
  
    select @c = count(*) from pii_hash_test where ssn_hash = dbo.crypt(@v, ssn_hash);  
    if (@c > 0)  
        begin  
            print 'Found match for ' + @v + ''  
            break  
        endSET @bruteNumber  = @bruteNumber  + 1  
END

Using this code running on a 4 year old MacBook Pro hosting SQL Server in a Docker container, it took over 12 hours to resolve the social back to those 4 records by simply trying every single number between 100,000,000 and 200,000,000. Yes, that is 100 million guesses ( out of a possible 1 trillion ). Using a relatively slow computer and configuration, that is all it took.

With faster compute power, it would be orders of magnitudes faster, so yes, 2 hours is within reason.

So why all of this detail? to say that no matter how stout the encryption, there are ways to attack the problem to get around the. More importantly though, to demonstrate just how complexity of a the data matters every bit as much as the encryption. ( and also to establish that there really is no foolproof way to store information like a Social Security number that is not subject to some form of brute force attack if the data is compromised ).