Coding Defensively

I talk with lots of application developers who think that security isn't their concern. In their view, security is up to Microsoft and either the security, the network, or the database administrator—in other words, someone else. Developers with this mentality think it's enough to get the database application running. Busy juggling projects and putting out fires, they say they don't have time to worry about security vulnerabilities. But that attitude doesn't fly in today's world of prevalent Web applications.

For example, one of the most dangerous system attacks—SQL Injection—is code-based, and defending against it falls squarely on the application developer's shoulders. A form of network attack, SQL Injection works by passing unexpected SQL code into an application. Unlike many security exploits you read about, SQL Injection isn't just a Microsoft or SQL Server problem. All SQL-based databases are open to SQL Injection attack.

Suppose you have a Web application that asks users to input a customer ID, then sends the following SQL statement to the database:

SELECT *FROM customersWHERE CustomerID= '" & Request.Form("txtCustID") & "'

The application expects the txtCustID field to resolve to one customer ID:

WHERE CustomerID='ANTON'

But if someone injected the valid SQL expression 1=1 into the txtCustID field, he or she could manipulate the application to return all rows in the customers table:

WHERE CustomerID='ANTON' or 1=1

Attackers can also use this injection technique to perform other network actions. For example, by inserting a semicolon into the txtCustID field, an attacker might be able to execute the xp_cmdshell stored procedure:

SELECT *FROM customersWHERE CustomerID='ANTON';EXEC master..xp_cmdshell 'ping 192.0.1.'

SQL Injection attacks all come through port 80, so no firewall can stop them. And no security tool, including Microsoft Baseline Security Analyzer (MBSA), can help defend against them. You must handle this form of attack the old-fashioned way—through defensive coding.

Your first line of defense: Don't authenticate by using more permissions than necessary. Applications that authenticate by using sa open the door to executing virtually any command through SQL Injection. Your second line of defense: Don't allow direct access to tables. Instead, funnel data access through stored procedures. Prohibiting direct table access prevents exploratory queries, making it much harder for hackers to gain information about your systems. Your third line of defense is code-based: Carefully validate all user input from your applications, looking not only for valid values but also for SQL Injection exposures. Check that input that's supposed to be numeric is really numeric. For strings, replace single quotes with two single quotes and scan for unexpected semicolons.

Defensive coding isn't something that only Microsoft needs to practice—it starts at home. To learn more about SQL Injection and how to defend against it, see the FAQs at http://www.sqlsecurity.com/faq-inj.asp.