ADO.NET 1.1 Security Guidelines - Exception Management

From Guidance Share

- J.D. Meier, Alex Mackman, Michael Dunner, Srinath Vasireddy, Ray Escamilla and Anandha Murukan

Contents 1 Trap and log ADO.NET exceptions 1.1 Trapping Exceptions 1.2 Logging Exceptions 2 Ensure database connections are always closed 3 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 );
 }
}

References

• See Building Secure Data Access at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh14.asp

Ensure database connections are always 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
}

References

• See Building Secure Data Access at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh14.asp

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 being returned to the end user. This information can include the database server name, database name, and connection credentials.

References

• See Building Secure Data Access at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh14.asp