MySql.Data.MySqlClient C# console app

Question

I am using:

                string selectString =
                "SELECT username, password " +
                "FROM users " +
                "WHERE username = '" + user + "' AND password = '" + password + "'";

                MySqlCommand mySqlCommand = new MySqlCommand(selectString, Program.mySqlConnection);
                Program.mySqlConnection.Open();
                String strResult = String.Empty;
                strResult = (String)mySqlCommand.ExecuteScalar();
                Program.mySqlConnection.Close();
                if (strResult.Length == 0)
                {
                    responseString = "invalid";
                    InvalidLogin = true;
                } else {
                    InvalidLogin = false;
                }

and at strResult.Length I get a NullReferenceException for some reason.

FYI, the password column is a hashed version of the password, and not the actual password. Testing against it would require you to compare input via the Password() function: dev.mysql.com/doc/refman/5.1/en/… — Lynn Crumbling
– Lynn Crumbling, Commented Oct 19, 2012 at 18:24
@LynnCrumbling, it isn't a hashed password if he didn't hash it when inserting it. — Gromer
– Gromer, Commented Oct 19, 2012 at 18:29
@Gromer sigh I hope you don't opt to pay your bill online, lest they're talking to their payment gateway without https.... — Lynn Crumbling
– Lynn Crumbling, Commented Oct 19, 2012 at 18:43

Aleksandar Vucetic · Accepted Answer · 2012-10-19 18:54:35Z

This is how your code should look like:

using(var connection = new MySQLConnection(connectionString))
{
    using(var command = connection.CreateCommand())
    {
        command.CommandText = @"
SELECT COUNT(*) 
FROM users
WHERE username = @user AND password = @password";
        command.Parameters.Add(new MySQLParameter("user", user));
        command.Parameters.Add(new MySQLParameter("password", password));

        var total = (int)command.ExecuteScalar();
        if(total == 0)
            InvalidLogin = true;
        else
            InvalidLogin = false;
    }
}

There are few things to notice

NEVER build your query string in that way you did it. Search on Google for "sql injection" to find more about what could happen to you. ALWAYS use parameters. I know, you wrote that this is console app, but good habits are important.
ALWAYS use using keyword when working with database connections and commands.
From your code, I have a feeling that you have global MySQLConnection object, right? NEVER do that! ADO.NET uses connection pooling, so opening new connection for your actions is not expensive operation. Make sure that you didn't disable connection pooling in your connection string.
Not related to ADO.NET, but important: you should hash your password.

To answer your question, the problem is in ExecuteScalar that you are using. It returns scalar variable (single value)...in your query, you are returning username and password, so you should use ExecuteReader instead...but I think that COUNT(*) in the query that I posted, together with ExecuteScalar might be a better solution.

Vijaychandar · Accepted Answer · 2012-10-19 18:42:56Z

2

Try this..

string selectString =
                "SELECT username, password " +
                "FROM users " +
                "WHERE username = '" + user + "' AND password = '" + password + "'";

                MySqlCommand mySqlCommand = new MySqlCommand(selectString, Program.mySqlConnection);
                Program.mySqlConnection.Open();
                String strResult = String.Empty;

                if (mySqlCommand.ExecuteScalar()== NULL)
                {
                    responseString = "invalid";
                    InvalidLogin = true;
                } else {
                    InvalidLogin = false;
                }
               Program.mySqlConnection.Close();

answered Oct 19, 2012 at 18:42

Vijaychandar

7145 silver badges21 bronze badges

1 Comment

Max0999 Over a year ago

I used (strResult == user) instead and everything started working, thanks. BTW I checked for injection. Thanks everyone for being so awesome!

mcalex · Accepted Answer · 2012-10-19 18:24:35Z

1

ExecuteScalar() returns a single value. You want ExecuteReader() because you're bringing back the Username and Password

answered Oct 19, 2012 at 18:24

mcalex

6,7985 gold badges53 silver badges86 bronze badges

1 Comment

mcalex Over a year ago

hmmmm. Have a look at Aleksandar Vucetic's suggestion about using Count(*) above, to see if you are getting any data from your query.

Collectives™ on Stack Overflow

MySql.Data.MySqlClient C# console app

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related