Prevent SQL injection with Microsoft Access and VB.NET

Question

I am new to ASP.NET, so I have some questions on how to prevent SQL injection in ASP.NET. My programming language is VB.NET, not C #, and I use Microsoft Access as my database.

My questions:

• How to protect my database from SQL injection?
• I read posts from other forums, and they talked, 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 through 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 the character "?" instead of "real" names for parameters, as Access OLEDB ignores parameter names. Parameters must be defined in the order in which they are displayed in OleDbCommand.CommandText .

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