Sql-server – Incorrect syntax near the keyword ‘WHERE’ – SQL Server 2014

sql serversql server 2014syntax

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.

Best Answer

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.

Related Question