Encode / Decode String Functon

In any system, there comes a time wherein sensitive data that gets stored in a database, SQL Server in this case, needs to be encoded in some way. Data such as password or credit card information can be dangerous on the hands of a person with malicious intent.

In this article, a SQL Server user-defined function is created that will encode any input string. The function will take a VARCHAR parameter, for the moment it is set to have a maximum of 100 characters but this can be changed accordingly. The output will be an NVARCHAR data type having the same length as the input string.

String Encoder

Function Definition

CREATE FUNCTION [dbo].[ufn_EncodeString] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN

    DECLARE @vEncryptedString NVARCHAR(100)
    DECLARE @vIdx INT
    DECLARE @vBaseIncrement INT

    SET @vIdx = 1
    SET @vBaseIncrement = 128
    SET @vEncryptedString = ''

    WHILE @vIdx <= LEN(@pClearString)
    BEGIN
        SET @vEncryptedString = @vEncryptedString + 
                                NCHAR(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
                                @vBaseIncrement + @vIdx - 1)
        SET @vIdx = @vIdx + 1
    END

    RETURN @vEncryptedString

END
GO

Function Description

The idea behind this simple encoder algorithm is transposing the ASCII value of each character in the input string by a certain increment value. This is accomplished by the following line:

SET @vEncryptedText = @vEncryptedText + NCHAR(ASCII(SUBSTRING(@pClearText, @vIdx, 1)) + 
                      @vBaseIncrement + @vIdx - 1)

In the user-defined function, the increment value is 128 (@vBaseIncrement). Aside from the increment value, the position of the character within the text is also added to the ASCII value. This is done to avoid having the same output character for the same input character. For example, if the position of the character is not added in the new ASCII value of the input character, the text "VVVV" will be encrypted to "ÖÖÖÖ", which can easily be deciphered. With the character position added to the ASCII value, the text "VVVV" will be encrypted to "Ö×ØÙ".

Using this user-defined function, the text "SQL Server" will be encoded to "ÓÒΣ×êøýíû". For credit card numbers, the text "1234-5678-9012-3456" will be encoded to "±³µ·±º¼¾À¶Ã»½¿»ÂÄÆÈ".

Although this may be a simple encoding algorithm, it is still better than having sensitive data stored in the database as plain text, where anybody who has access to the data can easily read it. Encoding the string is just providing an additional level of security to your data.

String Decoder

Now that the data is encoded in the table, it is now time to provide the user-defined function that will decode the encoded string. Below is the user-defined function that performs the opposite of the encoding function above.

Function Definition

CREATE FUNCTION [dbo].[ufn_DecodeString] ( @pEncryptedString NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN

DECLARE @vClearString VARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT

SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vClearString = ''

WHILE @vIdx <= LEN(@pEncryptedString)
BEGIN
    SET @vClearString = @vClearString + 
                        CHAR(UNICODE(SUBSTRING(@pEncryptedString, @vIdx, 1)) - 
                        @vBaseIncrement - @vIdx + 1)
    SET @vIdx = @vIdx + 1
END

RETURN @vClearString

END
GO

Additional Level of Security

One thing that is common between the two user-defined functions above is the use of the "WITH ENCRYPTION" clause when the function is created. This will encrypt the user-defined function in the database and no user will be able to look into the source code of the function, even if the user has access to it. This is just adding another level of security to the database. Having the code available to anybody makes the encryption useless as anybody can easily decrypt it. Also, make sure that only authorized users can execute the [dbo].[ufn_DecodeString] function. Having this function available for everyone, again makes the encryption useless.