Protecting Against SQL Injection Attacks in ASP.NET
Introduction
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 article will explain a number of precautions and methods to undertake 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.
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
What we will learn in this article:
- How to validate user input on ASP.NET Web Forms;
- How to create and use Stored Procedures instead of inline SQL Statements; and
- How to build type-safe parameters in our data access code.
Getting Started
In this example, we will be creating a sample database and a sample web form in Visual Studio .NET 2008. If you are using an older version of VS.NET, then there may be other steps needed that are not covered in this article. Also, we will be using VS.NET to create our SQL database and Stored Procedures.
But first, we will create the database before moving onto the Web Form. Let's go ahead and right-click on the App_Data folder in Solution Explorer and choose Add New Item.. SQL Server Database. Give it a name if you wish, and then click Ok.
[Click to enlarge]
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!
You'll notice that we then have the Server Explorer window open, displaying the database:
We can now create a table in our database by right-clicking the Tables folder and choosing to Add New Table. We will create a column ID, which will be the Primary Key and Identity Specification (in Column Properties), Name and Phone, which will both be of type varchar:
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.
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.
The code will look something like this:
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 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!
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:
Now moving to our code-behind, we will implement the button click handler and set the Repeater data source on page load:
{
Repeater1.DataBind();
protected void but_Submit_Click(object sender, EventArgs e)
{
{
{
else
{
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.
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.
Next, we will add a Class to the project. Right-click the Project in Solution Explorer and choose Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose to Add New Item.. Class. We will name it SQLInjection.cs
We will add the following assembly references to this class:
We use these namespaces because we will be referencing the WebConfigurationManager to get the Connection String from the Web.config, and also we will be using the SqlClient to connect to our database.
Our Connection String looks like this:
Before we add our methods to the new Class, let's add our Stored Procedures. These are just like SQL Statements, but they reside in the database.
To create a new Stored Procedure, right-click the Stored Procedures folder in the Server Explorer, then choose Add New Stored Procedure. We will have something like this:
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
) */
RETURN
We can replace this starter code with the following, to create a new Stored Procedure named sp_AddData:
@Phone varchar(12)
VALUES (@Name,@Phone)
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.
We also do the same to create another Stored Procedure:
AS
The Stored Procedures will be created once we save them.
Now that we have our Stored Procedures, we can turn our attention to our Class, in which we will create two methods - one to retrieve all the data from the database, and another to add a new record to the database:
{
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(allData);
connection.Close();
catch
{
return allData;
public static bool AddData(String theName, String thePhone)
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 25);
cmd.Parameters["@Name"].Value = theName;
cmd.Parameters.Add("@Phone", SqlDbType.VarChar, 12);
cmd.Parameters["@Phone"].Value = thePhone;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
return true;
catch
{
return false;
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!
Notice that we reference the name of the Stored Procedure in the SqlCommand, and also set the type of Stored Procedure. Furthermore, with the AddData method, we are required to pass the values to the Stored Procedure as to add to the database. We do this using the Parameters.Add method of the SqlCommand. We also specify the type, as to type-safe the input data.
The final thing that we need to do is to modify our button click code:
{
else
{
Repeater1.DataSource = SQLInjection.GetAllData();
Repeater1.DataBind();
Here, we are simply calling the method we created in the Class to add the contents of the textboxes to the database - but only if the Page Is Valid (is a valid US Phone number) and Name consists of alpha-numeric characters.
What we have Learned
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.
Attachments
|
|