Validating User Input - SQL Injection Attacks in VB.NET

This tutorial will show you how to manage your SQL data connections and data access methods to improve security issues against SQL Injection Attacks. VB.

Web Applications that allow or require user input to function are susceptible to certain attacks by malicious users. If not properly protected, user input can cause problems because users may be able to interject their own SQL commands into the application and cause havoc with your database(s) - modifying or even deleting sensitive or crucial data.

In this tutorial, we will look at ways we can prevent the user from doing this. First, let us start with a simple Web Form using Visual Studio .NET 2008 and a sample database. We will use a table named tblNames, and have an id column and a name column. The id will be Primary Key, and identity specification. We will then add some sample data to the database. If you have your own database and web application to use, please feel free.

We will be using the following connection string in the Web.config:

<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>

We will use a Repeater control to display data from our database, and then allow additions to the database with the use of two text boxes:

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!

<form id="form1" runat="server"> <asp:ScriptManager ID="SM1" runat="server" /> <asp:UpdatePanel ID="UP1" runat="server"> <ContentTemplate> <asp:Repeater ID="Repeater1" runat="server"> <ItemTemplate> ID: <%# Eval("id") %>, Name: <%# Eval("name") %>, Age: <%# Eval("age") %> <br /> </ItemTemplate> </asp:Repeater> Name: <asp:TextBox ID="fld_Name" runat="server" MaxLength="25" /> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="fld_Name" Text="*" ErrorMessage="Name is required." /> Age: <asp:TextBox ID="fld_Age" runat="server" Columns="3" MaxLength="3" /> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="fld_Age" Text="*" ErrorMessage="Age is required." /> <br /> <asp:Button ID="but_Submit" runat="server" Text="Add" onclick="but_Submit_Click" /> </ContentTemplate> </asp:UpdatePanel> </form>

The first step is to validate the user input on the front-end. We can do this using the built-in ASP.NET Validation controls. Regular Expression validators are great for making sure a user uses the correct format for entry, for such things as a US phone number, Zip code, or Social Security Number. However, when using validators, it is also important to use the Page.IsValid method on the code-behind. This will ensure that the validators work even when JavaScript is disabled in the client's browser.

Try Server Intellect for Windows Server Hosting. Quality and Quantity!

The next step is to validate the input on the code-behind. Notice below that we first check that the page is valid on button click. If the field validators' expressions are met, then the page is valid. Otherwise, the page is invalid and the code will not execute.
The next statement we carry out another test to see if the names field consists of alphanumeric characters - if it does, we execute the Add method. However, if the name field consists of illegal characters, an exception will occur and the database will not be accessed.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load Repeater1.DataSource = SQLInjection.GetAllData() Repeater1.DataBind() End Sub Protected Sub but_Submit_Click(ByVal sender As Object, ByVal e As EventArgs) If Page.IsValid Then If (Not Regex.IsMatch(fld_Name.Text, "^[a-zA-Z'./s]{1,40}$")) Then Throw New FormatException("Invalid name format.") Else SQLInjection.AddData(fld_Name.Text, Convert.ToInt32(fld_Age.Text)) Repeater1.DataSource = SQLInjection.GetAllData() Repeater1.DataBind() End If End If End Sub

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.

In order to use RegEx, we add the following assembly references:

Imports System.Text.RegularExpressions

Finally, in our data access method, we use Stored Procedures and parameters as an added measure of security. In these two methods, we show two different ways of using the parameters. In the second method, where we add data, we add a database type to the parameter. This is referred to as a type-safe stored procedure. If the type is not matched, the stored procedure will not execute.

Public Shared Function GetAllData() As DataTable Dim allData As New DataTable() Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()) Try Dim cmd As New SqlCommand("sp_GetAllData", connection) cmd.CommandType = CommandType.StoredProcedure connection.Open() Dim adapter As New SqlDataAdapter(cmd) adapter.Fill(allData) connection.Close() Catch connection.Close() End Try Return allData End Function Public Shared Function AddData(ByVal theName As String, ByVal theAge As Int32) As Boolean Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString()) Try Dim cmd As New SqlCommand("sp_AddData", connection) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@name", SqlDbType.VarChar, 25) cmd.Parameters("@name").Value = theName cmd.Parameters.Add("@age", SqlDbType.Int) cmd.Parameters("@age").Value = theAge connection.Open() cmd.ExecuteNonQuery() connection.Close() Return True Catch connection.Close() Return False End Try End Function

To access a SQL database and use the Web Configuration Manager, we add the following assemblies to our class:

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

Imports System.Data.SqlClient Imports System.Web.Configuration

Looking for the C#.NET 2008 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!