Defining the Database Connection

The Database Management System (DBMS) was one of the first applications developed for computer systems once computer technology moved beyond calculations. A DBMS provides the management capability, accessibility, and security required to handle large quantities of data. Databases (the containers that hold the data managed by the DBMS and accessed by the caller) serve many purposes when it comes to Web applications. The most common use of the database now is as a means of serving up current data for a requestor. Instead of seeing the same static report that you saw yesterday, you could now see the latest report with the most current data. Many business Web sites make use of database functionality for this purpose—at least where they see a need for dynamic data display.

A database can also act as input to a program that serves up the Web pages. Instead of forcing everyone who contributes to a Web site to write the required code, the author provides the content in the form of a database record. The program reads the record and creates the page based on the content of that record. Instead of a server directory full of Web pages, the developer ends up with a single template page and a lot of content. In theory, such a strategy reduces the space requirements for the Web site and makes it easier to maintain.

The following sections discuss some of the special considerations of making database access secure when working with .NET applications over the Internet. This isn’t a complete primer of database strategies, but it does discuss the main Web-based application security issues in detail.

Note

This book focuses on security issues. You can learn about general database techniques from my book Visual C# .NET Developer’s Handbook (Sybex, 2002), Visual Basic .NET Developer’s Handbook by Evangelos Petroutsos and Mark Ridgeway (Sybex 2003), and Mastering C# Database Programming by Jason Price (Sybex, 2003).

Securing the DBMS

One of the problems with creating a Web application using a desktop mentality is that you can fall into logic errors. For example, when a user accesses a database from the desktop, they have already logged into the server. Consequently, some administrators use just the security provided with the DBMS, which often equates to the database administrator (DBA) and everyone else. In other words, the administrator often protects the database by relying on the server’s security, rather than using the server built into the DBMS itself. Newer versions of products such as SQL Server can use Windows authentication, rather than a separate username and password, which can make the situation worse.

The problem with this strategy is that it can fall short when a developer exposes the database to the Internet through an application. Now, the component that the developer uses to exchange information is the logged in element—the entity logged into Windows for access purposes. Unless the DBMS provides additional security, a cracker could potentially gain access to the database through the component using the component’s credentials. Unfortunately, this kind of exploit has already occurred with devastating results. The InformIT article at http://www.informit.com/isapi/product_id~%7B9AF62809-408D-401A-9144-B0A695B72424%7D/content/index.asp describes common SQL Server exploits that rely on SQL language deficiencies and security holes. For another example of how this exploit can occur, read the Wired article at http://www.wired.com/news/infostructure/0,1377,57897,00.html.

Some application developers simply hard code the access information for SQL Server directly into the component or application. This technique is the same as leaving the DBMS without any protection at all because the protection is effectively disabled. The first input you should ask of any caller seeking access to the database is their credentials. Only allow access to the database once the DBMS verifies the credentials independently of any access built into your component for internal needs. Because of the nature of Web-based applications, you need to set up a cookie or other means of determining when a user makes a second request. Otherwise, you would need to ask the user for their credentials for every access—something that most users would find less than appealing.

You learned about the Internet Information Server (IIS) Lockdown tool in the “Using the IIS Lockdown Tool” section of Chapter 9. One of the screens you won’t see unless you choose the View Template Settings option is Additional Security as shown in Figure 10.1. The Microsoft Advanced Data Connector (MSADC) works with Remote Data Services (RDS) to provide database support for remote clients. RDS is an interesting technology because it places part of the burden of processing database requests on the client through RDS client components. However, the technology also opens a huge security hole and probably doesn’t offer enough of a performance boost to make securing the technology worth the effort. Consequently, most Web-based applications that provide database access don’t use the RDS technology. Unless you specifically use RDS in an application, you should check the MSADC option on the Additional Security dialog box shown in Figure 10.1. You can learn more about this technology at http://msdn.microsoft.com/library/en-us/iisref/htm/RemoteDataBindingwithRemoteDataService.asp.

Figure 10.1: Ensure you eliminate the MSADC folder on your server unless you actually need it.

Unfortunately, there’s little you can do about some exploits except ensure your copy of SQL Server includes all of the required patches. For example, the Slammer virus (see the CERT advisory at http://www.cert.org/advisories/CA-2003-04.html for details) is a problem with SQL Server itself and not a worm you can prevent through configuration alone. The fact that Slammer embarrassed Microsoft because some of the DBAs on their staff didn’t install the required patch shows that securing the DBMS is a task that everyone has to perform. According to a CNET article (http://news.com.com/2100-1001-982305.html), Microsoft failed to install the patches required to keep Slammer at bay.

Developing a Database Application

One of the most common exploits for any Web application that relies on forms is to trick the form into doing something the originator didn’t envision. The “Preventing Data Entry Errors” section of Chapter 3 discusses the need to verify and validate data entries before you use them. However, unlike a desktop application, Web applications often prove difficult to monitor. The reasons include the use of text for all entries, the lack of strong data typing, and the problems of forcing scripts to perform tasks normally associated with full-fledged programming languages. Fortunately, ASP.NET provides validators to make it easier to maintain security by ensuring the user can only input values that you want to use.

Note

This example relies on the ExistingMovies database. The \Chapter 10\MyData folder located on the Sybex Web site contains scripts for creating the ExistingMovies database and importing data for it. These scripts assume that you have a D drive with a MyData folder on it. If you want to use some other drive and folder, you’ll need to modify these entries in the scripts. Use SQL Query Analyzer or a similar tool to run the MovieGuide.SQL script first, and the ImportRecords.SQL script second. The MovieGuideData.DAT and MovieGuideData.FMT files must appear in the folder defined in the MovieGuide.SQL script or SQL Server will complain that it can’t find the files.

An Overview of Validators

Chapter 3 shows techniques for validating user input when working with a desktop application. In general, you’ll use inline code to perform the task using any of a number of simple procedures. Validating Web applications isn’t quite so easy, so Microsoft provides validators. A validator is a special control that ensures the user enters the right type of data and in the proper range. Each kind of validator performs a specific check, so you might use several validators together to provide full data entry coverage for your application.

Although validators vary in functionality, all validators require an error message found in the Text property that the user will see and use to correct the error on the Web page. For example, when a user types a value that’s outside the correct range, the RangeValidator displays the error message in the Text property. In addition, all validators provide a ControlToValidate property that you use to associate the control with the validator. (Some validators might require two controls as input.) Visual Studio .NET supports several validator types, but here are the four types you’ll commonly use for applications.

CompareValidator The CompareValidator accepts two controls as input and then compares the value of each control. If the two controls don’t match the condition you specify, the CompareValidator displays an error message. The name of the second control appears in the ControlToCompare property. The Operator property defines the comparison between the two controls. For example, if you choose the GreaterThan option, the value of the control listed in the ControlToValidate property must be greater than the value of the control listed in the ControlToCompare property. A Type property ensures the second control contains data of the correct type, but this is almost superfluous because the two controls won’t compare if their types don’t match.

RangeValidator The RangeValidator ensures the input in a control falls within a range of values. The MinimumValue and MaximumValue properties contain the limit of values the user can input. You’ll use the Type property to determine what type of data the control will accept. The RangeValidator accepts common types including string, integer, double, date, and currency. If the input doesn’t fall within the selected range of values or is of the wrong type, the control will display an error message.

RegularExpressionValidator The RegularExpressionValidator uses an expression consisting of a specially formatted string that defines the kind of input required to validate the content or format of the input. For example, you could use an expression that evaluates input for all numeric entries or all character entries. You’ll find that the Microsoft help topics tend to focus on the format of the expression, as do the built-in expressions. However, the example in this section will show you how to build an expression that defines the content of the expression. The expression used for comparison with the input of the target control appears in the ValidationExpression property. Click the ellipses in this property to display the Regular Expression Editor dialog box shown in Figure 10.2.

Figure 10.2: The Regular Expression Editor helps you choose a predefined expression or create one of your own.

RequiredFieldValidator This is the easiest of validators to understand. If the target control is blank, the validator displays an error message. Some developers will use an asterisk in place of the error message and simply display one error message for all required fields. However, the use of a custom error message for each controls means that you can provide example input for each field.

Notice that I haven’t mentioned any need for application code. All four validators perform their job without any coding on your part. The only work you need to perform is configuring the validator as described in the list. The validator performs the rest of the work for you at that point. (See the example in the “Building the Validated Database Application” section for further details.)

All of the validators provide client-side support. This feature will force the client to fix any errors in the form before the browser will send it to the server. Using a validator means your server will have to react to fewer poorly formatted messages and will work more efficiently. Of course, validators can only check for specific problems.

You can use multiple validators on one field to ensure the application detects as many problems as possible. In addition, the CustomValidator enables you to create special validators that can react to some unique conditions. Unfortunately, the CustomValidator requires you create code to make it functional, which makes a CustomValidator the custom programming solution for special situations.

Implementing the Security Setup

The “Using the IIS Lockdown Tool” section of Chapter 9 discusses the IIS Lockdown Tool. This too really can save your server from prying eyes. However, it can also cause a great deal of confusion if you don’t fully understand the implications of what this tool does. Once you lock down your server, you’ll find that some programs will suddenly experience errors and some won’t work at all. Database applications tend to fall in the latter category. In fact, if you see the mysterious message shown in Figure 10.3, you’re the victim of an IIS Lockdown Tool generated error—or, more precisely, you’re the victim of a configuration error that you didn’t know about.

Figure 10.3: IIS errors such as this one become common after an IIS Lockdown Tool session.

None of the documentation you’ll find explains this problem and there’s even a dearth of information on the topic on the Internet. At the time of this writing, I found seven obscure references to the problem using Google. You need to perform three steps to fix this problem.

1. Add one or more groups to your security setup using a utility such as the Active Directory Users and Computers console. The example in the “Building the Validated Database Application” section relies on four security groups (each of which offers a higher level of security than the previous group in the list): MovieViewer (read-only), MovieUser (read/write), MovieSupervisor (read/write, insert, and delete), and MovieOwner (all rights).

2. Assign each user that will access the database application to one of the groups you’ve just created. Again, you’ll need to use a utility such as the Active Directory Users and Computers console. You must include the IWAM group in this list. I suggest assigning the IWAM group the minimum possible rights to ensure the database remains safe. Web Application Management Interface (IWAM) is defined in the next section, “Building the Validated Database Application.”

3. Use the SQL Server Enterprise Manager to add these groups to the target database (MovieGuide for this example). The scripts used to create the database don’t include this step—it’s something you need to perform for your server. Simply locate the Users folder shown in Figure 10.4 and add the groups as users of the database.

   
   Figure 10.4: Make certain you add the groups you create to SQL Server.

The IWAM group is important because it lets IIS launch processes, sometimes anonymously. That’s why you have to give this group some rights, but you also want to keep those rights to a minimum.

Warning

IIS loads and caches your application in most cases. Caching seems to affect security as well. If you want to see the true effects of a security change, close all copies of Internet Explorer and restart IIS. In addition, you might also need to restart the local machine to see a change in local settings (logging out and back in is usually enough, but might not clear memory and could cause the error to return).

Building the Validated Database Application

This example includes several unique security features. First, it uses validators to ensure the user can only enter the correct data. Second, the application uses role-based security to determine the availability of features. Combined, these two features make it difficult for a cracker to gain access to your system.

Note

The Web examples in this book always come in two parts. The first part contains the code that you should place on your development machine. The second part has (Server) added to the folder name. Place this code on your Web server. For example, the server code for this example appears in the \Chapter 10\C#\DataBase (Server) and \Chapter 10\VB\DataBase (Server) folders of the source code located on the Sybex Web site. Move this code to your server and remove the (Server) part of the folder name, so the folder name now matches the name of the folder on your development machine. You’ll also need to modify the project file using a pure text editor such as Notepad. Open the Database.SLN (or other) project file and you’ll notice a Web site reference near the top. Change this Web site reference to match the URL on your development system.

The first part of this example shows how to use attributes and demonstrates role-based programming techniques that modify the appearance of output. Listing 10.1 contains the Web page rendering code for this program. This listing isn’t complete—it only shows the security-related code for the example. You’ll find the complete source for this example in the \Chapter 10\C#\DataBase and \Chapter 10\VB\DataBase folders of the source code located on the Sybex Web site.

Listing 10.1 Using Role-Based Security to Modify Page Rendering

[SqlClientPermissionAttribute(SecurityAction.Demand,     ConnectionString = "integrated security=SSPI;" +                        "data source=WINSERVER;" +                        "persist security info=True;" +                        "initial catalog=MovieGuide")] private void DisplayData() {    DataRow     CurRow;           // Current database row.    Object      CurColumn;        // Current column in the row.    HttpCookie  RowCookie;        // Determines the current record.    Int32       RowCookieValue;   // The numeric value of the cookie.    if (HttpContext.Current.User.IsInRole("DataCon\\MovieOwner"))    {       // Enable new buttons and fields.       btnUpdate.Visible = true;       btnAdd.Visible = true;       btnDelete.Visible = true;       btnRunScript.Visible = true;       Label12.Visible = true;       Label13.Visible = true;       Label14.Visible = true;       txtIsIn.Visible = true;       txtFormat.Visible = true;       txtDatePurchased.Visible = true;       // Check the password.       CheckPassword();    }    if (HttpContext.Current.User.IsInRole("DataCon\\MovieSupervisor"))    {       // Enable new buttons and fields.       btnUpdate.Visible = true;       btnAdd.Visible = true;       btnDelete.Visible = true;       Label12.Visible = true;       Label13.Visible = true;       txtIsIn.Visible = true;       txtFormat.Visible = true;       // Check the password.       CheckPassword();    }    if (HttpContext.Current.User.IsInRole("DataCon\\MovieUser"))    {       // Enable new buttons and fields.       btnUpdate.Visible = true;       Label12.Visible = true;       Label13.Visible = true;       txtIsIn.Visible = true;       txtFormat.Visible = true;       // Check the password.       CheckPassword();    }    // Fill the dataset with data.    sqlDataAdapter1.Fill(dataSet11);    // Determine whether there is a cookie to use.    if (Request.Cookies["RowNumber"] == null)       // If not, set the row to 0.       CurRow = dataSet11.ExistingMovies.Rows[0];    else    {       // Set the row value according to the cookie setting.       RowCookie = Request.Cookies["RowNumber"];       RowCookieValue = Int32.Parse(RowCookie.Value);       CurRow = dataSet11.ExistingMovies.Rows[RowCookieValue];    }    // Display the individual fields.    CurColumn = CurRow["InventoryID"];    txtInventoryID.Text = CurColumn.ToString(); ... Lots of other Fields ...    CurColumn = CurRow["DatePurchased"];    txtDatePurchased.Text = CurColumn.ToString(); }

One of the security problems you’ll discover when working with SQL Server is that the system doesn’t have the required permission. Some applications will perform a lot of work before they discover this fact. The example uses the [SqlClientPermissionAttribute] attribute to avoid this problem. The program will generate an exception and send an error message to the user before it performs any significant work. Notice that the attribute demands that the application user have the right to use a particular connection string. The ConnectionString argument is the same one that the sqlConnection1 control uses, so this permission determines that the caller has the required minimum rights.

The DisplayData() method begins by performing a series of role-based security checks. Many developers assume that role-based security only fulfills permission needs. However, this example demonstrates that role-based security affects every aspect of a program. In this case, the code uses role-based security to determine how ASP.NET renders the display. Callers with more rights see additional buttons (to perform additional tasks) and a larger number of data fields. The code also calls the CheckPassword() method, which appears in Listing 10.2 (discussed later in this section). Note that these rendering changes take place before ASP.NET creates the Web page code, so the caller with fewer privileges doesn’t even know the other options exist.

Only after the code checks the user’s security does it fill the sqlDataAdapter1 control with data. The data adapter contains the entire recordset, rather than just a single record. The code relies on the RowNumber cookie to provide the record it should display. If this cookie doesn’t appear, the code uses a default value. This code points out an important security consideration for Web applications—try to provide a default value whenever possible so your code can’t fail. The remaining code displays the data on screen.

One of the problems with the Security Support Provider Interface (SSPI) is that it can be extremely difficult and frustrating to work with. When a caller requests a Web page from IIS, it uses the credentials the user provides to perform any in-process tasks such as serving up the Web page, which is why the code in Listing 10.1 works as it does. The problem with IIS is that it runs every out-of-process server (including applications) using the Web Application Management Interface (IWAM) account. This account has the server name attached, so on my server it appears as IWAM_WINSERVER. The term IWAM comes from the IWAMAdmin interface that Visual C++ developers use to interact with IIS. The point is that this account should have minimal rights, so when the application calls SQL Server with integrated security=SSPI in the connection string, IIS sends the credentials for the IWAM account, which might not even be enabled after you run the IIS Lockdown Tool. Consequently, the application fails to perform as anticipated.

Tip

The Microsoft documentation has a general lack of SSPI information and programming examples. One of the better places to find SSPI specific code is Keith’s Security Sample Gallery (http://www.develop.com/kbrown/security/samples.htm). This site has a number of interesting tools you can use to make your SSPI experience easier. Of special importance is the SSPI Workbench—a tool you can use to explore SSPI authentication.

Listing 10.2 shows one way to overcome this problem. This solution has risks because someone could discover passwords if they poke around in memory. However, the solution doesn’t expose any passwords on disk, which is where most crackers will look for information. Note that this portion of the example relies on SSL for encryption, so you must set up your server to use SSL. See the “Adding SSL Support to a Server” section for details.

Listing 10.2 Setting the SQL Server Password

private void CheckPassword() {    HttpCookie  ReturnCookie;     // Return URL for this page.    String      PasswordUrl;      // URL of the Password screen.    String      UserName;         // The user’s name.    String      UserPassword;     // The user’s password.    // Create a return cookie.    ReturnCookie = new HttpCookie("ReturnUrl",       "http://" + this.Server.MachineName + "/" +       this.TemplateSourceDirectory + "/" + "Default.ASPX");    Response.AppendCookie(ReturnCookie);    if (Request.Cookies["UserName"] == null)    {       // Display the password screen.       PasswordUrl = "https://" + this.Server.MachineName + "/" +          this.TemplateSourceDirectory + "/" + "Password.ASPX";       Response.Redirect(PasswordUrl, true);    }    else    {       // Get the name and password.       UserName = Request.Cookies["UserName"].Value.ToString();       UserPassword = Request.Cookies["UserPassword"].Value.ToString();             // Create a new connection string for the database.       sqlConnection1.ConnectionString =          "packet size=4096;integrated security=false;" +          "data source=WINSERVER;persist security info=True;" +          "initial catalog=MovieGuide;User ;Password=" + UserPassword;    } }

The example begins by creating a “call home” cookie in the form of ReturnUrl. Some developers create a separate login screen for every purpose. Developing a good login screen that supports the call home cookie saves time and makes it easier to perform security updates. Notice again that the code provides separate paths for times when the cookie exists and when it doesn’t. In this case, the code calls on the Password Web page described in Listing 10.3 when the cookie doesn’t exist to allow the user to log into the system.

When the cookie does exist, the code uses it to create a new sqlConnection1 .ConnectionString value. This new string doesn’t rely on SSPI. Instead, it turns off the integrated security and relies on a username and password instead. This solution exposes the username and password in memory, so this solution isn’t as safe as using integrated security in one respect because the information could become available to crackers. However, because the average caller will use the integrated security and the example sets integrated security with minimal rights, this solution is actually safer than giving everyone equal access to SQL Server. In addition, by using a different username and password for SQL Server, the caller would need to know more information to gain the required access.

The Password Web page shown in Figure 10.5 demonstrates use of validators. If you allow the user to perform any kind of data entry on a Web page, you should include validators as well. In this case, each field uses two validators. The upper validator in both cases is a RegularExpressionValidator, while the lower validator is a RequiredFieldValidator.

Figure 10.5: Add validators to any Web page that requires user input of any kind.

The RegularExpressionValidator requires a ValidationExpression property entry to define how to validate the field entry. The txtName field is relatively easy because it only requires a name of a particular length. The ValidatorExpression entry in this case is [ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]{3,15}. The user can enter any name that is 3 to 15 characters long. The name can’t contain anything but letters.

It’s possible to get too explicit when you create a ValidatorExpression entry. For example, if you normally use two words separated by a special symbol or a number for passwords, you might be tempted to create a validator like this one (note that even though this expression appears on multiple lines in the book, it actually appears on one line in your code):

[ABCDEFGHIJKLMNOPQRSTUVWXYZ]{5,10}[1234567890!#$%%^&]{1}[ABCDEFGHIJKLMNOPQRSTUV WXYZ]{5,10}

This ValidatorExpression entry defines a password that contains a word, followed by a special symbol, followed by a word. The problem is that this entry gives the cracker too much information. Remember not to volunteer information the cracker could use against you. The actual ValidatorExpression entry for this example looks like this:

[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!#$%%^&]{5,20}

Although this ValidatorExpression entry is less specific, it prevents the user from entering a long string or using special symbols that crackers commonly use for exploits. The best part about using this form of protection is that, other than the validator expression, you don’t have to perform any special coding.

Listing 10.3 shows the Password Web page code for this example. All that the Web page really needs to do is package the name and password, and then return control to the caller.

Listing 10.3 Getting the Password

private void btnEnter_Click(object sender, System.EventArgs e) {    HttpCookie  UserName;      // User name cookie.    HttpCookie  UserPassword;  // User password cookie.    String      ReturnUrl;     // Return location.    // Store the cookies.    UserName = new HttpCookie("UserName", txtName.Text);    Response.AppendCookie(UserName);    UserPassword = new HttpCookie("UserPassword", txtPassword.Text);    Response.AppendCookie(UserPassword);    // Return to the original location.    if (Request.Cookies["ReturnUrl"] != null)    {       ReturnUrl = Request.Cookies["ReturnUrl"].Value.ToString();       Response.Redirect(ReturnUrl);    } }

The code begins by creating cookies to package the information. Because the caller uses SSL to request this page, you don’t need to worry about the cracker learning about the content of the cookies while the information is in transit.

After the code packages the cookies, it requests the return URL from the ReturnUrl cookie. This is one situation where it would be difficult to provide a secondary course of action if the caller failed to provide the ReturnUrl cookie. The problem is that you can’t return control anywhere without potentially creating a security hole. Consequently, the page doesn’t return anywhere if the ReturnUrl cookie is missing.