Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to encrypt value in SQL Server

Author: DevASP
Download Source Code : 423_Encrypt.zip

This article is about how you can encrypt a value before going to save it in a table. Here you can take the example of passwords which you want to store in an encrypted form.

  • Steps you will do.
  • Start SQL server and create a new user define function as below.

CREATE FUNCTION dbo.bs_RETURN_ENCRYPT_FIELD(@PASSWORD VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

SET @PASSWORD = LTRIM(RTRIM(@PASSWORD) )

--Returns a string encrypted with key k ( TEA encryption )

--New variant, fixing a couple of minor weaknesses

--TEA- Tiny Encryption Algorithm

DECLARE @K VARCHAR(16),@I BIGINT,@L BIGINT,@K0 BIGINT,@K1 BIGINT, @K2 BIGINT, @K3 BIGINT,@RESULT VARCHAR(8000),

@V0 BIGINT,@V1 BIGINT,@DELTA BIGINT,@SUM BIGINT,@N BIGINT,@Y BIGINT, @Z BIGINT,@T1 BIGINT,@T2 BIGINT,@T3 BIGINT,

@TVB VARBINARY(4),@J BIGINT, @TEMP BIGINT, @X BIGINT,@Q BIGINT

SET @K='EDWIFUNCTION' --Dummy String

SET @I=LEN(@K) --Lenght of dummy string

IF @I<16 --If the pwd<16 char

BEGIN

SET @K=@K+@K+@K+@K+@K+@K+@K+@K+@K+@K+@K+@K+@K+@K+@K+@K --Add pwd to itself

SET @K=LEFT(@K,16) --Store 16 Characters form left from @K

END

SET @L=(LEN(@PASSWORD) % 8) --Stores remainder of Password divided by 8

IF @L<>0 --If there are no complete 64 bit blocks

BEGIN

SET @I=(LEN(@PASSWORD))/8+1 --Stores length of the password didvided by 8 then add 1 to the result.

SET @L= @I*8-LEN(@PASSWORD) --Stores 8 times I - length of password

SET @PASSWORD=@PASSWORD+REPLICATE(CHAR(0),@L) --

END

SET @K0=ASCII(SUBSTRING(@K, 1,1))*16777216+ASCII(SUBSTRING(@K, 2,1))*65536+ --Stores first four letters ascii value

ASCII(SUBSTRING(@K, 3, 1))*256+ASCII(SUBSTRING(@K ,4, 1))

SET @K1=ASCII(SUBSTRING(@K, 5, 1))*16777216+ASCII(SUBSTRING(@K ,6, 1))*65536+ --Stores next four letters ascii value

ASCII(SUBSTRING(@K, 7, 1))*256+ASCII(SUBSTRING(@K, 8, 1))

SET @K2=ASCII(SUBSTRING(@K, 1,9))*16777216+ASCII(SUBSTRING(@K ,10, 1))*65536+ --Stores next four letters ascii value

ASCII(SUBSTRING(@K, 11, 1))*256+ASCII(SUBSTRING(@K ,12, 1))

SET @K3=ASCII(SUBSTRING(@K, 1,13))*16777216+ASCII(SUBSTRING(@K ,14, 1))*65536+ --Stores last four letters ascii value

ASCII(SUBSTRING(@K, 15, 1))*256+ASCII(SUBSTRING(@K ,16, 1))

SET @I=1

SET @RESULT=''

WHILE @I<=LEN(@PASSWORD)

BEGIN

SET @V0=ASCII(SUBSTRING(@PASSWORD,(@I-1)+ 1,1))*16777216+ASCII(SUBSTRING(@PASSWORD,(@I-1)+ 2,1))*65536+

ASCII(SUBSTRING(@PASSWORD, (@I-1)+3, 1))*256+ASCII(SUBSTRING(@PASSWORD ,(@I-1)+4, 1))

SET @V1=ASCII(SUBSTRING(@PASSWORD,(@I-1)+ 5, 1))*16777216+ASCII(SUBSTRING(@PASSWORD ,(@I-1)+6, 1))*65536+

ASCII(SUBSTRING(@PASSWORD, (@I-1)+7, 1))*256+ASCII(SUBSTRING(@PASSWORD, (@i-1)+8, 1))

SET @DELTA=2654435769

SET @SUM=0

SET @N=32

SET @Y=@V0

SET @Z=@V1

WHILE @N>0

BEGIN

SET @T1= (CONVERT(BIGINT,(@Z*16))) & CONVERT(BIGINT,4294967295)

SET @T2= (CONVERT(BIGINT,(@Z/32)) ) & CONVERT(BIGINT,4294967295)

IF (@SUM & 3)=0

SET @T3=@K0

ELSE

IF (@SUM & 3)=1

SET @T3=@K1

ELSE

IF (@SUM & 3)=2

SET @T3=@K2

ELSE

IF (@SUM & 3)=3

SET @T3=@K3

SET @Y=@Y+ (CONVERT(BIGINT,(@T1^@T2+@Z^@SUM+@T3))) & CONVERT(BIGINT,4294967295)

SET @Y=CONVERT(BIGINT,@Y) & CONVERT(BIGINT,4294967295)

SET @SUM=(@SUM+@DELTA) & CONVERT(BIGINT,4294967295)

SET @T1= (CONVERT(BIGINT,(@Y*16))) & CONVERT(BIGINT,4294967295)

SET @T2= (CONVERT(BIGINT,(@Y/32)) ) & CONVERT(BIGINT,4294967295)

IF ((@SUM/2048) & 3)=0

SET @T3=@K0

ELSE

IF ((@SUM/2048) & 3)=1

SET @T3=@K1

ELSE

IF ((@SUM/2048) & 3)=2

SET @T3=@K2

ELSE

IF ((@SUM/2048) & 3)=3

SET @T3=@K3

SET @Z=@Z+ (CONVERT(BIGINT,(@T1^@T2+@Y^@SUM+@T3))) & CONVERT(BIGINT,4294967295)

SET @Z=CONVERT(BIGINT,@Z) & CONVERT(BIGINT,4294967295)

SET @N=@N-1

END

SET @V0=@Y

SET @V1=@Z

SET @TVB=CONVERT(VARBINARY(4),@v0)

SET @RESULT= @RESULT+CONVERT(VARCHAR(4),@TVB)

SET @TVB=CONVERT(VARBINARY(4),@V1)

SET @RESULT= @RESULT+CONVERT(VARCHAR(4),@TVB)

SET @I=@I+8

END

--IF @l<>0

-- SET @result=LEFT(@result,LEN(@result)-@l)

RETURN @RESULT

END

 

Now lets suppose that you are going to store some information about the user who register to your application but you want to keep the password save by encrypting it. To get the encrypted value pass those values as parameter in the user define function as below.

 

dbo.bs_RETURN_ENCRYPT_FIELD(@Password)

 

Here @Password is a parameter supplied to the store procedure which will insert the values in DB. The above statement will return you the encrypted value of the @Password parameter. A scripted file is attached with this article for your help.

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Creating a Coma Parsing Function Using SQL Server

Disclaimer - Privacy
© 2002-2017 DevASP.net