June 28, 2004 at 2:45 am
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
June 28, 2004 at 6:16 am
Always, just make sure you alter any sensitive bits.
June 29, 2004 at 12:21 am
I'm up for it.....
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
June 29, 2004 at 5:57 am
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
June 29, 2004 at 5:59 am
urgh, I've just seen that the above has had all its tabbing/indentation removed.
Sorry...
June 29, 2004 at 6:55 am
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
June 29, 2004 at 9:29 am
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.
July 1, 2004 at 5:44 am
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)
July 1, 2004 at 5:48 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy