在ASP.NET Web应用程序preventing SQL注入 [英] Preventing SQL Injection on ASP.NET Web Application

问题描述

我是新的C＃和ASP.NET。

I am new to C# and ASP.NET.

我使用VS2005 C＃和SQL Server 2005，并已做了preventing SQL注入一些研究

I am using VS2005 C# and SQL Server 2005 and have done some research on preventing SQL injections

我在我的服务器端 Web应用中的几个功能，这我不确定他们是否需要输入验证。

I have a couple of functions in my server-side web application which I am unsure if they requires input validation.

1） 登录从工具箱控制。我已经直接从VS工具箱实现登录控制，我试图用的 RegularEx pressionValidator 的我的登录工具，但它似乎并没有工作。微软是否已经为工具内置的验证？

1) Login control from the toolbox. I have implemented the login control directly from the VS Toolbox, and I tried to use a RegularExpressionValidator for my login tool but it does not seem to work. Does Microsoft already have a in-built validation for the tool?

2） 上传 Excel文件表到SQL Server数据库。我有一个功能，它允许用户excel文件上传张到数据库中。一开始，我并不觉得有必要对其进行验证，因为没有打开的SQL查询，但在那之后我问自己，如果它有可能为用户输入的SQL查询中的Excel文件，这将导致上传时，SQL注入攻击。下面是我上传的code段，将期待着意见，如果需要验证：

2) Upload of excel file sheets into SQL Server database. I have a function which allows users to upload excel file sheets into the database. At the beginning I don't feel that there is a need to validate it as there is no open sql queries, but after that I'm asking myself if it is possible for the user to input SQL queries in the excel file which will cause SQL injection during uploading. Below is my upload code snippet, would be looking forward to advice if a validation is required:

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 8.0;";

using (OleDbConnection connection =
             new OleDbConnection(connStr))
{
    string selectStmt = string.Format("Select [COLUMNS]  FROM [userlist$]");

    OleDbCommand command = new OleDbCommand(selectStmt, connection);

    connection.Open();
    Console.WriteLine("Connection Opened");
    // Create DbDataReader to Data Worksheet
    using (DbDataReader dr = command.ExecuteReader())
    {
        // SQL Server Connection String
        string sqlConnectionString = "Data Source=<datasource>";

        // Bulk Copy to SQL Server
        using (SqlBulkCopy bulkCopy =
                   new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "UserDB";
            bulkCopy.WriteToServer(dr);;
        }
    }
}

---

3） 插入语句。我有一对夫妇的主要用于插入新记录到数据库中插入语句。由于这些语句不从数据库中实际的grep的数据，我不能确定是否需要验证。下面是一个示例插入语句：

---

3) INSERT statements. I have a couple of INSERT statements which is mainly used to insert new records into the database. As these statements do not actually grep data from the database, I am unsure if a validation is required. Below is a sample INSERT statement:

SqlConnection conn = new SqlConnection("<datasource>");
string sql = string.Format("INSERT INTO [UserData] (Username, Password, Role, Membership, DateOfReg) VALUES ('" + un.Text + "', '" + pw.Text + "', '" + role.Text + "', '" + ms.Text + "', '" + dor.Text + "')"); --> all *.Text are textboxes on the webpage

SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

---

4） 搜索在网页的功能。我有一个GridView中所显示的数据几个.aspx页。下面是使用一个文本框和一个DropDownList过滤我的搜索查询的例子：

---

4) Search function in webpage. I have several .aspx pages which displays data by GridView. Below is an example of my search query which uses a textbox and a dropdownlist filter:

SqlDataSource1.SelectCommand = "SELECT * FROM [UserData] where [" + DropDownList1.Text + "] like '%" + searchTextBox.Text + "%'";
SqlDataSource1.DataBind();

---

我想知道是什么做的 SQL语句本身就是一个输入验证检查，而无需创建额外的方法和功能，以上面的例子最简单的方法，因为我已经看到常规EX pression ，并使用 mysql_real_escape_string 。

---

I would like to know what is the easiest way to do a input validation check on the sql statements itself without creating additional methods and functions, to the above examples, as I have seen regular expression and using mysql_real_escape_string.

感谢您事先提供的任何意见和建议。

Thank you in advance for any advice and suggestions provided.

给定将是一件好事，以及直接的例子。

Direct examples given would be good as well.

推荐答案

有关你的问题3，这是你在找什么：

For your question 3, this is what you are looking for :

string sql = string.Format("INSERT INTO [UserData] (Username, Password, Role, Membership, DateOfReg) VALUES (@Username, @Password, @Role, @Membership, @DateOfReg)");
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
        cmd.Parameters.AddWithValue("Username", usernameTB.Text);
        cmd.Parameters.AddWithValue("Password", passwordTB.Text);
        cmd.Parameters.AddWithValue("Role", roleTB.Text);
        cmd.Parameters.AddWithValue("Membership", membershipTB.Text);
        cmd.Parameters.AddWithValue("DateOfReg", dorTB.Text);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

就这么简单。

这篇关于在ASP.NET Web应用程序preventing SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！