SQL Server 2005 - 160 bit Encryption/Decryption function

[CVMichael]

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

-- =============================================
-- 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

[CVMichael]

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:

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)

[tinat]

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

[ADQUSIT]

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

[CVMichael]

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

I hope I understood your question...

[ADQUSIT]

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.

[SqlNovice]

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!

[SqlNovice]

Also can I index the encrypted field? thanks!

[CVMichael]

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

[SqlNovice]

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