使用ASP.NET连接MySQL遇SQL语法错误，用户注册失败

"message": "Error creating user: MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-manager.users (username, password) VALUES ('test', 'test')' at line 1\r\n"

后端C#代码 #

using MySql.Data.MySqlClient;
using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.DependencyInjection;
using System.Data.SqlTypes;
using dotenv.net;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowLocalHost3000", builder =>
    {
        builder.WithOrigins("http://localhost:3000")
               .AllowAnyHeader()
               .AllowAnyMethod()
               .AllowCredentials();
    });
});

DotEnv.Load();
var dbServer = Environment.GetEnvironmentVariable("DB_SERVER");
var dbPort = Environment.GetEnvironmentVariable("DB_PORT");
var dbDatabase = Environment.GetEnvironmentVariable("DB_DATABASE");
var dbUser = Environment.GetEnvironmentVariable("DB_USER");
var dbPassword = Environment.GetEnvironmentVariable("DB_PASSWORD");

var connectionString = $"server={dbServer};port={dbPort};database={dbDatabase};user={dbUser};password={dbPassword};";

builder.Services.AddTransient<MySqlConnection>(_ => new MySqlConnection(connectionString));

var app = builder.Build();

app.UseCors("AllowLocalHost3000");
app.UseRouting();

app.MapGet("/", () => "Hello World!");

app.MapPost("/register", async (User user, MySqlConnection connection) => {
try {
    var q = "INSERT INTO employee_manager.users (`username`, `password`) VALUES (?, ?)";

    await connection.OpenAsync();
    using var cmd = new MySqlCommand(q, connection);
    cmd.Parameters.AddWithValue("@Username", user.Username);
    cmd.Parameters.AddWithValue("@Password", user.Password);
    await cmd.ExecuteNonQueryAsync();

   return Results.Json(new { StatusCode = 201, Message = "User created successfully" });


} catch (Exception ex) {
    return Results.Json(new { StatusCode = 500, Message = $"Error creating user: {ex}" });
}
finally {
    connection.Close();
}
});

app.Run();

前端React代码 #

async function registerHandler(e) {
    e.preventDefault();

    try {
      const response = await fetch(`${process.env.REACT_APP_SERVER_URL}/register`, {
        method: "POST",
        headers: { "Content-type": "application/json" },
        credentials: "include",
        body: JSON.stringify({ username, password }),
      });

      if (response.status === 201) {
        alert("Registration successful! You can now login");
        navigate("/login");
      }
    } catch (err) {
      console.log(err);
    }
  }

用户疑问：我已反复检查.env配置正确，且该查询语句在MySQL Workbench中可正常执行，猜测是否是数据库连接存在问题？

解决方案 #

核心问题：标识符特殊字符未转义 #

错误提示里的表名显示为-manager.users，说明你的数据库名是employee-manager（包含连字符）。SQL中含特殊字符（如连字符、空格）的标识符必须用反引号包裹，Workbench能执行是因为它自动帮你做了转义，但代码里没处理。另外，你的连接字符串已经指定了database={dbDatabase}，SQL语句里其实不需要重复写库名，这是更简单的解决方式：

方式1：简化SQL语句（推荐）

修改注册接口里的SQL语句，直接引用表名即可：

var q = "INSERT INTO `users` (`username`, `password`) VALUES (@Username, @Password)";

方式2：正确转义带特殊字符的库名

如果一定要保留库名，需把整个employee-manager.users用反引号包裹：

var q = "INSERT INTO `employee-manager`.`users` (`username`, `password`) VALUES (@Username, @Password)";

额外优化：参数占位符统一 #

你当前SQL用?占位，但参数用@Username命名占位符，虽然MySqlClient兼容，但统一写法更清晰：SQL语句用命名占位符@Username、@Password，参数添加保持不变。

可选优化：连接自动管理 #

去掉finally块里的connection.Close()，改用using语句自动管理连接生命周期，避免手动关闭的疏漏：

try {
    var q = "INSERT INTO `users` (`username`, `password`) VALUES (@Username, @Password)";

    using (await connection.OpenAsync())
    {
        using var cmd = new MySqlCommand(q, connection);
        cmd.Parameters.AddWithValue("@Username", user.Username);
        cmd.Parameters.AddWithValue("@Password", user.Password);
        await cmd.ExecuteNonQueryAsync();
    }

    return Results.Json(new { StatusCode = 201, Message = "User created successfully" });
} catch (Exception ex) {
    return Results.Json(new { StatusCode = 500, Message = $"Error creating user: {ex}" });
}

内容的提问来源于stack exchange，提问作者Daniel