Resolved sql fill not working

[rwahdan2023]

Hi,

I am trying to run this code but getting error.

code:

try
{
    SqlConnection conn =
    new SqlConnection("Data Source = .; " +
    "Initial Catalog = users; Persist Security Info = True; " +
    "User ID = sa; Password = ***********");

    SqlCommand cmd = new SqlCommand("select * from users");
    cmd.Connection = conn;
    cmd.Parameters.AddWithValue("@username", txtuser.Text);
    cmd.Parameters.AddWithValue("@password", txtpass.Text);

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);

    if (dt.Rows.Count > 0)
    {
        MessageBox.Show("Success!");
    }
    else
    {
        MessageBox.Show("Not Success!");
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

the error:
I am always getting success even if the user or password is incorrect

 

[Skydiver]

I'm assuming that by "error", you mean that you are showing a message box from line 27 at runtime. Can you confirm?

Or are you getting a compile time error, instead of a runtime error, you should show us the rest of the error message. What you are showing us is not very helpful.

 

[rwahdan2023]

I'm assuming that by "error", you mean that you are showing a message box from line 27 at runtime. Can you confirm?

Or are you getting a compile time error, instead of a runtime error, you should show us the rest of the error message. What you are showing us is not very helpful.

I am always getting the true part "success" even if i type the wrong username or password. it is still sucess message. always getting line 19 and never reaches line 23.

I am trying to check if the username and password in the form equals to the one's in the DB. Am I doing something wrong?

 

[Skydiver]

Your line 8 is doing a SQL query asking for all rows from the users table. As long as your table is not empty, you will get "Success".

 

[rwahdan2023]

Your line 8 is doing a SQL query asking for all rows from the users table. As long as your table is not empty, you will get "Success".

so how to check if the username in the textbox is one of the records in the DB?

 

[rwahdan2023]

Hi,

I am trying to run this code but getting error.

code:

try
{
    SqlConnection conn =
    new SqlConnection("Data Source = .; " +
    "Initial Catalog = users; Persist Security Info = True; " +
    "User ID = sa; Password = ***********");

    SqlCommand cmd = new SqlCommand("select * from users");
    cmd.Connection = conn;
    cmd.Parameters.AddWithValue("@username", txtuser.Text);
    cmd.Parameters.AddWithValue("@password", txtpass.Text);

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);

    if (dt.Rows.Count > 0)
    {
        MessageBox.Show("Success!");
    }
    else
    {
        MessageBox.Show("Not Success!");
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

the error:
I am always getting success even if the user or password is incorrect

I found a solution:

SqlDataAdapter sda = new SqlDataAdapter("SELECT COUNT(*) FROM users WHERE username='" + txtuser.Text + "' AND password='" + txtpass.Text + "'", conn);

 

[Skydiver]

That's terrible. You were already starting to do things right by using parameters, but now you revert to using code that is ripe of SQL injection attack.

Exploits of a Mom

xkcd.com

 

[rwahdan2023]

That's terrible. You were already starting to do things right by using parameters, but now you revert to using code that is ripe of SQL injection attack.

Exploits of a Mom

xkcd.com

I am with you but I am new to c#. Could you please help me resolve it?

 

[Skydiver]

But it's not a matter of writing C#. It's a matter of writing SQL. Edit your SQL to use parameters, and then go back to your old code which passed in values for those parameters.

 

[jmcilhinney]

You simply put the parameters into the SQL. Don't use string concatenation. There are countless examples already out there as this is a something every beginner learns. I just typed "ado.net query parameters" into a search engine and the very first result provided such an example. I would actively encourage you to use this site for the things that you can't work out on your own, but you should always try to work everything out on your own first.

 

[rwahdan2023]

I found the solution to add parameters to sql and assign them to values. Thank you all.

SqlCommand cmd = new SqlCommand("select username, password " +
    "from Users where username = @username AND " +
    "password = @password", conn);

cmd.Parameters.AddWithValue("@username", txtuser.Text);
cmd.Parameters.AddWithValue("@password", txtpass.Text);

 

[Skydiver]

Well done!

Now the next problem you need to solve is to that it is a very bad security idea save passwords in the database in plain text. Saving it as encrypted is slightly better, but is still a security risk. The best practice in terms of security is to save hashed versions of the password.

 

[rwahdan2023]

Well done!

Now the next problem you need to solve is to that it is a very bad security idea save passwords in the database in plain text. Saving it as encrypted is slightly better, but is still a security risk. The best practice in terms of security is to save hashed versions of the password.

Thnaks alot @Skydiver and I will look into this.

 

[jmcilhinney]

I would also suggest not using AddWithValue when adding parameters. It will work in most situations but the data type for the parameter is inferred from the value and sometimes the system may not infer the correct type and that can have negative effects. If you always use Add then you never have to wonder whether you can safely use AddWithValue or not.

cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtuser.Text;

 

[cjard]

I would also suggest not using AddWithValue when adding parameters.

True for SqlServer, not necessarily true for other DBMS, and definitely not something to worry about with MySQL