Building a Secure Data Access Component

The following code shows a sample implementation of a CheckProductStockLevel method used to query a products database for stock quantity. The code illustrates a number of the important security features for data access code introduced earlier in this chapter.

 using System; using System.Data; using System.Data.SqlClient; using System.Text.RegularExpressions; using System.Collections.Specialized; using Microsoft.Win32; using DataProtection;     public static int CheckProductStockLevel(string productCode) {   int quantity = 0;   // (1) Code protected by try/catch block   try   {     // (2) Input validated with regular expression     //     Error messages should be retrieved from the resource assembly to help     //     localization. The Localization code is omitted for the sake of brevity.     if (Regex.IsMatch(productCode, "^[A-Za-z0-9]{12}$") == false)       throw new ArgumentException("Invalid product code" );     //(3) The using statement ensures that the connection is closed     using (SqlConnection conn = new SqlConnection(GetConnectionString()))     {       // (4) Use of parameterized stored procedures is a countermeasure for       //     SQL injection attacks       SqlCommand cmd = new SqlCommand("spCheckProduct", conn);       cmd.CommandType = CommandType.StoredProcedure;           // Parameters are type checked       SqlParameter parm =                cmd.Parameters.Add("@ProductCode",                                   SqlDbType.VarChar,12);       parm.Value = productCode;       // Define the output parameter       SqlParameter retparm = cmd.Parameters.Add("@quantity", SqlDbType.Int);       retparm.Direction = ParameterDirection.Output;       conn.Open();       cmd.ExecuteNonQuery();       quantity = (int)retparm.Value;     }   }   catch (SqlException sqlex)   {     // (5) Full exception details are logged. Generic (safe) error message     //     is thrown back to the caller based on the SQL error code     //     Log and error identification code has been omitted for clarity     throw new Exception("Error Processing Request");   }   catch (Exception ex)   {     // Log full exception details     throw new Exception("Error Processing Request");   }   return quantity; }     // (6) Encrypted database connection string is held in the registry private static string GetConnectionString() {   // Retrieve the cipher text from the registry; the process account must be   // granted Read access by the key's ACL   string encryptedString = (string)Registry.LocalMachine.OpenSubKey(                                         @"Software\OrderProcessing\")                                         .GetValue("ConnectionString");   // Use the managed DPAPI helper library to decrypt the string   DataProtector dp = new DataProtector(DataProtector.Store.USE_MACHINE_STORE);   byte[] dataToDecrypt = Convert.FromBase64String(encryptedString);   return Encoding.ASCII.GetString(dp.Decrypt(dataToDecrypt,null)); } 

The code shown above exhibits the following security characteristics (identified by the numbers in the comment lines).

1. The data access code is placed inside a try/catch block . This is essential to prevent the return of system level information to the caller in the event of an exception. The calling ASP.NET Web application or Web service might handle the exception and return a suitably generic error message to the client, but the data access code does not rely on this.

2. Input is validated using a regular expression . The supplied product ID is checked to verify that it contains characters in the range A “Z and 0 “9 only, and does not exceed 12 characters . This is the first in a set of countermeasures designed to prevent SQL injection attacks.

3. The SqlConnection object is created inside a C# using statement . This ensures that the connection is closed inside the method regardless of whether an exception occurs. This mitigates the threat of denial of service attacks, which attempt to use all available connections to the database. You can achieve similar functionality by using a finally block.

4. Parameterized stored procedures are used for data access . This is another countermeasure to prevent SQL injection.

5. Detailed error information is not returned to the client . Exception details are logged to assist with problem diagnosis.

6. The Encrypted database connection string is stored in the registry . One of the most secure ways of storing database connection strings is to use DPAPI to encrypt the string and store the encrypted cipher text in a secured registry key that has a restricted ACL. (For example, use Administrators: Full Control and ASP.NET or Enterprise Services process account: Read, depending on which process hosts the component.)

   Other options are discussed in the "Database Connection Strings" section of this chapter.

   Note  

   The code shows how to retrieve the connection string from the registry and then decrypt it using the managed DPAPI helper library. This library is provided in "How To: Create a DPAPI Library" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication " at http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetHT07.asp .