使用此INSERT INTO语句和.NET OleDb命名空间时，如何解决语法错误? [英] How to solve a syntax error when using this INSERT INTO statement and the .NET OleDb namespace?

问题描述

当我尝试将值插入Access数据库时，我总是收到错误消息.

I keep getting an error when I attempt to insert values into a Access database.

该错误是语法错误，它导致以下异常:

The error is syntactic, which leads to the following exception:

OleDbException是INSERT INTO语句中未处理的语法错误.

OleDbException was unhandled Syntax error in INSERT INTO statement.

private OleDbConnection myCon;

public Form1()
{
    InitializeComponent();
    myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\File.mdb");
}

private void insertuser_Click(object sender, EventArgs e)
{
    OleDbCommand cmd = new OleDbCommand();
    myCon.Open();
    cmd.Connection = myCon;
    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "INSERT INTO User ([UserID], [Forename], [Surname], " +
                                        "[DateOfBirth], [TargetWeight], [TargetCalories], [Height]) " +
                      "VALUES ('" + userid.Text.ToString() + "' , '" +
                                    fname.Text.ToString() + "' , '" +
                                    sname.Text.ToString() + "' , '" +
                                    dob.Text.ToString() + "' , '" +
                                    tarweight.Text.ToString() + "' , '" +
                                    tarcal.Text.ToString() + "' , '" +
                                    height.Text.ToString() + "')";

    cmd.ExecuteNonQuery();
    myCon.Close();
}

推荐答案

好吧，您尚未指定错误是什么-但是您的第一个问题是直接将数据插入SQL语句中. 请勿这样做.您正在邀请 SQL注入攻击.

Well, you haven't specified what the error is - but your first problem is that you're inserting the data directly into the SQL statement. Don't do that. You're inviting SQL injection attacks.

使用参数化的SQL语句代替.完成此操作后，如果仍然有问题，请使用新代码编辑该问题，然后说明错误是什么.新代码可能已经很清晰了，因为不会涉及很大的串联，很容易隐藏不匹配的括号之类的东西.

Use a parameterized SQL statement instead. Once you've done that, if you still have problems, edit this question with the new code and say what the error is. The new code is likely to be clearer already, as there won't be a huge concatenation involved, easily hiding something like a mismatched bracket.

如评论中所述，Jet/ACE不允许使用DML，因此容易受到较少类型的SQL注入攻击.对于此INSERT语句，实际上可能没有 no 漏洞-但对于以类似方式编写的带有WHERE子句的SELECT，用户输入可能会绕开WHERE子句的某些保护.我强烈建议您理所当然地使用参数化查询:

As mentioned in comments, Jet/ACE is vulnerable to fewer types of SQL injection attack, as it doesn't permit DML. For this INSERT statement there may actually be no vulnerability - but for a SELECT with a WHERE clause written in a similar way, user input could circumvent some of the protections of the WHERE clause. I would strongly advise you to use parameterized queries as a matter of course:

• 它们意味着您不必转义用户数据
• 他们将数据与代码分开
• 如果您从Jet/ACE迁移(无论是移动此特定代码，还是只是您个人开始在不同的数据库上工作)，您将无需担心.
• 对于其他数据类型(例如日期)，您无需做任何工作即可将数据转换为适合数据库的格式

(您也不需要所有对ToString的调用.不仅我希望一个名为Text的属性已经是 一个字符串，而且还希望您正在使用字符串串联意味着无论如何字符串转换都会自动发生.)

(You also don't need all the calls to ToString. Not only would I expect that a property called Text is already a string, but the fact that you're using string concatenation means that string conversions will happen automatically anyway.)

这篇关于使用此INSERT INTO语句和.NET OleDb命名空间时，如何解决语法错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！