The Solutions
The following code snippet, written in C#, shows a "defense in depth" (providing more than one defensive mechanism in case the first one fails) example of how to mitigate some of these threats:

[WebMethod]
public decimal GetSalesFigures(string CustomerID) {

	SqlCommand cmd = null;
	decimal sum = 0.0;

	try {
		// Check for valid CustomerID
		// Must be two alpha, followed by 6 numeric
		// Case insensitive
		Regex reg = new Regex(@"^[a-z]{2}\d{6}$","i");
		if (!reg.Match(CustomerID).Success)
			throw new SoapException("Invalid Sales ID",
SoapException.ClientFaultCode);

		// Get connection string from external location
		SqlConnection sqlConn= new SqlConnection(ConnectionString); 

		// Add Sales ID parameter to the stored procedure
		string str="spGetSalesFigures";
		cmd = new SqlCommand(str,sqlConn);
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.Parameters.Add("@ID",CustomerID);

		cmd.Connection.Open();

		sum = (decimal)cmd.ExecuteScalar();

	} catch (Exception e) {
		throw new SoapException(e.Message,
SoapException.ClientFaultCode);
	} finally {
		// Shut down connection - even on failure
		if (cmd != null)
			cmd.Connection.Close();
	}

	return sum;
}

// Get connection string from external XML config file.
static internal string ConnectionString {
	get {
		XmlTextReader reader = null;
		string connstring = "";
		try {
			reader = new XmlTextReader (@"c:\config\config.Xml");

			while (reader.Read()) {
				if (reader.NodeType == XmlNodeType.Element &&
					reader.Name == "connectstring") {
					connstring =
reader.GetAttribute("value");
				} 
			}	
		} finally {
			if (reader != null)
				reader.Close();
		}

		return connstring;
	}
}

Let's look at the defensive strategies employed in the code. First, and critically important, the code uses a regular expression to validate the customer ID: two alphas followed by six numbers. Everything else is invalid and rejected.

Next, the code pulls the connection string in from an external XML file, which looks like this:

<?xml version="1.0" ?>
<connectstring value="data source=DBServer;
			user id=salesuser;
			password=)1tgAjo09Mn-;
			initial catalog=Sales" />

Notice that each connection is made as a single identity (salesuser) and it has a strong password. Not only that, but in SQL Server I have configured this account to have access only to the table in question and the stored procedure. Even then, access is read and execute only. This account is very restricted in what it can perform. Also note that the external XML file is outside the Web file space. This is just in case an attacker can read files in the directory or has access to the application binary data.

The application does not handcraft a SQL string; it calls a stored procedure and sets the parameters to the procedure using parameterized queries. This has two benefits: it mitigates SQL injection attacks and because the logic is held in a sproc, the attacker cannot easily determine database object names by reading the query.

Always Close Your Database Connections
In the solution snippet, the code handling the connection shutdown to SQL Server is held in a finally block. Code in a finally block is always called whether an exception is raised or not, so the connection to SQL Server is always shutdown. Because they are scarce, connections should be controlled meticulously. Otherwise, an attacker could create numerous zombie connections to the database and launch a denial of service attack—while a valid user may be unable to get a connection. Placing the shutdown code in a finally block means the connection will always close.

Michael Howard is a program manager on the Windows 2000 security team. He is the author of Designing Secure Web-Based Applications for Microsoft Windows 2000 and has spoken about security-related issues at many events, including Microsoft Tech·Ed, Microsoft Professional Developer's Conferences, and numerous industry gatherings. He can be reached at mikehow@microsoft.com.

Introduction

In this Article

Introduction

The Solutions