Accessing the Database

Now that you have set up everything you need for the Web site, you need to code some functionality for accessing not only the registry, but the database as well. To do this, you need to do four things:

1. Create a class to access the registry and retrieve your database settings.

2. Create a class to verify a user's identity.

3. Deploy the component to your "internal" web server (in other words, a server that doesn't sit right on the firewall).

4. Connect to this component.

Accessing the Registry

Open the NorthwindTraders project (from Chapter 10, "Using Reflection," because you need to edit the data-centric objects) and add a new class to the NorthwindDC project called RegReader. This class is going to be simple—it creates the connection string that will be used by each of the classes that currently reads the appSettings section of the configuration file.

Note

You could create this as a separate, reusable object, but you are not going to in the interests of simplicity. You are just going to add the functionality to validate a username/password combination in your already existing objects.

To do this you will create a simple shared object that reads the information from the registry key and stores it in two variables. It will then be the responsibility of this class to create the connection string to pass to the different classes.

Note

This change of course requires you to perform a search and replace on your code that is not perfect in an ideal world. One method around this would be to create a class that actually opens up a connection and returns a connection to the calling code. The only problem with this solution is that every class has to instantiate this object and destroy it every time—on top of declaring and destroying the connection object. This was a design choice on my part, but if you know that you will be changing the application to allow access to users who are not trusted, then you can make this decision up front.

Before you write the code for this, let's change the Northwind_DSN key in the configuration file by removing the Trusted_Connection=Yes; part of the string so that it looks like the following:

 value="server=localhost;Initial Catalog=Northwind" 

Add the code for the RegReader class (shown in Listing 12-3) to the RegReader code module (after deleting the existing class code).

Listing 12-3: The RegReader Class

 Option Explicit On Option Strict On Imports Microsoft.Win32 Imports System.Configuration Public Class RegReader     Private Shared UserName As String     Private Shared Password As String     Shared Sub New()         Dim regKey As RegistryKey         regKey = _         Registry.LocalMachine.OpenSubKey("SOFTWARE\\NorthwindTraders\\dbLogon", _         False)         UserName = CType(regKey.GetValue("username"), String)         Password = CType(regKey.GetValue("password"), String)     End Sub     Public Shared Function getConnString() As String        Dim strCN As String = ConfigurationSettings.AppSettings("Northwind_DSN")        Dim strID As String = "u;pwd=" & Password & ";"        Return strID & strCN    End Function End Class 

This code is fairly straightforward. It is shared, so it is only instantiated once. The read from the registry is performed on instantiation (and it is very fast), and the values are stored in two shared variables. Finally, the getConnString returns the connection string based partially on the values in the registry and partially on the string in the configuration setting. That way it is still easy to change the database location/catalog, but it is more difficult to get at the username and password values.

The last thing you need to do is replace the calls to the AppSettings method with a call to the getConnString method. To do this, go to Edit Find and Replace Replace in Files. You will need to find the following code:

 ConfigurationSettings.AppSettings("Northwind_DSN") 

and replace it with RegReader.getConnString. The only problem you will encounter is that the find and replace also replaces the configuration statement in the RegReader class, so you will have to put it back in after the find and replace.

At this point you can rebuild the application and re-deploy the data-centric objects, and the application should work as advertised. However, you still have one more little thing you need to take care of…adding and validating users.

Adding and Validating Users

You need to build the code to access the specific stored procedures that you need to call in your data-centric objects. Add a new class to the NorthwindDC project called WebUserDC. You are going to create two simple methods to add and validate users to your Web site. Set up the code module by importing the System.Data.sqlClient namespace and then add the code for the ValidateUser method as shown in Listing 12-4.

Listing 12-4: The ValidateUser Method

 Public Function ValidateUser(ByVal strUser As String, _ ByVal strPass As String) As Boolean      Dim strCN As String = RegReader.getConnString      Dim cn As New SqlConnection(strCN)      Dim cmd As New SqlCommand      Dim intValid As Integer      cn.Open()      With cmd          .Connection = cn          .CommandType = CommandType.StoredProcedure          .CommandText = "usp_ValidateUser"          .Parameters.Add("@uid", strUser)          .Parameters.Add("@pwd", strPass)          .Parameters.Add("@valid", Nothing).Direction = ParameterDirection.Output          .ExecuteNonQuery()          intValid = Convert.ToInt32(.Parameters("@valid").Value)      End With      cmd = Nothing      cn.Close()      If intValid = 1 Then         Return True      Else         Return False      End If End Function 

The ValidateUser method is simple in that it takes a username and a password hash, compares them against a value in the database, and returns a 1 if the values matched a record and 0 if they did not. You are simply returning a boolean indicating whether they were validated.

The AddUser method is also simple in that it takes a username and password hash and stores it in the database. Add this method to the WebUserDC class as shown in Listing 12-5.

Listing 12-5: The AddUser Method

 Public Function AddUser(ByVal strUser As String, _ ByVal strPass As String) As Boolean      Dim strCN As String = RegReader.getConnString      Dim cn As New SqlConnection(strCN)      Dim cmd As New SqlCommand      Dim intValid As Integer      Try           cn.Open()           With cmd                .Connection = cn                .CommandType = CommandType.StoredProcedure                .CommandText = "usp_AddNewUser"                .Parameters.Add("@uid", strUser)                .Parameters.Add("@pwd", strPass)                .ExecuteNonQuery()           End With           cmd = Nothing           cn.Close()           Return True           Catch               Return False           End Try End Function 

Now that you have set up your application to validate a user, you need to modify your Web service so that it can make these calls against your objects. Before you do that, rebuild and re-deploy the NorthwindDC assembly to the bin folder so that your Web service can reference your new methods.

Caution

I have shown you a way to secure the credentials to access the database and you have already seen that ASP.NET will write out an authentication cookie to the user's machine. But there is one thing I have not discussed—the "replay" attack. This is when a hacker basically "records" your session (including the authentication cookie) and then replays it back to the server, giving them access to everything to which you have access. Secure Sockets Layer (SSL) is an excellent means of defending against this type of attack. For more information on setting up a secure Web site and links to other information, visit http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/openhack.asp.

Updating the Web Service

This is a fairly simple process, so the code will not be explained. Open the Web service project you created in the previous chapter and add the two methods shown in Listing 12-6.

Listing 12-6: The Web Service Implementation of the Security Functions

 <WebMethod()> Public Function ValidateUser(ByVal strUser As String, _ ByVal strPass As String) As Boolean         Dim objWebUser As New NorthwindTraders.NorthwindDC.WebUserDC         Dim blnValid As Boolean = objWebUser.ValidateUser(strUser, strPass)         objWebUser = Nothing         Return blnValid End Function <WebMethod()> Public Function AddUser(ByVal strUser As String, _ ByVal strPass As String) As Boolean         Dim objWebUser As New NorthwindTraders.NorthwindDC.WebUserDC         Dim blnValid As Boolean = objWebUser.AddUser(strUser, strPass)         objWebUser = Nothing         Return blnValid End Function 

Remember to rebuild the project! Once you are done with this, you are ready to build your Web interface.