Frustrated by SYNTAX ERROR
-
Hi Guys,
I've been tearing my hair out over a syntax error in my 'LOG IN' stored procedure I'm writing for an ASP.NET web page; I'm really new to SQL and can't figure out what is wrong - is it OK to post my script here to get advice?
Don't want to upset anyone...
Many thanks in advance
Tim
-
Always, just make sure you alter any sensitive bits.
-
I'm up for it.....
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
Thanx guys, hope its not too big... The problem is with the outermost ELSE... I think my BEGIN & ENDs are not right maybe?
CREATE PROCEDURE spLOGON
@ORGID int,
@USERNAME nvarchar(50),
@PWORD nvarchar(50)
AS
--record counter
DECLARE @COUNT int
--no of users found that match USERNAME
DECLARE @USERS_COUNT int
--User ID from successful LOG ON
DECLARE @ID int
--Users PasswordFailCount
DECLARE @FAILCOUNT int
SET NOCOUNT ON
--Limit the initial query to a count of the records returned... if successful, do another LONGER query
-- only look for active records
SELECT @COUNT = COUNT(ID) FROM tblUSERS WHERE UserName = @USERNAME AND Password = @PWORD AND OrganisationID = @ORGID AND AccountActive = 1
IF @COUNT = 0
-- USERNAME was not found OR PASSWORD was wrong OR USERNAME not found in the specified ORGANISATION
SELECT @USERS_COUNT = COUNT(ID) FROM tblUSERS WHERE UserName = @USERNAME AND OrganisationID = @ORGID
BEGIN
IF @USERS_COUNT = 0
--USERNAME NOT FOUND OR NOT IN THE SPECIFIED ORGANISATION
BEGIN
INSERT tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'UserName not recognised')
--adds log of UNRECOGNISED USERNAME to the HISTORY table
SELECT 'USERNAME NOT RECOGNISED' AS LOGIN_ERROR
END
ELSE
--PASSWORD INCORRECT
--adds record of incorrect password attempt to the HISTORY table
INSERT tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'Password incorrect')
--GET THE USER'S ID NUMBER
SELECT @ID = ID FROM tblUSERS WHERE UserName = @USERNAME AND OrganisationID = @ORGID
--adds 1 to the FAILCOUNT
UPDATE tblUSERS SET PasswordFailCount = PasswordFailCount + 1 WHERE ID = @ID
--What's the FAILCOUNT now?
SELECT @FAILCOUNT = PasswordFailCount FROM tblUSERS WHERE ID = @ID
IF @FAILCOUNT >= (SELECT PasswordFailCount FROM tblORGANISATIONS WHERE ID = @ORGID)
--FAILCOUNT reached
--LOCKS the account if MAX FAILCOUNT (from ORGANISATION) is exceeded
BEGIN
UPDATE tblUSERS SET AccountLocked = 1 WHERE ID = @ID
--adds record of ACCOUNT LOCKED if account is locked
INSERT tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'Account locked - excessive failed password attempts')
SELECT 'INCORRECT PASSWORD - ACCOUNT NOW LOCKED' AS LOGIN_ERROR
RETURN
END
SELECT 'INCORRECT PASSWORD' AS LOGIN_ERROR
END
ELSE
-- USERNAME, PASSWORD and ORGANISATION IDs are correct; Now check that USER is not ACCOUNTLOCKED, INACTIVE
IF (SELECT AccountLocked FROM tblUSERS WHERE ID = @ID) = 1
BEGIN
--ACCOUNT IS LOCKED
--adds history record for LOGON attempt to locked account
INSERT tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'User access not permitted - ACCOUNT LOCKED')
SELECT 'ACCOUNT IS LOCKED' AS LOGIN_ERROR
RETURN
END
ELSE
-- USERNAME is correct, PASSWORD is correct, ORGANISATION is correct, ACCOUNT is not locked or inactive
BEGIN
--add successful LOGON history record
INSERT tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'Successful Log IN')
--resets FAIL COUNT, sets THIS_LOGON, ISLOGGEDON
UPDATE tblUSERS SET PasswordFailCount = 0, ThisLogon = GetDate() WHERE ID = @ID
--RETURN USER DETAILS
SELECT * FROM tblUSERS WHERE ID= @ID
END
SET NOCOUNT OFF
GO
-
urgh, I've just seen that the above has had all its tabbing/indentation removed.
Sorry...
-
Hopefully this works for you. Just ignore the temp tables as they were used instead of creating new tables in my db. It seemed to work O.K. for me
/****************************************************************************************************************/
DECLARE @ORGID INT
DECLARE @USERNAME VARCHAR(50)
DECLARE @PWORD VARCHAR(50)
SET @ORGID = 1
SET @USERNAME = 'testuser'
SET @PWORD = 'testpassword'
--record counter
DECLARE @COUNT INT
--no of users found that match USERNAME
DECLARE @USERS_COUNT INT
--User ID from successful LOG ON
DECLARE @ID INT
--Users PasswordFailCount
DECLARE @FAILCOUNT INT
SET NOCOUNT ON
/****************************************************************************************************************/
CREATE TABLE #tblUSERS
(
ID INT
, UserName VARCHAR(50)
, Password VARCHAR(50)
, OrganisationID INT
, AccountActive BIT
, AccountLocked BIT
, PasswordFailCount INT
, ThisLogon DATETIME
 
/****************************************************************************************************************/
CREATE TABLE #tblLOGON_HISTORY
(
USERNAME VARCHAR(50)
, REASON VARCHAR(1000)
 
/****************************************************************************************************************/
--Limit the initial query to a count of the records returned... if successful, do another LONGER query
-- only look for active records
SELECT
@COUNT = COUNT(ID)
FROM
#tblUSERS
WHERE
UserName = @USERNAME
AND Password = @PWORD
AND OrganisationID = @ORGID
AND AccountActive = 1
IF @COUNT = 0
-- USERNAME was not found OR PASSWORD was wrong OR USERNAME not found in the specified ORGANISATION
BEGIN
SELECT @USERS_COUNT = COUNT(ID) FROM #tblUSERS WHERE UserName = @USERNAME AND OrganisationID = @ORGID
IF @USERS_COUNT = 0
--USERNAME NOT FOUND OR NOT IN THE SPECIFIED ORGANISATION
BEGIN
INSERT #tblLOGON_HISTORY (USERNAME, REASON)
VALUES (@USERNAME, 'UserName not recognised')
--adds log of UNRECOGNISED USERNAME to the HISTORY table
SELECT 'USERNAME NOT RECOGNISED' AS LOGIN_ERROR
END
ELSE
BEGIN
--PASSWORD INCORRECT
--adds record of incorrect password attempt to the HISTORY table
INSERT #tblLOGON_HISTORY (USERNAME, REASON)
VALUES (@USERNAME, 'Password incorrect')
--GET THE USER'S ID NUMBER
SELECT @ID = ID FROM #tblUSERS WHERE UserName = @USERNAME AND OrganisationID = @ORGID
--adds 1 to the FAILCOUNT
UPDATE #tblUSERS SET PasswordFailCount = PasswordFailCount + 1 WHERE ID = @ID
--What's the FAILCOUNT now?
SELECT @FAILCOUNT = PasswordFailCount FROM #tblUSERS
WHERE ID = @ID
IF @FAILCOUNT >= (SELECT PasswordFailCount FROM tblORGANISATIONS WHERE ID = @ORGID)
--FAILCOUNT reached
--LOCKS the account if MAX FAILCOUNT (from ORGANISATION) is exceeded
BEGIN
UPDATE #tblUSERS SET AccountLocked = 1 WHERE ID = @ID
--adds record of ACCOUNT LOCKED if account is locked
INSERT #tblLOGON_HISTORY (USERNAME, REASON)
VALUES (@USERNAME, 'Account locked - excessive failed password attempts')
SELECT 'INCORRECT PASSWORD - ACCOUNT NOW LOCKED' AS LOGIN_ERROR
RETURN
END
SELECT 'INCORRECT PASSWORD' AS LOGIN_ERROR
END
END
ELSE
BEGIN
-- USERNAME, PASSWORD and ORGANISATION IDs are correct; Now check that USER is not ACCOUNTLOCKED, INACTIVE
IF (SELECT AccountLocked FROM #tblUSERS WHERE ID = @ID) = 1
BEGIN
--ACCOUNT IS LOCKED
--adds history record for LOGON attempt to locked account
INSERT #tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'User access not permitted - ACCOUNT LOCKED')
SELECT 'ACCOUNT IS LOCKED' AS LOGIN_ERROR
RETURN
END
ELSE
-- USERNAME is correct, PASSWORD is correct, ORGANISATION is correct, ACCOUNT is not locked or inactive
BEGIN
--add successful LOGON history record
INSERT #tblLOGON_HISTORY (USERNAME, REASON) VALUES(@USERNAME, 'Successful Log IN')
--resets FAIL COUNT, sets THIS_LOGON, ISLOGGEDON
UPDATE #tblUSERS SET PasswordFailCount = 0, ThisLogon = GetDate() WHERE ID = @ID
--RETURN USER DETAILS
SELECT * FROM #tblUSERS WHERE ID= @ID
END
END
/****************************************************************************************************************/
DROP TABLE #tblUSERS
DROP TABLE #tblLOGON_HISTORY
SET NOCOUNT OFF
-
I don't want to over simplify this but you may want to use EXISTS.
CREATE PROCEDURE [usp_CheckSecureLogin]
@UserName nvarchar(15),
@Password nvarchar(15),
@retval int OUTPUT
AS
IF EXISTS(SELECT * FROM Passwords WHERE (UserName = @UserName) AND (Password = @Password))
SELECT @retval = 1
ELSE
SELECT @retval = 0
GO
Yours truly.
-
WOW!! I was hoping for some help but I can't believe the trouble you guys have gone to to sort me out! Many, many thanks!!
SSTECHER - that worked a treat, and thanks to you the penny has finally dropped, BEGIN & END were my undoing! Now I understand and have learnt how to use this properly so BIG thanks to you!
BOB JOHNSON - Thanks for your reply! What advantage does IF EXISTS give me? Will getting rid of my @COUNT variable make it work faster?
There's so much to learn and now I feel really enthused thanks to all the help from you guys. What a nice bunch SQL people are!
Have a great day all!
Tim Morrison
Robertsbridge
(A small village, South of England)
-
Thanks for the kind words Tim, I'm sure one day you'll be helping me out with a problem.
The beauty of these forums, is that there is always someone willing to help.
Shane Stecher
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply