Search results matching tags 'SQL' and 'SHA-384'

Find a Hash Collision, Win $100

Mike C — Sat, 17 Apr 2010 21:38:00 GMT

Margarity Kerns recently published a very nice article at SQL Server Central on using hash functions to detect changes in rows during the data warehouse load ETL process. On the discussion page for the article I noticed a lot of the same old arguments against using hash functions to detect change. After having this same discussion several times over the past several months in public and private forums, I've decided to see if we can't put this argument to rest for a while. To that end I'm going to hold a little contest: Generate an SHA-1 hash collision and win $100 and a book (see bottom section for details). Before I get into the details of the contest I'm going to give a little background of how this came about.

Background Info

NOTE: If you aren't familiar with hash functions I highly recommend first reading the Wikipedia article at http://en.wikipedia.org/wiki/Cryptographic_hash_function.

The idea of using a hash function for change detection is not new. Essentially a hash function generates a "fingerprint" of your data that you can use to compare an inbound row and an existing row.

Some people are wary of hash functions because they map a theoretically infinite number of large inputs to a much smaller finite set of hash values. Most of the arguments people make against using hash functions for change detection boil down to variations of Murphy's Law:

"There's a chance of a hash collision [generating the same hash value for two different inputs], so a collision will happen!"

People have different ways of dealing with this issue, including taking one of the following positions:

The chance of collision is negligible so no additional precautions are required.
A collision will absolutely happen so I won't use hash functions for change detection at all!
A collision may happen so I want to use hash values only to initially narrow down the number of rows I need to compare fully.

Positions #1 and #2 above are at different ends of the spectrum. Position #3 sits in the middle as a compromise solution. While compromises may make for good politics, they often make for terrible technical solutions, as I'll discuss below.

Position #1: Odds of Collision are Low Enough to be Ignored

As far as position #1 is concerned, it depends on which hash function you're using. You need to choose a true one-way collision-free* cryptographic hash function with a wide bit length. I normally recommend an SHA-2 hash function (256, 384 or 512 bit hash value), or when that's not available the SHA-1 160 bit hash function. The odds of generating a collision with a 160 bit hash function are 2^80. That is to say you can expect a collision after you generate hashes for 1,208,925,819,614,629,174,706,176 rows of data.

Of course if you're identifying rows by their natural or business keys this alternatively means you need to generate 1,208,925,819,614,629,174,706,176 variations of that single row before you'll hit a collision with SHA-1.

To put that number in perspective, consider that Google processes 20,000,000,000,000,000 bytes (20 petabytes) of data per day. If you were to store a single row in a database table for every single byte Google processes each day, it would take you 60,446,290 days (approximately 156,600 years) to store 1,208,925,819,614,629,174,706,176 rows in that table.

I personally assume position #1 on this subject, with the assumption that you have chosen a good solid hash function for the job. More on this later.

*A collision-free cryptographic hash function is a one-way hash function with negligible probability of generating the same hash value for two different inputs. SHA-1 and SHA-256 are examples of collision-free cryptographic hash functions.

Position #2: I Don't Trust Hash Functions

This position can't really be argued with. As shown above the odds of a collision with SHA-1 or another collision-free hash function are extremely low. But if you don't trust it, you just don't trust it. So the alternative is to compare every inbound column with every existing column. It will cost you in efficiency on wide tables, but if you're not concerned about processing power, server resources and execution time this classic method of change detection is well-proven to be 100% effective.

Position #3: The Compromise - Use Hash Values to Initially Narrow Down Results

This position is the compromise position that combines the implementation of #1 and #2 above. It sounds wonderful in theory - use a hash function to narrow down your results, eliminating rows that don't need to be compared column by column; then compare all of the columns in the remaining rows that haven't been eliminated. So let's look at a scenario:

You are processing Row A through your ETL process into a target table. Row B is the equivalent row in the target table (it has the same natural key/business key as Row A). This assumes we are first locating the equivalent row in the target table by natural key/business key of the incoming row.

There are three possible scenarios:

Row B exists in the target table, and is equal to Row A (no change).
Row B exists in the target table, but it is not equal to Row A (update).
Row B does not exist in the target table (insert Row A).

Let's say you've generated two hash values, h(A) is the hash for Row A and h(B) is the hash for Row B. Now we need to use h(A) and h(B) to eliminate rows to get rid of the extra column by column comparisons. Here are the rules you need to implement to use h(A) and h(B) to eliminate extra comparisons in this compromise solution:

