从ASP.NET C插入SQL数据库的问题＃ [英] Issues Inserting into SQL Database from ASP.NET C#

问题描述

我的列数比我在这里提供的要多得多，但我只想尝试发布主键列TrackingID（nchar [10]）作为测试 - 如果我可以使用它，我可以让其他人工作。我有一个Windows窗体应用程序，可以进行类似的查询并运行得很完美，所以看看它是如何同时C＃我很难理解出了什么问题。是连接字符串还是sql查询？表名称为Full。总共有23列，但唯一的主键是跟踪ID，它不能为空。在所有情况下，这些查询都是OnClick按钮事件。你可以看到我尝试了几条路线。我希望将其设置为参数化以防止SQL注入。您将看到的第一部分代码来自C＃中的Windows窗体应用程序（虽然易受攻击）可以始终如一地工作。下面，您将看到asp.net代码。

I have a lot more columns than what I have here for you, but I'm just trying to get to post the primary key column TrackingID (nchar[10]) as a test - if I can get that to work, I can get the rest of them to work. I have a windows forms application that makes a similar query and runs perfectly, so seeing as how it's both C# I'm having a hard time understanding what's going wrong. Is it the connection string or the sql query? The table name is Full. There are 23 columns total, but the only primary key is Tracking ID which cannot be null. In all instances, these queries are a OnClick button event. As you will be able to see I have tried several routes. I am hoping to set this up to be parameterized to prevent SQL Injections. The first bit of code you will see is from a Windows Forms Application in C# that (while is vulnerable) works consistently. Below, you will see the asp.net code.

private void licensesubmitbutton_Click(object sender, EventArgs e)
    {
        try
        {
            System.Data.SqlClient.SqlConnection sqlConnection1 =
    new System.Data.SqlClient.SqlConnection("Data Source=data99;Initial Catalog=LicenseInventoryMgr;Integrated Security=True;Connect Timeout=0;Trusted_Connection=Yes");

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT LIMTable (Software,Host,AssetTag,ActivationDate,LicenseNumber) VALUES ('" + softwarecombobox.Text + "','" + hostnamebox.Text + "','" + assettagcombobox.Text + "','" + dateactivateddatetimepicker.Value.ToString("yyyy-MM-dd") + "','" + licensekeytextbox.Text + "')";
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();
            cmd.ExecuteNonQuery();
            sqlConnection1.Close();
            MessageBox.Show("Successfully Submitted!");
        }
        catch (System.Exception )
        {
            MessageBox.Show("Submission unsuccessful. Try saving and refreshing first!");
        }
    }

这是我尝试过的ASP.NET代码。

And here is the ASP.NET code I've tried.

protected void submit_Click(object sender, EventArgs e)
   {
       try
       {
           SqlConnection conn = new SqlConnection(@"Data Source=data99;Initial Catalog=LFM_Archive;Integrated Security=True");
           string sql = "INSERT INTO Full (TrackingID) values (@TrackingID)";
           conn.Open();
           SqlCommand cmd = new SqlCommand(sql, conn);
           cmd.Parameters.Add("@TrackingID", SqlDbType.NChar);
           cmd.Parameters["@TrackingID"].Value = 6789;
           cmd.ExecuteNonQuery();
           conn.Close();

           //System.Data.SqlClient.SqlConnection sqlConnection1 =
           //    new System.Data.SqlClient.SqlConnection("Data Source=data99;Initial Catalog=LFM_Archive;Integrated Security=True;Connect Timeout=0;Trusted_Connection=Yes");
           //System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
           //cmd.CommandType = System.Data.CommandType.Text;
           //cmd.CommandText = "INSERT INTO LFM_Archive (TrackingID) VALUES ('" + empfirst.Text + "')";
           //cmd.Connection = sqlConnection1;

           //sqlConnection1.Open();
           //cmd.ExecuteNonQuery();
           //sqlConnection1.Close();

           //SqlConnection conn = new SqlConnection("Data Source=data99;Initial Catalog=LFM_Archive;Integrated Security=True;Connect Timeout=0;Trusted_Connection=Yes");
           //SqlCommand cmd = new SqlCommand("INSERT INTO Full (TrackingID) VALUES (12345)");
           ////cmd.CommandType = CommandType.StoredProcedure;
           ////    cmd.Parameters.Add("@trackingid)", SqlDbType.NChar, 10);
           ////    cmd.Connection.Open();
           ////    cmd.ExecuteNonQuery();
           ////    cmd.Connection.Close();

           //("sender","reciever" html can be enabled for body.)
           MailMessage m1 = new MailMessage("[e-mail]", "[e-mail]");
           m1.Subject = "SQL Query Success";
           m1.Body = "SQL Query Success :)";
           m1.IsBodyHtml = true;
           SmtpClient smtp = new SmtpClient();
           smtp.Host = "mailserver99";
           smtp.EnableSsl = false;
           //System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();
           //NetworkCred.UserName = "username";
           //NetworkCred.Password = "password";
           smtp.UseDefaultCredentials = true;
           //smtp.Credentials = NetworkCred;
           smtp.Port = 25;
           smtp.Send(m1);

       }
       catch
       {
           MailMessage m2 = new MailMessage("[e-mail]", "[e-mail]");
           m2.Subject = "SQL Query Failed";
           m2.Body = "SQL Query Failed :(";
           m2.IsBodyHtml = false;
           SmtpClient smtp = new SmtpClient();
           smtp.Host = "mailserver99";
           smtp.EnableSsl = false;
           smtp.UseDefaultCredentials = true;
           smtp.Port = 25;
           smtp.Send(m2);
       }
   }

