Data Security Tips

Chapter 9 - Data-Driven ASP.NET Applications in the Real World
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

It's critical that you prevent unauthorized users from accessing the information in your database. Hackers are constantly trying to find ways to steal data from online data sources, or simply to foul up systems in any way possible for the joy of it. There are books written on the subject of security in reference to web applications, and you should study the many techniques thoroughly. In this section, we're going to look at just one example of how a hacker could abuse poor programming techniques to control your data source in a way you never expected. Also, we'll point out the need to secure the 'sa' account, and to use accounts that have only the privileges they need for the tasks they are to perform on behalf of the application for which they're used.

Use Querystring Values Cautiously

Passing querystring values that will be used within SQL statements can be extremely dangerous. SQL allows you to append multiple statements together for execution by separating them with semicolons. This means that a malicious hacker can do the same, depending on how your code is constructed.

Try It Out - Hacking Querystring Values

Create the page below; it's called Read_Employee.aspx.

    <%@ Import namespace="system.data" %>    <%@ Import namespace="system.data.sqlclient" %>    <script language="vb" runat="server">    Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)      Dim sql As String = "SELECT * FROM Employees WHERE EmployeeID = " & _                           Request.QueryString("EmployeeID")      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")      Dim conn As New SqlConnection(strConnection)      conn.Open()      Dim cmd As New SqlCommand(sql, conn)      Response.Write("SQL: " & sql & "<br/>")      Dim dr As SqlDataReader      dr = cmd.ExecuteReader()      Do While dr.Read()        Response.Write(dr("FirstName").ToString & " " & dr("LastName").ToString)      Loop      conn.Close()    End Sub    </script> 

What we're doing in this code is passing an employee's ID number, and returning the employee's name. We do this by appending the EmployeeID querystring value to the SQL query in the statement shown below:

      Dim sql As String = "SELECT * FROM Employees WHERE EmployeeID = " & _                           Request.QueryString("EmployeeID") 

Let's see an example of this when it's run as it's supposed to be run. The URL on our server is http://localhost/BegASPNETdb/ch09/Read_Employee.aspx?EmployeeID=5. This returns the following result, which may be different for you, depending on the state of your sample database.

Now let's look at an example of a malicious querystring value:

    http://localhost/BegASPNETdb/ch09/Read_Employee.aspx?EmployeeID=6;INSERT+INTO+E mployees(FirstName,LastName)+VALUES+('Tom','Hacker') 

Here are the results:

Unbeknown to you, a hacker just successfully inserted a bogus record into your database! There are two ways around this vulnerability. The first is to use a stored procedure to return the data you need. Here's an example.

    CREATE PROCEDURE ReadEmployee(@EmployeeID as integer) AS    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID 

This prevents your code from being used to pass any extraneous information to the SQL Server, since the SQL Server will return an error if the passed value isn't an integer.

The second way, if you still want to generate the SQL statement within your code dynamically, is to use a parameter. Here's how we'd change the above example to do this.

    <%@ Import namespace="system.data" %>    <%@ Import namespace="system.data.sqlclient" %>    <script language="vb" runat="server">    Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)      Dim EmployeeID As Integer      Dim sql As String = "SELECT * FROM Employees WHERE EmployeeID = @EmployeeID"      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")      Dim conn As New SqlConnection(strConnection)      conn.Open()      Dim cmd As New SqlCommand(sql, conn)      EmployeeID = CInt(Request.Querystring("EmployeeID"))      cmd.Parameters.Add("@EmployeeID", EmployeeID)      Response.Write("SQL: " & sql & "<br/>")      Dim dr As SqlDataReader      dr = cmd.ExecuteReader()      Do While dr.Read()        Response.Write(dr("FirstName").ToString & " " & dr("LastName").ToString)      Loop      conn.Close()    End Sub    </script> 

Now, an attempt to pass anything other than an integer will cause an error in the statement that converts the querystring value to an integer:

      EmployeeID = CInt(Request.querystring("EmployeeID")) 

Even if the hacker could somehow get past this, SQL would cause an error if the parameter's value weren't numeric.

Don't use the System Administrator (sa) Account

It shouldn't need saying again, but time and again we come across people using the 'sa' account for database access from their web applications. Don't do it! If a hacker finds a way to execute unauthorized SQL through your application, using the 'sa' user is like confronting a robber that's broken into your house and giving them the keys to the safe and the car in the garage as well.

Note 

Make sure that your 'sa' account doesn't have a blank password, and make sure that the password it does have is difficult to guess.

Create Multiple SQL Users Depending on your Needs

If you have an application that your employees use, and you're also creating other applications that use the same data for different user populations, such as vendors and customers, don't use the same SQL user. Create a separate user for each user population, and give them only the rights they need. In many projects, you'll have an intranet site and an extranet site that makes a subset of the intranet site's data available. In a case like this, create two users, one called something like Intranet_App_User, and the other called Extranet_App_User.

These are tips from the real world. Hopefully, they'll spur you to keep security in the forefront of your thoughts as you develop your own web applications.