A. h(A) is equal to h(B): according to the compromise, if h(A) = h(B) we need to compare all columns of the inbound row against the existing row since the belief is that the hash function can/will generate collisions. The idea is that h(A) may have generated the same value as h(B) even if A <> B. So we need to:

(1) Compare all columns in A and B. If A = B then perform no action.

(2) Compare all columns in A and B. If A <> B then update.

B. h(A) is not equal to h(B): cryptographic hash functions guarantee that they will generate the same hash value for the exact same inputs. So we can eliminate full row comparisons if h(A) <> h(B). We know automatically that if h(A) <> h(B) then A <> B. Just perform the update.

C. h(B) is NULL: that is, if Row B does not exist in the target table than h(B) is NULL. This is a case where no further full-row comparisons are necessary. Just insert the row.

Now consider a slowly changing dimension (SCD) in a datamart application. Many SCDs change slowly over time (hence the name slowly changing dimension). This means that new rows (updates and inserts) are far less common than receiving duplicate rows during ETL. So the vast majority of your inbound data will fall under rule A(1) above. So you're still performing comparisons of all columns for the vast majority of rows in a given table just to figure out that you don't need to update them after all!

If you eliminate even 90% of the inbound rows under rule A(1) above you haven't saved much processing (you're still comparing all columns for changes for 90% of your inbound rows). You probably actually cost yourself a lot of time and efficiency since you haven't accounted for the overhead of generating hash values for 100% of the inbound rows.

The only way this compromise is more efficient is if a very large percentage of your inbound rows (much greater than 50+%) are inserts under Rule C or updates under Rule B above. If the majority of your inbound rows are duplicates of existing rows under Rule A, you gain nothing.

The Contest

One-way collision-free cryptographic hash functions are supposed to have negligible probability of a hash collision, or two different inputs generating the same output. Hash collisions are what cause change detection with hashes to fail.

For instance, consider the following example of an MD5 hash collision:

DECLARE @A varbinary(8000),
      @B varbinary(8000),
      @hA binary(16),
      @hB binary(16);

SELECT @A = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab58712467eab4004583eb8fb7f8955ad340609f4b30283e488832571415a085125e8f7cdc99fd91dbdf280373c5bd8823e3156348f5bae6dacd436c919c6dd53e2b487da03fd02396306d248cda0e99f33420f577ee8ce54b67080a80d1ec69821bcb6a8839396f9652b6ff72a70,
      @B = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab50712467eab4004583eb8fb7f8955ad340609f4b30283e4888325f1415a085125e8f7cdc99fd91dbd7280373c5bd8823e3156348f5bae6dacd436c919c6dd53e23487da03fd02396306d248cda0e99f33420f577ee8ce54b67080280d1ec69821bcb6a8839396f965ab6ff72a70;

SELECT @hA = HASHBYTES('MD5', @A),
      @hB = HASHBYTES('MD5', @B);

SELECT CASE WHEN @A = @B
                  THEN '@A Equals @B'
                  ELSE '@A Is Not Equal To @B'
                  END AS AB_Equal,
            CASE WHEN @hA = @hB
                  THEN '@hA Equals @hB'
                  ELSE '@hA Is Not Equal To @hB'
                  END AS Hash_Equal;

The results are shown below:

When you run this you'll notice that the query reports the two source varbinary strings @A and @B are not equal, yet the two MD5 hashes they generate are equal. This is an example of a simple hash collision with MD5.

Now the challenge is to populate the following script with two different binary values that generate the same hash value. The output should be the same as shown above in the MD5 example.

-- Begin script
DECLARE @A varbinary(8000),
      @B varbinary(8000),
      @hA binary(20),
      @hB binary(20);

-- Replace the ? below with binary strings

SELECT @A = ?,
      @B = ?;

SELECT @hA = HASHBYTES('SHA1', @A),
      @hB = HASHBYTES('SHA1', @B);

SELECT CASE WHEN @A = @B
                  THEN '@A Equals @B'
                  ELSE '@A Is Not Equal To @B'
                  END AS AB_Equal,
            CASE WHEN @hA = @hB
                  THEN '@hA Equals @hB'
                  ELSE '@hA Is Not Equal To @hB'
                  END AS Hash_Equal;
-- End script

The first person who sends me an example of two varbinary strings that generate the same SHA1 hash value will win $100 (US$) and a copy of my book Pro T-SQL 2008 Programmer's Guide.

And here are the inevitable conditions:

No NULLs. @A and @B in the script above cannot be set to NULL for purposes of this contest.
8,000 bytes or less. The T-SQL HASHBYTES function accepts varbinary(8000) values, so the values passed into it in this contest must be 8,000 bytes in length or less. The values assigned to @A and @B above must be 8,000 bytes or less in length.
No unnecessary changes to the script. The only change allowed to the script above are the replacement of the question marks (?) with binary strings. No other changes to the script are authorized.
Only one person will win. The first person who sends me a copy of the above script with two different binary values that generate an SHA-1 hash collision will win.
Void where prohibited. Obviously if contests like this aren't legal in your country, state, county, city, etc. then you can't take part. Petition your government to make it legal :)
Time limits. Entries must be received prior to midnight U.S. Eastern Standard Time on October 31, 2010.
Decisions of the judge are final. For purposes of this contest that would be me.
SQL Server 2005 or 2008. Entries must be runnable on SQL Server 2005 and SQL Server 2008 Developer Edition, and the results must be reproducible.

If a winning entry is received prior to the deadline, I'll post an update entry to the blog with the winning script and the name of the winner.

Let's Hash a BLOB

Mike C — Sun, 12 Apr 2009 20:50:00 GMT

In my last post I talked about how to work around a couple of the limitations of SQL Server encryption by using SQL CLR and the .NET Framework to encrypt a BLOB value (up to 2.1 GB in size), using any supported algorithm you choose. In the example I used AES to encrypt data using a passphrase. SQL Server 2008 also allows you to generate cryptographic hashes of binary data using the MD2, MD4, MD5 or SHA-1 hash algorithms. The HashBytes function provides this functionality, as shown in the sample below:

SELECT HashBytes(N'SHA1', 'This is a test');

The result is a 160-bit binary hash value:


0xA54D88E06612D820BC3BE72877C74F257B561B19

The MD2, MD4 and MD5 algorithms all produce 128-bit binary hash values. There's a problem: the MDn-series of 128-bit hashes are currently considered insecure by cryptographers, and it's not advisable to use them for cryptographically secure applications. SHA-1 is considered more secure, but it's hash value length of 160 bits is considered relatively small by today's standards. The National Institute of Standards and Technology has already taken steps to require all federal agencies to stop using SHA-1 in 2010. Instead they are requiring federal agencies to use SHA-2 family of algorithms, which includes SHA-256, SHA-384 and SHA-512, which generate more cryptographically secure hash values that are 256, 384 and 512 bits in length.

SQL Server's HashBytes function doesn't give you the ability to access these more secure hash functions, but they are available through the .NET Framework and SQL CLR. The SQL CLR GetHash function below mirrors the functionality of the built-in HashBytes function. This function accepts an Algorithm name, which can be SHA256, SHA384, or SHA512. You also need to pass the Plaintext value to be hashed to the function. While HashBytes is limited to an 8,000 byte plaintext value (it truncates everything past 8,000 bytes), the GetHash function accepts a varbinary(max) up to 2.1 GB in size.


[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlBytes GetHash
(
  SqlString Algorithm, 
  [SqlFacet(MaxSize = -1)] SqlBytes Plaintext
)
{
  if (Algorithm.IsNull || Plaintext.IsNull)
    return SqlBytes.Null;
  bool HashDefined = true;
  HashAlgorithm Hash = null;
  switch (Algorithm.Value.ToUpper())
  {
    case "SHA256":
      Hash = new SHA256Managed();
      break;
 
    case "SHA384":
      Hash = new SHA384Managed();
      break;
 
    case "SHA512":
      Hash = new SHA512Managed();
      break;
 
    default:
      HashDefined = false;
      break;
  }
  if (!HashDefined)
    throw new Exception("Unsupported hash algorithm - use SHA256, SHA384 or SHA512");
  byte[] HashBytes = Hash.ComputeHash(Plaintext.Value);
  // Convert result to a SqlBytes value
  return new SqlBytes(HashBytes);
}

As you can see, accessing .NET Framework hash functionality through the SQL CLR is very simple. In a future post I'll talk about making your hash values even more secure to protect against so-called "rainbow table" attacks.