SQL Server 2005 - 160 bit Encryption/Decryption function-VBForums
Results 1 to 10 of 10

Thread: SQL Server 2005 - 160 bit Encryption/Decryption function

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,728

    SQL Server 2005 - 160 bit Encryption/Decryption function

    I got bored at work, and I made this function in a few minutes...

    It's using SHA1 (160 bits) hash to encrypt. You can encrypt any data type, you just have to convert/cast your data to VARBINARY(MAX), and from VARBINARY(MAX) to your data type when you decrypt.

    The algorithm is the same as this one here: VB - 128, 160 and 256 Bit File Encryption/Decryption with MD5, SHA1 and SHA256
    Code:
    -- =============================================
    -- Author:	<Michael Ciurescu>
    -- Create date: <2010-08-04>
    -- Description:	<160 bit Encryption/Decryption>
    -- =============================================
    CREATE FUNCTION [dbo].[fn_Encrypt]
    (
    	  @StuffToEncrypt VARBINARY(MAX)
    	, @Password VARCHAR(MAX)
    )
    RETURNS VARBINARY(MAX)
    AS
    BEGIN
    	/*
    		DECLARE @Data VARCHAR(MAX)
    		DECLARE @DecData VARCHAR(MAX)
    		DECLARE @EncData VARBINARY(MAX)
    		
    		-- Data to encrypt
    		SET @Data = 'testing blah blah ... 12345678'
    		
    		-- Encrypt
    		SET @EncData = dbo.fn_Encrypt(CAST(@Data AS VARBINARY(MAX)), 'password123')
    		SELECT @EncData AS [Encrypted Data], DATALENGTH(@EncData) AS [Data Length]
    		
    		-- Decrypt
    		SET @DecData = CAST(dbo.fn_Encrypt(@EncData, 'password123') AS VARCHAR(MAX))
    		SELECT @DecData AS [Decrypted Data], DATALENGTH(@DecData) AS [Data Length]
    	*/
    	
    	DECLARE @OutData VARBINARY(MAX)
    	DECLARE @Key VARBINARY(MAX)
    	DECLARE @KeyLen INT, @K INT, @B INT, @Blocks INT
    	
    	SET @Key = HashBytes('SHA1', @Password)
    	SET @KeyLen = DATALENGTH(@Key)
    	SET @OutData = 0x
    	SET @B = 0
    	SET @Blocks = DATALENGTH(@StuffToEncrypt) / @KeyLen
    	
    	WHILE @B <= @Blocks BEGIN
    		SET @K = 0
    		SET @Key = HashBytes('SHA1', @Password + CAST(@B AS VARCHAR(10)))
    		
    		WHILE @K < @KeyLen BEGIN
    			IF (@B * @KeyLen + @K + 1) > DATALENGTH(@StuffToEncrypt) BEGIN
    				BREAK
    			END ELSE BEGIN
    				SET @OutData = @OutData +
    					CAST(CAST(SUBSTRING(@StuffToEncrypt, @B * @KeyLen + @K + 1, 1) AS INT) ^
    					CAST(SUBSTRING(@Key, @K + 1, 1) AS INT) AS VARBINARY(1))
    			END
    			
    			SET @K = @K + 1
    		END
    		
    		SET @B = @B + 1
    	END
    	
    	RETURN @OutData
    END

  2. #2

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,728

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    Just in case you don't realize the potential of this, you can basically encrypt an entire table by converting the table to XML, like in this example:
    Code:
    DECLARE @DataTable XML
    DECLARE @EncDataTable VARBINARY(MAX)
    DECLARE @DecDataTable XML
    
    -- Convert the table to XML
    SET @DataTable = (
    	SELECT *
    	FROM dbo.BackupUsers AS Data -- table to encrypt
    	FOR XML AUTO, ELEMENTS
    )
    
    SELECT @DataTable, DATALENGTH(@DataTable)
    
    -- Encrypt
    SET @EncDataTable = dbo.fn_Encrypt(CAST(@DataTable AS VARBINARY(MAX)), 'test password')
    SELECT @EncDataTable, DATALENGTH(@EncDataTable)
    
    -- Decrypt
    SET @DecDataTable = CAST(dbo.fn_Encrypt(@EncDataTable, 'test password') AS XML)
    SELECT @DecDataTable, DATALENGTH(@DecDataTable)
    
    -- select the data from the XML table
    SELECT data.value('UserID[1]', 'INT') AS UserID
    	, data.value('UserName[1]', 'varchar(50)') AS UserName
    	, data.value('Admin[1]', 'bit') AS [Admin]
    	, data.value('DateCreated[1]', 'datetime') AS DateCreated
    	, data.value('Active[1]', 'bit') AS Active
    FROM @DecDataTable.nodes('/Data') R(data)
    Last edited by CVMichael; Aug 5th, 2010 at 11:05 AM.

  3. #3
    New Member
    Join Date
    Oct 2010
    Posts
    1

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    Hi Michael,

    I was on a robot website and it made me think of you!

    I stalked you... Sorry! Send me a PM with your contact details!

    Tina

  4. #4
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    936

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    hi Michael, i would like to ask you one thing that is that all coding you made in sql server? i mean i am not getting it that is it written in sql server or in vb.net? please assist me

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,728

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    the code that I have posted here is all for SQL Server (2005 or over)

    I hope I understood your question...

  6. #6
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    936

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    yup, you got that. thank you sir. would you like to answer me that how do i improve my sql server knowledge. i am absolutely novice for this and i use sql server 2005 standard edition.

  7. #7
    New Member
    Join Date
    Aug 2011
    Posts
    3

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    Hi Michael this is a great function. I tested it and it runs pretty faster too.... How can I use this to update the encrypted data and then decrypt the updated data... Thanks!

    I need to use this function to insert, select, update and in views... is it possible? THANKS!

  8. #8
    New Member
    Join Date
    Aug 2011
    Posts
    3

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    Also can I index the encrypted field? thanks!

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,728

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    First of all, encryption is build into SQL Server: http://msdn.microsoft.com/en-us/library/ms179331.aspx

    But I don't think you can set an index even if with the built in encryption.

    Here is something about indexing encrypted data: http://blogs.msdn.com/b/raulga/archi...11/549754.aspx

    But the processing necessary to make the index takes away the speed gain of the index. In other words it's a lot of work, and probably little gain in speed.

    One way you can do index is to make another column that contains a HASH of the data, and the hash result can be indexed.

    To update encrypted data, obviously you have to decrypt, make the change, then encrypt back...

  10. #10
    New Member
    Join Date
    Aug 2011
    Posts
    3

    Re: SQL Server 2005 - 160 bit Encryption/Decryption function

    Thanks a lot Michael for explaining me everything. Can I ask yo onething.. have you compared this with symmetric with certificate or asymmetric encryption in SQL 2005 and above? I am working with SQL 2005 and above.. should I go with this or should I use the one MS SQL Server has... your function is very easy to implement and we can create stored procedure, views and triggers and use your function for inserts, update.. Thanks again for a wonderful function.

    Laura

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.