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.
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:
<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>
</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.
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
|
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
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
|
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 |
|
