c# - 用户 '' @localhost 的访问被拒绝(使用密码 : no)

Question

大家好阅读本文。我目前正在将我的 MariaDB 数据库移植到我的 Raspberry Pi，它可以正常工作。但是，当我将 C# ASP.Net Core 后端也移植到 Pi 时，我收到以下错误消息:

 Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
      Route matched with {action = "Validate", controller = "User"}. Executing controller action with signature System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.JsonResult] Validate(feedback_app_backend.Models.User) on controller feedback_app_backend.Controllers.UserController (feedback_app_backend).
Access denied for user ''@'localhost' (using password: NO)
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action feedback_app_backend.Controllers.UserController.Validate (feedback_app_backend) in 783.1495ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'feedback_app_backend.Controllers.UserController.Validate (feedback_app_backend)'
fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HM0IVJDTQRVM", Request id "0HM0IVJDTQRVM:00000001": An unhandled exception was thrown by the application.
MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user ''@'localhost' (using password: NO)
   at feedback_app_backend.Controllers.UserController.Validate(User user) in C:\Users\NoChance\Desktop\feedback_app_backend\Controllers\UserController.cs:line 88
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 1351.7589ms 500

似乎没有从我的连接字符串中正确读取用户。我没有这个问题，当我在我的电脑上托管后端时，只有当我尝试访问 pi 上的后端时才会发生这种情况。有什么想法吗？

这是我的连接字符串，它位于 appsettings.json 中

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=feedbackapp;User Id=root;Password=mypass"
  },
  "AllowedHosts": "*"
}

我尝试了不同的连接字符串，但似乎都不起作用。

编辑:我将我的连接字符串更新为:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3306;Database=feedbackapp;Uid=root;Pwd=mypass;"
  },
  "AllowedHosts": "*"
}

我仍然遇到同样的错误:/

这是我的连接是如何建立的:

应用设置.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"
  },
  "AllowedHosts": "*"
}

用户查询

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;
using feedback_app_backend.Disposables;
using feedback_app_backend.Models;
using MySql.Data.MySqlClient;

namespace feedback_app_backend.Querys
{
    public class UserQuery
    {
        public UserDB Db { get; }

        public UserQuery(UserDB db)
        {
            Db = db;
        }

        public async Task<User> AddUserAsync(User user, CustomQuestionnaireDB questionnaireDB, FeedbackQuestionDB questionDB)
        {
            try
            {
                await questionnaireDB.Connection.OpenAsync();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(questionnaireDB);
            User userExists = await GetUserAsync(user, questionnaireDB, questionDB).ConfigureAwait(true);

            if (userExists == null)
            {
                //Get Questionnaire ID
                CustomQuestionnaire questionnaire = new CustomQuestionnaire();
                questionnaire.QuestionnareID = await query.GetNewQuestionnaireIDAsync();

                User newUser = new User();
                //Remove all null values
                newUser = user;
                newUser.QuestionnaireID = questionnaire.QuestionnareID;
                newUser.Given = new List<CustomQuestionnaire>();
                newUser.Received = new List<CustomQuestionnaire>();
                newUser.UUID = Guid.NewGuid().ToString();

                //Prepare the database
                using var cmd = Db.Connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO user (uuid, first_name, last_name, email, pass, title, questionnaire_id, created_on) 
                VALUES (@uuid, @first_name, @last_name, @email, @password, @title, @questionnaire_id, @created_on);";
                BindParams(cmd, newUser);
                await cmd.ExecuteNonQueryAsync();

                questionnaire.UUID = newUser.UUID;

                FeedbackQuestionQuery questionQuery = new FeedbackQuestionQuery(questionDB);
                int index = await query.CreateQuestionnaire(questionnaire, questionQuery);

                if (index != -1)
                {
                    newUser.CustomQuestionnaire = questionnaire;
                    await UpdateAsync(newUser);
                    return newUser;
                }
                else
                {
                    return null;
                }
            }
            else
            {
                return userExists;
            }

        }

        public async Task<User> GetUserAsync(User user, CustomQuestionnaireDB queryDB, FeedbackQuestionDB questionDB)
        {

            try
            {
                await queryDB.Connection.OpenAsync();
            }
            catch (Exception)
            {
                Console.WriteLine("Connection is already open (UserQuery 80)");
            }
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(queryDB);

            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid,first_name,last_name,email,title,questionnaire_id FROM user WHERE `email` = @email AND `pass` = @pass";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = user.EMail,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@pass",
                DbType = DbType.String,
                Value = user.Password,
            });
            List<User> users = await ReadAllAsync(await cmd.ExecuteReaderAsync());

