query database and log in if username and password exist

Question

Have database added into web forms project. Database Table named Users consist 3 columns. Id, Username, Password. Data in database is manually inserted. If user on login page inserts correct user and password, it should be redirected on another page.

My c# code :

using(SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True"))
            {
                SqlCommand cmd = new SqlCommand("select * from Users;");
                cmd.Connection = con;
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                con.Close();
            }

How to get data from database and check if user put correct username and password to log in ?

Refer some basic tutorials of `ADO.NET` please. It's way too broad for this community. — Rahul Singh, Aug 14 '15 at 13:12
I would agree that this is too broad. Would also strongly recommend you read this: http://stackoverflow.com/questions/549/the-definitive-guide-to-form-based-website-authentication?rq=1 . There are so many ways you can get this wrong doing it all by yourself. — Paddy, Aug 14 '15 at 13:22

score 2 · Answer 1 · answered Aug 14 '15 at 13:20

your method should something like this

string Command = "SELECT Id FROM User WHERE Username = @Username AND Password = @Password;";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    myConnection.Open();
    using (SqlCommand myCommand = new SqlCommand(Command, myConnection))
    {
        myCommand.Parameters.Add("@Username", tbUser.Text);
        myCommand.Parameters.Add("@Password", tbPass.Text);
        return myCommand.ExecuteScalar() != null;
    }
}

Fidel · Accepted Answer · 2015-08-14T15:54:40.420

Ideally the password shouldn't be stored as plaintext (it should be salted and hashed instead).

But if it is plaintext, it'd be a case of:

    using(SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True"))
    {
        SqlCommand cmd = new SqlCommand("select * from Users where username like @username and password = @password;");
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@password", password);
        cmd.Connection = con;
        con.Open();

        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        con.Close();

        bool loginSuccessful = ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0));

        if (loginSuccessful)
        {
            Console.WriteLine("Success!");
        } else {
            Console.WriteLine("Invalid username or password");
        }
    }

Thank you for reply. Also added code segment to get username because of need later to store in session. Here is code : `string username = Convert.ToString(ds.Tables[0].Rows[0]["Username"]);` — LoverBugs, Aug 14 '15 at 14:31

sri harsha · Answer 3 · 2015-08-14T18:07:48.600

Keep the code in button click event. After user enters username and password in respective textboxes, clicks on login button, in that button click event give the code like below. In aspx file give the labels like

<asp:Label ID="lbluser" runat="server" Visible="false"></asp:Label>
<asp:Label ID="lblpwd" runat="server" Visible="false"></asp:Label>
<asp:Label ID="lblmessage" runat="server"  Visible="false" Text="Incorrect Username and Password"></asp:Label>

SqlCommand cmd = new SqlCommand("select * from Users where username=@username and password=@password ", con);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
con.Open();

SqlDataReader dr = default(SqlDataReader);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while ((dr.Read()) == true)
{
   lbluser.Text = dr["UserName"].ToString();
   lblpwd.Text = dr["password"].ToString();
if ((txtUsername.Text.Trim() == lbluser.Text.Trim())  &(txtPassword.Text.Trim() == lblpwd.Text.Trim()))
        {
            Response.Redirect("nextpage.aspx");
        }
        else
        {
            lblmessage.Visible = true;
        }
    }
   dr.Close();
con.Close();

Sri, I'm afraid your code is vulnerable to sql injection. User input should never become a part of your query - it's better to use parameterised queries instead. — Fidel, Aug 14 '15 at 15:17

query database and log in if username and password exist

3 Answers3