最终结果显示此窗体中的查询成功或失败，并且/或发送电子邮件。当我不再需要测试时，电子邮件将是表单已发布到存档的简单通知.asp.net应用程序中的注释代码仅表示我尝试过它，当它不起作用时，我评论它并尝试了一种新方法。有一次我知道它是连接字符串，因为我在字符串中调用了一个用户实例，然后我的服务器人通知我，由于用户实例是一个SQL，因此生成了一个错误-Express ONLY命令和d oes不适用于我们拥有的SQL Server 2008.



**更新**问题是此代码始终发送失败的消息并且从未实际插入数据库。



**更新**为catch添加了更好的代码，所以它现在告诉我异常：

The end result displays query success or failure in the form window that this pulls from, and/or sends an email. When I no longer have to test it, the emails will be simple notifications that the form has been posted to the archive. Commented code in the asp.net application only means that I tried it and when it didn't work, I commented it and tried a new method. At one point I knew it was the connection string because I had called a User Instance in the string, which I was then notified by our Server guy that an error was being generated since User Instance is a SQL-Express ONLY command and does not work on SQL Server 2008 which we have.

**UPDATE** The problem is that this code always sends a failed message and never actually inserts into the db.

**UPDATE** Added better code to the catch so it now tells me about the exception:

Quote：

关键字'Full'附近的语法不正确。

Incorrect syntax near the keyword 'Full'.

** UPDATE **Full显然是一个关键字，所以我编辑了代码来显示，[Full]所以它会从字面上解释它。我运行代码并获得了成功消息，但没有任何内容插入到数据库中，所以在那里某处有一个未被捕获的异常。我试图让它将数字6789作为测试插入到LFM_Archive数据库中的Full表的TrackingID列中。如果我能在那里找到6789，那么我很高兴。



**更新**改为[Full]很好，我还需要在values关键字后面的列中添加单引号，如下所示：INSERT INTO [Full]（TrackingID）值（'@ TrackingID'）。但是，我收到了以下异常：

**UPDATE** "Full" is apparently a keyword, so I edited the code to show instead, [Full] so it would literally interpret it. I ran the code through and got a success message, but nothing was inserted into the DB so there was an uncaught exception somewhere in there. I am trying to get it to insert the numbers 6789 as a test into the TrackingID column of the Full table in the LFM_Archive database. If I can get 6789 to post there, then I'm good to go.

**UPDATE** Changing to [Full] was good, I needed to also add single quotations to the column after the values keyword to display as follows: INSERT INTO [Full] (TrackingID) values ('@TrackingID'). However, I then recieved the following exception:

Quote：

字符串或二进制数据将被截断。该声明已被终止。

String or binary data would be truncated. The statement has been terminated.

**更新**我从（'@ TrackingID'）中取出单引号，结果是以下异常：

**UPDATE** I took the single quotes out of the ('@TrackingID') and that resulted with the following exception:

Quote：

此SqlParameterCollection不包含带有ParameterName'@ trackid'的SqlParameter。

An SqlParameter with ParameterName '@trackingid' is not contained by this SqlParameterCollection.

推荐答案

您需要做的第一件事就是停止使用空捕获，并捕获异常实例。这样您就可以通过电子邮件发送实际的故障信息以及失败消息，这应该至少可以从SQL的角度为您提供问题的线索。在你掌握了这些信息之前，我们只是在猜测。

The first thing you need to do, is stop using empty catches, and catch the exception instance. That way you can email the actual fault info as well as the fail message, and that should at lest give you a clue what the problem is from SQLs point of view. Until you have that info, we're all just guessing.

catch (Exception ex)
{
    MailMessage m2 = new MailMessage("[e-mail]", "[e-mail]");
    m2.Subject = "SQL Query Failed";
    m2.Body = "SQL Query Failed :(" + ex.Message;
    m2.IsBodyHtml = false;
    SmtpClient smtp = new SmtpClient();
    smtp.Host = "mailserver99";
    smtp.EnableSsl = false;
    smtp.UseDefaultCredentials = true;
    smtp.Port = 25;
    smtp.Send(m2);
}

是，是的，我知道捕获异常而非特定异常很糟糕但在这种情况下，你需要的第一件事就是找出抛出的异常......

Yes, yes, I know "Catching Exception instead of specific exceptions is bad" but in this case the first thing you need is to find out what exception is being thrown...

cmd.CommandText =INSERT into LIMTable（Software，Host，AssetTag， ActivationDate，LicenseNumber）VALUES（'+ softwarecombobox.Text +'，'+ hostnamebox.Text +'，'+ assettagcombob ox.Text +'，'+ dateactivateddatetimepicker.Value.ToString（yyyy-MM-dd）+'，'+ licensekeytextbox.Text +'）;





在此查询中您忘记了进入

cmd.CommandText = "INSERT into LIMTable (Software,Host,AssetTag,ActivationDate,LicenseNumber) VALUES ('" + softwarecombobox.Text + "','" + hostnamebox.Text + "','" + assettagcombobox.Text + "','" + dateactivateddatetimepicker.Value.ToString("yyyy-MM-dd") + "','" + licensekeytextbox.Text + "')";


in this query you forget "into"

这篇关于从ASP.NET C插入SQL数据库的问题＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！