Encrypt fields with these stored procedures and functions: TEA (new variant), TEA, RC4, Vigenere, Caesar, XOR, XOR8 for SQL Server This is a collection of stored procedures and encryption functions (UDF's) in TSQL.
2001-08-22
729 reads
TSQL Encryption TEA, RC4, Vigenere, Caesar, XOR
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function CRYPTX8( @s VARCHAR(1024), @k VARCHAR(8) )
--Returns a string s1 encrypted/decrypted with key s2, up to 8 chars ( XOR encryption ).
returns VARCHAR(1024)
as
BEGIN
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp tinyint, @x tinyint
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
BEGIN
SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
SET @k=LEFT(@k,8)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate('*',@l)
END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @j=0
WHILE @j<8
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
SET @x=ASCII(SUBSTRING(@k,@j+1,1))
SET @result=@result + CHAR(@temp ^ @x)
SET @j=@j+1
END
SET @i=@i+8
END
IF @l<>0
BEGIN
SET @result=LEFT(@result,LEN(@result)-@l)
END
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function DEC_CAESAR( @s VARCHAR(255), @x tinyint )
returns VARCHAR(255)
as
BEGIN
--Returns a string decrypted with key x ( Caesar Cipher encryption )
DECLARE @result VARCHAR(255), @i int, @j int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i,1))
SET @j=CONVERT(int,@temp)-@x
IF @j<0
SET @j=@j+255
SET @result=@result + CHAR(@j)
SET @i=@i+1
END
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function DEC_TEA(@s VARCHAR(8000), @k VARCHAR(16))
returns VARCHAR(8000)
as
BEGIN
--Returns a string decrypted with key k ( TEA encryption )
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 1,1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 2,1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+3, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+4, 1))))
SET @v1=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+6, 1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+7, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+8, 1))))
set @delta=2654435769
SET @sum=3337565984
set @n=32
SET @y=@v0
SET @z=@v1
WHILE @n>0
BEGIN
set @t1= (convert(bigint,(@y*16)) +@k2) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@y+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@y/32)) +@k3) & convert(bigint,4294967295)
SET @z=@z- (convert(bigint,(@t1^@t2^@t3))) & convert(bigint,4294967295)
SET @z=convert(bigint,@z) & convert(bigint,4294967295)
--print str(@y)+' '+str(@t1)+' '+str(@t2)+' '+str(@t3)+' '+str(@z)
set @t1= (convert(bigint,(@z*16)) +@k0) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@z+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@z/32)) +@k1) & convert(bigint,4294967295)
SET @y=@y- (convert(bigint,(@t1^@t2^@t3))) & convert(bigint,4294967295)
SET @y=convert(bigint,@y) & convert(bigint,4294967295)
SET @sum=@sum-@delta
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
SET @result=REPLACE(@result,CHAR(0),'')
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function DEC_TEA2(@s VARCHAR(8000), @k VARCHAR(16))
returns VARCHAR(8000)
as
BEGIN
--Returns a string decrypted with key k ( TEA encryption )
--New variant, fixing a couple of minor weaknesses
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 1,1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 2,1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+3, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+4, 1))))
SET @v1=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+6, 1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+7, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+8, 1))))
set @delta=2654435769
SET @sum=3337565984
set @n=32
SET @y=@v0
SET @z=@v1
WHILE @n>0
BEGIN
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 @sum=(@sum-@delta) & convert(bigint,4294967295)
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 @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
SET @result=REPLACE(@result,CHAR(0),'')
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function DEC_VIGENERE(@s VARCHAR(1024), @k VARCHAR(8))
returns VARCHAR(255)
as
BEGIN
--Returns a string decrypted with key k ( Vigenere Cipher encryption )
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp int, @x int
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
BEGIN
SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
SET @k=LEFT(@k,8)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate('*',@l)
END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @j=0
WHILE @j<8
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
SET @x=ASCII(SUBSTRING(@k,@j+1,1))
SET @temp=@temp-@x
IF @temp<0
SET @temp=@temp+255
SET @result=@result + CHAR(@temp)
SET @j=@j+1
END
SET @i=@i+8
END
IF @l<>0
SET @result=LEFT(@result,LEN(@result)-@l)
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function ENC_CAESAR( @s VARCHAR(255), @x tinyint )
returns VARCHAR(255)
as
BEGIN
--Returns a string encrypted with key x ( Caesar Cipher encryption )
DECLARE @result VARCHAR(255), @i int, @j int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i,1))
SET @j=CONVERT(int,@x)+@temp
IF @j>255
SET @j=@j-255
SET @result=@result + CHAR(@j)
SET @i=@i+1
END
return @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function ENC_TEA(@s VARCHAR(8000), @k VARCHAR(16))
returns VARCHAR(8000)
as
BEGIN
--Returns a string encrypted with key k ( TEA encryption )
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=ASCII(SUBSTRING(@s,(@i-1)+ 1,1))*16777216+ASCII(SUBSTRING(@s,(@i-1)+ 2,1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+3, 1))*256+ASCII(SUBSTRING(@s ,(@i-1)+4, 1))
SET @v1=ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))*16777216+ASCII(SUBSTRING(@s ,(@i-1)+6, 1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+7, 1))*256+ASCII(SUBSTRING(@s, (@i-1)+8, 1))
set @delta=2654435769
SET @sum=0
set @n=32
SET @y=@v0
SET @z=@v1
WHILE @n>0
BEGIN
SET @sum=@sum+@delta
set @t1= (convert(bigint,(@z*16)) +@k0) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@z+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@z/32)) +@k1) & convert(bigint,4294967295)
SET @y=@y+ (convert(bigint,(@t1^@t2^@t3))) & convert(bigint,4294967295)
SET @y=convert(bigint,@y) & convert(bigint,4294967295)
set @t1= (convert(bigint,(@y*16)) +@k2) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@y+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@y/32)) +@k3) & convert(bigint,4294967295)
SET @z=@z+ (convert(bigint,(@t1^@t2^@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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function ENC_TEA2(@s VARCHAR(8000), @k VARCHAR(16))
returns VARCHAR(8000)
as
BEGIN
--Returns a string encrypted with key k ( TEA encryption )
--New variant, fixing a couple of minor weaknesses
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=ASCII(SUBSTRING(@s,(@i-1)+ 1,1))*16777216+ASCII(SUBSTRING(@s,(@i-1)+ 2,1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+3, 1))*256+ASCII(SUBSTRING(@s ,(@i-1)+4, 1))
SET @v1=ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))*16777216+ASCII(SUBSTRING(@s ,(@i-1)+6, 1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+7, 1))*256+ASCII(SUBSTRING(@s, (@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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function ENC_VIGENERE(@s VARCHAR(1024), @k VARCHAR(8))
returns VARCHAR(255)
as
BEGIN
--Returns a string encrypted with key k ( Vigenere Cipher encryption )
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp int, @x int
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
BEGIN
SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
SET @k=LEFT(@k,8)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate('*',@l)
END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @j=0
WHILE @j<8
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
SET @x=ASCII(SUBSTRING(@k,@j+1,1))
SET @temp=@temp+@x
IF @temp>255
SET @temp=@temp-255
SET @result=@result + CHAR(@temp)
SET @j=@j+1
END
SET @i=@i+8
END
IF @l<>0
SET @result=LEFT(@result,LEN(@result)-@l)
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function RC4( @strInput VARCHAR(1024), @strPassword VARCHAR(8) )
returns VARCHAR(1024)
as
BEGIN
--Returns a string encrypted with key k ( RC4 encryption )
--Original code: Eric Hodges http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=29691&lngWId=1
--Translated to TSQL by Joseph Gama
DECLARE @i int, @j int, @n int, @t int, @s VARCHAR(256), @k VARCHAR(256),
@tmp1 CHAR(1), @tmp2 CHAR(1), @result VARCHAR(1024)
SET @i=0 SET @s='' SET @k='' SET @result=''
WHILE @i<=255
BEGIN
SET @s=@s+CHAR(@i)
SET @k=@k+CHAR(ASCII(SUBSTRING(@strPassword, 1+@i % LEN(@strPassword),1)))
SET @i=@i+1
END
SET @i=0 SET @j=0
WHILE @i<=255
BEGIN
SET @j=(@j+ ASCII(SUBSTRING(@s,@i+1,1))+ASCII(SUBSTRING(@k,@i+1,1)))% 256
SET @tmp1=SUBSTRING(@s,@i+1,1)
SET @tmp2=SUBSTRING(@s,@j+1,1)
SET @s=STUFF(@s,@i+1,1,@tmp2)
SET @s=STUFF(@s,@j+1,1,@tmp1)
--print str(@i)+' '+str(@j)+' '+str(ascii(SUBSTRING(@s,@i+1,1)))+' '+str(ascii(SUBSTRING(@k,@i+1,1)))
SET @i=@i+1
END
--SET @i=1 WHILE @i<=256 BEGIN print ASCII(SUBSTRING(@s,@i,1)) SET @i=@i+1 END
SET @i=0 SET @j=0
SET @n=1
WHILE @n<=LEN(@strInput)
BEGIN
SET @i=(@i+1) % 256
SET @j=(@j+ASCII(SUBSTRING(@s,@i+1,1))) % 256
SET @tmp1=SUBSTRING(@s,@i+1,1)
SET @tmp2=SUBSTRING(@s,@j+1,1)
SET @s=STUFF(@s,@i+1,1,@tmp2)
SET @s=STUFF(@s,@j+1,1,@tmp1)
SET @t=((ASCII(SUBSTRING(@s,@i+1,1))+ASCII(SUBSTRING(@s,@j+1,1))) % 256)
--print str(ASCII(SUBSTRING(@s,@t+1,1))) + ' ' + str(ASCII(SUBSTRING(@strInput,@n,1)))
IF ASCII(SUBSTRING(@s,@t+1,1))=ASCII(SUBSTRING(@strInput,@n,1))
SET @result=@result+SUBSTRING(@strInput,@n,1)
ELSE
SET @result=@result+CHAR(ASCII(SUBSTRING(@s,@t+1,1)) ^ ASCII(SUBSTRING(@strInput,@n,1)))
SET @n=@n+1
END
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_CRYPTX8( @s VARCHAR(1024), @k VARCHAR(8), @enc VARCHAR(1024) out)
--Returns a string s1 encrypted/decrypted with key s2, up to 8 chars ( XOR encryption ).
AS
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp tinyint, @x tinyint
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
BEGIN
SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
SET @k=LEFT(@k,8)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate('*',@l)
END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @j=0
WHILE @j<8
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
SET @x=ASCII(SUBSTRING(@k,@j+1,1))
SET @result=@result + CHAR(@temp ^ @x)
SET @j=@j+1
END
SET @i=@i+8
END
IF @l<>0
BEGIN
SET @result=LEFT(@result,LEN(@result)-@l)
END
SET @enc= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_DEC_CAESAR ( @s VARCHAR(255), @x tinyint, @dec VARCHAR(255) out )
--Returns a string decrypted with key x ( Caesar Cipher encryption )
AS
DECLARE @result VARCHAR(255), @i int, @j int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i,1))
SET @j=CONVERT(int,@temp)-@x
IF @j<0
SET @j=@j+255
SET @result=@result + CHAR(@j)
SET @i=@i+1
END
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_DEC_TEA (@s VARCHAR(8000), @k VARCHAR(16), @dec VARCHAR(8000) out )
--Returns a string decrypted with key k ( TEA encryption )
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
AS
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 1,1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 2,1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+3, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+4, 1))))
SET @v1=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+6, 1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+7, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+8, 1))))
set @delta=2654435769
SET @sum=3337565984
set @n=32
SET @y=@v0
SET @z=@v1
WHILE @n>0
BEGIN
set @t1= (convert(bigint,(@y*16)) +@k2) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@y+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@y/32)) +@k3) & convert(bigint,4294967295)
SET @z=@z- (convert(bigint,(@t1^@t2^@t3))) & convert(bigint,4294967295)
SET @z=convert(bigint,@z) & convert(bigint,4294967295)
--print str(@y)+' '+str(@t1)+' '+str(@t2)+' '+str(@t3)+' '+str(@z)
set @t1= (convert(bigint,(@z*16)) +@k0) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@z+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@z/32)) +@k1) & convert(bigint,4294967295)
SET @y=@y- (convert(bigint,(@t1^@t2^@t3))) & convert(bigint,4294967295)
SET @y=convert(bigint,@y) & convert(bigint,4294967295)
SET @sum=@sum-@delta
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
SET @result=REPLACE(@result,CHAR(0),'')
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_DEC_TEA2(@s VARCHAR(8000), @k VARCHAR(16), @dec VARCHAR(8000) out )
--Returns a string decrypted with key k ( TEA encryption )
--New variant, fixing a couple of minor weaknesses
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
AS
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 1,1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 2,1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+3, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+4, 1))))
SET @v1=convert(bigint,(ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))))*16777216+
convert(bigint,(ASCII(SUBSTRING(@s ,(@i-1)+6, 1))))*65536+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+7, 1))))*256+
convert(bigint,(ASCII(SUBSTRING(@s, (@i-1)+8, 1))))
set @delta=2654435769
SET @sum=3337565984
set @n=32
SET @y=@v0
SET @z=@v1
WHILE @n>0
BEGIN
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 @sum=(@sum-@delta) & convert(bigint,4294967295)
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 @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
SET @result=REPLACE(@result,CHAR(0),'')
set @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_DEC_VIGENERE(@s VARCHAR(1024), @k VARCHAR(8), @dec VARCHAR(255) out )
--Returns a string decrypted with key k ( Vigenere Cipher encryption )
AS
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp int, @x int
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
BEGIN
SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
SET @k=LEFT(@k,8)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate('*',@l)
END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @j=0
WHILE @j<8
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
SET @x=ASCII(SUBSTRING(@k,@j+1,1))
SET @temp=@temp-@x
IF @temp<0
SET @temp=@temp+255
SET @result=@result + CHAR(@temp)
SET @j=@j+1
END
SET @i=@i+8
END
IF @l<>0
SET @result=LEFT(@result,LEN(@result)-@l)
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_ENC_CAESAR( @s VARCHAR(255), @x tinyint, @dec VARCHAR(255) out)
--Returns a string encrypted with key x ( Caesar Cipher encryption )
AS
DECLARE @result VARCHAR(255), @i int, @j int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i,1))
SET @j=CONVERT(int,@x)+@temp
IF @j>255
SET @j=@j-255
SET @result=@result + CHAR(@j)
SET @i=@i+1
END
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_ENC_TEA(@s VARCHAR(8000), @k VARCHAR(16), @dec VARCHAR(8000) out)
--Returns a string encrypted with key k ( TEA encryption )
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
AS
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=ASCII(SUBSTRING(@s,(@i-1)+ 1,1))*16777216+ASCII(SUBSTRING(@s,(@i-1)+ 2,1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+3, 1))*256+ASCII(SUBSTRING(@s ,(@i-1)+4, 1))
SET @v1=ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))*16777216+ASCII(SUBSTRING(@s ,(@i-1)+6, 1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+7, 1))*256+ASCII(SUBSTRING(@s, (@i-1)+8, 1))
set @delta=2654435769
SET @sum=0
set @n=32
SET @y=@v0
SET @z=@v1
WHILE @n>0
BEGIN
SET @sum=@sum+@delta
set @t1= (convert(bigint,(@z*16)) +@k0) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@z+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@z/32)) +@k1) & convert(bigint,4294967295)
SET @y=@y+ (convert(bigint,(@t1^@t2^@t3))) & convert(bigint,4294967295)
SET @y=convert(bigint,@y) & convert(bigint,4294967295)
set @t1= (convert(bigint,(@y*16)) +@k2) & convert(bigint,4294967295)
set @t2= (convert(bigint,(@y+@sum)) & convert(bigint,4294967295))
set @t3= (convert(bigint,(@y/32)) +@k3) & convert(bigint,4294967295)
SET @z=@z+ (convert(bigint,(@t1^@t2^@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
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_ENC_TEA2(@s VARCHAR(8000), @k VARCHAR(16), @dec VARCHAR(8000) out)
--Returns a string encrypted with key k ( TEA encryption )
--New variant, fixing a couple of minor weaknesses
--TEA- Tiny Encryption Algorithm, copyrightDavid J Wheeler & Roger M Needham
AS
DECLARE @result VARCHAR(8000), @l bigint, @i bigint, @j bigint, @temp bigint, @x bigint
declare @y bigint, @z bigint, @sum bigint, @delta bigint, @n bigint, @q bigint
declare @k0 bigint, @k1 bigint, @k2 bigint, @k3 bigint
declare @v0 bigint, @v1 bigint, @tvb VARBINARY(4), @t1 bigint, @t2 bigint, @t3 bigint
SET @i=LEN(@k)
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)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate(CHAR(0),@l)
END
SET @k0=ASCII(SUBSTRING(@k, 1,1))*16777216+ASCII(SUBSTRING(@k, 2,1))*65536+
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+
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+
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+
ASCII(SUBSTRING(@k, 15, 1))*256+ASCII(SUBSTRING(@k ,16, 1))
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @v0=ASCII(SUBSTRING(@s,(@i-1)+ 1,1))*16777216+ASCII(SUBSTRING(@s,(@i-1)+ 2,1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+3, 1))*256+ASCII(SUBSTRING(@s ,(@i-1)+4, 1))
SET @v1=ASCII(SUBSTRING(@s,(@i-1)+ 5, 1))*16777216+ASCII(SUBSTRING(@s ,(@i-1)+6, 1))*65536+
ASCII(SUBSTRING(@s, (@i-1)+7, 1))*256+ASCII(SUBSTRING(@s, (@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
set @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_ENC_VIGENERE(@s VARCHAR(1024), @k VARCHAR(8), @dec VARCHAR(255) out)
--Returns a string encrypted with key k ( Vigenere Cipher encryption )
AS
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp int, @x int
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
BEGIN
SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
SET @k=LEFT(@k,8)
END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
BEGIN
SET @i=(LEN(@s))/8+1
SET @l= @i*8-len(@s)
SET @s=@s+replicate('*',@l)
END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @j=0
WHILE @j<8
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
SET @x=ASCII(SUBSTRING(@k,@j+1,1))
SET @temp=@temp+@x
IF @temp>255
SET @temp=@temp-255
SET @result=@result + CHAR(@temp)
SET @j=@j+1
END
SET @i=@i+8
END
IF @l<>0
SET @result=LEFT(@result,LEN(@result)-@l)
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_RC4T( @strInput VARCHAR(1024), @strPassword VARCHAR(8), @dec VARCHAR(1024) out)
--Returns a string encrypted with key k ( RC4 encryption )
--Original code: Eric Hodges http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=29691&lngWId=1
--Translated to TSQL by Joseph Gama
AS
DECLARE @i int, @j int, @n int, @t int, @s VARCHAR(256), @k VARCHAR(256),
@tmp1 CHAR(1), @tmp2 CHAR(1), @result VARCHAR(1024)
SET @i=0 SET @s='' SET @k='' SET @result=''
WHILE @i<=255
BEGIN
SET @s=@s+CHAR(@i)
SET @k=@k+CHAR(ASCII(SUBSTRING(@strPassword, 1+@i % LEN(@strPassword),1)))
SET @i=@i+1
END
SET @i=0 SET @j=0
WHILE @i<=255
BEGIN
SET @j=(@j+ ASCII(SUBSTRING(@s,@i+1,1))+ASCII(SUBSTRING(@k,@i+1,1)))% 256
SET @tmp1=SUBSTRING(@s,@i+1,1)
SET @tmp2=SUBSTRING(@s,@j+1,1)
SET @s=STUFF(@s,@i+1,1,@tmp2)
SET @s=STUFF(@s,@j+1,1,@tmp1)
--print str(@i)+' '+str(@j)+' '+str(ascii(SUBSTRING(@s,@i+1,1)))+' '+str(ascii(SUBSTRING(@k,@i+1,1)))
SET @i=@i+1
END
--SET @i=1 WHILE @i<=256 BEGIN print ASCII(SUBSTRING(@s,@i,1)) SET @i=@i+1 END
SET @i=0 SET @j=0
SET @n=1
WHILE @n<=LEN(@strInput)
BEGIN
SET @i=(@i+1) % 256
SET @j=(@j+ASCII(SUBSTRING(@s,@i+1,1))) % 256
SET @tmp1=SUBSTRING(@s,@i+1,1)
SET @tmp2=SUBSTRING(@s,@j+1,1)
SET @s=STUFF(@s,@i+1,1,@tmp2)
SET @s=STUFF(@s,@j+1,1,@tmp1)
SET @t=((ASCII(SUBSTRING(@s,@i+1,1))+ASCII(SUBSTRING(@s,@j+1,1))) % 256)
--print str(ASCII(SUBSTRING(@s,@t+1,1))) + ' ' + str(ASCII(SUBSTRING(@strInput,@n,1)))
IF ASCII(SUBSTRING(@s,@t+1,1))=ASCII(SUBSTRING(@strInput,@n,1))
SET @result=@result+SUBSTRING(@strInput,@n,1)
ELSE
SET @result=@result+CHAR(ASCII(SUBSTRING(@s,@t+1,1)) ^ ASCII(SUBSTRING(@strInput,@n,1)))
SET @n=@n+1
END
set @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_XORCHAR( @s VARCHAR(255), @x tinyint, @dec VARCHAR(255) out)
--Returns a string encrypted/decrypted with key t ( XOR encryption )
AS
DECLARE @result VARCHAR(255), @i int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i,1))
SET @result=@result + CHAR(@temp ^ @x)
SET @i=@i+1
END
SET @dec= @result
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE function XORCHAR( @s VARCHAR(255), @x tinyint )
--Returns a string encrypted/decrypted with key t ( XOR encryption )
returns VARCHAR(255)
as
BEGIN
DECLARE @result VARCHAR(255), @i int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
BEGIN
SET @temp=ASCII(SUBSTRING(@s,@i,1))
SET @result=@result + CHAR(@temp ^ @x)
SET @i=@i+1
END
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--test SP
DECLARE @msg varchar(12), @pwd varchar(8), @msgenc varchar(255)
SET @msg='Hello World!'
SET @pwd='abc'
EXEC sp_XORCHAR @msg,157,@msgenc OUT
PRINT @msgenc
EXEC sp_XORCHAR 'Õøññò½Êòïñù¼',157,@msgenc OUT
PRINT @msgenc
EXEC sp_RC4T @msg,@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_RC4T '…øµE7ÿº ^‹ ë',@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_ENC_VIGENERE @msg,@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_DEC_VIGENERE '©ÇÏÍу¸ÑÓÎÇ‚',@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_ENC_TEA2 @msg,@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_DEC_TEA2 'ÇÜ2Æýo¾£te¥€a8·',@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_ENC_TEA @msg,@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_DEC_TEA '¹Ý³HS°ŸIgî ¡r«',@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_ENC_CAESAR @msg,13,@msgenc OUT
PRINT @msgenc
EXEC sp_DEC_CAESAR 'Uryy|-d|yq.',13,@msgenc OUT
PRINT @msgenc
EXEC sp_CRYPTX8 @msg,@pwd,@msgenc OUT
PRINT @msgenc
EXEC sp_CRYPTX8 ')
C6
@',@pwd,@msgenc OUT
PRINT @msgenc
--test UDF
print dbo.ENC_TEA2('Hello world!','secret')
print dbo.DEC_TEA2('£òËS«(HÑ æéÈ','secret')
print dbo.ENC_TEA('Hello world!','secret')
print dbo.DEC_TEA('ò"Owžo™zN;{Þ Ì','secret')
print dbo.RC4('Hello world!','secret')
print dbo.RC4('¥S¾pí„¡É@§ßý','secret')
print dbo.ENC_VIGENERE('Hello world!','secret')
print dbo.DEC_VIGENERE('»ÊÏÞÔ”êÔåÑÇ“','secret')
print dbo.ENC_CAESAR('Hello world!',15)
print dbo.DEC_CAESAR('Wt{{~/†~{s0',15)
print dbo.CRYPTX8('Hello world!','SECRET')
print dbo.CRYPTX8(' />*t$*!)''s','SECRET')
print dbo.XORCHAR('Hello world!',158)
print dbo.XORCHAR('Öûòòñ¾éñìòú¿',158)
£òËS«(HÑ æéÈ
Hello world!
ò"Owžo™zN;{Þ Ì
Hello world!
¥S¾pí„¡É@§ßý
Hello world!
»ÊÏÞÔ”êÔåÑÇ“
Hello world!
Wt{{~/†~{s0
Hello world!
/>*t$*!)'s
Hello world!
Öûòòñ¾éñìòú¿
Hello world!Encryption functions TEA (new variant), TEA, RC4, Vigenere, Caesar, XOR, XOR8 for SQL Server
This is a collection of stored procedures and encryption functions (UDF's) in TSQL.
Stored Procedures
SP_ENC_TEA2(@s VARCHAR(1024), @k VARCHAR(16), @e VARCHAR(1024)) - Returns a string s encrypted with key k (TEA encryption, new variant)
SP_DEC_TEA2(@s VARCHAR(1024), @k VARCHAR(16) , @e VARCHAR(1024)) - Returns a string s decrypted with key k (TEA encryption, new variant)
SP_ENC_TEA(@s VARCHAR(1024), @k VARCHAR(16) , @e VARCHAR(1024)) - Returns a string s encrypted with key k (TEA encryption)
SP_DEC_TEA(@s VARCHAR(1024), @k VARCHAR(16) , @e VARCHAR(1024)) - Returns a string s decrypted with key k (TEA encryption)
SP_RC4(@strInput VARCHAR(1024), @strPassword VARCHAR(8) , @e VARCHAR(1024)) - Returns a string s encrypted/decrypted with key k (RC4 encryption)
SP_ENC_VIGENERE(@s VARCHAR(1024), @k VARCHAR(8) , @e VARCHAR(1024)) - Returns a string encrypted with key k (Vigenere Cipher encryption)
SP_ENC_CAESAR(@s VARCHAR(255), @x tinyint, @e VARCHAR(255)) - Returns a string s encrypted with key x (Caesar Cipher encryption)
SP_DEC_CAESAR(@s VARCHAR(255), @x tinyint, @e VARCHAR(255)) - Returns a string decrypted with key x (Caesar Cipher encryption)
SP_XORCHAR(@s VARCHAR(255), @x tinyint, @e VARCHAR(255)) - Returns a string encrypted/decrypted with key t (XOR encryption)
SP_CRYPTX8(@s VARCHAR(1024), @k VARCHAR(8) , @e VARCHAR(1024)) - Returns a string s1 encrypted/decrypted with key s2, up to 8 chars (XOR encryption).
Functions:
ENC_TEA2(@s VARCHAR(1024), @k VARCHAR(16)) - Returns a string s encrypted with key k (TEA encryption, new variant)
DEC_TEA2(@s VARCHAR(1024), @k VARCHAR(16)) - Returns a string s decrypted with key k (TEA encryption, new variant)
ENC_TEA(@s VARCHAR(1024), @k VARCHAR(16)) - Returns a string s encrypted with key k (TEA encryption)
DEC_TEA(@s VARCHAR(1024), @k VARCHAR(16)) - Returns a string s decrypted with key k (TEA encryption)
RC4(@strInput VARCHAR(1024), @strPassword VARCHAR(8)) - Returns a string s encrypted/decrypted with key k (RC4 encryption)
ENC_VIGENERE(@s VARCHAR(1024), @k VARCHAR(8)) - Returns a string encrypted with key k (Vigenere Cipher encryption)
ENC_CAESAR(@s VARCHAR(255), @x tinyint) - Returns a string s encrypted with key x (Caesar Cipher encryption)
DEC_CAESAR(@s VARCHAR(255), @x tinyint) - Returns a string decrypted with key x (Caesar Cipher encryption)
XORCHAR(@s VARCHAR(255), @x tinyint) - Returns a string encrypted/decrypted with key t (XOR encryption)
CRYPTX8(@s VARCHAR(1024), @k VARCHAR(8)) - Returns a string s1 encrypted/decrypted with key s2, up to 8 chars (XOR encryption).
TEA- Tiny Encryption Algorithm, copyright David J Wheeler & Roger M Needham
http://vader.brad.ac.uk/tea/tea.shtml
RC4 code based on Eric Hodges’ RC4 Implementation
http://www.planet-source-code.com/vb/scripts/showcode.asp?lngWId=1&txtCodeId=29691
They deserve the credit for it.
Related content
2001-08-22
383 reads
hx_ShowFixedDriveSpace
Very simple, how much space is free on the hard drives of the SQL Server you are connected to.
2001-08-22
669 reads
hx_OLEDBproviders
Get OLE Providers info I have found this useful in tracking down application driver/connection issues. I only had to use it 2 times. But it did help
2001-08-22
416 reads
2001-08-22
712 reads