How to authenticate login with a pre-hashed mysql password in C#?

Question

So, in my mysql database, I have a whole table for users which contains hashed (with the PASSWORD() mysql function) passwords. In C#, I made a program that can insert new records into the database and display existing ones.

Now I'm at the stage when I want to make a login, which checks if the entered data (username and password) equal to the ones that are in the users table. (The users table contains id, username, password columns.)

What I tried so far:

In my class that is for database operations: (The c.conn, c.open(), c.close() are just functions defined by me in a class called Connect.)

public bool logIn(string usrName, string pswd)
        {
            bool success = false;
            string dbusrname; string dbpswd;
            c.open();
            string hashedEnteredPswd = "PASSWORD(@password);";
            cmd = new MySqlCommand(hashedEnteredPswd, c.conn);
            cmd.Parameters.AddWithValue("@password", pswd);
            cmd.ExecuteNonQuery();
            c.close();
            query = "SELECT * FROM admins;";
            c.open();
            cmd = new MySqlCommand(query, c.conn);
            var reader=cmd.ExecuteReader();
            while (reader.Read())
            {
                dbusrname = reader["users"].ToString();
                dbpswd = reader["password"].ToString();
                if (dbusrname == usrName && dbpswd == hashedEnteredPswd)
                {
                    success = true;
                }
                else
                {
                    success = false;
                }
            }
            c.close();
            return success;
        }

In the code of my form:

        private void Container_Load(object sender, EventArgs e)
        {
            c.open();
            clearPanels();
            panelLogIn.Visible = true;
        }

        private void buttonLogIn_Click(object sender, EventArgs e)
        {
            if (dbops.logIn(textBoxUID.Text, textBoxPSWD.Text))
            {
                clearPanels();
                panelMain.Visible = true;
            }
        }

In my misery, I tried hashing the entered password with a mysql function in C#(I know, I know...)

Ofc when If I run this, I get the following exception: MySql.Data.MySqlClient.MySqlException: 'PROCEDURE test.PASSWORD does not exist' which is expected and I understand why it doesn't work.

But then, how should do this correctly? How on earth do I make it work?

should you not need to do `SELECT PASSWORD(@password);`? Also you need to store the value from your query, otherwise `hashedEnteredPswd` will literally equal `PASSWORD(@password);` — Sasha, Jan 15 '20 at 15:53

score 0 · Answer 1 · answered Jan 15 '20 at 16:10

By your code, you check each user in the database and compare it. That's not efficient.

The "SQL way" to do that is querying exactly what you need.

string query = "SELECT user FROM admins WHERE username = " + username + " AND password = " + password;

(Note that this solution is not safe againts SQL injections and not elegent, Learn more about adding parameters)

Regarding your error, You have tried to hash using PASSWORD function that has been deprecated for version > 5.7.5. Here are some other ways to hash your passwords.

It's a much easier and more simple way than mine. I just had to add a mysql hash function to the query and check if it returned any rows. It works perfectly now, thanks mate! — scratchingmyhead, Jan 15 '20 at 16:42

score 0 · Accepted Answer · answered Jan 15 '20 at 16:36

There are mentions (though nothing official), that mysql Password function does the following

"*" + SHA1(SHA1(pass))

This is not to be relied upon.

You could try the following though (it's not advised, but it might solve your problem).

// remove the previous commands 
query = "SELECT * FROM admins Where password = PASSWORD(@password);

Then create the parameter for the query.

Best way to do it though, would be to hash it yourself with a modern hashing algorithm before inserting it into the database, and rehash it before checking it. Ignore the password function altogether.

How to authenticate login with a pre-hashed mysql password in C#?

2 Answers2