Obscure Data with the Rot13 Cipher.

How protected is the data in your database? For most databases the answer is: "Not very".

Have you ever thought of encrypting your database? Make no mistake about it your database is vulnerable to prying eyes.

The only practical way that I know of right now to encrypt an entire SQL Server database is to use the Windows Encrypted File System (EFS). The EFS is an extension of the NTFS file system that implements strong encryption on data files. SQL Server 2005 will have another solution when it arrives next year.

External encryption protects your database from examination of its files but doesn't protect from any method that can query the database. To do that you must encrypt the database data separately.

This week's UDF is a user contribution from Luke Schollmeyer. His function, ufn_Rot13 implements the Rot13 cipher. If you're not familiar with it Rot13 is a letter exchange cipher that rotates characters 13 places in the alphabet.

Rot13 happens to be used in the Windows registry and in many web cookies because it's simple to implement, pretty fast, and obscures the contents of the data from simple examination. It takes at least a little effort to decrypt it.

There is a wide range of encryption solutions available ranging from weak encryption, which I call "Encryption that'll fool your kid sister", to strong encryption, which I call "Encryption that will fool the CIA". Rot13 is on the weak, "fool your kid sister" end of the spectrum but it's better than nothing, unless you let your self be mislead into thinking that your data is well protected.

In any case encryption can be fun so let's look at the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION ufn_Rot13 (@phrase varchar(8000))
	RETURNS varchar(8000)
AS
BEGIN
	DECLARE @j int, --// loop iterator
			@phraseOUT varchar(8000), 	--// function output
			@letter int --// ascii letter 
		
	--// initializing variables
	SET @j = 1
	SET @phraseOUT = ''

	--// main iterator through each letter of input phrase
	WHILE @j <= len(@phrase) BEGIN
			--// pick out letter
		SET @letter = ASCII(SUBSTRING(@phrase, @j, 1))

			-- // rotate letter 13 places in either upper or  
			-- lower case ascii range and add to output variable
		SELECT @phraseOUT = @phraseOUT + CASE 
						WHEN @letter BETWEEN 97 AND 122 
                         THEN CASE WHEN @letter - 13 < 97 
                                  THEN char((@letter - 13) + 122 - 96) 
                                  ELSE char(@letter - 13) END
						WHEN @letter BETWEEN 65 AND 90 
                         THEN CASE WHEN @letter - 13 < 65 
                                   THEN char((@letter - 13) + 90 - 64)
                                   ELSE char(@letter - 13) END
						ELSE CHAR(@letter)
						END	
		SET @j = @j + 1
	END 

	RETURN @phraseOUT
END 
	
go

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXEC ON dbo.ufn_Rot13 to PUBLIC
GO

The following script illustrates test the function on a simple phrase from the first like of my college drinking song. It starts with the plain-text, which is the text that is going to be encrypted. This is converted by the Rot13 function to the cipher-text, the encrypted version of plain-text. Finally we convert back to decrypt ed-text and compare to plain-text. Here we go:

DECLARE @PlainText varchar(8000)  -- Original Message
      , @CryptoText varchar(8000) -- Encrypted text
      , @Decrypted varchar(8000)  -- After decryption

SELECT @PlainText = 'We are ever true to Brown'
SELECT @CryptoText = dbo.ufn_rot13(@PlainText)
SELECT @Decrypted = dbo.ufn_rot13(@Cryptotext)

SELECT CASE WHEN @PlainText = @Decrypted 
            THEN 'Equal' ELSE 'Not equal' END [Test]
      , @CryptoText [Encrypted]
      , @Decrypted [Decrypted]

go

(Results)
Test  Encrypted                  Decrypted                
----- -------------------------- -------------------------
Equal Jr ner rire gehr gb Oebja  We are ever true to Brown

As you can see words in the Encrypted text have the same length as the words in the plain text and it wouldn't take long to guess that this is a simple cipher. And my kid sister has grown up and has a degree in Systems Engineering from the University of Pennsylvania and has worked as a computer programmer so I suppose she might have the necessary tools to figure this one out.

Luke has also supplied an alternative implementation that uses an external table of numbers rather than a loop. Because there isn't a loop, this revised version should run much faster than the original. I'll show you that one next week.

Thanks Luke.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick