c# - 使用 C# 和 asp.net 将 sql 插入 access db 时出现语法错误

Question

我在线上遇到错误

cmd.ExecuteNonQuery();

说

Syntax error in INSERT INTO statement

我认为我的查询应该是有效的，我不明白这个问题，我已经研究了几个小时。它的顺序正确，将 [] 放在 Users 周围对我不起作用，希望得到帮助。

<%@ Page Language ="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>   
    <script runat="server">
    protected void Page_Load()
    {

            String username = Request.Form["username"];
            String email = Request.Form["email"];
            String password = Request.Form["password1"];
            var age = Request.Form["age"];
            String country = Request.Form["country"];
            String hobbie = Request.Form["skin"];
            String sql;

            sql = "INSERT INTO Users(UserName, Email, Password, Age, Country, Hobbie) VALUES('" + username + "','" + email + "','" + password + "'," + age + ",'" + country + "','" + skin + "')";

            String Path = Server.MapPath("App_Data/Users.accdb");
            String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Path;

            OleDbConnection conn = new OleDbConnection(connStr);
            conn.Open();

            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();

            conn.Close();
    }

Answer 1

PASSWORD 是 MS-Access 的保留关键字。使用方括号括起来

    sql = "INSERT INTO Users(UserName, Email, [Password] ....

但是请更改该 sql 文本以使用参数化查询。此外，如果您能够使用实际值发送此命令，您的查询仍然容易受到 Sql 注入(inject)漏洞的影响，并且当您的输入字符串包含单引号时可能会出现解析问题

   sql = @"INSERT INTO Users(UserName, Email, [Password], Age, Country, Hobbie) 
           VALUES(?,?,?,?,?,?)";
   ....

   OleDbCommand cmd = new OleDbCommand(sql, conn);
   cmd.Parameters.AddWithValue("@p1", username);
   .... and so on for the other 5 parameters required by the query ....