Vigenere Cipher ala T-SQL

I started thinking about how this would work and got a bit obsessed...the Vigenere cipher is a very old cipher (but relatively secure for its time, with a resistance to basic frequency analysis) that uses a shared key and a Vigenere table, basically a grid of letters.

It uses a repeating pattern of your keyword as a sort of keystream, similar to a one time pad (which are superior, having no repeating pattern). Out comes the T-SQL hammer, because this looks like a nail to me:
DECLARE @TextToEncrypt VARCHAR(500) = 'This is an especially secret message that I wish to encrypt with a Vigenere cipher.';

--Strip out non-alpha chars
WHILE PATINDEX('%[^a-z]%', @TextToEncrypt) > 0
        SET @TextToEncrypt = STUFF(@TextToEncrypt, PATINDEX('%[^a-z]%',@TextToEncrypt),1,'');
--Set to upper case
SET @TextToEncrypt = UPPER(@TextToEncrypt);

--Shared secret key
DECLARE @Key VARCHAR(20) = 'YOUDNEVERGUESS';
--Let's make sure to "clean" that one too:
--Strip out non-alpha chars
WHILE PATINDEX('%[^a-z]%', @Key) > 0
        SET @Key = STUFF(@Key, PATINDEX('%[^a-z]%',@Key),1,'');
--Set to upper case
SET @Key = UPPER(@Key);

--Pad out key to length of cleartext
DECLARE @KeyPad VARCHAR(500);
SELECT @KeyPad=LEFT(REPLICATE(@Key,(LEN(@TextToEncrypt)/LEN(@Key))+1),LEN(@TextToEncrypt));

--Vigenere Cipher Encryption and Output
DECLARE @CipherText VARCHAR(500)='';
DECLARE @i INT = 1;
WHILE @i <= LEN(@TextToEncrypt)
BEGIN
        SET @CipherText = @CipherText +
                CHAR(65+((ASCII(SUBSTRING(@TextToEncrypt, @i, 1))-65+ASCII(SUBSTRING(@KeyPad, @i, 1))-65) % 26));
        SET @i=@i+1;
END

PRINT @CipherText
GO

The basic math for encryption is for each character (assuming 0-25 A-Z values), (plaintext + key) mod 26 = ciphertext. Output is thoroughly indecipherable, lacking the key (or a great deal of spare time): RVCVVWVRVYJIUAYZFBFIXVVZGIKKYUYWUEOMNOMLLGCBWULTOAZZBENAESHHEIXMGNYV

So to decrypt...my math is a bit shoddy but it is “theoretically” (ciphertext – key) mod 26 = plaintext. Something is going awry when cipher value is greater than key value, but anyway here’s the code with an IF/THEN workaround:
--Strike that...reverse it...
DECLARE @CipherText VARCHAR(500) = 'RVCVVWVRVYJIUAYZFBFIXVVZGIKKYUYWUEOMNOMLLGCBWULTOAZZBENAESHHEIXMGNYV';

--Shared secret key
DECLARE @Key VARCHAR(20) = 'YOUDNEVERGUESS';

--Pad out key to length of ciphertext
DECLARE @KeyPad VARCHAR(500);
SELECT @KeyPad=LEFT(REPLICATE(@Key,(LEN(@CipherText)/LEN(@Key))+1),LEN(@CipherText));

--Vigenere Cipher Decryption and Output
DECLARE @ClearText VARCHAR(500)='';
DECLARE @ClearChar CHAR(1);
DECLARE @i INT = 1;
WHILE @i <= LEN(@CipherText)
BEGIN
        --This is a cheating IF-THEN because I have something in the math not quite right...but she works
        IF (ASCII(SUBSTRING(@CipherText, @i, 1))>=ASCII(SUBSTRING(@KeyPad, @i, 1)))
                SET @ClearChar=CHAR(65+(((ASCII(SUBSTRING(@CipherText, @i, 1))-65)-(ASCII(SUBSTRING(@KeyPad, @i, 1))-65)) % 26));
        ELSE
                SET @ClearChar=CHAR(65+(26+(((ASCII(SUBSTRING(@CipherText, @i, 1))-65)-(ASCII(SUBSTRING(@KeyPad, @i, 1))-65)) % 26)));
        SET @ClearText = @ClearText + @ClearChar;
        SET @i=@i+1;
END

PRINT @ClearText;

