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

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