Geeks With Blogs
Blog Moved to http://podwysocki.codebetter.com/ Blog Moved to http://podwysocki.codebetter.com/
In previous days I have talked about other ways to make your application secure.  All of these lessons come down to a basic set of points:
 
1.  Never trust user input
2.  Use least privileged accounts
 
What SQL Injection Is
 
Today we will cover SQL injection, what it means and how to protect against this attack.  SQL injection is a security vulnerability in an application that calls the database with SQL with unescaped characters which can lead to malicious data manipulation.  This can be even more dangerous if the user account used for the SQL connection has elevated privileges such as database owner.
 
Below is an example of a common SQL injection attack:
 
string query = "SELECT * FROM Employees WHERE LastName = '"+ txtLastName.Text + "'";
 
The value from the txtLastName text box is the following:
';  DROP DATABASE Northwind --
 
What the above statement does is escapes the SELECT SQL statement and injects the DROP DATABASE statement to be executed as well.  If the SQL user has elevated privileges such as System Administrator, this could cause the Northwind database to be dropped from the SQL Server Instance.
 
Preventing SQL Injection
 
In order to avoid SQL Injection attacks, we will concentrate on a few areas.  We will talk about each step in detail below.  The steps that can be taken to mitigate the risk of SQL Injection are the following:
 
1.  Constrain user input and clean data
2.  Use typed parameters for data access commands
3.  Use a least privileged account for data access
4.  Trap exceptions and hide SQL errors from the user
 
Constrain user input and clean data
 
The golden rule is to never trust the user input.  In order to secure our application, we must verify the data at all levels.  We must constrain and clean the data at each level. 
 
First off, let's talk about what we can do with ASP.NET in terms of data validation.  We have many options to validate data with such as the RegularExpressionValidator (which I covered yesterday), the RangeValidator and any custom validator you wish to create.
 
Let's take the examples from yesterday in terms of constraining input with the zip code.  This has an ASP.NET text box control and a RegularExpressionValidator to validate that the data is in fact a valid zip code.  Below is the example:
 
<asp:TextBox ID="txtZipCode" runat="server" />
<asp:RegularExpressionValidator ID="regexZipCode" runat="server"    
     ErrorMessage="Invalid Zip Code."
     ControlToValidate="txtZipCode"    
     ValidationExpression="(?<zip>\d{5})(?:-(?<suffix>\d{4}))?" />
 
We can also use this same logic in the code-behind or levels below by using the Regex class in the System.Text.RegularExpressions namespace.  Below is an example of the zip code validation:
 
if(!Regex.IsMatch(txtZipCode.Text, "(?<zip>\d{5})(?:-(?<suffix>\d{4}))?"))
{
     // Handle error
}
 
Regular expressions can be valuable tools in removing undesired input as well.  For example, we may want to remove special characters that can be used especially with SQL injection attacks such as the double-hyphen.  It can also be used to constrain the length of a field as well.
 
Use typed parameters for data access commands
 
Many applications using SQL Server have switched to stored procedures and with good reason.  This can cut down on the attack surface for SQL Injection attacks.  There are caveats to this of course, but let's review how stored procedures can help.
 
First off, let's look at an example of a simple stored procedure and how we can prevent SQL Injection attacks this way:
 
SqlCommand command = new SqlCommand("usp_GetEmployeeByEmployeeId");
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@EmployeeId", SqlDbType.Int);
command.Parameters["@EmployeeId"].Value = txtEmployeeId.Text;
 
What we did above is to constrain the user input so that it must be an integer instead of just a string that could contain malicious data.  We can also use the parameters to check for length as well for string data, such as a social security number parameter can only contain 11 characters which includes the dashes.
 
Now does this mean by using stored procedues that we will be insulated against bad data?  Not necessarily, especially if there is dynamic SQL inside the stored procedure. 
 
Below is an example of what not to do inside a stored procedure:
 
CREATE PROCEDURE dbo.usp_GetEmployeeByLastName
@LastName NTEXT
AS
DECLARE @SQL VARCHAR(4000)
SET @SQL = 'SELECT * FROM Employees WHERE LastName = ''' + @LastName + ''''
EXEC(@SQL)
GO
What should be done instead is:
CREATE PROCEDURE dbo.usp_GetEmployeeByLastName
@LastName VARCHAR(35)
AS
SELECT *
FROM Employees
WHERE LastName = @LastName
GO
 
The above stored procedure reduces the risk from the bad example.
 
Use a least privileged account for data access
 
Another best practice when it comes to data access is to use the least privileged account as possible for data access.  Grant only the smallest level of access possible with your user account, such as public, and execute privileges on the stored procedures that this user absolutely needs if using solely stored procedures.  If you are also allowing dynamic sql, allow only the smallest amount of privileges needed.
 
Trap exceptions and hide SQL errors from the user
 
In order to prevent potentially sensitive information being displayed to the user, use structured error handling.  This includes logging the error, but showing a user friendly custom message to the user.  The user should never see a raw exception message nor stacktrace from your application.  This can show the attacker much information about your database and its structure.
 
So, as you can see, we need to be vigilant when it comes to data access and following best practices. 
 
Here is an additional resources to look at regarding SQL Injections:
 
 
 
Posted on Thursday, May 25, 2006 2:46 PM Microsoft , .NET , C# , ASP.NET | Back to top


Comments on this post: .NET Code Access Security - Understanding and Preventing SQL Injection

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Matthew Podwysocki | Powered by: GeeksWithBlogs.net