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