ADO.NET 2.0 Security Guidelines - Exception Management

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Chaitanya Bijwe


Contents

Use Finally Blocks to Make Sure that Database Connections Are Closed

If exceptions occur, you must make sure that database connections are closed. To do so, use finally blocks. However, be aware that exceptions are expensive. Do not catch exceptions and then return them if your data access logic cannot add any value. A less costly approach is to permit the exception to propagate from the database to the caller. Similarly, do not wrap transaction attempts with try/catch blocks unless you plan to implement retry mechanisms. Use code similar to the following example to make sure that database connections are closed.


// Data access method
public string RetrieveProductName(int productID)
{
 ...
 SqlConnection conn = null;
 try
 {
   // Open database connection and perform data access
   ...  
 }
 catch (SqlException sqlex)
 {
   // Log exception details on the server
   ...
   // Re-throw a new more relevant exception 
   ...
 }
 finally
 {
   if(conn != null) 
      conn.Close(); // Ensures connection is closed
 }
}
 

Consider Employing the Using Statement to Make Sure that Database Connections Are Closed

The using statement is available to developers who use the Microsoft Visual C#® 1.1 and 2.0 and to developers who use Microsoft Visual Basic® .NET 2.0. At compile time, the using statement automatically generates a try and finally block that calls Dispose on the object allocated inside the using block. For connection objects, this ensures that the connection's Close method is called. The following code illustrates this syntax.

using ( SqlConnection conn = new SqlConnection( _connString) )
{
 // Open the connection and access the database
 ...
} // Dispose is called and the connection closed
 

During compilation, the preceding code is converted into the following equivalent code.

SqlConnection conn = new SqlConnection( _connString);
try
{
 // Open the connection and access the database
 ...
}
finally{
 conn.Dispose(); // Closes the connection
}
 

Avoid Propagating ADO.NET Exceptions to Users

You should prevent ADO.NET exception details from being displayed to your application users. ADO.NET exceptions include many details that are useful to an attacker, including database server names, database names, table names, and so on. Use try/catch blocks to trap exceptions on the server, log appropriate details for subsequent diagnostics, and return an appropriate error message to the user. Display only generic information. It is usually enough for users to know that an error has occurred and the operation did not succeed, and to have a failure error code that they can report to a help desk.

One approach is to catch ADO.NET exceptions on the server and then propagate a custom exception object from your data access code, as shown in the following example.


// Data access method
public string RetrieveProductName(int productID)
{
 ...
 try
 {
   // Open database connection and perform data access
   ...  
 }
 catch (SqlException sqlex)
 {
   // Log exception details on the server
   ...
   // Re-throw a new more relevant exception 
   throw new DataAccessException( 
                     "Failed to retrieve product information for product ID: " +
                     ProductID.ToString());,
 }
 finally
 {
   if(conn != null) 
      conn.Close(); // Ensures connection is closed
 }
}
 

Note that when ADO.NET data access code generates a database exception, the abstract System.Data.Common.DbException is thrown. The actual type of exception generated by ADO.NET depends on the data provider. For example, the exception type could be any of the following:

  • System.Data.Odbc.OdbcException
  • System.Data.OleDb.OleDbException
  • System.Data.OracleClient.OracleException
  • System.Data.SqlClient.SqlException

Note If you are writing provider independent code, catch the DBException type. Otherwise catch one of the provider specific exception types listed above, depending on the provider you are using.

In ASP.NET, Use a Generic Error Page

In ASP.NET applications, it is a good practice to use a generic error page that is displayed for all unhandled exceptions. Define an application-level global error handler in Global.asax to catch any exceptions that are not handled in code. Do this to avoid accidentally returning detailed error information to the client. Use code similar to the following.


<%@ Application Language="C#" %>
<%@ Import Namespace="System.Diagnostics" %>

<script language="C#" runat="server">
void Application_Error(object sender, EventArgs e)
{
   //get reference to the source of the exception chain
   Exception ex = Server.GetLastError().GetBaseException();

   // log the details of the exception and page state to the
   // event log.
   EventLog.WriteEntry("My Web Application",
     "MESSAGE: " + ex.Message + 
     "\nSOURCE: " + ex.Source, 
     EventLogEntryType.Error);

   // Optional e-mail or other notification here...
}
</script>
 

Log ADO.NET Exception Details on the Server

Log detailed ADO.NET exception information on the server. This information is essential for problem diagnosis. The following code shows a helper routine that you can use to log SqlException details to the application event log.


using System.Data;
using System.Data.SqlClient;
using System.Data.Diagnostics;
...
// Data Access method
public string RetrieveProductName( int productID )
{
 SqlConnection conn = null;
 // Enclose all data access code within a try block
 try
 {
   conn = new SqlConnection(_connString);
   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 DataAccessException(
                 "Unknown ProductID: " + productID.ToString(), sqlex );
 }
 catch (Exception ex)
 {
   // Handle generic exception condition . . .
   throw ex;
 }
 finally
 {
   if(conn != null) 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 );
 }
}
 

For more information about logging errors, see "How to: Write to the Application Event Log (Visual C#)" at http://msdn2.microsoft.com/en-us/library/42ste2f3, and How to: Write to an Application Event Log" at http://msdn2.microsoft.com/en-us/library/07347hdt.

Personal tools