Cool Tips‎ > ‎Data Encryption‎ > ‎

Data Encryption - 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', 'ô¤à.')

 
ċ
Encryption - RC4 encrypt and decrypt.sql
(3k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments