How to return true if name and password both match? (MySQL/C#)

Question

I am currently working on a simple login system with C# and MySQL and I have everything working so far but I can't seem to get the login verification working, I can't work out the correct way of checking the records in the table as I have tried a few different ways that I found online but none were working.

public bool loginCorrect(String name, String password)
    {
        if (this.OpenConnection() == true) {
            using (MySqlCommand cmd = new MySqlCommand ("SELECT * FROM logins WHERE name = @UserName AND password = @Password")) {
                cmd.Parameters.AddWithValue ("@UserName", name);
                cmd.Parameters.AddWithValue ("@Password", password);
                //Now we are going to read the data imput
                MySqlDataReader myLoginReader = cmd.ExecuteReader ();
                //if the data matches the rows (username, password), then you enter to the page                 
                bool isExist = myLoginReader.HasRows();

                //Close Reader                 
                myLoginReader.Close ();            
            }                          
            //Close Connection             
            this.CloseConnection ();
        }

        return isExist;
    }

This is the current code for the login verification which doesn't work. I want the boolean to return true when the passed parameters name and password match which the ones in the database table 'logins'.

Below is my working Insert method just in case you need to compare the structure.

public void Insert(String username, String password)
    {

        string query = "INSERT INTO logins (name, password) VALUES ('" + username + "','" + password + "')";

        //open connection
        if (this.OpenConnection () == true) 
        {
            //Create command and assign query
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();

        }

    }

EDIT: I have altered the loginCorrect method as suggested and I get these errors when compiling: bool isExist = myLoginReader.HasRows(); // Non-Invocable member 'System.Data.Common.DbDataReader.HasRows' cannot be used like a method.

And: return isExist; // The name 'isExist' does not exist in the current context.

EDIT 2 Current Code

        public void Insert(String name, String password)
    {

        //open connection
        if (this.OpenConnection()) 
        {
            string query = "INSERT INTO logins (name, password) VALUES (@UserName, @Password)";

            //Create command and assign query
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Add parameters
            cmd.Parameters.AddWithValue ("@UserName", name);
            cmd.Parameters.AddWithValue ("@Password", password);

            //Execute command
            cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();
        }
    }

    public bool loginCorrect(String name, String password)
    {
        bool isExist = false;
        if (this.OpenConnection()) {
            using (MySqlCommand cmd = new MySqlCommand ("SELECT * FROM logins WHERE `name` = @UserName AND password = @Password")) {
                cmd.Parameters.AddWithValue ("@UserName", name);
                cmd.Parameters.AddWithValue ("@Password", password);
                //Now we are going to read the data imput
                MySqlDataReader myLoginReader = cmd.ExecuteReader ();
                //if the data matches the rows (username, password), then you enter to the page                 
                isExist = myLoginReader.HasRows;

                //Close Reader                 
                myLoginReader.Close ();            
            }                          
            //Close Connection             
            this.CloseConnection ();
        }

        return isExist;
    }

Which gives

System.InvalidOperationException: Connection must be valid and open.

SOLUTION

        public void Insert(String name, String password)
    {

        //open connection
        if (this.OpenConnection()) 
        {
            string query = "INSERT INTO logins (name, password) VALUES (@UserName, @Password)";

            //Create command and assign query
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Add parameters
            cmd.Parameters.AddWithValue ("@UserName", name);
            cmd.Parameters.AddWithValue ("@Password", password);

            //Execute command
            cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();
        }
    }

    public bool loginCorrect(String name, String password)
    {
        bool isExist = false;
        if (this.OpenConnection()) {
            using (MySqlCommand cmd = new MySqlCommand ("SELECT * FROM logins WHERE `name` = @UserName AND password = @Password", connection)) {
                cmd.Parameters.AddWithValue ("@UserName", name);
                cmd.Parameters.AddWithValue ("@Password", password);
                //Now we are going to read the data imput
                MySqlDataReader myLoginReader = cmd.ExecuteReader ();
                //if the data matches the rows (username, password), then you enter to the page                 
                isExist = myLoginReader.HasRows;

                //Close Reader                 
                myLoginReader.Close ();            
            }                          
            //Close Connection             
            this.CloseConnection ();
        }

        return isExist;
    }

The problem was not passing the connection in the loginCorrect method

Firstly, parametize your inserts to avoid SQL injection. Here's a video on the topic: https://www.youtube.com/watch?v=_jKylhJtPmI — Neta, Nov 02 '15 at 22:07
Even if you find a technical way of solving the problem, please scrap the code and [start from scratch](http://stackoverflow.com/q/1054022/335858). — Sergey Kalinichenko, Nov 02 '15 at 22:09
@Steve I guess it doesn't really matter as he said the insert works. Do you believe `SqlDataReader` fails because of that? — Neta, Nov 02 '15 at 22:09
For one, you will need to add `@` to your command parameter names e,g, `cmd.Parameters.AddWithValue ("@UserName", name); cmd.Parameters.AddWithValue ("@Password", password);` — Scott P, Nov 02 '15 at 22:10
By "not working" you mean doesn't compile, throws error, always returns false or what? — Sami, Nov 02 '15 at 22:11
Also, seems like `SqlDataReader` has a more appropriate function `.HasRows()` — Neta, Nov 02 '15 at 22:12
HasRows is a property, not a method, right? You should lose the parenthesis. — , Nov 02 '15 at 22:29
Do not continually change your original question embedding the suggestion received. This makes comments and answer incoherent with your problem — Steve, Nov 02 '15 at 22:32

Neta · Accepted Answer · 2015-11-02T22:33:34.897

1

Try this, combined with some of the comments we gave you:

User check:

public bool loginCorrect(String name, String HASHED_AND_SALTED_PASSWORD)
    {
        bool isExist;
        if (this.OpenConnection()) {
            using (SqlCommand cmd = new SqlCommand ("SELECT * FROM logins WHERE `name` = @UserName AND password = @Password")) {
                cmd.Parameters.AddWithValue ("@UserName", name);
                cmd.Parameters.AddWithValue ("@Password", HASHED_AND_SALTED_PASSWORD);
                //Now we are going to read the data imput
                SqlDataReader myLoginReader = cmd.ExecuteReader ();
                //if the data matches the rows (username, password), then you enter to the page                 
                isExist = myLoginReader.HasRows;

                //Close Reader                 
                myLoginReader.Close ();            
            }                          
            //Close Connection             
            this.CloseConnection ();
        }

        return isExist;
    }

If you return inside the if, the connections won't be closed.

Insert - I'm not too experienced with C# classes, hope I did it correctly.

public void Insert(String username, String password)
{
    //Hash and salt password here (this is pseudo code)
    String HASHED_AND_SALTED_PASSWORD = StrongHash(password + GenerateSalt());

    //open connection
    if (this.OpenConnection()) 
    {
        string query = "INSERT INTO logins (`name`, password) VALUES (@UserName, @Password)";

        //Create command and assign query
        MySqlCommand cmd = new MySqlCommand(query, connection);

        //Add parameters
        cmd.Parameters.AddWithValue ("@UserName", name);
        cmd.Parameters.AddWithValue ("@Password", HASHED_AND_SALTED_PASSWORD);

        //Execute command
        cmd.ExecuteNonQuery();

        //close connection
        this.CloseConnection();
    }
}

A few other things - Make sure you store a properly hashed and salted password, using a strong hashing algorithm. Even if this is for a school project, you'd want to get used to best practices. I've fixed a few unnecessary things I saw like if (this.OpenConnection() == true).

Let us know if this works.

EDIT: Sorry, my bad. HasRows is a property, not a function. Try this instead: myLoginReader.HasRows; (Removed parentheses) Also, declare Bool isExist at the top.

edited Nov 02 '15 at 22:33

answered Nov 02 '15 at 22:17

Neta

871
5
14
30

Using your suggestion, only two lines are now giving errors: String HASHED_AND_SALTED_PASSWORD = StrongHash(password + GenerateSalt()); // StrongHash and GenerateSalt don't exist in current context And: return isExist; // isExist doesn't exist in current context – JoeBenyon Nov 02 '15 at 22:31
As I mentioned, `String HASHED_AND_SALTED_PASSWORD = StrongHash(password + GenerateSalt());` is pseudo code, it won't run. I believe someone gave you a link in the comments to a guide explaining how to save passwords, until you follow it ignore my `HASHED_AND_SALTED_PASSWORD` variable and use the `password` variable you were using before. – Neta Nov 02 '15 at 22:35
Okay, I declared isExist and using my password variable I get this exception: System.InvalidOperationException: Connection must be valid and open. – JoeBenyon Nov 02 '15 at 22:38
Say, where is the `connection` variable in the insert coming from? I believe you need it in the user check as well. Look here: https://msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx – Neta Nov 02 '15 at 22:43
They are both getting it from the same place when using if(this.OpenConnection()){} Both Insert and User Check are in the same Class DataBase.cs along with OpenConnection – JoeBenyon Nov 02 '15 at 22:48
I'm a bit confused that you used `SqlCommand` for one check and `MySqlCommand` for the other. Is there a specific reason? Try using `MySqlCommand` for both (since you know it's working) and passing `connection` in the user check as well – Neta Nov 02 '15 at 22:51
I've been using MySqlCommand for both but I think I posted the wrong one, and how do you mean by passing connection? **See Edit 2** – JoeBenyon Nov 02 '15 at 22:56
`MySqlCommand cmd = new MySqlCommand(query, connection);` instead of `MySqlCommand cmd = new MySqlCommand(query);` – Neta Nov 02 '15 at 22:58
Yes! That solved the problem. Passing the connection meant that there were no errors and the correct result was returned (true). I will post the final solution – JoeBenyon Nov 02 '15 at 23:00

How to return true if name and password both match? (MySQL/C#)

1 Answers1