ost Web services created with Visual Studio.NET are written in a managed language such as C# or VB.NET, which has security benefits. Managed code can help mitigate security threats such as buffer overruns, and with code-access permissions the programmer can limit the code’s capabilities to prevent attackers from using it for malicious coding.

However, after security reviews of numerous Web services during the past year, I have noted two critical mistakes that VS.NET Web services designers make. Both relate to the connection between the Web service and the database servers, such as SQL Server and Oracle.

The bad news is these mistakes occur equally in Java applications, ASP applications, C++ applications, and Visual Basic applications. The good news is they have easy fixes, which I will present in this article.

Mistake #1: Using the Sysadmin Account to Connect to the Database Server
Just about every SQL sample I see for a Web service has code that connects to the SQL database as the sysadmin. It is a very common mistake. Think about it; how many times have you seen a connection string like this:

strConnection = "data source=DBServer;uid=sa;pwd="

The user identity that connects to the database is sa (SQL Server’s sysadmin account), and the password is empty. I’m not even going to insult your intelligence by telling you how bad having no password is. For now, I’ll address the sa issue.The principal of least privilege, a very important security maxim, dictates that you use an account that has only the necessary set of privileges to perform a task—and no more. So if your application queries a couple of tables and perhaps updates another table, why do you need sa to do this? Sure, sa can query and update tables, but it also can do much more that you may not want it to. It can delete any table in the system, call any stored procedure, adjust all data in every table, define new databases, and so on. So you absolutely should not use sa to perform simple queries and updates. It is much too powerful. It can do anything to the database, and a flaw in your application may leave your data vulnerable to attack.

Apply the principal of least privilege when connecting to the database. It takes a little more time to program an account with only the necessary privileges but it’ll also take much more time for an attacker to compromise your data. Also, don’t store connection strings in the application itself. Pull the data from an external source. Not only is it more secure but it also is more maintainable.

Mistake #2: Handcrafting SQL Statements
Look at the following, seemingly innocuous code:

strSQL = "select sum(cost) from sales where id='" + id + "'";

Do you have code like this? At a Writing Secure Code presentation I conducted at the recent Microsoft Professional Developer’s Conference in Los Angeles, 80 percent of the approximately 500-person audience claimed they did. The problem with this code is that it provides the user’s variable id, opening the door for an attacker to “piggyback” SQL statements in the variable.

For example, an attacker could set the id variable to the following:

1' drop table sales --

This builds the following code:

select sum(cost) from sales where id='1' drop table sales -- '

This code will perform a query looking for any rows where id = 1, which may or may not exist. The sales table is then dropped or, more plainly, deleted. The “–” at the end of the input comments out any other data in the SQL query that the flawed code built, which increases the probability of the input being valid SQL that executes without failing.