Server Intellect

How to Protect Against SQL Injection Attacks in ASP.NET

Category: ASP.NET

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:

<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") %>, Phone: <%# Eval("Phone") %>
<br />
</ItemTemplate>
</asp:Repeater>
Name: <asp:TextBox ID="fld_Name" runat="server" MaxLength="25" /> Phone: <asp:TextBox ID="fld_Phone" runat="server" Columns="12" MaxLength="12" /> <br />
<asp:Button ID="but_Submit" runat="server" Text="Add" onclick="but_Submit_Click" />
</ContentTemplate>
</asp:UpdatePanel>
</form>

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:

<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") %>, Phone: <%# Eval("Phone") %>
<br />
</ItemTemplate>
</asp:Repeater>
Name: <asp:TextBox ID="fld_Name" runat="server" MaxLength="25" />
Phone: <asp:TextBox ID="fld_Phone" runat="server" Columns="12" MaxLength="12" />
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
ErrorMessage="RegularExpressionValidator" ControlToValidate="fld_Phone" Text="Invalid US Phone"
ValidationExpression="((\(\d{3}\) ?)|(\d{3}-))?\d{3}-\d{4}" />
<br />
<asp:Button ID="but_Submit" runat="server" Text="Add" onclick="but_Submit_Click" />
</ContentTemplate>
</asp:UpdatePanel>
</form>

Now moving to our code-behind, we will implement the button click handler and set the Repeater data source on page load:

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}$"))
{
//Error
}
else
{
//Add to db
}
}
}

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:

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

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:

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

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:

CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
) */
AS
/* SET NOCOUNT ON */
RETURN

We can replace this starter code with the following, to create a new Stored Procedure named sp_AddData:

CREATE PROCEDURE dbo.sp_AddData
@Name varchar(25),
@Phone varchar(12)
AS
INSERT INTO tblNames ([Name],Phone)
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:

CREATE PROCEDURE dbo.sp_GetAllData

