Validating User Input - SQL Injection Attacks in .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. C#.

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 used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.

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

If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.

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 void Page_Load(object sender, EventArgs e) { Repeater1.DataSource = SQLInjection.GetAllData(); Repeater1.DataBind(); } protected void but_Submit_Click(object sender, EventArgs e) { if (Page.IsValid) { if (!Regex.IsMatch(fld_Name.Text, @"^[a-zA-Z'./s]{1,40}$")) { throw new FormatException("Invalid name format."); } else { SQLInjection.AddData(fld_Name.Text, Convert.ToInt32(fld_Age.Text)); Repeater1.DataSource = SQLInjection.GetAllData(); Repeater1.DataBind(); } } }

We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

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

using 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 static DataTable GetAllData() { DataTable allData = new DataTable(); SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString()); try { SqlCommand cmd = new SqlCommand("sp_GetAllData", connection); cmd.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(allData); connection.Close(); } catch { connection.Close(); } return allData; } public static bool AddData(String theName, Int32 theAge) { SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString()); try { SqlCommand cmd = 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; } }

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.

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

using System.Data.SqlClient; using System.Web.Configuration;

Looking for the VB.NET 2008 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!