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

Comments (0)