Simple XOR Encryption With T-SQL and Built-in Functions

Some time ago I presented an article about why XOR encryption can be a useful "just enough" technique to easily handle encryption and decryption of strings such as connection strings in configuration files.

By carefully choosing the value to use as the operand in the XOR function, we can create an encrypted string that is "XML Safe". One such ASCII value I found was the integer 129. The advantage of XOR is that it is bidirectional - when you XOR the result of a previous XOR operation, you get back the original value. This means you only need one method to both encrypt and decrypt a string.

I should hasten to note that XOR encryption is NOT a strong encryption algorithm - it's probably one of the first things a determined hacker would try. However, for simply keeping your stuff "away from prying eyes" -- it can often be "just enough".

The following example illustrates how one can create a UDF in SQL Server to perform the same operation.



CREATE FUNCTION [dbo].[ufn_EncryptDecryptString] ( @pClearString VARCHAR(MAX) )
RETURNS NVARCHAR(MAX) 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)) ^129)
SET @vIdx = @vIdx + 1
END
RETURN @vEncryptedString
END
GO

This sample results in an encrypted string:


Declare @test varchar(200)
Select @test= dbo.ufn_EncryptDecryptString( 'this is a string to be encrypted.')
Print @test

Result:
õéèò¡èò¡à¡òõóèïæ¡õî¡ãä¡äïâóøñõäå¯

Now if we pass the result back into the function:


Declare @test varchar(200)
Select @test= dbo.ufn_EncryptDecryptString( 'õéèò¡èò¡à¡òõóèïæ¡õî¡ãä¡äïâóøñõäå¯')
Print @test

Result:

this is a string to be encrypted.

Using this in a stored proc or other SQL is easy. Assume you have a table NOTES with a column "NOTE":


INSERT INTO NOTES (NOTE)
VALUES (dbo.ufn_EncryptDecryptString('this is some text to encrypt via XOR' ))

SELECT
dbo.ufn_EncryptDecryptString( NOTE)
FROM NOTES

NOTE: SQL Server 2005 offers new encryption functions such as EncryptByPassPhrase and DecryptByPassPhrase, but they require the use of a varbinary column, which introduces a higher level of diffculty; the main issue being the conversion of the HEX string of encrypted text to varbinary for passing into the Decrypt function. Here is an example of usage:


Declare @string nvarchar(MAX)
set @string = 'this is text to encrypt.'
declare @encrypted_str NVARCHAR(MAX)
select @encrypted_str = EncryptByPassPhrase('pigscanfly', @string)
DECLARE @decrypted_str VARBINARY(MAX)
SET @decrypted_str = DecryptByPassPhrase('pigscanfly', @encrypted_str);
-- display decrypted text
SELECT CONVERT(NVARCHAR(MAX), @decrypted_str) AS PlainText;

Finally, you can "custom generate" your own random unique keys of any desired length. Here is an example of a stored proc that does just this:


CREATE PROCEDURE [dbo].[sp_GenerateKey]
(
@Length int,
@Key nvarchar(MAX) OUTPUT
)
AS
DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''
SET NOCOUNT ON
SET @counter = 1
WHILE @counter < (@Length + 1)
BEGIN
SET @RandomNumber = Rand()
SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
SET @counter = @counter + 1
SET @RandomID = @RandomID + @CurrentCharacter
END
Set @key= @RandomID

The reason I bring this to light is that it's become very popular to use GUIDs (newid()) as a primary key today. However, the GUID is QUITE LONG. If you know that you may only ever have say, 10 million rows, you can use a method like the above to generate unique keys that are much shorter.

With the use of "Rand()" you aren't guaranteed that what you get back will be absolutely unique as is the case with a GUID. So, the best bet is to have your stored proc "Look up" the generated key in the table where you intend to use it, and ensure it is unique. If not, you would simply execute the sproc again. If your inserted "key" is in a Primary Key or unique index column, you would get back an error on the insert that tells you to generate another key. Also, with this you want to use the Collation order "CS" - Case Sensitive.

I use a technique like this (although not with SQL) to generate the "short urls" for my ittyurl.net site.