An In-Depth Defense in Depth Example

Now that we've looked at some common mistakes and some best practices for securely building database applications, let's look at a secure in-depth example. The following code, from a sample Web service written in C#, has multiple layers of defense. If one defensive mechanism fails, at least one other defense will protect the application and the data.

// // SafeQuery // using System; using System.Data; using System.Data.SqlTypes; using System.Data.SqlClient; using System.Security.Principal; using System.Security.Permissions; using System.Text.RegularExpressions; using System.Threading; using System.Web; using Microsoft.Win32; ... [SqlClientPermissionAttribute(SecurityAction.PermitOnly,      AllowBlankPassword=false)] [RegistryPermissionAttribute(SecurityAction.PermitOnly,      Read=@"HKEY_LOCAL_MACHINE\SOFTWARE\Client")] static string GetName(string Id) {     SqlCommand cmd = null;     string Status = "Name Unknown";     try {         //Check for valid shipping ID.         Regex r = new Regex(@"^\d{4,10}$");         if (!r.Match(Id).Success)             throw new Exception("Invalid ID");         //Get connection string from registry.         SqlConnection sqlConn= new SqlConnection(ConnectionString);         //Add shipping ID parameter.         string str="sp_GetName";         cmd = new SqlCommand(str,sqlConn);         cmd.CommandType = CommandType.StoredProcedure;         cmd.Parameters.Add("@ID",Convert.ToInt64(Id));         cmd.Connection.Open();         Status = cmd.ExecuteScalar().ToString();     } catch (Exception e) {         if (HttpContext.Current.Request.UserHostAddress == "127.0.0.1")                Status = e.ToString();              else                 Status = "Error Processing Request";             } finally {         //Shut down connection--even on failure.         if (cmd != null)             cmd.Connection.Close();     }     return Status; } //Get connection string. internal static string ConnectionString {     get {         return (string)Registry             .LocalMachine             .OpenSubKey(@"SOFTWARE\Client\")             .GetValue("ConnectionString");     } }

Numerous layers of defense are used here each is explained in detail later:

Blank passwords are never allowed when connecting to the database. This is in case the administrator makes a mistake and creates an account with a blank password.
This code can read only one specific key from the registry; it cannot be made to perform other registry operations.
The code is hard-core about valid input: 4 10 digits only. Anything else is bad.
The database connection string is in the registry, not in the code and not in the Web service file space, such as a configuration file.
The code uses a stored procedure, mainly to hide the application logic in case the code is compromised.
You can't see this in the code, but the connection is not using sa. Rather, it's using a least-privilege account that has query and execute permissions in the appropriate tables.
The code uses parameters, not string concatenation, to build the query.
The code forces the input into a 64-bit integer.
On error, the attacker is told nothing, other than that a failure occurred.
The connection to the database is always shut down regardless of whether the code fails.

At first glance, the code looks more complex, but it really isn't. Let me explain how this code is more secure than the first example. I'll hold off on explaining the permission attributes before the function call until the end of this section.

First, this code mandates that a user identity number must be between 4 and 10 digits. This is indicated using the regular expression ^\d{4,10}$, which looks only for 4- to 10-digit numbers (\d{4,10}) from the start (^) to the end ($) of the input data. By declaring what is valid input and rejecting everything else, we have already made things safer an attacker cannot simply append SQL statements to the shipping ID. Regular expressions in managed code are exposed through the System.Text.RegularExpressions namespace.

The code includes even more defenses. Note that the SqlConnection object is built from a connection string from the registry. Also, take a look at the accessor function ConnectionString. To determine this string, an attacker would have to not only access the source code to the Web service but also access the appropriate registry key.

The data in the registry key is the connection string:

data source=db007a; user id=readuser; password=&ugv4!26dfA-+8; initial catalog=client

Note that the SQL database is on another computer named db007a. An attacker who compromises the Web service will not gain automatic access to the SQL data. In addition, the code does not connect as sa; instead, it uses a specific account, readuser, with a strong (and ugly) password. And this special account has only read and execute access to the appropriate SQL objects in the client database. If the connection from the Web service to the database is compromised, the attacker can run only a handful of stored procedures and query the appropriate tables; she cannot destroy the master database nor can she perform attacks such as deleting, inserting, or modifying data.

The SQL statement is not constructed using the insecure string concatenation technique; instead, the code uses parameterized queries to call a stored procedure. Calling the stored procedure is faster and more secure than using string concatenation because the database and table names are not exposed and stored procedures are optimized by the database engine.

Note that when an error does occur, the user (or attacker) is told nothing unless the request is local or on the same machine where the service code resides. If you have physical access to the Web service computer, you own the computer anyway! You could also add code to limit access to the error message to administrators only by using code like this:

AppDomain.CurrentDomain.SetPrincipalPolicy (PrincipalPolicy.WindowsPrincipal); WindowsPrincipal user = (WindowsPrincipal)Thread.CurrentPrincipal; if (user.IsInRole(WindowsBuiltInRole.Administrator)) { //user is an admin we can divulge error details. }

Next, the SQL connection is always closed in the finally handler. If an exception is raised in the try/catch body, the connection is gracefully cleaned up, thereby mitigating a potential denial of service (DoS) threat if connections to the database were not closed.

So far, what I've explained is generic and applies to just about any programming language. Now I want to point out a .NET Framework specific defense outlined in the sample code that uses permission attributes.

Notice the two security attributes at the start of the function call. The first, SQLClientPermissionAttribute, allows the SQL Server .NET Data Provider to ensure that a user has a security level adequate to access a data source in this case, by setting the AllowBlankPassword property to false the use of blank passwords is forbidden. This code will raise an exception if you inadvertently attempt to connect to SQL Server by using an account that has a blank password.

The second attribute, RegistryPermissionAttribute, limits which registry key or keys can be accessed and to what degree they can be manipulated (read, write, and so on). In this case, by setting the Read property to @"HKEY_LOCAL_MACHINE\SOFTWARE\Shipping", only one specific key, which holds the connection string, can be read. Even if an attacker can make this code access other parts of the registry, it will fail.

All these mechanisms together lead to very secure database communication code. You should always use such mechanisms and layer them in such a way that your code is safe from attack.