MySQL connection seems to be only reading first query

Question

I'm working on a C# project and until now I haven't had any difficulties whatsoever, the problem is while coding a simple login form.

Here's the code for it:

    MySqlConnection con = new MySqlConnection();
    MySqlCommand com = new MySqlCommand();

    public LoginForm()
    {
        InitializeComponent();

        con.ConnectionString = @"Data Source=localhost;port=3306;Initial Catalog=databaseName;User id=root;password=''";
    }

    private void button1_Click(object sender, EventArgs e)
    {
        con.Open();

        com.Connection = con;
        com.CommandText = "select * from users";
        MySqlDataReader dr = com.ExecuteReader();
        if (dr.Read())
        {
            if (txtBoxUsername.Text.Equals(dr["username"].ToString()) && txtBoxPassword.Text.Equals(dr["password"].ToString()))
            {
                lblIncorrect.Text = "";
                this.Hide();
                InjectForm f2 = new InjectForm();
                f2.Show();
            }
            else
            {
                lblIncorrect.Text = "Incorrect userename or password.";
                txtBoxPassword.Text = "";
            }
        }
        con.Close();
    }

As you can see, on button click it will open my MySql connection and select everything from the table users. Then check if the username and password entered on the text boxes match with the ones stored on phpMyAdmin.

The problem comes now, if I use the first user, it will sign in correctly and do what it's supposed to do, in the other hand though, if I sign in with my second user, it will throw the "Incorrect username or password." text.

This should all work but it just doesn't.

Any ideas?

Instead of selecting all the rows from the table you should select only rows matching with username and password using where clause in the query. — Chetan, Aug 09 '21 at 15:12
What if you try to login with your second user first? I suppose it is also not working? If so, check your user permissions, from which hosts are they allowed to connect to which database and so on. — Steeeve, Aug 09 '21 at 15:21
@Steeeve you're correct, I can't login with the second user firts neither, all of the accounts that can access my database have all permissions. — Zarcero, Aug 09 '21 at 15:34
I wanted to post an answer, but I'm still not sure, what are you trying to accomplish. As for the MySQL part: your are reading only the first record from your `MySqlDataReader`. You are checking, if this record matches your input, if not you are doing nothing more with the MySqlDataReader. So please edit your question to clarify your goal. — Steeeve, Aug 09 '21 at 15:43
@Steeeve am I only reading the first record? I thought I was reading them all, the goal I am trying to achieve is to check if the user inputed in txtBoxUsername is equal to any of the records in the table and if so, check the password and let them log in. — Zarcero, Aug 09 '21 at 15:51
You shouldn't be storing passwords as plain text. Also you should be opening/closing your connection each time. — Tu deschizi eu inchid, Aug 09 '21 at 15:54
@user9938 I know that I shouldn't be storing them as text, this is just a test. — Zarcero, Aug 09 '21 at 15:57
@Zarcero With if `rd.Read()` you are just looking for the first record in the MySqlDataReader. Use a loop instead, like `while(rd.Read()) { ... }`. You also don't need to query all the users (`select * from users`) if you want only one of them. You should use a [`WHERE`](https://dev.mysql.com/doc/refman/8.0/en/select.html) clause in your SQL statement to get only the needed user. — Steeeve, Aug 09 '21 at 16:12

Meysam Asadi · Accepted Answer · 2021-08-09T18:22:54.977

For optimal memory consumption and speed up, just fetch the desired record. You will notice a difference in execution speed when the data becomes very large.

change button1_Click to :

private void button1_Click(object sender, EventArgs e)
{
     con.Open();

     com.Connection = con;
     com.CommandText = "select * from users where username = @username and password = @password";
     com.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtBoxUsername.Text;
     com.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtBoxPassword.Text;
     MySqlDataReader dr = com.ExecuteReader();
     if (dr.HasRows)
     {
         while (dr.Read())
         {
             lblIncorrect.Text = "";
             this.Hide();
             InjectForm f2 = new InjectForm();
             f2.Show();
             break;
         }
     }
     else
     {
         lblIncorrect.Text = "Incorrect userename or password.";
         txtBoxPassword.Text = "";
     }
     con.Close();
}

Note: use while(dr.Read()) to read all rows to scroll through all rows.

@MeysamAsadi If you write an answer, you should put `con`, `com`, `dr` (and maybe `f2` with `ShowDialog`) in a `using` block. Also check which variable name you would like to use: `com` or `command`. And why break the while-loop? If the database design can give you more than one row, the behavior is not specified. If you only want the first possible row, add `CommandBehavior.SingleRow` to `ExecuteReader` and you don't need the while-loop and `if (dr.HasRows)`. Just my 2 cents ;) — Steeeve, Aug 09 '21 at 18:07

MySQL connection seems to be only reading first query

1 Answers1