Stored Procedure For Login Page and Custom Error Handling

This is a continuation of the previous article in which we used a generic handler to generate a CAPTCHA image.

1. Generation of CAPTCHA Image Using Generic Handler for Login Page

I have added many columns to the basic UserMaster Table to integrate a standard login policy.

#	Column	DataType	Function
1	IsLocked	Varchar(1)	If the user attempts login with the wrong password for 5 times then the user account is locked ('Y': Unlocked , 'N': Locked)
2	IsLogin	Bit	This flag states the User Login Status
3	LoginCnt	Int	Count of Login attempts with invalid password
4	UserSession	Varchar(200)	This is the user's unique session id
5	LastLogin	DateTime	DateTime of last login
6	IsActive	Bit	User Account Status

Step 1: Prepare Table structure



Set the Key/Constraint on the table on the LoginID column since it must be unique.



Step 2: Stored Procedure

CREATE PROCEDURE [dbo].[USP_UserLogin]
    @loginID        VARCHAR(25) ,
    @password       VARCHAR(200),
    @sessionID      VARCHAR(200),
    @mode           VARCHAR(15)
AS
BEGIN

IF    @mode = 'LOGIN'
BEGIN

-- USER NOT EXISTS
IF NOT EXISTS(  SELECT 1
                FROM tbl_UserMaster
                WHERE LoginID = @loginID AND IsDelete = 0 )
        BEGIN
            SELECT 'USER NOT EXISTS.' AS RES
            RETURN;
        END

-- USER INACTIVE
IF EXISTS ( SELECT 1
            FROM tbl_UserMaster
            WHERE LoginID = @loginID
            AND IsActive = 0 )
        BEGIN
            SELECT 'USER INACTIVE.' AS RES
            RETURN;
        END

-- USER LOCKED
IF EXISTS ( SELECT 1
            FROM tbl_UserMaster
            WHERE LoginID = @loginID
            AND IsLocked = 'Y' AND IsActive = 1 AND IsDelete = 0 )
        BEGIN
            SELECT 'USER LOCKED.' AS RES
            RETURN;
        END

-- PASSWORD CHECK
IF NOT EXISTS ( SELECT 1
                FROM tbl_UserMaster
                WHERE LoginID = @loginID
                AND [Password] = @password
                AND IsActive = 1 AND IsDelete = 0 )
        BEGIN
-- UPDATE INCORRECT LOGIN ATTEMPT
            UPDATE tbl_UserMaster
            SET LoginCnt = ISNULL(LoginCnt,0)  + 1
            WHERE LoginID = @loginID

-- IF INCORRECT LOGIN ATTEMPT MORE THAN 5 TIMES THEN LOCK USER ACCOUNT
            IF((SELECT LoginCnt FROM tbl_UserMaster WHERE LoginID = @loginID ) > 5 )
            BEGIN
                UPDATE tbl_UserMaster
                SET IsLocked = 'Y'
                WHERE LoginID = @loginID
            END

            SELECT 'PASSWORD INCORRECT.' AS RES
        END
        ELSE
        BEGIN
            SELECT UserID ,UserName ,LoginID ,LastLogin ,UserRole ,IsLogin
            FROM tbl_UserMaster
            WHERE LoginID = @loginID
            AND [Password] = @password
            AND IsActive = 1 AND IsDelete = 0


-- UPDATE USER LOGIN DETAILS
            IF ( (SELECT IsLogin    FROM tbl_UserMaster
            WHERE LoginID = @loginID AND [Password] = @password AND IsActive = 1 AND IsDelete = 0 ) = 0 )
            BEGIN
                UPDATE tbl_UserMaster
                SET IsLogin = 1 ,
                    userSession = @sessionID ,
                    LastLogin = GETDATE()
                WHERE LoginID = @loginID
            END
        END
END

ELSE IF @mode = 'CHANGELOGIN'
BEGIN

    UPDATE tbl_UserMaster
    SET IsLogin = 1 ,
        userSession = @sessionID ,
        LastLogin = GETDATE()
    WHERE LoginID = @loginID
END
END

Step 3: Passing the user credentials from Login.aspx and authenticating CAPTCHA spambots/adbots from signing up.