            //Check if any users where found
            if (users.Count > 0)
            {
                User _user = users[0];
                CustomQuestionnaire questionnaire = await query.GetCustomQuestionnaireByUUIDAsync(_user);
                //Check if user already has a questionnaire
                if (questionnaire != null)
                {
                    //Load in all Questions
                    questionnaire.Questions = await query.GetAllQuestionnaireQuestions(questionnaire, questionDB);
                    _user.CustomQuestionnaire = questionnaire;

                    //Set received feedback
                    List<CustomQuestionnaire> received = await query.GetReceivedQuestionnairesAsync(_user, questionDB);
                    if (received != null)
                    {
                        _user.Received = received;
                    }
                }
                return _user;
            }
            else
            {
                return null;
            }


        }

        public async Task<User> GetUserByUUIDAsync(String uuid, CustomQuestionnaireDB queryDB, FeedbackQuestionDB questionDB)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid,first_name,last_name,email,title,questionnaire_id FROM user WHERE `uuid` = @uuid;";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@uuid",
                DbType = DbType.String,
                Value = uuid,
            });

            List<User> users = await ReadAllAsync(await cmd.ExecuteReaderAsync());
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(queryDB);
            if (users.Count > 0)
            {
                User _user = users[0];
                CustomQuestionnaire questionnaire = await query.GetCustomQuestionnaireByUUIDAsync(_user);
                if (questionnaire != null)
                {
                    questionnaire.Questions = await query.GetAllQuestionnaireQuestions(questionnaire, questionDB);
                    _user.CustomQuestionnaire = questionnaire;
                }
                return _user;
            }
            else
            {
                return null;
            }
        }


        public async Task<User> GetUserByEMailAsync(String email, CustomQuestionnaireDB queryDB, FeedbackQuestionDB questionDB)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid,first_name,last_name,email,title,questionnaire_id FROM user WHERE `email` = @email;";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = email,
            });

            List<User> users = await ReadAllAsync(await cmd.ExecuteReaderAsync());
            CustomQuestionnaireQuery query = new CustomQuestionnaireQuery(queryDB);
            if (users.Count > 0)
            {
                User _user = users[0];
                CustomQuestionnaire questionnaire = await query.GetCustomQuestionnaireByUUIDAsync(_user);
                if (questionnaire != null)
                {
                    questionnaire.Questions = await query.GetAllQuestionnaireQuestions(questionnaire, questionDB);
                    _user.CustomQuestionnaire = questionnaire;
                }
                return _user;
            }
            else
            {
                return null;
            }
        }

        public async Task<List<User>> GetAllUsersAsync(User user)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"SELECT uuid, first_name, last_name, email, title, questionnaire_id FROM user WHERE `email` <> @email";
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = user.EMail,
            });
            List<User> result = await ReadAllAsync(await cmd.ExecuteReaderAsync());
            return result.Count > 0 ? result : null;
        }

        private async Task<List<User>> ReadAllAsync(DbDataReader reader)
        {
            List<User> users = new List<User>();
            using (reader)
            {
                while (await reader.ReadAsync())
                {
                    var user = new User()
                    {
                        UUID = reader.GetString(0),
                        FirstName = reader.GetString(1),
                        LastName = reader.GetString(2),
                        EMail = reader.GetString(3),
                        Title = reader.GetString(4),
                        QuestionnaireID = reader.GetInt32(5)
                    };
                    users.Add(user);
                }
            }
            return users;
        }

        public async Task UpdateAsync(User user)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"UPDATE user SET `first_name` = @first_name, `last_name` = @last_name, `email` = @email, `title` = @title, "
            + "`questionnaire_id` = @questionnaire_id, `created_on` = @created_on WHERE `uuid` = @uuid;";
            BindParams(cmd, user);
            await cmd.ExecuteNonQueryAsync();
        }

        public async Task DeleteUserAsync(User user)
        {
            using var cmd = Db.Connection.CreateCommand();
            cmd.CommandText = @"DELETE FROM user WHERE `uuid` = @uuid;";
            BindParams(cmd, user);
            await cmd.ExecuteNonQueryAsync();
        }

        private void BindParams(MySqlCommand cmd, User user)
        {
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@uuid",
                DbType = DbType.String,
                Value = user.UUID,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@first_name",
                DbType = DbType.String,
                Value = user.FirstName,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@last_name",
                DbType = DbType.String,
                Value = user.LastName,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@email",
                DbType = DbType.String,
                Value = user.EMail,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@password",
                DbType = DbType.String,
                Value = user.Password,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@title",
                DbType = DbType.String,
                Value = user.Title,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@questionnaire_id",
                DbType = DbType.Int32,
                Value = user.QuestionnaireID,
            });
            cmd.Parameters.Add(new MySqlParameter
            {
                ParameterName = "@created_on",
                DbType = DbType.Int64,
                Value = user.CreatedOn,
            });
        }


    }
}

用户数据库

using System;
using MySql.Data.MySqlClient;

namespace feedback_app_backend.Disposables
{
    public class UserDB : IDisposable
    {
        public MySqlConnection Connection { get; }
        public UserDB(string connectionString)
        {
            Connection = new MySqlConnection(connectionString);
        }

        public void Dispose() => Connection.Dispose();
    }
}

Startup.cs -> 配置服务

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<IConfiguration>(Configuration);
    services.AddControllers();
    services.AddTransient<UserDB>(_ => new UserDB(Configuration["ConnectionStrings:DefaultConnection"]));
    services.AddTransient<CustomQuestionnaireDB>(_ => new CustomQuestionnaireDB(Configuration["ConnectionStrings:DefaultConnection"]));
    services.AddTransient<FeedbackQuestionDB>(_ => new FeedbackQuestionDB(Configuration["ConnectionStrings:DefaultConnection"]));
}

编辑 3:我针对更多上下文更新了错误

编辑 4:

它在本地工作，当它从 appsettings 读取我的连接字符串时，但我可以通过直接将连接字符串作为字符串(而不是从 appsettings.json 读取)来重现错误。当我这样做时，我得到了与我的覆盆子相同的错误

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<IConfiguration>(Configuration);
    services.AddControllers();
    services.AddTransient<UserDB>(_ => new UserDB(Configuration["Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"]));
    services.AddTransient<CustomQuestionnaireDB>(_ => new CustomQuestionnaireDB(Configuration["Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"]));
    services.AddTransient<FeedbackQuestionDB>(_ => new FeedbackQuestionDB(Configuration["Server=localhost;Port=3307;Database=feedbackapp;Uid=root;Pwd=mypass;"]));
}

Answer 1

我终于修好了。我从 appsettings.json 部分删除了读数并调整了我的数据库以像这样工作。

using System;
using MySql.Data.MySqlClient;

namespace feedback_app_backend.Disposables
{
    public class CustomQuestionnaireDB : IDisposable
    {
        MySqlConnection conn = new MySqlConnection("server=localhost;user id=user;password=mypass;database=mydb");
        public MySqlConnection Connection { get; }
        public CustomQuestionnaireDB()
        {
            Connection = new MySqlConnection(conn.ConnectionString);
        }

        public void Dispose() => Connection.Dispose();
    }
}