Coffeehouse Thread

25 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

MD5 Hashes in SQLServer2005?

Back to Forum: Coffeehouse
  • User profile image
    jsampsonPC

    Accidentally posted in coffeehouse, sorry.

    I'm building a new table, and trying to store the contents of a field in MD5 hash-form. Granted, I'm a MySQL guy coming to .NET, so some things may look / feel a little strange, but this is confusing.

    MD5 hashes are generally 32 regular characters, are they not?

    Why then when I run the following query, I get this result:

    SELECT HashBytes('MD5','Testing')

    <Binary Data>

    Shouldn't it just show me the friggin hash?

  • User profile image
    jsampsonPC

    Okay, cast created this: újZ2$×ÚfÙà½ì%ö,ð

    I'm just used to using PHP's md5() function, or mysql's password() function and see the pretty "1f3870be274f6c49b3e31a0c6728957f".

  • User profile image
    Secret​Software

    master.dbo.fn_varbintohexstr(HashBytes('MD5',
    'test'))

  • User profile image
    Matthew van Eerde

    No, an MD5 hash is a binary number.  You can use CAST to see the hash:

    SELECT CAST(HashBytes('MD5','Testing') AS varchar(255))

    EDIT: apparently that doesn't work... sorry

  • User profile image
    jsampsonPC

    Hey Secret, thanks for the line. I'll go and try that right now.

    Meanwhile, I learned about the FormsAuthentication.HashPasswordForStoringInConfigFile() method today, which gives me the functionality I need on the forms-end!

        protected string MD5(string text)
        {
            // Return our md5 text
            return FormsAuthentication.HashPasswordForStoringInConfigFile(text, "MD5");
        }

  • User profile image
    blowdart

    jsampsonPC wrote:
    Hey Secret, thanks for the line. I'll go and try that right now.

    Meanwhile, I learned about the FormsAuthentication.HashPasswordForStoringInConfigFile() method today, which gives me the functionality I need on the forms-end!

        protected string MD5(string text)
        {
            // Return our md5 text
            return FormsAuthentication.HashPasswordForStoringInConfigFile(text, "MD5");
        }


    I really hope you're salting those hashes.

  • User profile image
    Sven Groot

    Just FYI, what the HashPasswordForStroringInConfigFile method does is essentially this:

    private static string ByteArrayToHexString(byte[] bytes)
    {
       StringBuilder result = new StringBuilder(bytes.Length * 2);
       foreach( byte b in bytes)
       {
          result.Append(b.ToString(
    "X"));
       }
       return result.ToString();
    }

    private
    static string MD5(string text)
    {
       System.Security.Cryptography.
    MD5 md5 = System.Security.Cryptography.MD5.Create();
       byte[] hash = md5.ComputeHash(Encoding.UTF8.GetBytes(text));
       return ByteArrayToHexString(hash);
    }


    EDIT:
    Blowdart wrote:
    I really hope you're salting those hashes.

    AFAIK the HashPasswordForStoringInConfigFile method does not allow salts. The code I posted above could easily be modified to add a salt though.
  • User profile image
    blowdart

    Sven Groot wrote:
    
    Blowdart wrote:
    I really hope you're salting those hashes.

    AFAIK the HashPasswordForStoringInConfigFile method does not allow salts. The code I posted above could easily be modified to add a salt though.


    Ah no it wouldn't, but what I was assuming was he was just using it as a short cut to get a hash that would then be stored in a database.

  • User profile image
    jsampsonPC

    blowdart wrote:
    
    Sven Groot wrote:
    
    Blowdart wrote:
    I really hope you're salting those hashes.

    AFAIK the HashPasswordForStoringInConfigFile method does not allow salts. The code I posted above could easily be modified to add a salt though.


    Ah no it wouldn't, but what I was assuming was he was just using it as a short cut to get a hash that would then be stored in a database.


    I'm quick to admit that my knowledge of cryptography is shallow, so I'm on wikipedia right now reading about salts. Basically it's a secret key that is hashed with the user-provided text?

    So I could have a salt like: 1z250x72hgm2l3sg3456hz3t346njs4e
    And my user-submitted data: mypassword

    Would I then create a hash of "Salt" + "Text"?

    This would help thwart dictionary attacks on the MD5 hashes, I'm assuming?

  • User profile image
    blowdart

    jsampsonPC wrote:
    

    So I could have a salt like: 1z250x72hgm2l3sg3456hz3t346njs4e
    And my user-submitted data: mypassword

    Would I then create a hash of "Salt" + "Text"?

    This would help thwart dictionary attacks on the MD5 hashes, I'm assuming?


    Yup. The salt ideally should be different for every user. Something that is known for every users. Like, oh, their username Smiley

  • User profile image
    jsampsonPC

    blowdart wrote:
    
    jsampsonPC wrote:
    

    So I could have a salt like: 1z250x72hgm2l3sg3456hz3t346njs4e
    And my user-submitted data: mypassword

    Would I then create a hash of "Salt" + "Text"?

    This would help thwart dictionary attacks on the MD5 hashes, I'm assuming?


    Yup. The salt ideally should be different for every user. Something that is known for every users. Like, oh, their username


    Okay, really I don't store the users password in the database (Or, I have the option of not storing it in the database). Instead, I would store the result of the username + supplied-password in MD5 hash-format in the database.

    Sound about right?

  • User profile image
    blowdart

    jsampsonPC wrote:
    
    Okay, really I don't store the users password in the database (Or, I have the option of not storing it in the database). Instead, I would store the result of the username + supplied-password in MD5 hash-format in the database.

    Sound about right?


    Pretty much it yes. Although SHA256 is probably better than MD5

  • User profile image
    W3bbo

    blowdart wrote:
    
    jsampsonPC wrote:
    
    Okay, really I don't store the users password in the database (Or, I have the option of not storing it in the database). Instead, I would store the result of the username + supplied-password in MD5 hash-format in the database.

    Sound about right?


    Pretty much it yes. Although SHA256 is probably better than MD5


    MD5 considered dangerous like.... 3 years ago. It's surprising how many people still use it. Personally I want to see the function removed from the next release of PHP5 and replace it with a generic "hash()" function which generates a hash based on the current most secure algo, so you don't need to update your apps everytime someone finds a collision.

  • User profile image
    jsampsonPC

    W3bbo wrote:
    
    blowdart wrote:
    
    jsampsonPC wrote:
    
    Okay, really I don't store the users password in the database (Or, I have the option of not storing it in the database). Instead, I would store the result of the username + supplied-password in MD5 hash-format in the database.

    Sound about right?


    Pretty much it yes. Although SHA256 is probably better than MD5


    MD5 considered dangerous like.... 3 years ago. It's surprising how many people still use it. Personally I want to see the function removed from the next release of PHP5 and replace it with a generic "hash()" function which generates a hash based on the current most secure algo, so you don't need to update your apps everytime someone finds a collision.


    I've read also that SHA wasn't completely secure either, and has been compromised too.

    PHP also has Crypt() if you don't like MD5.
    string crypt ( string $str [, string $salt] )
    The salt (when not provided) is randomnly generated each time this function is used.

    Kinda neat the way this is setup.

  • User profile image
    Matthew van Eerde

    I think using a fixed GUID for the salt will be safer against rainbow attacks than the username.

    SHA-1 is considered weak, but SHA-256 is well respected.

  • User profile image
    blowdart

    W3bbo wrote:
    

    MD5 considered dangerous like.... 3 years ago. It's surprising how many people still use it. Personally I want to see the function removed from the next release of PHP5 and replace it with a generic "hash()" function which generates a hash based on the current most secure algo, so you don't need to update your apps everytime someone finds a collision.


    Of course you leave out the final word of the paper; "Someday"

    Hash algorithms will have collisions; because they're not injective; well unless you want the hash larger than the input. The goal is just to minimise those.

  • User profile image
    Matthew van Eerde

    blowdart wrote:
    Hash algorithms will have collisions; because they're not injective; well unless you want the hash larger than the input. The goal is just to minimise those.


    The attack potential if collisions are too easy to produce is real.

    See the Birthday attack wikipedia article, especially the part starting "Alice wants to trick Bob"...

  • User profile image
    blowdart

    Matthew van Eerde wrote:
    
    blowdart wrote:
    Hash algorithms will have collisions; because they're not injective; well unless you want the hash larger than the input. The goal is just to minimise those.


    The attack potential if collisions are too easy to produce is real.

    See the Birthday attack wikipedia article, especially the part starting "Alice wants to trick Bob"...


    Oh of course, but with salting the risk goes down somewhat and, because generally passwords are rather short, the risk goes down again.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.