C# – How to validate username and password using ASP.NET and SQL Server

asp.netc

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();
}

Best Answer

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();
    }
}
Related Topic