|
|
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.
|