Resulting output: THISISANESPECIALLYSECRETMESSAGETHATIWISHTOENCRYPTWITHAVIGENERECIPHER

There you are...it’s a very simple solution and not at all secure in a comparable way to modern computer-based symmetric algorithms, but kind of fun to understand and practice string manipulation with.

Synchronous One-Time Password Token via T-SQL

While studying for the CISSP exam...which I passed last Saturday...I had this problem rankling around in my brain: how exactly time-based synchronization tokens, like the cryptocard devices we use at work for two factor authentication, might work. T-SQL isn't the best way to do this of course (although if you could carry around an entire instance of SQL Server inside a tiny crypto token that'd be pretty cool) but it's what I know. So after I kept failing to get something working while trying to use ENCRYPTBYPASSPHRASE and its decrypting cousin, I was out jogging in the rain and realized that a hash function is actually how you could do it. These type of functions depend on a combination of a secret key tied to that user, known only to the cryptocard itself and the authentication server, and a time based counter, which is how they avoid replay attacks (sniffing the wire and grabbing the credentials to reuse later). Instead of using the secret key to encrypt the timestamp or (somewhat oddly, but possible) the timestamp to encrypt the secret key...what you can do is just concatenate all that together, and hash it. The hash is a one way function so you can't reconstitute it back to its original text and find the secret key.

So imagine this code running on your token device when you push the button. The user and secret key are fixed in the device's firmware/ROM (before being distributed to the user). User probably not even necessary but it makes a nice addition. Then what happens is you take the user, key, and timestamp with seconds stripped off, and concatenate it into a long, rather unique string. Then hash it. Because nobody wants to enter a 160 bit hash in a window, we simplify it down by converting to int, making it positive, and taking the first 8 numbers off of that...and then output to the user on our little LCD panel.

/*This is the CryptoCard Side*/
DECLARE @UserID VARCHAR(10) = 'MyUserID';
DECLARE @SecretKey VARCHAR(100) = 'd4&g!29Betcetcetc';
DECLARE @DateHHMM VARCHAR(100) = DATEADD(MINUTE,DATEDIFF(MINUTE,0,GETDATE()),0);
DECLARE @TokenValue CHAR(8);

--Concatenate user ID, secret key, and date (floored to minute)
--Hash with SHA
--Convert to integer and ABS negative values
--Pull first 8 characters as token value
SELECT @TokenValue=RIGHT(ABS(CAST(HASHBYTES('SHA1',@UserID+@SecretKey+@DateHHMM)AS INT)),8);
SELECT @TokenValue;
GO
/*-----------------------------------*/

Then we, to authenticate, put in our username and password (basic single factor authentication), and are prompted to enter in this token (providing the multifactor, something-you-have + something-you-know authentication).

The server obviously validates the user password against the directory service or authentication server, and to authenticate the token it could do something like this. First grab the secret key for the specified user seeking authentication, make a small table variable for token values, and then insert the 'integerized' hash value for all possible values given the time (floored to the minute level), the user, and the secret key, within the last 5 minutes, and with some future minutes thrown in just in case clocks get off a bit. It's a fuzzy match, but so are biometrics, for that matter. If the provided token value is in that table variable, congrats, you've been authenticated. If not, sorry, no dice.

Now, nobody would use SQL Server to do this, nor should they, but to a DBA that has a hammer, everything looks like a nail, I suppose.
/*This is the Authentication Server Side*/
--User Provides User ID and token value
DECLARE @ProvidedUserID VARCHAR(10) = 'MyUserID';
DECLARE @ProvidedTokenValue CHAR(8) = '82650027';

--At this point, the system would retrieve the stored shared secret key into memory for that user from some sort of table or structure
--However to simplify this example, we're just going to hardcode it:
DECLARE @SecretKey VARCHAR(100) = 'd4&g!29Betcetcetc';

DECLARE @PossibleRecentValues TABLE (TokenValue CHAR(8));

--Compute possible values for last 5 minutes, plus two future to account for time sync issues
INSERT INTO @PossibleRecentValues (TokenValue) VALUES
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,2,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,1,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,GETDATE()),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-1,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-2,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-3,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-4,GETDATE())),0) AS VARCHAR(100)))AS INT)),8));

IF EXISTS (SELECT 1 FROM @PossibleRecentValues WHERE TokenValue=@ProvidedTokenValue)
        PRINT 'User is authenticated';
ELSE
        PRINT 'Token expired or invalid';