6.6. SQL injection protection

A SQL injection attack exposes unintended data to an application user by taking advantage of poor or missing application input parsing. As an example, consider the following code:

var city;
city = request.form ("shippingCity");
var sql = "select * from orders where ShipCity = '" + city + "'";

The intention of this code is that the city variable will be populated with something like Prague. However, what would happen if the following value was entered in the shippingCity form field?

Prague'; select * from creditCards--

The semicolon character marks the end of one command. The rest of the input will run as a separate query, and by adding the comments (--) characters to the end of the input, we ensure any code added to the end of the variable will be ignored, thus increasing the chances of running the injected code.

In our previous example, the command first selects orders from Prague, and then selects records from the CreditCards table, and the results of both queries are returned to the application. It's easy to imagine adding a range of other code such as dropping tables, deleting data, or running update commands. In most applications, the code will execute with database owner privileges, so chances are the code will be able to run anything the hacker injects.

Although more in the domain of application development, protecting the database from SQL injection attacks is certainly something that a DBA should be well aware of. Fortunately, there are some well-established practices to prevent injection attacks. The implementation of these best practices are probably outside the domain of DBA responsibilities, but DBAs should ensure that development and test staff are aware of the potential threat and encourage both development and testing strategies to mitigate this risk.

• All user input should be validated by the application before being submitted to SQL Server to ensure it doesn't contain any escape or comment characters. The application should either reject the input or strip out such characters. In our example, the following characters should be removed or rejected: ', ;, and --.

• Transact SQL statements shouldn't be dynamically built with user input appended. Stored procedures should be used that accept input parameters.

• Application testing strategies should be developed that test applications with a variety of input values.

• Applications should anticipate not only injection attacks but also attacks that try to crash the system—for example, a user supplying a large binary file (MPEG, JPEG, and so forth) to a form field designed to accept a username or city.

• Input should be validated at multiple levels. For example, it's not enough to only validate input at the application if the user is able to execute a stored procedure with a malformed parameter.

As the gatekeepers of an organization's data, DBAs have a crucial role to play in ensuring data is protected from unauthorized access. The next section examines best practices for doing so.