Implement RC4 Encryption
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown
Date: 23 Sep 2008
Description
Encryption of data is a huge multi-facted and complex topic that fills entire books.
In my opinion, encryption of data, or any other data security measure, is not a one-size-fits-all technology, nor should any organisation rely on just one form of encryption or data security. Security of any kind is very much subject to a vicious cycle of invention-circumvention, and should therefore be regularly reviewed and updated. This has been the case with physical security for as long as it has existed (locks, safes etc) and will likely continue to be the case with software and data security
Security of data is never going to be absolute, and all any organisation can do is protect against the majority of criminals or criminal intent. The dedicated hacker is a hard animal to defeat, but the casual thief is much easier to thwart.
I have worked for organisations at all ends of the security spectrum, from financial to production line. Not all of them use the most leading edge methods for every piece of data as that would be prohibitively expensive and complex, but all of them used data security technology.
RC4 encryption is an older technology, whose algorithms are public knowledge, but to the casual or mildy experiences hacker, it can still be a locked door.
It is relatively simple to implement, and if the appropriate measures are taken to secure all (or part) of the encryption key, it can be effective enough.
The following code provides a function-based implementation of RC4 encryption. Key management is not covered as that is usually site-specific.
Code
Inner Function:
DROP FUNCTION dbo.fnInitRc4
GO
CREATE FUNCTION dbo.fnInitRc4
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS
BEGIN
DECLARE @Key TABLE (i TINYINT, v TINYINT)
DECLARE @Index SMALLINT,
@PwdLen TINYINT
SELECT @Index = 0,
@PwdLen = LEN(@Pwd)
WHILE @Index <= 255
BEGIN
INSERT @Key
(
i,
v
)
VALUES (
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)
INSERT @Box
(
i,
v
)
VALUES (
@Index,
@Index
)
SELECT @Index = @Index + 1
END
DECLARE @t TINYINT,
@b SMALLINT
SELECT @Index = 0,
@b = 0
WHILE @Index <= 255
BEGIN
SELECT @b = (@b + b.v + k.v) % 256
FROM @Box AS b
INNER JOIN @Key AS k ON k.i = b.i
WHERE b.i = @Index
SELECT @t = v
FROM @Box
WHERE i = @Index
UPDATE b1
SET b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM @Box b1
WHERE b1.i = @Index
UPDATE @Box
SET v = @t
WHERE i = @b
SELECT @Index = @Index + 1
END
RETURN
END
GO
Encoding/Decoding Function:
DROP FUNCTION dbo.fnEncDecRc4
GO
CREATE FUNCTION dbo.fnEncDecRc4
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Box TABLE (i TINYINT, v TINYINT)
INSERT @Box
(
i,
v
)
SELECT i,
v
FROM dbo.fnInitRc4(@Pwd)
DECLARE @Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)
SELECT @Index = 1,
@i = 0,
@j = 0,
@Cipher = ''
WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT @i = (@i + 1) % 256
SELECT @j = (@j + b.v) % 256
FROM @Box b
WHERE b.i = @i
SELECT @t = v
FROM @Box
WHERE i = @i
UPDATE b
SET b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM @Box b
WHERE b.i = @i
UPDATE @Box
SET v = @t
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @i
SELECT @k = (@k + v) % 256
FROM @Box
WHERE i = @j
SELECT @k = v
FROM @Box
WHERE i = @k
SELECT @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)
SELECT @Index = @Index +1
END
RETURN @Cipher
END
Test Code:
select dbo.fnEncDecRc4('abc', '1234123412349999')
select dbo.fnEncDecRc4('abc', 'ü¯ê.iíÞG.Õ\þ..÷í')
select dbo.fnEncDecRc4('abc', '9999')
select dbo.fnEncDecRc4('abc', 'ô¤à.')