MD5 (or similar) Hashed column 1

[Luzian]

In MySql, there is a function called Password() which hashes a string. Is there an equivalent in MS SQL?


Example of its hypothetical usage:

DECLARE @is_valid_login bit;

IF EXISTS
(
	SELECT [id]
	FROM [Users]
	WHERE [username] = @username
	AND [b]Password([/b][password][b])[/b] = @password
) SET @is_valid_login = 1;
ELSE SET @is_valid_login = 0;

 

[Luzian]

correction:

INSERT INTO [Users] ([username], [password])
VALUES ('bob', [b]Password([/b]'mypassword'[b])[/b])
GO

DECLARE @is_valid_login bit;

IF EXISTS
(
    SELECT [id]
    FROM [Users]
    WHERE [username] = 'bob'
    AND [password] = [b]Password([/b]'mypassword'[b])[/b]
) SET @is_valid_login = 1;
ELSE SET @is_valid_login = 0;

 

[mrdenny]

SQL 2000 - Yes, but it's not supported and it's not very seure. There are third party extended stored procedures which can be used to encrypt data.

SQL 2005 - Yes. It supports all sorts of encryption.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luzian]

Today I've created a CLR User-Defined Function in C# that does the MD5 hashing. Works great.

using System;
using System.Security.Cryptography;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;


public partial class StoredFunctions
{
	[SqlFunction]
	public static SqlString Md5Hash(SqlString input)
	{
		MD5 md5 = MD5.Create();

		byte[] data = md5.ComputeHash(Encoding.Default.GetBytes(input.Value));

		StringBuilder s_builder = new StringBuilder();

		foreach (byte b in data)
		{
			s_builder.Append(b.ToString("x2"));
		}

		// Return the hexadecimal string.
		return (SqlString)s_builder.ToString();
	}

	[SqlFunction]
	public static SqlBoolean VerifyMd5Hash(SqlString clear_text, SqlString hash)
	{
		SqlString hash_of_clear_text = Md5Hash(clear_text);

		StringComparer comparer = StringComparer.OrdinalIgnoreCase;

		return comparer.Compare(hash_of_clear_text.Value, hash.Value) == 0 ?
			SqlBoolean.True : SqlBoolean.False;

	}
}

Compiled to a dll, then in sql, created an assembly:

CREATE ASSEMBLY IssueTracker
FROM 'c:\somepath\encryptionhelper.dll'
WITH PERMISSION_SET=SAFE
GO

CREATE FUNCTION [inet].[fn_Md5Hash]
(@clear_text nvarchar(50))
RETURNS nvarchar(100)
AS
EXTERNAL NAME IssueTracker.StoredFunctions.Md5Hash
GO

 

[mrdenny]

Here is an FAQ on using the internal code of SQL 2005 to do encryption faq962-5964.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luzian]

Thanks, but I will favor md5 hashing where data cannot be "decrypted", only verified.

 

[mrdenny]

I never notices that there was no one way hashing option native to SQL before. I guess I just haven't needed one yet. I've submitted a request to Microsoft to have one added.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=182659

The more people that vote for it the better chance it will be added as a native feature in the next release or service pack.

Props to you for posting your MD5 code.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Luzian]

MD5 is not really encryption. Encrypted data can be decrypted. Data that goes through the MD5 algorithm, cannot be decrypted. The main purpose for MD5 is for verification that the data is what it's supposed to be: password verification and applcations downloaded from the internet that could have possibly been tampered with in malice (viruses, spyware). An MD5 hash will allways be a small string, that if you change any bit of the original data, becomes a completely different string.

 

[Luzian]

You know that already, mrdenny. This info is for anyone else who doesn't know.

 

[mrdenny]

That's true, it's not encryption. The folks in Redmond should have included it now that I think about it. Hopefully they will include it in R2 (SQL 2007) or a service pack before that.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

The nice folks over at Microsoft have told me that hashing is already built into SQL 2005. Some info is available here

http://msdn2.microsoft.com/en-us/library/ms174415.aspx.

It supports MD2, MD4, MD5, SHA1, and SHA2. It's provided via a system function.

select HASHBYTES('MD5', 'TextString')
go
--Returns 0x6417BEFD10D8A79DAFDAA7029F7B43C6

According to the URL above it supports up to upto a NVARCHAR(MAX) and will return upto 8000 binary characters back.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com