Sql Injection Prevention With Microsoft Access And Vb.net

I'm a beginner in ASP.NET so I have some questions about how to prevent SQL injection in ASP.NET. My programming language is VB.NET, not C#, and I'm using Microsoft Access as my da

Solution 1:

Here is a very simple ASP.NET example using a parameterized query via OleDb in VB.NET:

Default.aspx

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

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

Default.aspx.vb

PublicClass _Default
    Inherits System.Web.UI.Page

    ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load

    EndSubProtectedSub btnAddUser_Click(sender AsObject, e As EventArgs) Handles btnAddUser.Click
        Dim newID AsLong = 0Using con AsNew OleDb.OleDbConnection
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\__tmp\testData.accdb;"
            con.Open()
            Using cmd AsNew 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()
            EndUsingUsing cmd AsNew OleDb.OleDbCommand
                cmd.Connection = con
                cmd.CommandText = "SELECT @@IDENTITY"
                newID = cmd.ExecuteScalar()
            EndUsing
            con.Close()
        EndUsingMe.spanStatus.InnerText = "User """ & Me.FirstName.Text & " " & Me.LastName.Text & _
                """ has been added (ID: " & newID.ToString() & ")."EndSubEndClass

Notes:

• The parameterized query uses "?" instead of "real" names for the parameters because Access OLEDB ignores parameter names. The parameters must be defined in the exact order that they appear in the OleDbCommand.CommandText.

• The [UsersTable] table has an AutoNumber primary key, and SELECT @@IDENTITY retrieves the new key value created by the INSERT INTO statement.