set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:              Brendon D Allen
-- Create date: 21st July 2009
-- Description: Checks Single One Time Use Passwords
-- =============================================
ALTER PROCEDURE [dbo].[usp_MobileOneTimePassword]

       @username varchar(25),
       @type varchar(1),
       @challenge varchar(6)

AS
BEGIN

       SET NOCOUNT ON;

       -- Sets the maximum period a group of 30 Second Passwords are Valid For!
   declare @maxperiod as int;
       set @maxperiod = 3*60; -- In Seconds = +/- 3 Minutes

       declare @counter as int;
       declare @timestring as varchar(11);
       declare @key as varchar(32);
       declare @pin as varchar(4);
       declare @hash as varbinary(20);
       declare @phash as varchar(6);
       declare @uChallenge     as varchar(6);

       -- Get the Key and the pin number
       set @key = (SELECT tokenID FROM accounts WHERE username = @username);
       set @pin = (SELECT tokenPIN FROM accounts WHERE username = @username);
       set @uChallenge = (SELECT otp FROM Used_OTP WHERE otp = @Challenge);

       -- See if this challenge has been used before!
       IF @uChallenge = @Challenge
       BEGIN
               print 'Auth Fail';
       END
       ELSE
       BEGIN

               set @counter = (SELECT (DATEDIFF(s, '19700101', GETUTCDATE())-@maxperiod))

               -- Loop through and generate all possible codes for the 3 Minute Period
               WHILE @counter <= (SELECT (DATEDIFF(s, '19700101', GETUTCDATE()) + @maxperiod))
               BEGIN

                       -- Increment the Counter
                       set @counter = (SELECT (@counter + 1));

                       -- Set the TimeString
                       set @timestring = (SELECT SUBSTRING(CAST(@counter as varchar(10)),0,LEN(@counter)));

                       -- Hash up the codes using MD5
                       set @hash = HashBytes('MD5',@timestring+@key+@pin);

                       -- Convert this to plain text so we can compare against it
                       set @phash = sys.fn_varbintohexsubstring(0,(SELECT SUBSTRING(@hash,0,4)),1,0);

                       -- Compare the generated code with the Challenge the user sent
                       if @phash = @challenge
                       BEGIN

                               -- Insert the used challenge into a table to protect against double use
                               INSERT INTO t_Used_OTP (otp) VALUES (@challenge);
                               print 'Auth OK';
                               BREAK;

                       END

               END

       END

END