-
Aug 5th, 2010, 09:28 AM
#1
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
-
Aug 5th, 2010, 10:02 AM
#2
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 10:05 AM.
-
Oct 12th, 2010, 11:15 PM
#3
New Member
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
-
May 5th, 2011, 12:49 PM
#4
Fanatic Member
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
-
May 5th, 2011, 01:21 PM
#5
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...
-
May 5th, 2011, 01:25 PM
#6
Fanatic Member
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.
-
Aug 2nd, 2011, 10:03 AM
#7
New Member
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!
-
Aug 2nd, 2011, 10:26 AM
#8
New Member
Re: SQL Server 2005 - 160 bit Encryption/Decryption function
Also can I index the encrypted field? thanks!
-
Aug 2nd, 2011, 11:24 PM
#9
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...
-
Aug 3rd, 2011, 08:50 AM
#10
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|