AS
SELECT * FROM tblNames

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:

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, String thePhone)
{
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("@Phone", SqlDbType.VarChar, 12);
cmd.Parameters["@Phone"].Value = thePhone;

connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
return true;
}
catch
{
connection.Close();
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:

if (!Regex.IsMatch(fld_Name.Text, @"^[a-zA-Z'./s]{1,40}$"))
{
throw new FormatException("Invalid name format.");
}
else
{
SQLInjection.AddData(fld_Name.Text, fld_Phone.Text);
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



Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!

Leave a Comment

Comments on this Article

Post a Comment
Name:
Website:
Email:
Comments:

#1 Joseph Marinaccio

Posted By: Joseph Marinaccio | 12.22.2008 at 11:01 PM

Another awesome article!

Thank you,

Joseph Marinaccio

Marinaccio Family Design

#2 Dulwan Baddewithana

Posted By: Dulwan Baddewithana | 4.08.2009 at 9:40 PM

Hey thanks for the nice article..

#3 wwwww

Posted By: wwwww | 7.13.2009 at 8:29 PM

><script><"Hacking Your Website"></script>

#4 EWREW

Posted By: EWREW | 7.13.2009 at 8:30 PM

><script><"Hacking Your Website"></script>

#5 coolnik999

Posted By: coolnik999 | 7.31.2009 at 1:00 PM

this technical team of yours is very good.....

#6 Teaching degree

Posted By: Teaching degree | 8.29.2009 at 12:16 AM

Furthermore, with the AddData method, we are required to pass the values to the Stored Procedure as to add to the database.

#7 Social work degree

Posted By: Social work degree | 8.29.2009 at 12:16 AM

It was great post.

#8 Nutrition degree

Posted By: Nutrition degree | 8.29.2009 at 12:16 AM

The Stored Procedures will be created once we save them.

#9 Nursing Diploma

Posted By: Nursing Diploma | 8.29.2009 at 12:17 AM

Notice here, we also use Regular Expressions to verify that the name only consists of alpha-numeric characters.

#10 Computer school

Posted By: Computer school | 8.29.2009 at 12:17 AM

I really gathered much from this.Thanks for sharing.

#11 Tiffany Rings

Posted By: Tiffany Rings | 10.23.2009 at 7:37 AM

i like

#12 Tom Ford Eyeglasses

Posted By: Tom Ford Eyeglasses | 11.20.2009 at 5:26 PM

When we are working in ASP.NET there are certain issues like the injection attacks, these tips are really useful for protecting. THanks

#13 Tom Ford Eyeglasses

Posted By: Tom Ford Eyeglasses | 11.23.2009 at 5:43 AM

I found this post very interesting and useful. The presentation is very good. Nice work. Keep it up.

#14 free online games

Posted By: free online games | 11.24.2009 at 10:57 PM

Furthermore, with the AddData method, we are required to pass the values to the Stored Procedure as to add to the database.

#15 handbags shop

Posted By: handbags shop | 12.26.2009 at 12:27 AM

i like

#16 Red Caviar

Posted By: Red Caviar | 1.06.2010 at 6:12 AM

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 same

#17 bus lines New York

Posted By: bus lines New York | 1.06.2010 at 6:49 PM

Protecting against injection attack is very important for your code security. I am happy to find the coding. Thanks for sharing.

#18 new york web design

Posted By: new york web design | 1.07.2010 at 2:48 AM

Furthermore, with the AddData method, we are required to pass the values to the Stored Procedure as to add to the database.

#19 tiensshop

Posted By: tiensshop | 1.09.2010 at 3:25 AM

I am happy to find the coding which is Protecting against injection attack because this very important for your code security. . Thanks for sharing.

#20 Movers Long Island

Posted By: Movers Long Island | 1.14.2010 at 4:38 PM

Really nice and helpful post. I always appreciate topics like these being discussed to aware people. thanks for sharing.

#21 flowers san jose

Posted By: flowers san jose | 1.16.2010 at 1:13 PM

this is great

#22 Limousine Service NY

Posted By: Limousine Service NY | 1.17.2010 at 4:49 PM

SQL Injection attacks in ASP.net is a big problem, I am happy to find how to cover that. Thanks for the coding

#23 Digital Printing

Posted By: Digital Printing | 1.28.2010 at 10:33 PM

I must say that this is a great post. I loved reading it. You have done a great job

#24 PMI-001

Posted By: PMI-001 | 2.12.2010 at 1:22 AM

Clarifications: on the acknowledgment to cilia vs. action above. While not absolutely incorrect, this acknowledgment is inadequate. Accoutrement abide aural a process. A action can accommodate assorted threads. The operating arrangement contains assorted processes. A action is associated with a specific job object; this article is acclimated to ascertain the abstracts structures appropriate for a action to become executable. A action is executable because one of the accoutrement that it contains is executable. When the action is called for execution, the abstracts structures are mapped for the CPU and beheading begins on the executable thread.

#25 642-825

Posted By: 642-825 | 2.12.2010 at 1:22 AM

AS EJB technology enables accelerated and simplified development of distributed, transactional, defended and carriageable applications based on Java technology which technology is accessible in .Net?

#26 1Y0-A05

Posted By: 1Y0-A05 | 2.12.2010 at 1:23 AM

Machine config:it sets the abject agreement for all the .net assemblies active on the server web config:This sets the abject config for all the web app and override settings in apparatus configuration

#27 70-642

Posted By: 70-642 | 2.13.2010 at 5:50 AM

Below is a simple function that will give you some protection against an SQL Injection attempt.

The sample script below retrieves the form values entered into the textboxes 'txtUsername' and 'txtPassword' and assigns them to variables 'sUsername' and 'sPassword'. The next line of code calls the function IllegalChars and passes in the variables as parameters.

The function IllegalChars holds an array of illegal characters and words, it loops through these checking for their presence against our variables using the InStr function. If any are present in either of our variables then IllegalChars returns False. In that scenario the visitor will be redirected to the file 'no_access.asp'.

#28 online sildenafil

Posted By: online sildenafil | 2.17.2010 at 3:03 PM

Try Server Intellect for Windows Server Hosting. Quality and Quantity - VERY quality!

#29 coach handbag outlet

Posted By: coach handbag outlet | 2.25.2010 at 11:28 PM

Should I give up?

#30 SalazarMandy27

Posted By: SalazarMandy27 | 2.27.2010 at 1:48 AM

If you are in the corner and have no cash to go out from that, you will have to take the <a href="http://lowest-rate-loans.com/topics/home-loans">home loans</a>. Because that will aid you emphatically. I take car loan every year and feel myself great just because of this.

#31 multiplayer games

Posted By: multiplayer games | 3.07.2010 at 3:32 AM

I found your site from cuil and it is very informative. Thank you very much for sharing such an awesome blog post!!!!

#32 security cameras

Posted By: security cameras | 3.10.2010 at 8:00 AM

Wow. This Blog is truly a gold mine. I will actually try these tips and let you know how they work out! Thanks again mate.