SQL Injection prevention with Microsoft Access and VB.NET

Question

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 database.

My questions are:

1. How to protect my database from SQL injection?
2. I have been reading postings from other forums and they said using parameters with stored procedures, parameters with dynamic SQL. Can they be implemented in a Microsoft Access database?

Answer 1

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

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:\__tmp\testData.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

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.