How to validate username and password using ASP.NET and SQL Server?

Question

I'm trying to write an asp.net code in C# which will basically have a login page with username and pass. If I enter username and pass as "admin" it will open the Admin.aspx.

Likewise for "employee" it's employee.aspx and for "manager" it's manager.aspx.

I have written quite a bit but stuck at the end.. please help how to open the appropriate page.. The username and password are stored in a database and I have to match it with the database

protected void Button1_Click(object sender, EventArgs e)
{
     SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=payroll;Integrated Security=True");
     SqlCommand cmd = new SqlCommand("Select employeeid FROM employees WHERE username='" + TextBox1.Text + "'and password='"+TextBox2.Text+"'", con);

     cmd.CommandType = CommandType.Text;
     cmd.Parameters.AddWithValue("@username", TextBox1.Text);
     cmd.Parameters.AddWithValue("@password", TextBox2.Text);

     con.Open();

     SqlDataReader dr = cmd.ExecuteReader();

     if (dr.Read())                         //I'M WRONG FROM HERE ONWARDS.
     {
        Response.Redirect("Admin.aspx"); 
     }

     con.Close();
     dr.Close();
}

marc_s · Accepted Answer · 2015-08-29 12:28:46Z

You should:

REALLY use parametrized query to avoid SQL injection and other messy business
put all your disposable objects like SqlConnection and SqlCommand into using(...) { ... } blocks
read both the employeeid and the role of the employee to make the decision where to jump off to
avoid using AddWithValue which has its drawbacks (see )
ALSO: you should NOT store your passwords in CLEAR TEXT in your table! You should ALWAYS hash & salt your password - no exception

Try this code:

protected void Button1_Click(object sender, EventArgs e)
{
    // define your connection string (typically from a .config file) and your query WITH parameters!
    string connectionString = "Data Source=(local);Initial Catalog=payroll;Integrated Security=True";
    string query = "SELECT employeeid, role FROM employees WHERE username=@user AND and password=@pwd;";

    // set up a connection and command in using() blocks
    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(query, con))
    {
        // add parameters and set their values
        cmd.Parameters.Add("@user", SqlDbType.VarChar, 50).Value = TextBox1.Text;
        cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 50).Value = TextBox2.Text;

        // open connection
        con.Open();

        // establish data reader
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            // if at least one row is returned....  
            if (dr.Read()) 
            {  
                // get employee ID and role from the reader
                string employeeId = dr.GetFieldValue<string>(0);
                string role = dr.GetFieldValue<string>(1);

                // depending on role, jump off to various pages
                switch (role)
                {
                    case "admin":
                       Response.Redirect("Admin.aspx"); 
                       break;

                    case "manager":
                       Response.Redirect("manager.aspx"); 
                       break;

                    default:
                       Response.Redirect("employee.aspx"); 
                       break;
                    }
                } 
                else
                {
                   // what do you want to do if NO ROW was returned? E.g. user/pwd combo is wrong
                }

            dr.Close();
        }    

        con.Close();
    }
}

user786 · Accepted Answer · 2015-08-29 11:18:57Z

1

You need one extra thing and that is dropdown list and that dropdown will be used to specify user type.

Or better would be if you have separate page for each login type.

answered Aug 29, 2015 at 11:18

user786

4,4406 gold badges53 silver badges112 bronze badges

2 Comments

Sam Over a year ago

yes I can do with the dropdown menu..I was trying this so it would do it automatically.....

user786 Over a year ago

then just do what u are doing. and when you authenticate against db, at that time also get role and based on that redirect to respective page

Maysam · Accepted Answer · 2015-08-29 12:14:41Z

1

if (dr.Read()){                         
  if(dr.HasRow()){
    if(dr["username"].ToLower() == "admin"){ 
        Response.Redirect("Admin.aspx");
    }else if (dr["username"].ToLower() == "manager") {
        Response.Redirect("manager.asp");
    }//and more else if   
  }                                                

}

edited Aug 29, 2015 at 12:14

answered Aug 29, 2015 at 11:24

Maysam

7,38313 gold badges75 silver badges116 bronze badges

3 Comments

Sam Over a year ago

okay trying this..just a question though..with your code- if(dr["username"].ToLower() == "admin") it is only checking the username...do i have to do the password check here by using && dr["password"] ? or it will be done by the query itself?

Maysam Over a year ago

Hm, you should check dr.HasRow() just before Ifs. If it returns true means username and password combination is true.

Sam Over a year ago

okay..so do i need the cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@username", TextBox1.Text); cmd.Parameters.AddWithValue("@password", TextBox2.Text); ??

Collectives™ on Stack Overflow

How to validate username and password using ASP.NET and SQL Server?

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related