(Refer to the aspx script from the previous article "Generation of CAPTCHA Image Using Generic Handler for Login Page".)

#region " [ using ] "
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
#endregion

public partial class Login : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            UpdateCaptchaText();
        }
    }

    #region " [ Button Event ] "
    protected void btnRefresh_Click(object sender, ImageClickEventArgs e)
    {
        UpdateCaptchaText();
    }

    protected void btnLogin_Click(object sender, EventArgs e)
    {
        if (!string.Equals(txtCode.Text.Trim(), (string)Session["Captcha"]))
        {
            lblError.Text = "Enter correct code.";
            return;
        }

        lblError.Text = string.Empty;
        DataTable dtUser = new DataTable();
        string userSession = Guid.NewGuid().ToString();
        Session["UserSession"] = userSession;
        try
        {
            dtUser = checkUserLogin(userSession, "LOGIN");

            if (dtUser != null)
            {
                if (dtUser.Columns.Contains("RES"))
                {
                    lblError.Text = dtUser.Rows[0][0].ToString();
                    ClearPage();
                }
                else
                {
                    Session["UserID"] = dtUser.Rows[0]["UserID"];
                    Session["UserName"] = dtUser.Rows[0]["UserName"];
                    Session["LastLogin"] = dtUser.Rows[0]["LastLogin"];

                    Response.Redirect("~/Welcome.aspx");
                }
            }
            else
            {
                ClearPage();
                lblError.Text = "Unexpected error.";
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            dtUser.Dispose();
        }
    }
    #endregion

    #region " [ Private Function ] "
    private DataTable checkUserLogin(string userSession, string mode)
    {
        DataSet dsData = new DataSet();
        SqlConnection sqlCon = null;
        SqlDataAdapter sqlCmd = null;

        try
        {
            using (sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
            {
                sqlCmd = new SqlDataAdapter("USP_UserLogin", sqlCon);
                sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure;
                sqlCmd.SelectCommand.Parameters.AddWithValue("@loginID", txtLogin.Text.Trim());
                sqlCmd.SelectCommand.Parameters.AddWithValue("@password", txtPassword.Text.Trim());
                sqlCmd.SelectCommand.Parameters.AddWithValue("@sessionID", userSession);
                sqlCmd.SelectCommand.Parameters.AddWithValue("@mode", mode);

                sqlCon.Open();
                sqlCmd.Fill(dsData);

                sqlCon.Close();
            }
        }
        catch
        {
            throw;
        }
        return dsData.Tables[0];
    }

    private void ClearPage()
    {
        txtCode.Text = string.Empty;
        txtPassword.Text = string.Empty;
        txtCode.Text = string.Empty;
        UpdateCaptchaText();
    }

    private void UpdateCaptchaText()
    {
        txtCode.Text = string.Empty;
        Random randNum = new Random();

        //Store the captcha text in session to validate
        Session["Captcha"] = randNum.Next(10000, 99999).ToString();
        imgCaptcha.ImageUrl = "~/ghCaptcha.ashx?" + Session["Captcha"];
    }
    #endregion
}

Step 4: Insert dummy data into the tbl_userMaster table as in the following:

INSERT INTO tbl_UserMaster
           (UserName ,LoginID , [Password]
           , IsLocked, IsLogin, LoginCnt, Email
           , ContactNo, UserRole, IsActive, IsDelete)
VALUES     ('Daniel Craig', 'daniel', 'Password@123',
        'N', 0, 0, '[email protected]',
        '9837197322', 1, 1, 0 )

Step 5: Log into the new account, you will have the following page.

After successful login, the user is redirected to the home.aspx page.

Step 6: Error handling

1. If invalid credentials are entered:
   
   
   
   
2. If the user enters an invalid CAPTCHA code:
   
   
   
   
3. If the user enters an invalid password:
   
   
   
   
4. If the user has more than 5 incorrect login attempts then the user is locked as in the following:
   
   
   
   To unlock the user, change the loginCnt value to 0 and LoginLocked flag to "N".
   
5. If the user is inactive:
   
   
   

Enhancements

1. Filtering an XSS attack that occurrs from the login page.
2. Alert the user if another session is active on another machine for the requested credentials.
3. Having a single session per user, in other words only one session at a time will be active for each user.