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. SQL Server now has several options that offer varying degrees of protection such as TDE and column encryption and application encryption that goes all the way from your application into the rows of the database.
In long-ago version of SQL Server, such as SQL 2000, the only practical way that I know of was 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 provided for columnar encryption and now we have TDE.
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 and columnar encryption is a good way to do that. But if you’re needs very limited, you might roll your own. This 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 yourself be misled into thinking that your data is well protected.
In any case, encryption can be fun so let’s look at the CREATE FUNCTION script:
GO
SET ANSI_NULLS ON
GOCREATE 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:
, @CryptoText varchar(8000) — Encrypted text
, @Decrypted varchar(8000) — After decryptionSELECT @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.