Skip to main content

To N or Not To N, That is The Question?

In Microsoft SQL Server you can hash using T-SQL[1]:

declare @hash varchar(200)
set @hash = '15174141714252'
print hashbytes('MD5', @hash)

This is a nifty feature, of course, because you can now send your passwords over the unsecured SQL connection and do your hashing on the server. Secure your connection [2], please, before doing this.

Note the use of varchar(200) in the code block. The Microsoft sample shows the use of nVarChar. Does it matter what we use? Turns out, yes. The code block above returns:

0x5B17965D4E33B04FD8848E536165D013

That is also the same hash produced using System.Encoding.GetBytes(blah) and the .NET MD5 digest provider.

If you opt to use nVarChar:

declare @hash nvarchar(200)
set @hash = convert(nvarchar(200), '15174141714252')
print hashbytes('MD5', @hash)
You will get something different:

0xBA48394E1385A2C633AB7F8339231B56
nVarChar and nChar use Unicode encoding [3] to process the string bytes. The default encoding on your system may not be Unicode, so you have to be especially careful. If you used nvarchar in your update command, but varchar in your stored procedure that took the password in plain text, you would likely produce a bunch of hashes that will not match.

[1] https://msdn.microsoft.com/en-us/library/ms174415.aspx
[2] https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx
[3] https://msdn.microsoft.com/en-us/library/ms186939.aspx

Popular posts from this blog

Clustered Foolishness

I had morning coffee with a well respected friend of mine recently. Aside from chatting about the usual wifery and family, we touched on the subject of clustered indices and SQL Server performance. A common misconception in the software industry is that a clustered index will make your database queries faster. In fact, most cases will demonstrate the polar opposite of this assumption. The reason for this misconception is a misunderstanding of how the clustered index works in any database server. A clustered index is a node clustering of records that share a common index value. When you decide on an index strategy for your data, you must consider the range of data to be indexed. Remember back to your data structures classes and what you were taught about hashtable optimizations. A hashtable, which is another way of saying a database index, is just a table of N values that organizes a set of M records in quickly accessible lists that are of order L, where L is significantly less than M. ...
Read more

Deadly Information

Remember back to 2006 when a young girl killed herself [1] , [4] after being tricked and harassed by a faux boy she found on the Web using MySpace. The trial against the faux boy, an adult woman (Lori Drew), did not result in prosecution for the death of Megan, much to the dismay of many.  Yet, today we read about another trial where someone is being accused of second degree murder because they may have mentioned something slanderous about another person who was later killed by a hit man [2] . In this case, though, the person on trial is a former FBI agent who was working deep cover to infiltrate organized crime. In both cases, someone released information to third parties that resulted in the death of another person.  Neither defendant in either of these cases actually committed the act of murder, though. In the case of the FBI agent, though, the murder charge is being taken seriously. Yet, in the MySpace slander case, the murder charge was not taken seriously. How are t...
Read more

Faster Climate Change

CNN reports that a WWF study has found that global climate change is happening faster than predicted in 2007 and that there will not be any arctic ice by 2013, or 2040. [1] Then it goes on to say that global sea level will increase by 1.08 meters by the end of the century, which is 2100, 92 years from now. Quite honestly, nobody really cares what is going to happen to the planet in 98 years. Why? Because in 98 years we (as humans) will either: (1) Obliterate ourselves because God told us to do it. (2) Eat eachother because there will no longer be any land available to grow crops and sustain living quarters for our 50 billion people. (3) Suffocate because our planet will no longer smell nice thanks to 50 billion people producing lots of solid waste in our oceans. (4) Leave the planet because there will no longer be enough fresh water to sustain our lives. Wait a minute. Consider (4) for a moment. Where can we get an abundance of fresh water TODAY? Anyone? Yeah, the arctic! It's goin...
Read more