Common Security Mistakes

An In-Depth Example

Now that we have looked at some best practices for building Web services and some common mistakes, let's look at an in-depth example. We will discuss a common insecure scenario, and then we will look at ways to secure the Web service.

The Insecure Version (Do Not Try This at Home!)

Look at the following C# code, and see if you can figure out the vulnerabilities:

[WebMethod(Description="Dangerous Shipping Status")] public string GetShippingStatus(string Id) {     string Status = "No";     string sqlstring ="";     try {         SqlConnection sql= new SqlConnection(             @"data source=localhost;" +              "user id=sa;password=password;" +              "initial catalog=Shipping");         sql.Open();         sqlstring="SELECT HasShipped" +             " FROM detail " +             " WHERE ID='" + Id + "'";         SqlCommand cmd = new SqlCommand(sqlstring,sql);         if ((int)cmd.ExecuteScalar() != 0)             Status = "Yes";     } catch (SqlException se) {         Status = sqlstring + " failed\n\r";         foreach (SqlError e in se.Errors) {             Status += e.Message + "\n\r";         }     } catch (Exception e) {         Status = e.ToString();     }     return Status; }

Did you spot the bugs? OK, here they are. The first mistake is connecting to the SQL database as sa, the sysadmin account. You do not need such a high-powered account to simply query a table. A small slip-up, and sa can do anything it wants—it can delete or modify tables, even the master SQL Server table.

Next the sysadmin account has an easy-to-guess password. Third, the password is embedded in the Web service page. If an attacker accesses this page, she will know the connection details and know that SQL Server is on the Web service machine.

Perhaps the most dangerous issue is that the code is susceptible to SQL injection because the attacker can set the ID to a valid value followed by a series of dangerous SQL statements that are all executed. Also, if the SQL communication fails for some reason, such as an invalid SQL statement or a connection failure, the Web service will send a great deal of data back to the attacker, including the text that makes up the SQL statement. This is simply too much to tell an attacker. In fact, it is of little use to anyone other than a developer.

The code has one last bug—can you spot it? You probably will not because it is subtle. Imagine that the attacker sends a string to this code that causes an invalid SQL statement to be built. The SQL classes will throw an exception. However, the connection to SQL Server will not be closed. Eventually, it will be garbage-collected. But what if the attacker sends thousands of invalid requests? The connections to SQL Server will be exhausted, and valid connections will fail. Oops! This could be a wonderful DoS attack.

A Secure Solution

Now let's look at a version that has multiple layers of defense so that if one defensive mechanism fails, at least one other will protect the application and the data:

[SqlClientPermissionAttribute(SecurityAction.PermitOnly, AllowBlankPassword=false)] [RegistryPermissionAttribute(SecurityAction.PermitOnly, Read=@"HKEY_LOCAL_MACHINE\SOFTWARE\Shipping")] public string SafeGetShippingStatus(string Id) {     SqlCommand cmd = null;     string Status = "No";     try {         // Check for valid shipping ID.         Regex r = new Regex(@"^\d{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_HasShipped";         cmd = new SqlCommand(str,sqlConn);         cmd.CommandType = CommandType.StoredProcedure;         cmd.Parameters.Add("@ID",Id);         cmd.Connection.Open();         if ((int)cmd.ExecuteScalar() != 0)             Status = "Yes";     } catch (Exception e) {         if (HttpContext.Current.Request.UserHostAddress == "127.0.0.1")             Status = e.ToString();         else             Status = "Error.";     } finally {         // Shut down connection--even on failure.         if (cmd != null)             cmd.Connection.Close();     }     return Status; } // Get connection string. internal string ConnectionString {     get {         return (string)Registry                     .LocalMachine                     .OpenSubKey(@"SOFTWARE\Shipping\")                     .GetValue("ConnectionString");     } }

At first glance, the code looks more complex, but it really is not. Let me explain how this code is more secure than the first example. (I will hold off on explaining the attributes before the function call until the end of this section.)

First, this code mandates that a shipping identity number must be exactly 10 digits. This is indicated using the regular expression ^\d{10}$, which looks only for 10-digit numbers (\d{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 are exposed through System.Text. RegularExpressions.)

The code includes even more defenses. Notice that the SqlConnection object is built from a connection string from the registry. Also, take a look at the accessor function ConnectionString. In order 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=shipuser; password=&ugv4!26dfA-+8; initial catalog=Shipping

Notice that the SQL database is now on another computer. An attacker who compromises the Web service will not gain automatic access to the SQL data. Also, the code does not connect as sa; instead, it uses a specific account, shipuser, with a strong password. And this special account has only read and execute access to the appropriate SQL objects. 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.

The SQL statement is not constructed using the insecure string concatenation technique; rather, 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 Web service resides. If you have physical access to the Web service computer, you “own” the computer anyway!

Next, the SQL connection is always closed down in the finally handler so that if an exception is raised in the try/catch body, the connection is gracefully cleaned up, thereby mitigating the DoS threat.

As promised, I will explain 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, the use of blank passwords is forbidden. If you inadvertently attempt to connect to SQL Server using this code and using a blank password, it will raise an exception. The second attribute, RegistryPermissionAttribute, limits which registry key or keys can be accessed and to what degree (read, write, and so on). In this case, only one specific key, which holds the connection string, can be read. If an attacker tries to make this code access other parts of the registry, it will fail.

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