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!

CodePudding user response：

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!");
             }
         }
     }