Exception Management

Exception conditions can be caused by configuration errors, bugs in your code, or malicious input. Without proper exception management, these conditions can reveal sensitive information about the location and nature of your data source in addition to valuable connection details. The following recommendations apply to data access code:

• Trap and log ADO.NET exceptions .

• Ensure database connections are always closed .

• Use a generic error page in your ASP.NET applications .

Trap and Log ADO.NET Exceptions

Place data access code within a try / catch block and handle exceptions. When you write ADO.NET data access code, the type of exception generated by ADO.NET depends on the data provider. For example:

• The SQL Server .NET Framework data provider generates SqlExceptions .

• The OLE DB .NET Framework data provider generates OleDbExceptions .

• The ODBC .NET Framework data provider generates OdbcExceptions .

Trapping Exceptions

The following code uses the SQL Server .NET Framework data provider and shows how you should catch exceptions of type SqlException .

 try {   // Data access code } catch (SqlException sqlex) // more specific { } catch (Exception ex) // less specific { } 

Logging Exceptions

You should also log details from the SqlException class. This class exposes properties that contain details of the exception condition. These include a Message property that describes the error, a Number property that uniquely identifies the type of error, and a State property that contains additional information. The State property is usually used to indicate a particular occurrence of a specific error condition. For example, if a stored procedure generates the same error from more than one line, the State property indicates the specific occurrence. Finally, an Errors collection contains SqlError objects that provide detailed SQL server error information.

The following code fragment shows how to handle a SQL Server error condition by using the SQL Server .NET Framework data provider:

 using System.Data; using System.Data.SqlClient; using System.Diagnostics;     // Method exposed by a Data Access Layer (DAL) Component public string GetProductName( int ProductID ) {   SqlConnection conn = new SqlConnection(         "server=(local);Integrated Security=SSPI;database=products");   // Enclose all data access code within a try block   try   {     conn.Open();     SqlCommand cmd = new SqlCommand("LookupProductName", conn );     cmd.CommandType = CommandType.StoredProcedure;         cmd.Parameters.Add("@ProductID", ProductID );     SqlParameter paramPN =          cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );     paramPN.Direction = ParameterDirection.Output;         cmd.ExecuteNonQuery();     // The finally code is executed before the method returns     return paramPN.Value.ToString();   }   catch (SqlException sqlex)   {     // Handle data access exception condition     // Log specific exception details     LogException(sqlex);     // Wrap the current exception in a more relevant     // outer exception and re-throw the new exception     throw new Exception(                   "Failed to retrieve product details for product ID: " +                    ProductID.ToString(), sqlex );   }   finally   {     conn.Close(); // Ensures connection is closed   } }     // Helper routine that logs SqlException details to the // Application event log private void LogException( SqlException sqlex ) {   EventLog el = new EventLog();   el.Source = "CustomAppLog";   string strMessage;   strMessage = "Exception Number : " + sqlex.Number +                "(" + sqlex.Message + ") has occurred";   el.WriteEntry( strMessage );       foreach (SqlError sqle in sqlex.Errors)   {     strMessage = "Message: " + sqle.Message +                  " Number: " + sqle.Number +                  " Procedure: " + sqle.Procedure +                  " Server: " + sqle.Server +                  " Source: " + sqle.Source +                  " State: " + sqle.State +                  " Severity: " + sqle.Class +                  " LineNumber: " + sqle.LineNumber;     el.WriteEntry( strMessage );   } } 

Ensure Database Connections Are Closed

If an exception occurs, it is essential that database connections are closed and any other limited resources are released. Use finally blocks, or the C# using statement to ensure that connections are closed whether an exception condition occurs or not. The above code illustrates the use of the finally block. You can also use the C# using statement, as shown below:

 using ((SqlConnection conn = new SqlConnection(connString))) {   conn.Open();   // Connection will be closed if an exception is generated or if control flow   // leaves the scope of the using statement normally } 

Use a Generic Error Page in Your ASP.NET Applications

If your data access code is called by an ASP.NET Web application or Web service, you should configure the < customErrors > element to prevent exception details propagating back to the end user . You can also specify a generic error page by using this element, as shown below.

 <customErrors mode="On" defaultRedirect="YourErrorPage.htm" /> 

Set mode="On" for production servers. Only use mode="Off" when you are developing and testing software prior to release. Failure to do so results in rich error information, such as that shown in Figure 14.4, being returned to the end user. This information can include the database server name, database name , and connection credentials.

Figure 14.4: Detailed exception information revealing sensitive data

Figure 14.4 also shows a number of vulnerabilities in the data access code near the line that caused the exception. Specifically:

• The connection string is hard-coded.

• The highly privileged sa account is used to connect to the database.

• The sa account has a weak password.

• The SQL command construction is susceptible to SQL injection attack; the input is not validated , and the code does not use parameterized stored procedures.