Data Encryption and Decryption

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.

 

 

Inner Function:

USE [yourdbname]
GO
/****** Object:  UserDefinedFunction [dbo].[fnInitRc4]    Script Date: 3/12/2014 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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

 Encryption and Decryption related funcation:

USE [yourdbname]
GO
/****** Object:  UserDefinedFunction [dbo].[fnEncDecRc4]    Script Date: 3/12/2014 12:20:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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

 

 

thanks

venkatesh