Tip of the Day : LeetCode 584 - Find Customer Referee
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
```