system.data.dll附加信息中出现类型为'system.data.oledb.oledbexception'的未处理异常：INSERT into语句中出现语法错误。 [英] An unhandled exception of type 'system.data.oledb.oledbexception' occurred in system.data.dll additional information: syntax error in INSERT into statement.

问题描述

这就是我的问题所在，数据库类：

this is where my problem take place,the Data Base class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;

namespace LibraryProject
{
    class DataBase
    {
        public static void Execute(String SQL)
        {
            OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LibraryProject.mdb");
            cn.Open();
            OleDbCommand cm = new OleDbCommand();
            cm.ExecuteNonQuery();
            cn.Close();
        }

        public static DataTable ExecuteSelect(String SQL)
        {
            OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LibraryProject.mdb");
            cn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter(SQL,cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
                cn.Close();
            return dt;
        }
    }
}

问题的位置在此行的前面==> cn.close（）;



System.Data.dll中出现未处理的System.Data.OleDb.OleDbException类型异常

附加信息：语法错误INSERT INTO语句。





这是我写的插入函数，类userpass：

the problem's place is in front of this line==>cn.close();

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: Syntax error in INSERT INTO statement.


this is where I wrote Insert function,class userpass:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace LibraryProject
{
    class userpass
    {
        public String user { get; set; }
        public String pass { get; set; }




        public userpass(String user , String pass)
        {
            this.user = user;
            this.pass = pass;
        }
        public userpass()
        {
            user = "-";
            pass = "-";
        }
        

      public void Insert()
        {
            String SQL;
            SQL = "INSERT INTO userpass(user,pass) VALUES ('" + this.user + "','" + this.pass + "')";
            DataBase.ExecuteSelect(SQL);
        }

    

        public Boolean CheckUserPass(String u, String p)
        {
            String SQL;
            SQL = "SELECT * FROM userpass WHERE user='"+u+"' AND pass='"+p+"' ";
            DataTable dt = new DataTable();
            dt = DataBase.ExecuteSelect(SQL);
            if (dt.Rows.Count == 0)
                return false;
            else
                return true; 
        }

    }
}

以及最后我如何回忆我在表格中的SAVE按钮：

and finally how I recall Insert in SAVE button in my form:

userpass ms = new userpass();
            ms.user = txtuser.text;
            ms.pass = txtpass.Text;
            ms.Insert();

我尝试过：



很抱歉，但正如我之前所说，我已经尝试过任何解决问题的方法，因为我们的导师告诉我们我们的方式是正确的，应该正常工作所以我不知道该怎么办!! ！

What I have tried:

Sorry but as I said before I have tried any way around the problem cause our mentor told us our way is TRUE and SHOULD work properly so I don't know what to do now!!!

推荐答案

我不在乎你的导师说什么：那很危险。

永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

I don't care what your mentor says: that's dangerous.
Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        {
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
        cmd.ExecuteNonQuery();
        }
    }

修复整个应用程序，你的问题可能会消失。



此外，您不应该以明文形式存储密码 - 这是一个主要的安全风险。有关如何在此处执行此操作的信息：密码存储：如何做到这一点。 [ ^ ]

Fix that throughout your app, and your problem may go away.

In addition, you should never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

请仔细阅读解决方案1.



现在修复你的危险代码。我看到两件事：



1.）public void Insert（）

Please read Solution 1 carefully.

Now to fix your dangerous code. I see two things:

1.) public void Insert()

public void Insert()
{
String SQL;
SQL = "INSERT INTO userpass(user,pass) VALUES ('" + this.user + "','" + this.pass + "')";

// **** The following should be DataBase.Execute(SQL) **** 
DataBase.ExecuteSelect(SQL);
}

2.）public static void Execute（String SQL）

这还没有完成。你既不给OleDbCommand提供SQL也不提供连接。



最后要注意资源。这意味着对于实现IDisposable的类，使用获得的帮助是有意义的。有关详细信息，请参阅这个： c＃ - 应该使用Using语句？ - 堆栈溢出 [ ^ ]

引用：

很抱歉，但正如我之前所说，我尝试过任何方式问题导致我们的导师告诉我们我们的方式是正确的并且应该正常工作

Sorry but as I said before I have tried any way around the problem cause our mentor told us our way is TRUE and SHOULD work properly

教导你的导师关于'SQL注射'。



没有必要解决你的问题，但你有另一个问题。

永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

我该怎么办？解释没有技术术语的SQL注入？ - 信息安全堆栈交换 [ ^ ]

Teach your mentor about 'SQL Injection'.

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

这篇关于system.data.dll附加信息中出现类型为'system.data.oledb.oledbexception'的未处理异常：INSERT into语句中出现语法错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！