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