Your first task in writing secure SQL database code is to understand what data you need to protect, why you need to protect it and what you're protecting it from. This will help ensure that you build in appropriate security controls for the particular functionality and features that you need. The time to do this analysis is during the database design stage and the process of identifying and evaluating the risks is called threat modeling.
Threat modeling involves categorizing the information the database will store and process in order to identify potential threats to it. This will involve having your security pros and database developers sit down together to analyze the database from an attacker's point of view. This process not only helps everyone understand how and why a hacker may attack the database but ensures that the necessary security controls can be fitted into the architecture design early. It also creates a set of documents that identify and justify the security requirements of the database.
Currently the most potent threat against databases is SQL injection and your key defense will be to use parameterized stored procedures. This method makes requests to the database using parameters and user-defined subroutines instead of building commands using values supplied directly by a user. SQL parameters are not only type safe but greatly reduce the likely success of a SQL injection attack and can be used as a means of controlling access to the database.
When coding your database routines the assumption should be that all data passed to them is from an untrusted source. This means that all routines should validate the data they process for type, length, format and range. Many developers sanitize input by filtering out known unsafe characters but this isn't that effective since malicious users can usually find an alternative means of bypassing this type of validation. Instead, write validation routines to check for known secure, safe input. Any data that isn't accepted should be rejected. The database should log such events but not return any system information in its error message to the calling application or user because this could be useful to an attacker.
You will need to train your developers how to write code with security in mind. This doesn't have to be as expensive as it may sound. There are lots of excellent and free Web application development forums and online tutorials available on the Internet. One of the leaders in this field is the Open Web Application Security Project, which has a lot of examples of how to code securely. Even when your developers can write robust code, it must still be tested for technical and logical vulnerabilities using dynamic and static analysis.
Static analysis involves reviewing an application's source code without executing the application itself, while dynamic analysis reveals how the application behaves when it's running, and how it interacts with other processes and the operating system itself. I should mention here that when testing a new system, real customer data should never be used. Finally, when developing and deploying a database you should never rely solely on your developers to provide your security. You need a defense-in-depth strategy to thwart would-be attackers. Your network administrator should ensure that any accounts that are used by Web applications to access the database are granted the least possible privileges and that sensitive information is encrypted when in transit over the network.
For more information:
This was first published in February 2010