Tip of the Day : How to Call NEWID Function in a User-Defined Function
Simple String Encryption/Decryption Functon

Simple String Encryption/Decryption 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 encrypted 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 encrypt 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 Encryption

CREATE FUNCTION [dbo].[ufn_EncryptString] ( @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

Description

The idea behind this simple encryption 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 encrypted to "ÓÒΣ×êøýíû".  For credit card numbers, the text "1234-5678-9012-3456" will be encrypted to "±³µ·±º¼¾À¶Ã»½¿»ÂÄÆÈ". 

Although this may be a simple encryption 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.  Encrypting the string is just providing an additional level of security to your data.

String Decryption

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

CREATE FUNCTION [dbo].[ufn_DecryptString] ( @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_DecryptText]" function.  Having this function available for everyone, again makes the encryption useless.