HASHBYTES
-
Comments posted to this topic are about the item HASHBYTES
-
Hashbrowns, HASHBYTES, Hashish its all the same 😛
Nice question and explanation. Thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Very interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Interesting question, however, I don't understand why the "LEFT JOIN"-construction was used.
Distracts a little bit from what was the essential part of the question.
-
This was removed by the editor as SPAM
-
Nice and interesting one......
-
Good one....
-
Very interesting question - thanks.
-
Can we get the syntax highlighting working again? These are hard to read without it. What happened?
-
Nice question.
Sidebar:
I love HASHBYTES for detecting EXACT duplicates in certain situations where there are lots of columns. Lots of folks try CHECKSUM first.....only to be disappointed in the end. It all depends on the situation on when and how you use it.
-
Excellent question on an interesting topic. Thanks much to the Devil.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Nice question, but the real point is somewhat buried in rather a lot of coding clutter.
Of course the answer relies on there being no hash collision between one of the three 24-bit strings and the corresponding 160 bit string, which is true for these three 24 bit strings but maybe not for all 24-bit strings, since the 160th power of 2 is large enough to cover the hash result space for MD5. So answering 3 is just a (very) high probability guess unless once either knows that these 3 values are safe or runs the code (my answer was based on the very high probability guess).
Something that might have been usefully mentioned in the explanation is that MD5 is now regarded as an insecure hash, and should never be used when a secure hash is required. Worries that it might be too easy to manufacture collisions have been around since 1996, it has been known that these worries are very well founded at least since 2004, X509 Certificate collisions were successfully created in 2005, and several further problems have been discovered since, leading to successful creation of a forged intermediate certificate authority certificate in 2008 (which caused Verisign to stop using Md5 for such certificates), culminating in the successful forgery of a Microsoft windows code signing certicicate in 2012. But people continue to treat it as a secure hash, despite all the warnings and known problems.
Tom
-
L' Eomot Inversé (1/21/2014)
Of course the answer relies on there being no hash collision between one of the three 24-bit strings and the corresponding 160 bit string, which is true for these three 24 bit strings but maybe not for all 24-bit strings, since the 160th power of 2 is large enough to cover the hash result space for MD5. So answering 3 is just a (very) high probability guess unless once either knows that these 3 values are safe or runs the code (my answer was based on the very high probability guess).Agreed - all hashing functions have collisions when provided with enough data; it's an aspect of generating a small amount of data to represent a large amount of data.
MD5 is particularly bad - other than CHECKSUM variants, it's the most common really broken algorithm I'm aware of today.
MD5 collision Stackexchange discussion: http://crypto.stackexchange.com/questions/1434/are-there-two-known-strings-which-have-the-same-md5-hash-value
MD5 collision examples in SQL (originally from http://www.sqlnotes.info/2012/01/20/md5-collision/[/url]
DECLARE @a VARBINARY(927)
DECLARE @b-2 VARBINARY(927)
-- Classic example documented by Peter Selinger Feb 22, 2006: http://www.mscs.dal.ca/~selinger/md5collision/
SET @a = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab58712467eab4004583eb8fb7f8955ad340609f4b30283e488832571415a085125e8f7cdc99fd91dbdf280373c5bd8823e3156348f5bae6dacd436c919c6dd53e2b487da03fd02396306d248cda0e99f33420f577ee8ce54b67080a80d1ec69821bcb6a8839396f9652b6ff72a70
SET @b-2 = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab50712467eab4004583eb8fb7f8955ad340609f4b30283e4888325f1415a085125e8f7cdc99fd91dbd7280373c5bd8823e3156348f5bae6dacd436c919c6dd53e23487da03fd02396306d248cda0e99f33420f577ee8ce54b67080280d1ec69821bcb6a8839396f965ab6ff72a70
-------------------------------------------------*---------------------------------------------------*---------------------------*-----------------------------------------------*---------------------------------------------------*---------------------------*---------
-- X.509 example, referenced in http://stackoverflow.com/questions/1224113/examples-of-hash-collisions
SET @a = 0x3082039ba003020102020309cfc7300d06092a864886f70d0101040500305a310b3009060355040613025553311c301a060355040a1313457175696661782053656375726520496e632e312d302b06035504031324457175696661782053656375726520476c6f62616c2065427573696e6573732043412d31301e170d3038313130333037353230325a170d3039313130343037353230325a3082011c310b300906035504061302555331493047060355040a1340692e62726f6b652e7468652e696e7465726e65742e616e642e616c6c2e692e676f742e7761732e746869732e742d73686972742e7068726565646f6d2e6f726731133011060355040b130a475431313032393030313131302f060355040b1328536565207777772e726170696473736c2e636f6d2f7265736f75726365732f637073202863293038312f302d060355040b1326446f6d61696e20436f6e74726f6c2056616c696461746564202d20526170696453534c2852293149304706035504031340692e62726f6b652e7468652e696e7465726e65742e616e642e616c6c2e692e676f742e7761732e746869732e742d73686972742e7068726565646f6d2e6f726730820122300d06092a864886f70d01010105000382010f003082010a0282010100b2d32581aa28e878b1e50ad53c0f36576ea95f06410e6bb4cb07170000005bfd6b1c7b9ce8a9a3c5450b36bb01d153aac3088f6ff84f3e87874411dc60e0df9255f9b8731b5493c59fd046c460b63562cdb9af1ca86b1ac95b3c9637c0ed67efbbfec08b9c502f29bd83229e8e08faac1370a2587f62628a11f789f6dfb667597316fb63168ab49138ce2ef5b6be4ca49449e465510a4215c9c130e269d5457da526bbb961ec6264f039e1e7bc68d850519e1d60d3d1a3a70af80320a170011791364f0270318683ddf70fd8071d11b31304a5daf0ae50b1280e63692a0c826f8f4733df6ca20692f14f45bed93036a32b8cd677ae35637f4e4c9a934836d99f0203010001a381bd3081ba300e0603551d0f0101ff0404030204f0301d0603551d0e04160414cda683faa56037f796371729de4178f1878955e7303b0603551d1f043430323030a02ea02c862a687474703a2f2f63726c2e67656f74727573742e636f6d2f63726c732f676c6f62616c6361312e63726c301f0603551d23041830168014bea8a07472506b44b7c923d8fba8ffb3576b686c301d0603551d250416301406082b0601050507030106082b06010505070302300c0603551d130101ff04023000
SET @b-2 = 0x3082039ba003020102020141300d06092a864886f70d0101040500305a310b3009060355040613025553311c301a060355040a1313457175696661782053656375726520496e632e312d302b06035504031324457175696661782053656375726520476c6f62616c2065427573696e6573732043412d31301e170d3034303733313030303030305a170d3034303930323030303030305a303c313a3038060355040313314d443520436f6c6c6973696f6e7320496e632e2028687474703a2f2f7777772e7068726565646f6d2e6f72672f6d64352930819f300d06092a864886f70d010101050003818d0030818902818100baa659c92c28d62ab0f8ed9f46a4a437ee0e196859d1b3039951d6169a5e376b15e00e4bf58464f8a3db416f35d59b151fdbc438527081975e8fa0b5f77e39f032ac1ead44d2b3fa48c3ce919becf49c7ce15af5c8376b9a83dee7ca2097314273159168f488aff92828c5e90f73b0174b134c9975d044e67e086c1af24f1b410203010001a382022430820220300b0603551d0f0404030201c6300f0603551d130101ff040530030101ff301d0603551d0e04160414a704601fab724308c57f089055561cd6cee638eb301f0603551d23041830168014bea8a07472506b44b7c923d8fba8ffb3576b686c308201be06096086480186f842010d048201af168201ab33000000275e39e089610f4ea3c5450b36bb01d153aac3088f6ff84f3e87874411dc60e0df9255f9b8731b5493c59fd046c460b63562cdb9af1ca8691ac95b3c9637c0ed67efbbfec08b9c502f29bd83229e8e08faac1370a2587f62628a11f789f6dfb667597316fb63168ab49138ce2ef5b6be4ca49449e465110a4215c9c130e269d5457da526bbb961ec6264f039e1e7bc68d850519e1d60d3d1a3a70af80320a170011791364f0270318683ddf70fd8071d11b31304a5dcf0ae50b1280e63692a0c826f8f4733df6ca20692f14f45bed93036a32b8cd677ae35637f4e4c9a934836d99f0203010001a381bd3081ba300e0603551d0f0101ff0404030204f0301d0603551d0e04160414cda683faa56037f796371729de4178f1878955e7303b0603551d1f043430323030a02ea02c862a687474703a2f2f63726c2e67656f74727573742e636f6d2f63726c732f676c6f62616c6361312e63726c301f0603551d23041830168014bea8a07472506b44b7c923d8fba8ffb3576b686c301d0603551d250416301406082b0601050507030106082b06010505070302300c0603551d130101ff04023000
--------------------------------*********-*******--******-*-*-*-*****-***-*-***-***
select LEN(@a) LengthA, LEN(@b) LengthB
select case when @a = @b-2 then 1 else 0 end [@a = @b-2 ?]
select HASHBYTES('MD5', @a) [MD5 Hash from @a], HASHBYTES('MD5', @b-2) [MD5 hash from @b-2];
select HASHBYTES('SHA1', @a) [SHA-1 Hash from @a], HASHBYTES('SHA1', @b-2) [SHA-1 hash from @b-2];
select HASHBYTES('SHA2_512', @a) [SHA-512 Hash from @a], HASHBYTES('SHA2_512', @b-2) [SHA-512 hash from @b-2];
SELECT CASE WHEN HASHBYTES('MD5', @a) = HASHBYTES('MD5', @b-2) THEN 'MD5 collision' ELSE 'MD5 unique' END AS [MD5]
,CASE WHEN HASHBYTES('SHA1','') IS NULL THEN 'UNKNOWN: SHA1 not available' WHEN HASHBYTES('SHA1', @a) = HASHBYTES('SHA1', @b-2) THEN 'SHA-1 collision' ELSE 'SHA-1 unique' END AS [SHA1]
,CASE WHEN HASHBYTES('SHA2_512','') IS NULL THEN 'UNKNOWN: SHA-512 not available' WHEN HASHBYTES('SHA2_512', @a) = HASHBYTES('SHA2_512', @b-2) THEN 'SHA-512 collision' ELSE 'SHA-512 unique' END AS [SHA-512]
P.S. MD5 is a 16 byte result, not 20 bytes. SHA-1 is 20 bytes. SHA-256 is 32 bytes, and SHA-512 is 64 bytes. All can have collisions, though at this time I'm only familiar with techniques to deliberately generate collisions for MD5 (see "evilize" library in the MD5 collision paper, above).
-
Mighty (1/21/2014)
Interesting question, however, I don't understand why the "LEFT JOIN"-construction was used.Distracts a little bit from what was the essential part of the question.
Left join... Where a column in the right side table is null...
That is a simple and common way to find things that don't match, so I thought it framed the question very well. I got it wrong, assuming the same input should produce the same hash, but there's the catch, it's not just a hash it's hashbyte so the number of bytes used by the data type is important too. Learned something new 🙂
-
I selected correct answer but for wrong reasons. Thanks for the question. Nice to know about it. 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply