none
how to md5 encrypt data using TSQL

    Question

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

    Friday, August 03, 2007 9:34 AM

Answers

  • 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.

    Tuesday, August 03, 2010 6:03 PM
  • 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


    Friday, August 03, 2007 4:01 PM

All replies

  • 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

     

    Friday, August 03, 2007 10:25 AM
  • 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


    Friday, August 03, 2007 4:01 PM
  • Hi

    That's Great,

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


    just impossible is impossible
    Friday, March 26, 2010 12:38 PM
  • 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.

    Tuesday, August 03, 2010 6:03 PM
  • 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

    Thursday, November 15, 2012 5:00 PM
  • 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




    Thursday, November 15, 2012 6:52 PM