HASHBYTES

  • twin.devil

    SSC-Insane

    Points: 22208

    Comments posted to this topic are about the item HASHBYTES

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Hashbrowns, HASHBYTES, Hashish its all the same ๐Ÿ˜›

    Nice question and explanation. Thanks

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    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

  • Mighty

    SSCrazy Eights

    Points: 8435

    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

  • Bangla

    Hall of Fame

    Points: 3137

    Nice and interesting one......

  • Anipaul

    SSC-Insane

    Points: 24681

    Good one....

  • Ed Wagner

    SSC Guru

    Points: 286958

  • dlumpp

    SSC-Addicted

    Points: 445

    Can we get the syntax highlighting working again? These are hard to read without it. What happened?

  • sneumersky

    SSCertifiable

    Points: 7667

    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.

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

  • TomThomson

    SSC Guru

    Points: 104765

    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

  • Nadrek

    SSC-Insane

    Points: 20039

    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 paper: [/url]

    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 VARBINARY(927)

    -- Classic example documented by Peter Selinger Feb 22, 2006: http://www.mscs.dal.ca/~selinger/md5collision/

    SET @a = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab58712467eab4004583eb8fb7f8955ad340609f4b30283e488832571415a085125e8f7cdc99fd91dbdf280373c5bd8823e3156348f5bae6dacd436c919c6dd53e2b487da03fd02396306d248cda0e99f33420f577ee8ce54b67080a80d1ec69821bcb6a8839396f9652b6ff72a70

    SET @B = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab50712467eab4004583eb8fb7f8955ad340609f4b30283e4888325f1415a085125e8f7cdc99fd91dbd7280373c5bd8823e3156348f5bae6dacd436c919c6dd53e23487da03fd02396306d248cda0e99f33420f577ee8ce54b67080280d1ec69821bcb6a8839396f965ab6ff72a70

    -------------------------------------------------*---------------------------------------------------*---------------------------*-----------------------------------------------*---------------------------------------------------*---------------------------*---------

    -- X.509 example, referenced in http://stackoverflow.com/questions/1224113/examples-of-hash-collisions

    SET @a = 0x3082039ba003020102020309cfc7300d06092a864886f70d0101040500305a310b3009060355040613025553311c301a060355040a1313457175696661782053656375726520496e632e312d302b06035504031324457175696661782053656375726520476c6f62616c2065427573696e6573732043412d31301e170d3038313130333037353230325a170d3039313130343037353230325a3082011c310b300906035504061302555331493047060355040a1340692e62726f6b652e7468652e696e7465726e65742e616e642e616c6c2e692e676f742e7761732e746869732e742d73686972742e7068726565646f6d2e6f726731133011060355040b130a475431313032393030313131302f060355040b1328536565207777772e726170696473736c2e636f6d2f7265736f75726365732f637073202863293038312f302d060355040b1326446f6d61696e20436f6e74726f6c2056616c696461746564202d20526170696453534c2852293149304706035504031340692e62726f6b652e7468652e696e7465726e65742e616e642e616c6c2e692e676f742e7761732e746869732e742d73686972742e7068726565646f6d2e6f726730820122300d06092a864886f70d01010105000382010f003082010a0282010100b2d32581aa28e878b1e50ad53c0f36576ea95f06410e6bb4cb07170000005bfd6b1c7b9ce8a9a3c5450b36bb01d153aac3088f6ff84f3e87874411dc60e0df9255f9b8731b5493c59fd046c460b63562cdb9af1ca86b1ac95b3c9637c0ed67efbbfec08b9c502f29bd83229e8e08faac1370a2587f62628a11f789f6dfb667597316fb63168ab49138ce2ef5b6be4ca49449e465510a4215c9c130e269d5457da526bbb961ec6264f039e1e7bc68d850519e1d60d3d1a3a70af80320a170011791364f0270318683ddf70fd8071d11b31304a5daf0ae50b1280e63692a0c826f8f4733df6ca20692f14f45bed93036a32b8cd677ae35637f4e4c9a934836d99f0203010001a381bd3081ba300e0603551d0f0101ff0404030204f0301d0603551d0e04160414cda683faa56037f796371729de4178f1878955e7303b0603551d1f043430323030a02ea02c862a687474703a2f2f63726c2e67656f74727573742e636f6d2f63726c732f676c6f62616c6361312e63726c301f0603551d23041830168014bea8a07472506b44b7c923d8fba8ffb3576b686c301d0603551d250416301406082b0601050507030106082b06010505070302300c0603551d130101ff04023000

    SET @B = 0x3082039ba003020102020141300d06092a864886f70d0101040500305a310b3009060355040613025553311c301a060355040a1313457175696661782053656375726520496e632e312d302b06035504031324457175696661782053656375726520476c6f62616c2065427573696e6573732043412d31301e170d3034303733313030303030305a170d3034303930323030303030305a303c313a3038060355040313314d443520436f6c6c6973696f6e7320496e632e2028687474703a2f2f7777772e7068726565646f6d2e6f72672f6d64352930819f300d06092a864886f70d010101050003818d0030818902818100baa659c92c28d62ab0f8ed9f46a4a437ee0e196859d1b3039951d6169a5e376b15e00e4bf58464f8a3db416f35d59b151fdbc438527081975e8fa0b5f77e39f032ac1ead44d2b3fa48c3ce919becf49c7ce15af5c8376b9a83dee7ca2097314273159168f488aff92828c5e90f73b0174b134c9975d044e67e086c1af24f1b410203010001a382022430820220300b0603551d0f0404030201c6300f0603551d130101ff040530030101ff301d0603551d0e04160414a704601fab724308c57f089055561cd6cee638eb301f0603551d23041830168014bea8a07472506b44b7c923d8fba8ffb3576b686c308201be06096086480186f842010d048201af168201ab33000000275e39e089610f4ea3c5450b36bb01d153aac3088f6ff84f3e87874411dc60e0df9255f9b8731b5493c59fd046c460b63562cdb9af1ca8691ac95b3c9637c0ed67efbbfec08b9c502f29bd83229e8e08faac1370a2587f62628a11f789f6dfb667597316fb63168ab49138ce2ef5b6be4ca49449e465110a4215c9c130e269d5457da526bbb961ec6264f039e1e7bc68d850519e1d60d3d1a3a70af80320a170011791364f0270318683ddf70fd8071d11b31304a5dcf0ae50b1280e63692a0c826f8f4733df6ca20692f14f45bed93036a32b8cd677ae35637f4e4c9a934836d99f0203010001a381bd3081ba300e0603551d0f0101ff0404030204f0301d0603551d0e04160414cda683faa56037f796371729de4178f1878955e7303b0603551d1f043430323030a02ea02c862a687474703a2f2f63726c2e67656f74727573742e636f6d2f63726c732f676c6f62616c6361312e63726c301f0603551d23041830168014bea8a07472506b44b7c923d8fba8ffb3576b686c301d0603551d250416301406082b0601050507030106082b06010505070302300c0603551d130101ff04023000

    --------------------------------*********-*******--******-*-*-*-*****-***-*-***-***

    select LEN(@a) LengthA, LEN(@b) LengthB

    select case when @a = @B then 1 else 0 end [@a = @B ?]

    select HASHBYTES('MD5', @a) [MD5 Hash from @a], HASHBYTES('MD5', @B) [MD5 hash from @B];

    select HASHBYTES('SHA1', @a) [SHA-1 Hash from @a], HASHBYTES('SHA1', @B) [SHA-1 hash from @B];

    select HASHBYTES('SHA2_512', @a) [SHA-512 Hash from @a], HASHBYTES('SHA2_512', @B) [SHA-512 hash from @B];

    SELECT CASE WHEN HASHBYTES('MD5', @a) = HASHBYTES('MD5', @B) 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) 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) 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).

  • davoscollective

    SSCertifiable

    Points: 6325

    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 ๐Ÿ™‚

  • sqlnaive

    SSCoach

    Points: 17435

    I selected correct answer but for wrong reasons. Thanks for the question. Nice to know about it. ๐Ÿ™‚

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply