I really don't know why my Visual Studio MySQL form is giving this error

Question

I was recently working on a login form in Visual Studio using C# with MySQL and I kept getting this error. I have been struggling on this for hours on end now with no luck. I have double checked pretty much everything and I don't think the database information is incorrect. It may be something with the user account not having sufficient permissions to do these actions, but I have checked that over and I think it has the right permissions on the database. Does anyone know how to fix this?

Error

MySql.Data.MySqlClient.MySqlException: 'Authentication to host 'localhost' for user 'username' using method 'sha256_password' failed with message: Access denied for user 'username'@'localhost' (using password: YES)'

"con" Variable

MySqlConnection con = new MySqlConnection("server = localhost; user=username;database=database;port=3306;password=pass");
int i;

Login Button Click Event

private void submit_Click(object sender, EventArgs e)
        {
            i = 0;
            con.Open();
            MySqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from login where username='"+username.Text+"' and password='"+password.Text+"'";
            cmd.ExecuteNonQuery();
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(dt);
            i = Convert.ToInt32(dt.Rows.Count.ToString());

            if (i == 0)
            {
                MessageBox.Show("You have entered an invalid username or password.");
            }
            else
            {
                this.Hide();
                Login fm = new Login();
                fm.Show();
            }
            con.Close();
        }

You should be sure to have granted to the user login permissions from the current computer. They are not automatic. Second point. Learn ASAP how to build a sql command using parameters. This code will fail if the password or the username contains a single quote, but worse, it could be easily used to create an [SQL Injection hack](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, Jan 02 '22 at 11:17
your question has nothing to do with Visual Studio. VS is just a development environment that helps writing, compiling and debugging code, but has nothing to do with the running program and the interaction between your code and the mysql db. — jps, Jan 02 '22 at 11:21
Side points: You *must* parameterize your SQL otherwise you leave yourself open to SQL injection attacks and syntax errors. You need to dispose your connection, command etc with `using`. Don't `select *` just select the columns you need. In this case you are just checking for existence so you can do `select 1` then instead of using an adapter you can use `i = (int)cmd.ExecuteScalar()`. Make sure to close the connection before blocking with a message box. — Charlieface, Jan 02 '22 at 12:41

score 0 · Answer 1 · answered Jan 02 '22 at 11:46

Try using the open-source MySqlConnector package in place of Connector/Net if you aren't already using it. Especially if you use the MariaDB fork of MySQL.

Make sure you can get to the MySQL database with a non-Oracle-furnished client like HeidiSQL.

Try re-creating that username user on your MySQL server specifying the newer password authentication method.

CREATE USER 'username'@'localhost' IDENTIFIED WITH sha256_password BY 'pass';
FLUSH PRIVILEGES;

I really don't know why my Visual Studio MySQL form is giving this error

1 Answers1