asp.net – 使用Microsoft Access和VB.NET进行SQL注入预防

Default.aspx的

<%@ Page Title="Home Page" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false"
    CodeBehind="Default.aspx.vb" Inherits="vbOleDbSite._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <p>
        First Name: <asp:TextBox ID="FirstName" runat="server"></asp:TextBox><br />
        Last Name: <asp:TextBox ID="LastName" runat="server"></asp:TextBox><br />
        &nbsp;<br />
        <asp:Button ID="btnAddUser" runat="server" Text="Add User" />
        &nbsp;<br />
        Status: <span id="spanStatus" runat="server">Awaiting submission...</span>
    </p>
</asp:Content>

Default.aspx.vb

Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object,ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnAddUser_Click(sender As Object,e As EventArgs) Handles btnAddUser.Click
        Dim newID As Long = 0
        Using con As New OleDb.OleDbConnection
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:__tmptestData.accdb;"
            con.Open()
            Using cmd As New OleDb.OleDbCommand
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO UsersTable (LastName,FirstName) VALUES (?,?);"
                cmd.Parameters.AddWithValue("?",Me.LastName.Text)
                cmd.Parameters.AddWithValue("?",Me.FirstName.Text)
                cmd.ExecuteNonQuery()
            End Using
            Using cmd As New OleDb.OleDbCommand
                cmd.Connection = con
                cmd.CommandText = "SELECT @@IDENTITY"
                newID = cmd.ExecuteScalar()
            End Using
            con.Close()
        End Using
        Me.spanStatus.InnerText = "User """ & Me.FirstName.Text & " " & Me.LastName.Text & _
                """ has been added (ID: " & newID.ToString() & ")."
    End Sub
End Class

笔记：

>参数化查询使用“？”而不是参数的“真实”名称,因为Access OLEDB忽略参数名称.必须按照它们在OleDbCommand.CommandText中出现的确切顺序定义参数.> [UsersTable]表具有AutoNumber主键,SELECT @@ IDENTITY检索INSERT INTO语句创建的新键值.