Common Database Vulnerabilities: Defects

If you have been working in (or close to) the database field for a while, you have probably heard of or seen a defect caused by a missing WHERE clause. This type of defect is very common and often leads to data corruption.

A good friend of mine, just having started out on his first job out of college, did what you should expect of any good employee. He was working on familiarizing himself with the system the company developed, which in his case meant reading the code. What he ran across there startled him. He found a WHERE clause on an UPDATE statement that did not make sense.

Long story short, after reviewing the code with a more senior colleague, they determined that this UPDATE statement, which was supposed to update a single order, in some cases updated a bunch of orders. The system was the in-house order system of the company itself and they used it for a product type with a long lead cycle. Between order placement and delivery often several months passed. While they had noticed an oddly high number of "I did not order this" complaints from customers, because of the long feedback loop they were not able to tie it to the order system, particularly as the misguided UPDATE always updated the correct record. It just also updated a few unrelated orders.

The missing WHERE clause

So, you might ask, what does this have to do with security? In the above case, the missing (part of the) WHERE clause caused tremendous damage to the business, but this was not a security concern. In other cases however, a defect like a missing WHERE clause section might cause data to be shown to a user that they were not supposed to see.

Imagine, for example, that your shopping cart would start allowing customers to pay with other customers' credit cards. This would certainly be a disaster. Cases like this, involving customers being able to access other customers' data, are actually quite common.

Avoid Defects with Test Driven Design

One of the ways to prevent defects like this is to use Test Driven Design as a development methodology. The main purpose of Test Driven Design is actually to guide us to well-designed software. However, using this methodology tends to lead to a code base that is well tested and does not contain edge-case-disasters like missing parts of WHERE clauses.

I am not saying the Test Driven Design is the only solution to this problem. However, in my experience Test Driven Design is a very powerful methodology that if implemented correctly, tends to result in a significantly reduced defect count. As an added benefit, you will get a lot more confident in your code and changes will be much simpler to implement.

tSQLt

tSQLt, an open source unit testing framework for SQL Server allows you to write your database code using Test Driven Design. tSQLt is similar to other unit testing frameworks like NUnit or Junit and it is available for free at tSQLt.org.

You can find an introduction to tSQLt on that website and an introduction to Test Driven Design in this article that I wrote a while back for SQL Server Pro.

As always, the time to act is now. Do not wait until someone else discovers or even exploits a defect-caused vulnerability in your code base. Be proactive about it; implement good coding and design techniques. Start by downloading tSQLt now.

The Most Common Database Problems

Defects are one of the most commonly encountered database vulnerabilities. In this series of posts, I discuss 10 of them. Below are the ones that are published so far: