Xamarin MysqlConnector weird NullReferenceException error

Question

I'm wanting to make a small and simple mobile app for a school project, I know connecting to a db from a phone is not good for security reasons but basically only I will touch it.

So to connect my Xamarin app to Mysql I downloaded the extension MysqlConnector (https://www.nuget.org/packages/MySqlConnector/2.1.8?_src=template)

Everything seemed to work at first, but now I think that there is a problem in their library that is not compatible with Xamarin:

I seem to always get a nullreference exception at the second query at line reader = cmd.ExecuteReader();. I don't know why, nothing is null, I've printed everything. (I've put a comment on the line where it happens) I seriously doubt it is a problem in their library since they have 37.2M downloads in total. But maybe it is just a compatability conflict, but that makes it odd that the first query works then.

Here is all my current code:

using PuppyChinoBestelling.Views;
using System;
using System.Collections.Generic;
using System.Text;
using Xamarin.Forms;
using MySqlConnector;
using System.Threading.Tasks;

namespace PuppyChinoBestelling.ViewModels
{
    public class LoginViewModel : BaseViewModel
    {
        public Command LoginCommand { get; }
        public string Mail { get; set; }
        public string Pass { get; set; }

        public LoginViewModel()
        {
            Pass = string.Empty;
            Mail = string.Empty;
            LoginCommand = new Command(OnLoginClicked);
        }

        
        private async void OnLoginClicked(object obj)
        {
            
            MySqlConnection conn = new MySqlConnection("private");

            try
            {
                conn.Open();
                Console.WriteLine("Conn opened!");
            }
            catch(Exception ex)
            {
                Console.WriteLine("Error " + ex.Message);
            }


            string sql = @"SELECT * FROM users WHERE email = @email;";
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@email", Mail);

            var reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                sql = @"SELECT * FROM users WHERE email = @email;";
                cmd = conn.CreateCommand();
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                cmd.Parameters.AddWithValue("@email", Mail);

                reader = cmd.ExecuteReader(); //null reference happening here idk why

                string pwdHashed = reader.GetString(5);

                bool validPwd = BCrypt.Net.BCrypt.Verify(Pass, pwdHashed);

                conn.Close();

                if (validPwd)
                {
                    await Shell.Current.GoToAsync($"//{nameof(AboutPage)}");
                }
                else
                {
                    Console.WriteLine("Foute logingegevens!");
                }
            }
            else
            {
                Console.WriteLine("Je bestaat niet!");
            }


        }



        
    }
}

Thanks in advance!

Charlieface · Accepted Answer · 2022-05-22 10:30:50Z

1

It's hard to say for certain, but it's likely the issue is because you are not closing the reader and command, and you can't have multiple commands on the same connection.

Also, you need to advance the reader using reader.Read.

In any case there is no need to run the command twice in the first place. You already had all the information on the first run.

You also need to dispose everything with using. This automatically closes the connection.
Don't SELECT *, just select the columns you need.
Ideally, you would calculate the hash for the given password, and send it to the database server to check, rather than pulling out the real password hash from the database (could be a security risk).
Don't store hashes as strings. Instead store them as binary with the varbinary data type, and cast to byte[] on the C# side.

Unclear why you are handling errors only for opening the connection, not for executing the command.

      private async void OnLoginClicked(object obj)
      {

          const string sql = @"
      SELECT Pass
      FROM users
      WHERE email = @email;
      ";
          using (var conn = new MySqlConnection("private"))
          using (var cmd = new MySqlCommand(sql, conn))
          {
              try
              {
                  conn.Open();
                  Console.WriteLine("Conn opened!");
              }
              catch(Exception ex)
              {
                  Console.WriteLine("Error " + ex.Message);
                  return; // no point continuing
              }
              cmd.Parameters.AddWithValue("@email", Mail);

              using (var reader = cmd.ExecuteReader())
              {
                  if (!reader.Read())
                  {
                      Console.WriteLine("Je bestaat niet!");
                      return; // no point continuing
                  }

                  string pwdHashed = (string)reader["Pass"];
                  conn.Close();
                  bool validPwd = BCrypt.Net.BCrypt.Verify(Pass, pwdHashed);

                  if (validPwd)
                  {
                      await Shell.Current.GoToAsync($"//{nameof(AboutPage)}");
                  }
                  else
                  {
                      Console.WriteLine("Foute logingegevens!");
                  }
              }
          }
      }

An alternative method is to remove the reader altogether and use ExecuteScalar

     private async void OnLoginClicked(object obj)
     {
         
         const string sql = @"
     SELECT Pass
     FROM users
     WHERE email = @email;
     ";
         using (var conn = new MySqlConnection("private"))
         using (var cmd = new MySqlCommand(sql, conn))
         {
             try
             {
                 conn.Open();
                 Console.WriteLine("Conn opened!");
             }
             catch(Exception ex)
             {
                 Console.WriteLine("Error " + ex.Message);
                 return; // no point continuing
             }
             cmd.Parameters.AddWithValue("@email", Mail);

             string pwdHashed = cmd.ExecuteScalar() as string;
             conn.Close();
             if (pwdHashed is null)
             {
                 Console.WriteLine("Je bestaat niet!");
                 return; // no point continuing
             }

             bool validPwd = BCrypt.Net.BCrypt.Verify(Pass, pwdHashed);

             if (validPwd)
             {
                 await Shell.Current.GoToAsync($"//{nameof(AboutPage)}");
             }
             else
             {
                 Console.WriteLine("Foute logingegevens!");
             }
         }
     }

edited May 22, 2022 at 10:30

answered May 22, 2022 at 9:57

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

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

14 Comments

MousTea Over a year ago

Hey thanks for the quick response, I've tried your code (ofcourse changed mysql row name and connectionstring put back in) I seem to get a NullReferenceException, but now it is happening on another line: It never shows me which line it is because when it throws the error my Visual Studio always brings up "Beak mode" tab with message: "No compatible code running on the thread". And on top of this it displays the error, see screenshot: imgur.com/a/NXn5QXP I have to manually find the error with breakpoints, breakpoints telling me the error is in the if with (!reader.HasRows).

Charlieface Over a year ago

Use reader.Read() instead of HasRows, see edit. Also you could instead use ExecuteScalar

MousTea Over a year ago

Yes it works thankyou so much man, I had been looking so long on this error :) What is the difference between ExecuteScalar and reader?

MousTea Over a year ago

Why doesn't it work with reader.read but it works with executescalar?

Charlieface Over a year ago

Not sure, it should work, although I have seen mention that when there is an error then it returns null.

|

Collectives™ on Stack Overflow

Xamarin MysqlConnector weird NullReferenceException error

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related