Database Programming: A Decrypter For Omni

This post will conclude (for me and for now, at least) a discussion begun here and continued here and here.

When last we spoke, I was searching for a SQL Server 2000-specific decrypter for Omnibuzz. Well, I've got one. It might be a tad over-engineered for a production implementation (and performance may suffer), but it's offered mostly in the spirit of "see? it's possible."

First, there's a UDF to pull back individual decrypted characters from individual rows of the encrypted table; this UDF is called fnDecryptSingleCharacter. Then, there's a UDF that uses fnDecryptSingleCharacter to construct each decrypted value; this UDF is called fnDecrypt. Finally, we'll run a very simple SELECT against the table and the function to get the results. We'll start with the SELECT and the results, so you'll be patient enough to go through the code..

SELECT Val_Id,

[Value],

dbo.fnDecrypt (Val_Id) AS DecryptedString
FROM Bad_Encryption

..yields..

Val_Id Value DecryptedString

----------- ------------------------- --------------------

1 OAMBNRITBAUQZYZZ OMNIBUZZ

2 SAQWLR SQL

3 GZAXRCBVABGNEM GARBAGE

4 CAOWLELYEHCGTHOLR@ COLLECTOR

(4 row(s) affected)

Here's fnDecryptSingleCharacter, a relatively straightfoward JOIN:

CREATE FUNCTION dbo.fnDecryptSingleCharacter (

@Val_Id int,

@PositionToReturn int

)

RETURNS char(1)

AS

BEGIN

DECLARE @CharToReturn char(1)

SELECT @CharToReturn = SUBSTRING(be.[Value],sb.Id,1)

FROM dbo.Bad_Encryption be

JOIN dbo.SetBuilderOdd sb

ON sb.Position = @PositionToReturn

AND be.Val_Id = @Val_Id

RETURN (@CharToReturn)

END

Here's fnDecrypt, which is, as you'll see, a blunt stick. There are probably ways to make this more performant, but here it is:

CREATE FUNCTION dbo.fnDecrypt (

@Val_Id int

)

RETURNS varchar(50)

AS

BEGIN

DECLARE @DecryptedString nvarchar(50)

SELECT @DecryptedString = dbo.fnDecryptSingleCharacter (@Val_Id, 1) +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 2),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 3),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 4),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 5),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 6),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 7),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 8),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 9),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 10),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 11),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 12),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 13),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 14),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 15),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 16),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 17),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 18),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 19),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 20),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 21),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 22),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 23),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 24),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 25),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 26),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 27),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 28),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 29),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 30),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 31),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 32),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 33),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 34),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 35),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 36),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 37),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 38),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 39),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 40),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 41),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 42),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 43),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 44),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 45),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 46),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 47),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 48),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 49),'') +

ISNULL(dbo.fnDecryptSingleCharacter (@Val_Id, 50),'')

RETURN (@DecryptedString)

END

With this code in place, you can run the SELECT at the top of this post to see the decrypted results:

SELECT  Val_Id,

        [Value],

        dbo.fnDecrypt (Val_Id) AS DecryptedString
FROM    Bad_Encryption

..which, as you'll recall, yields..

Val_Id Value DecryptedString

----------- ------------------------- --------------------

1 OAMBNRITBAUQZYZZ OMNIBUZZ

2 SAQWLR SQL

3 GZAXRCBVABGNEM GARBAGE

4 CAOWLELYEHCGTHOLR@ COLLECTOR

(4 row(s) affected)

Can you optimize fnDecrypt? It seems to me to call out for a CASE statement; perhaps I'll get to that after the holiday if someone else doesn't beat me to the punch.

     -wp