Preventing SQL Injection Attacks in ASP.NET 4.0
Unscrupulous users can use poorly secured input forms as a means to gain access to and modify sensitive data, or even eliminate the whole database. This tutorial will explain a number of precautions and methods you can use to beef up the security around your ASP.NET Web Applications. The common points that make your data access code vulnerable to these types of attacks include:
- Little or no validation on user input;
- Using database accounts with too high an authority, or privileges; and
- Dynamic SQL statements without the safeguard of type-safe parameters.
We chose Server Intellect for its cloud servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
What we will learn in this article:
- How to validate user input on ASP.NET Web Forms;
- Use ASP.NET Validation Controls to secure your input fields.
- How to build type-safe parameters in our data access code.
Getting Started
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.
Creating The Web Form
Now we have created our table, we can move onto our Web Form. We will create a simple form where we allow the user to insert new records, of which will be Name and Phone, to a SQL database. We will use a Repeater to display the results on the page, and then two textboxes and a button. This code also contains the validation controls we will use and cover throughout this tutorial. The code will look something like this:
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="SM1" runat="server" />
<asp:UpdatePanel ID="UP1" runat="server">
<ContentTemplate>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
ID:
<%# Eval("ID") %>, Name:
<%# Eval("Name") %>, Phone:
<%# Eval("Phone") %>
<br />
</ItemTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [tblNames]"></asp:SqlDataSource>
<br />
<label for="fld_Name"><strong>Name:</strong> </label><asp:TextBox ID="fld_Name" runat="server" MaxLength="25" CausesValidation="True" Width="171px"></asp:TextBox><br />
<br />
<label for="fld_Phone"><strong>Phone:</strong> </label><asp:TextBox ID="fld_Phone" runat="server" MaxLength="12" Columns="12" ValidationGroup="userInfo" CausesValidation="True" Width="172px"></asp:TextBox><br />
<asp:Button ID="userAdd" runat="server" Text="Add User" OnClick="userAdd_Click" Width="102px" /><br />
<asp:Literal ID="litStatus" runat="server"></asp:Literal>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
Now that we have our template, create a SQLDataSource using our test database so that our repeater lists out all of the users that will be added. Click the smart tab choose new data source and connect to the database.mdf file. On this form, you’ll notice we have the Repeater set up so that we can display each of the records from the database. We also display two textboxes – one for the Name, and one for the Phone. We have set the MaxLength of the textboxes to make sure we know how long the data is going to be. Here is where we will tighten up security on the front-end by adding an ASP.NET Validator. We will us a Regular Expression validator to allow only a string in the format of a US Phone Number to be entered. Drag a Regular Expression Validator onto the ASPX page from the Validation Toolbox. We can then goto the Properties and click on the ValidationExpression option. We will then be presented with a number of preset expressions. Scroll down to US Phone Number and click Ok.
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.
This validator will only work if the user’s browser has JavaScript enabled, so to strengthen this implementation, it is recommended that we use the Page.IsValid method on the codebehind. Our ASPX code will now look something like this:<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="SM1" runat="server" />
<asp:UpdatePanel ID="UP1" runat="server">
<ContentTemplate>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
ID:
<%# Eval("ID") %>, Name:
<%# Eval("Name") %>, Phone:
<%# Eval("Phone") %>
<br />
</ItemTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [tblNames]"></asp:SqlDataSource>
<asp:RequiredFieldValidator ID="nameEmpty" runat="server" ErrorMessage="Please Enter A Name" Display="Dynamic" ControlToValidate="fld_Name " ValidationGroup="use rInfo" ForeColor="#CC0000"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="nameInvalid" runat="server" ErrorMessage="Please Enter A Valid Name" ControlToValidate="fld_Name" ValidationExpression="^[a-zA-Z'./s]{1,40}$" ValidationGroup="userInfo" Display="Dynamic" ForeColor="#CC0000"></asp:RegularExpressionValidator><br />
<label for="fld_Name"><strong>Name:</strong> </label> <asp:TextBox ID="fld_Name" runat="server" MaxLength="25" CausesValidation="True" Width="171px"></asp:TextBox><br />
<asp:RegularExpressionValidator ID="phoneInvalid" runat="server" ErrorMessage="Please Enter A Valid Phone Number" Display="Dynamic" ControlToValidate="fld_Phone" ValidationGroup="userInfo" ValidationExpression="((\(\d{3}\) ?)|(\d{3}-))?\d{3}-\d{4}" ForeColor="#CC0000"></asp:RegularExpressionValidator><br />
<label for="fld_Phone"><strong>Phone:</strong> </label> <asp:TextBox ID="fld_Phone" runat="server" MaxLength="12" Columns="12" ValidationGroup="userInfo" CausesValidation="True" Width="172px"></asp:TextBox> <br /><asp:Button ID="userAdd" runat="server" Text="Add User" OnClick="userAdd_Click" Width="102px" /><br />
<asp:Literal ID="litStatus" runat="server"></asp:Literal>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
Now we will move onto the code behind and implement the IsValid and Regex checks in case JavaScript is disabled. If the JavaScript is disabled we will inform the user that the name is invalid in a literal control using a Regex check and if everything is clear the function will submit to the database.
protected void Page_Load(object sender, EventArgs e)
{
}
protected void userAdd_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
if (!Regex.IsMatch(fld_Name.Text, "^[a-zA-Z'./s]{1,40}$"))
{
//If Javascript is Disabled
litStatus.Text = "Invalid Name";
}
else
{
string fldName = fld_Name.Text;
string fldPhone = fld_Phone.Text;
SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("Insert INTO tblNames(Name, Phone) VALUES (@Name, @Phone) ", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", fldName);
cmd.Parameters.AddWithValue("@Phone", fldPhone);
using (conn)
{
conn.Open();
cmd.ExecuteNonQuery();
}
litStatus.Text = "Record Added!";
}
}
}
Notice here, we also use Regular Expressions to verify that the name only consists of alpha-numeric characters. To use RegEx in the code-behind, we need to add the System.Text.RegularExpressions reference.
Success
Congratulations! We have learned how to protect our database from SQL Injection Attacks by making it more difficult for users to gain unauthorized access via input forms.
Protecting Against SQL Injection Attacks
