0

The error I get is

Incorrect syntax near the keyword 'WHERE'.

Script:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[msp3_account_login]
     @LOGINID nvarchar(50),
     @PASSWORD nvarchar(50),
     @Status NVARCHAR(50),  -- A status symbol, 1/0. Don't know what this represents
     @IPAddress NVARCHAR(50)  --Either our server IP, or their remote IP
AS
BEGIN
    DECLARE @COUNT INT;
    SET @COUNT = (SELECT COUNT(FLD_LOGINID) FROM TBL_ACCOUNT WHERE [FLD_LOGINID] = @LoginID AND [FLD_PASSWORD] = @PASSWORD) --AND GETDATE() > (SELECT * FROM fc_ConvertToDate([FLD_PASSFAILTIME])));

    IF (@COUNT > 0)
    BEGIN
        SELECT * 
        FROM TBL_ACCOUNT  
        WHERE FLD_LOGINID = @LoginID
    END

    IF (@COUNT = 0)
    BEGIN
        DECLARE @TotalTry SMALLINT;
        SET @TotalTry = (SELECT ISNULL([FLD_PASSFAILCOUNT], 0) FROM [TBL_ACCOUNT] WHERE [FLD_LOGINID] = @LoginID);

        IF @TotalTry > 3
        BEGIN
            DECLARE @TryAgainDate DATETIME;
            SET @TryAgainDate = DATEADD(MINUTE, 5, GETDATE());

            UPDATE [TBL_ACCOUNT] 
            SET [FLD_PASSFAILTIME] = (SELECT * 
                                      FROM [fc_ConvertDate2](@TryAgainDate))
        END
        ELSE
        BEGIN
            UPDATE [TBL_ACCOUNT] 
            SET [FLD_PASSFAILCOUNT] = [FLD_PASSFAILCOUNT] + 1 
            WHERE [FLD_LOGINID] = @LoginID;
        END
     END

     --UPDATE TBL_ACCOUNT SET [FLD_VER] = '1'
     --SELECT * FROM TBL_ACCOUNT
     RETURN 0;
END

We can't hash the passwords, we do not have the source code for the applications, it's an old game.

1

You can confirm whether a given value contains a capital letter or special character with a function. Using a function will allow you to fully customize exactly which characters you want to reject/accept.

Then just use the CheckPassword function in your stored proc, and it will return a BIT/INT value that indicates whether the password is valid or not.

Or if you want to keep it in the procedure, just use REGEX, something like:

WHERE @PASSWORD COLLATE sql_latin1_general_cp1_cs_as NOT LIKE '%[ABCDEFGHIJKLMNOPQRZTUVWXYZ]%'
AND @PASSWORD NOT LIKE '%[!@#$%^&*();[[]],./]%'

The COLLATE is necessary to make the value Case Sensitive, which is important in your case.

You should add the above either as an initial check, or as a new function which you call within the procedure. This check/function will essentially not allow the rest of the procedure to run if it finds that the password does not meet the criteria.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.