ADO.NET学习之防止SQL注入,存储过程,SqlDataReader

ADO.NET学习之防止SQL注入

防止SQL注入

使用参数化查询来防止SQL注入

        // Parameterized query. @ProductName is the parameter
        string Command = "Select * from tblProductInventory where ProductName like @ProductName";
        SqlCommand cmd = new SqlCommand(Command, con);
        // Provide the value for the parameter
        cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");
        con.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();

在输入框中输入ip'; Delete from tblProductInventory --，使用SQL Server Profiler查看，会发现执行如下：

exec sp_executesql N'Select * from tblProductInventory where ProductName like @ProductName',N'@ProductName nvarchar(40)',@ProductName=N'ip''; Delete from tblProductInventory --%'

使用存储过程来防止SQL注入

创建如下的存储过程：

Create Procedure spGetProductsByName
@ProductName nvarchar(50)
as
Begin
 Select * from tblProductInventory 
 where ProductName like @ProductName + '%'
End

测试存储过程Execute spGetProductsByName 'ip'

使用存储过程

        //指定存储过程的名字
        SqlCommand cmd = new SqlCommand("spGetProductsByName", con);
        //指定 T-SQL 命令是存储过程
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");
        con.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();

带输出参数的存储过程

新建如下的表tblEmployees ，插入一些事例数据：

Create Table tblEmployees
(
 EmployeeId int identity primary key,
 Name nvarchar(50),
 Gender nvarchar(10),
 Salary int
) 

Insert into tblEmployees values('Mike','Male',5000)
Insert into tblEmployees values('Pam','Female',3500)
Insert into tblEmployees values('John','Male',2350)
Insert into tblEmployees values('Sara','Female',5700)
Insert into tblEmployees values('Steve','Male',4890)
Insert into tblEmployees values('Sana','Female',4500)

注意插入数据的时候，没有指定EmployeeId

创建一个存储过程spAddEmployee ，向表中添加一行数据

Create Procedure spAddEmployee  
@Name nvarchar(50),  
@Gender nvarchar(20),  
@Salary int,  
@EmployeeId int Out  
as  
Begin  
 Insert into tblEmployees values(@Name, @Gender, @Salary)  
 Select @EmployeeId = SCOPE_IDENTITY()  
End 

1.@Name, @Gender和@Salary是输入参数
2.@EmployeeId是输出参数
3.这个存储过程有2行代码，第一行是向tblEmployees 表中添加数据，第二行是获取自动产生EmployeeId 列的identity 值

测试一下：

Declare @EmpId int
Execute spAddEmployee 'John', 'Male', '7500', @EmpId Out
Print 'Employee Id = ' + Cast(@EmpId as nvarchar(2))

创建如下的页面：

        <table style="border: 1px solid black; font-family: Arial">
            <tr>
                <td>
                    Employee Name
                </td>
                <td>
                    <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Gender
                </td>
                <td>
                    <asp:DropDownList ID="ddlGender" runat="server">
                        <asp:ListItem>Male</asp:ListItem>
                        <asp:ListItem>Female</asp:ListItem>
                    </asp:DropDownList>
                </td>
            </tr>
            <tr>
                <td>
                    Salary
                </td>
                <td>
                    <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Label ID="lblMessage" runat="server"></asp:Label>
                </td>
            </tr>
        </table>

显示效果如下：

button的事件如下，向表中添加一条记录，并获取到添加的Id

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string CS = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("spAddEmployee", con);
            //存储过程类型
            cmd.CommandType = CommandType.StoredProcedure;
            //添加value
            cmd.Parameters.AddWithValue("@Name", txtEmployeeName.Text);
            cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedValue);
            cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
            //输出参数
            SqlParameter outPutParameter = new SqlParameter();
            outPutParameter.ParameterName = "@EmployeeId";
            outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
            outPutParameter.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(outPutParameter);

            con.Open();
            cmd.ExecuteNonQuery();
            //获取输出参数的值
            string EmployeeId = outPutParameter.Value.ToString();
            lblMessage.Text = "Employee Id = " + EmployeeId;
        }
    }

SqlDataReader

DataReader允许你以只进、只读流的方式每次读取一条select命令返回的记录。这种方式有时候被称为流水游标。使用DataReader是获取数据最简单的方式，不过它缺乏非连接的DataSet所具有的排序功能。
DataReader提供了最快捷且毫无拖沓的数据访问

不能使用new操作符创建一个SqlDataReader对象，会报错The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.

SqlDataReader rd = new SqlDataReader();

SqlCommand 对象的ExecuteReader() 方法返回一个SqlDataReader对象

SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);
SqlDataReader reader = command.ExecuteReader()

ExecuteReader()方法可以有一个可以枚举类型CommandBehavior作为参数的重载版本。一个有用的值是CommandBehavior.CloseConnection.把它传给ExecuteReader()方法后，关闭DataReader的同时，DataReader会关闭关联的连接

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //不需要关闭Connection，关闭reader就行
        reader.Close();

需要注意的一点是SqlDataReader是connection oriented，所以要显式的调用 Open() 方法

最简单的使用方式是把SqlDataReader 与 GridView绑定起来，把它指派给GridView的DataSource属性。注意：与SqlConnection 对象一样，要把SqlDataReader 对象包裹在一个using块中。

string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        ProductsGridView.DataSource = reader;
        ProductsGridView.DataBind();
    }
}

通过SqlDataReader 对象的Read() 方法，来循环遍历每一行。
如下的例子，计算10%的折扣：

    protected void Page_Load(object sender, EventArgs e)
    {
        string CS = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        {
            con.Open();
            SqlCommand command = new SqlCommand("select * from tblProductInventory02", con);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                //创建DataTable 
                DataTable sourceTable = new DataTable();
                sourceTable.Columns.Add("ID");
                sourceTable.Columns.Add("Name");
                sourceTable.Columns.Add("Price");
                sourceTable.Columns.Add("DiscountedPrice");

                while (reader.Read())
                { 
                    //计算10%的折扣
                    int OriginalPrice = Convert.ToInt32(reader["UnitPrice"]);
                    double DiscountedPrice = OriginalPrice * 0.9;

                    DataRow datarow = sourceTable.NewRow();
                    datarow["ID"] = reader["ProductId"];
                    datarow["Name"] = reader["ProductName"];
                    datarow["Price"] = OriginalPrice;
                    datarow["DiscountedPrice"] = DiscountedPrice;
                    //添加DataRow 到 DataTable
                    sourceTable.Rows.Add(datarow);

                }

                GridView1.DataSource = sourceTable;
                GridView1.DataBind();
            }
        }
    }

效果如下：

NextResult()

Read()方法是遍历result set中的row，NextResult()是遍历多个result sets

string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        ProductsGridView.DataSource = reader;
        ProductsGridView.DataBind();

        while (reader.NextResult())
        {
            CategoriesGridView.DataSource = reader;
            CategoriesGridView.DataBind();
        }
    }
}

空值

当DataReader遇到数据库里的空值时，它返回一个常量DBNull.Value。试图访问该值或者装换它的数据会产生异常。因此，在可能出现空值时，必须使用如下的代码对齐进行检测：

        int? numberOfHires;
        if (reader["NumberOfHires"] == DBNull.Value)
        {
            numberOfHires = null;
        }
        else
        {
            numberOfHires = (int?)reader["NumberOfHires"];
        }

资源

• SqlDataReader object in ADO.NET - Part 8
• SqlDataReader object NextResult() method - Part 9