how to md5 encrypt data using TSQL

Question

how can i encrypt using md5 for encrypt my data using TSQL and from Enterprise Manager?

Answer 1

With difficulty...It doesn't have any encryption functions built in. (to my knowledge)

 

In the past I had to create a CLR stored proc and used that to encrypt it as .net has some good encryption routines built in.

 

Unless anyone else has any ideas?

 

Chris

 

Answer 2

Easily done, but badly documented. In 2k5 and above:

DECLARE @Ciphertext varbinary(20)
DECLARE @Plaintext varchar
SET @Plaintext = 'SomePlainText'
set @Ciphertext = HashBytes('MD5', @Plaintext)

print @Ciphertext

Answer 3

Hi

That's Great,

Do you know How to convert it back? (decryption)

---

just impossible is impossible

Answer 4

You don't. MD5 isn't truly encryption, as it cannot be reversed. MD5 is hashing; more for use as data-validity checks... similar to a check-digit or check-sum.

If you want to be able to go back, you need to look at true encryption, not hashing.

Answer 5

Is there a way to do a MD5 out of the full result set of a sql query?  Not row by row but the whole set of rows.  This would be used to as a hash on data extracts to be sent to consumers where they can recalculate the hash/checksum to determine if data inside the file has changed

Select * from customers

Which results on these Rows

#FirstName, #LastName, Telephone
Rigo, Valdez, 3123-123-123
Carlos, Smith, 123-123-1234
Ann,Rams,123,123-1234

Feed them to MD5 / checksum -

Get This:

e51ba302d4870977e96808069e6d5054

Answer 6

Is there a way to do a MD5 out of the full result set of a sql query?  Not row by row but the whole set of rows.  This would be used to as a hash on data extracts to be sent to consumers where they can recalculate the hash/checksum to determine if data inside the file has changed

Select * from customers

Which results on these Rows

#FirstName, #LastName, Telephone
Rigo, Valdez, 3123-123-123
Carlos, Smith, 123-123-1234
Ann,Rams,123,123-1234

Feed them to MD5 / checksum -

Get This:

e51ba302d4870977e96808069e6d5054

Try this -

DECLARE @tab TABLE
						(Id INT IDENTITY(1,1),FirstName VARCHAR(10)
						,LastName VARCHAR(10),Telephone VARCHAR(13),MD5 VARCHAR(32))
INSERT INTO @tab
VALUES('Rigo','Valdez','3123-123-123',NULL)
			,('Carlos','Smith','123-123-1234',NULL)
			,('Ann','Rams','123-123-1234',NULL)
--Before MD5
SELECT * FROM @tab
UPDATE @tab SET
	MD5 = CONVERT(VARCHAR(32), 
	HashBytes('MD5',
	ISNULL(FirstName,'')
	+'-'+ISNULL(LastName,'')
	+'-'+ISNULL(Telephone,'')
	),2)
	
--After MD5 - Row by Row with MD5
SELECT * FROM @tab;
--Create a table get Pivot all MD5 values
DECLARE @MD5Output TABLE
				(md51 VARCHAR(32)
				,md52 VARCHAR(32),md53 VARCHAR(32)
				,md5Result VARCHAR(32))
INSERT INTO @MD5Output
SELECT [1],[2],[3],NULL
FROM(
SELECT Id,MD5 
FROM @tab) AS t
PIVOT (MAX(MD5) FOR Id IN
			([1],[2],[3])
			) AS PVT
--New table after Pivoting MD5
SELECT * FROM @MD5Output;
--Update the New table with only MD5Values
UPDATE @MD5Output SET
	md5Result = CONVERT(VARCHAR(32), 
	HashBytes('MD5',
	ISNULL(md51,'')
	+'-'+ISNULL(md52,'')
	+'-'+ISNULL(md53,'')
	),2)
--Final result for entire table 
--(not row by row)	
SELECT md5Result 
FROM @MD5Output

 1.First generate MD5Keys for each row.

2.Pivot only the generated MD5Key values.

3.Generate a new MD5Key including only the MD5 values generated in Step2

Output -

Id	FirstName	LastName	Telephone	MD5
1	Rigo	Valdez	3123-123-123	NULL
2	Carlos	Smith	123-123-1234	NULL
3	Ann	Rams	123-123-1234	NULL

After generating MD5Keys for each row -

Id	FirstName	LastName	Telephone	MD5
1	Rigo	Valdez	3123-123-123	93BE7C2176F0E9428B94106A508E24AD
2	Carlos	Smith	123-123-1234	20D5ED604A184DE732FA0E8E91CA62D8
3	Ann	Rams	123-123-1234	30EB59B1E3731E8E9C5AAEF96C68413D

Pivot only the MD5 values-

md51	md52	md53	md5Result
93BE7C2176F0E9428B94106A508E24AD 20D5ED604A184DE732FA0E8E91CA62D8 30EB59B1E3731E8E9C5AAEF96C68413D	NULL

Result with only one MD5 -

md5Result
D00DEC3E21D549E16FECB77CE995C366

---

Narsimha