1

My MySQL connection throws null reference although this code worked well one year ago.
The line where the debugger indicates the exception contains
"connection = new MySqlConnection(connectionString);":

DBConnect MySqlConnection = new DBConnect();

string[] resultArray = MySqlConnection.GetValidUser(
    "tbl_user",
    tbEmail.Text, 
    tbPassword.Text
);

//THROWS null reference exception in method 'private bool OpenConnection()'
//A first chance exception of type 'System.ArgumentException' occurred in System.Data.dll

This is my DBConnect class:

class DBConnect
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    public DBConnect()
    {
        server = "x.x.x.x";
        database = "...";
        uid = "...";
        password = "...";

        string connectionString = "SERVER=" + server + ";" +
                                  "DATABASE=" + database + ";" +
                                  "UID=" + uid + ";" +
                                  "PASSWORD=" + password + ";";

        connection = new MySqlConnection(connectionString);
    }

    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            switch (ex.Number)
            {
                case 0:
                    MessageBox.Show("Cannot connect to MySQL server.");
                    break;

                case 1045:
                    MessageBox.Show("Invalid username or password.");
                    break;
            }
            return false;
        }
    }

    public string[] GetValidUser(string dbTable, string dbUsername, string dbPassword)
    {
        string query = "SELECT id,email,password FROM " + dbTable +
                       " WHERE email='" + dbUsername +
                       "' AND password='" + dbPassword + "'";

        string[] resultArray = new string[3];

        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            MySqlDataReader dataReader = cmd.ExecuteReader();

            while (dataReader.Read())
            {
                resultArray[0] = dataReader.GetInt32(0).ToString();
                resultArray[1] = dataReader.GetString(1);
                resultArray[2] = dataReader.GetString(2);
            }

            dataReader.Close();
            this.CloseConnection();
        }
        return resultArray;
    }
}

The original code for the database class can be found here.

16
  • 1
    Exactly which line throws this exception? Also: using statements! (try some) Commented Mar 25, 2015 at 9:33
  • 3
    Obligatory feedback, but: "you need to parameterize that SQL" and "stop storing passwords as plain text" (note: dbTable can't be parameterized, but should be white-listed or hard-coded instead) Commented Mar 25, 2015 at 9:35
  • @MarcGravell: overlooked it. However, the code is open for sql injection with using string concatenation to build the sql query. Instead use sql-parameters. Such classes often have issues like this or the difficulty to use the using-statement to ensure that everything gets disposed and closed properly. Commented Mar 25, 2015 at 9:38
  • 3
    @Tim indeed; on this one occasion, this is even greater than that, since I could craft an attack that returned someone else's password to me, which is just - mind bogglingly bad, as in "we need to tell our users that they need to change all their passwords everywhere, because we've probably leaked them already, and have no clue/evidence that we have" - heck, I could probably steal all their passwords and email addresses with Havij based on that code Commented Mar 25, 2015 at 9:40
  • 3
    However - and I cannot emphasize this enough - the code shown is jaw droppingly insecure; we aren't griefing you for fun and giggles - we're trying to tell you that this code, no matter where used, is currently an active risk to both your organisation and your users. If you have any kind of "hey boss, I think we might have a problem here" process / channel: invoke it now - you really really have a major problem here. This "major problem" has nothing to do with the null-reference-exception, and will not go away by fixing the NRE. Commented Mar 25, 2015 at 10:00

2 Answers 2

9

This is not an answer to the NullReferenceException - we're still working through that in the comments; this is feedback for the security parts.

The first thing we can look at is SQL injection; this is very easy to fix - see below (note I've tidied some other things too)

// note: return could be "bool" or some kind of strongly-typed User object
// but I'm not going to change that here
public string[] GetValidUser(string dbUsername, string dbPassword)
{
    // no need for the table to be a parameter; the other two should
    // be treated as SQL parameters
    string query = @"
SELECT id,email,password FROM tbl_user
WHERE email=@email AND password=@password";

    string[] resultArray = new string[3];

    // note: it isn't clear what you expect to happen if the connection
    // doesn't open...
    if (this.OpenConnection())
    {
        try // try+finally ensures that we always close what we open
        {
            using(MySqlCommand cmd = new MySqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("email", dbUserName); 
                // I'll talk about this one later...
                cmd.Parameters.AddWithValue("password", dbPassword); 

                using(MySqlDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read()) // no need for "while"
                                           // since only 1 row expected
                    {
                        // it would be nice to replace this with some kind of User
                        //  object with named properties to return, but...
                        resultArray[0] = dataReader.GetInt32(0).ToString();
                        resultArray[1] = dataReader.GetString(1);
                        resultArray[2] = dataReader.GetString(2);

                        if(dataReader.Read())
                        { // that smells of trouble!
                            throw new InvalidOperationException(
                                "Unexpected duplicate user record!");
                        }
                    }
                }
            }
        }
        finally
        {
            this.CloseConnection();
        }
    }
    return resultArray;
}

Now, you might be thinking "that's too much code" - sure; and tools exist to help with that! For example, suppose we did:

public class User {
    public int Id {get;set;}
    public string Email {get;set;}
    public string Password {get;set;} // I'll talk about this later
}

We can then use dapper and LINQ to do all the heavy lifting for us:

public User GetValidUser(string email, string password) {
    return connection.Query<User>(@"
SELECT id,email,password FROM tbl_user
WHERE email=@email AND password=@password",
      new {email, password} // the parameters - names are implicit
    ).SingleOrDefault();
}

This does everything you have (including safely opening and closing the connection), but it does it cleanly and safely. If it method returns a null value for the User, it means no match was found. If a non-null User instance is returned - it should contain all the expected values just using name-based conventions (meaning: the property names and column names match).

You might notice that the only code that remains is actually useful code - it isn't boring plumbing. Tools like dapper are your friend; use them.


Finally; passwords. You should never store passwords. Ever. Not even once. Not even encrypted. Never. You should only store hashes of passwords. This means that you can never retrieve them. Instead, you should hash what the user supplies and compare it to the pre-existing hashed value; if the hashes match: that's a pass. This is a complicated area and will require significant changes, but you should do this. This is important. What you have at the moment is insecure.

Sign up to request clarification or add additional context in comments.

Comments

2

Among other things, it sounds like you have problems with the connection string - from comments:

While "connection = new MySqlConnection(); connection.ConnectionString = connectionString;" throws an exception the statement "connection = new MySqlConnection();" does not...

The difference here is simply: in the latter you aren't setting the connection string - so it sounds like your connection string is not correctly escaping the values (most likely, the password); you could try:

var cs = new DbConnectionStringBuilder();
cs["SERVER"] = server;
cs["DATABASE"] = database;
cs["UID"] = uid;
cs["PASSWORD"] = password;
var connectionString = cs.ConnectionString;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.