Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: (Almost) A Decrypter For Omni..

Database Programming: (Almost) A Decrypter For Omni..

  • Comments 2
  • Likes

Omnibuzz saw my comment (I left another one over the weekend and it showed up right away; I wonder if the ones I left previously were eaten by some flavor of the issue I ran into trying to leave a comment here) and left two comments.

Omni's first comment refers to the syntax I used to attack his original task, and asks whether calculated position columns might be preferable to rendered ones.  I always prefer to render data, especially data that's not going to change (such as the particular mathematical relationships under consideration in this sample).  In my experience, SQL Server will almost always perform better with rendered columns than calculated columns, so that's how I tend to approach things.  One caveat: I didn't test calculated columns in this particular scenario, because I was pretty sure of myself. <g>

Omni's second comment reminds me that I'd promised to take a look at the decryption syntax.  Right now, I've sort-of got the decryption query Omni seeks.

Why sort of?  Because this solution will only work in SQL Server 2005, and Omni seeks a SQL Server 2000-based solution, so we are "not quite there."  However, when I have the cycles to spin, I can "downgrade" this syntax to SQL Server 2000.  When I've got that done, I'll of course post it here, but hopefully the SQL Server 2005 syntax is of interest in the meantime.

If you're interested in running this query, you'll need to run the syntax in the original post (in order to create schema that this query uses).  Open another query window, point it at the database in which the first query ran, and run the following:

--  declare a memory variable to hold the decrypted value to test..

DECLARE @TestString varchar(50)

 

--  select a value to decrypted test

--SET @TestString = 'OMNIBUZZ'

--SET @TestString = 'SQL'

SET @TestString = 'GARBAGE'

--SET @TestString = 'COLLECTOR'

 

 

--  return details about the decrypted records (if any)

;WITH CTE (SourceId, Position, RunningDecrypted, Encrypted)

AS

(

SELECT      be.Val_Id,

            sb.Position,

            CAST(SUBSTRING(@TestString,sb.Position,1) AS nvarchar(50)) AS RunningDecrypted,

            be.[Value] AS Encrypted

FROM        dbo.Bad_Encryption be

JOIN        SetBuilderOdd sb

ON          be.FirstCharValue               = LEFT(@TestString,1)

AND         SUBSTRING(be.[Value],sb.Id,1)   = SUBSTRING(@TestString,sb.Position,1)

AND         be.ValueLength                  = LEN(@TestString) * 2

AND         sb.Position                     <= LEN(@TestString)

WHERE       sb.Position                     =  1

UNION ALL

SELECT      be.Val_Id,

            sb.Position,

            CAST(RunningDecrypted + SUBSTRING(@TestString,sb.Position,1) AS nvarchar(50)),

            be.[Value]

FROM        dbo.Bad_Encryption be

JOIN        SetBuilderOdd sb

ON          be.FirstCharValue               = LEFT(@TestString,1)

AND         SUBSTRING(be.[Value],sb.Id,1)   = SUBSTRING(@TestString,sb.Position,1)

AND         be.ValueLength                  = LEN(@TestString) * 2

AND         sb.Position                     <= LEN(@TestString)

INNER JOIN  CTE

ON          sb.Position = CTE.Position + 1

)

SELECT  base.SourceId, base.Encrypted, base.RunningDecrypted AS Decrypted

FROM    CTE base

WHERE   base.Position = (SELECT MAX(Position) FROM CTE WHERE SourceId = base.SourceId)

This query produces the following results:

SourceId    Encrypted          Decrypted

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

3           GZAXRCBVABGNEM     GARBAGE

 

(1 row(s) affected)

As I said, when I get bandwidth, I'll find a way to generate this result in SQL Server 2000.

Please post a comment if you've got any questions or insights!

     -wp

Comments
  • For 2005, How about this?

    select * from bad_encryption a

    cross apply (

    select substring(a.value,s.id ,1)

    from dbo.SetBuilderOdd s

    where s.id <= len(a.value)

    for xml path('')

                ) as b(actual)

    Rob Farley also posted a similar result with a slight variation.

    And I would love to see a set based solution for decryption in SQL Server 2000. And I am yet trying.

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment