Does it make sense to validate data taken from the database?

Question

This is intended to be a general language-agnostic question. It is written around a weird example simply because I could not find a better way to ask. Also, the "pseudocodes" provided look like a mixture of Java and JavaScript only because those are the languages I'm most familiarized with, but hopefully they will be easy to understand for everyone.

Let's say I have a String field called tuples in a database. When the user clicks a button (the Store Numbers button), the function store_numbers is executed, as follows (in pseudocode):

function store_numbers() {
    // Make a bunch of calculations and generate some integer tuples,
    //     such as (0,1), (4,4) and (-500,0)
    // Create a string with those tuples, semicolon-separated,
    //     with the exact format above, such as
    //     "(0,1);(4,4);(-500,0)"
    // Insert this string in the 'tuples' field of the database (for the given UserID)
}

And this is the only way, in the whole software, that the tuples field of the database is altered (at least in the current version of the software).

Now, let's say the same user comes back tomorrow and clicks another button (the Calculate Stuff button), and the function calculate_stuff is executed. This function will read the string tuples from the database, and is supposed to make various calculations with the tuples. QUESTION: Can I assume the tuples string is perfectly formatted? Or should I validate the string that came from the database first?

OPTION 1: Assuming it's perfectly formatted:

function calculate_stuff() {
    String tuples = // read string from database...
    // immediately begin calculations assuming string is perfect
    for (String tuple in tuples.split(';')) {
        String[] temp = tuple.replace('(','').replace(')','').split(',');
        int x = Math.parseInt(temp[0]);
        int y = Math.parseInt(temp[1]);
        // ...
    }
    // ...

    // If somehow the string was wrong, ugly errors will be thrown,
    //     and the software will crash.
    // But this shouldn't happen, since the string was crafted
    //     correctly in the store_numbers() function.
}

OPTION 2: Validating it first:

function calculate_stuff() {
    String tuples = // read string from database...

    // Check if the string is OK first:
    if (!is_a_semicolon_separated_list_of_integer_tuples(tuples)) {
        error("Oh no! Something terrible happened! How come??");
    } else {
        // Proceed calculations...
    }
}

MY THOUGHTS: Option 1 will not give any decent error message to the user - the software will crash instead. This is terrifying in a UX perspective, but since the string was correctly crafted by the other function, this really should never happen. On the other hand, maybe it doesn't hurt to add the check? Well... Coding the is_a_semicolon_separated_list_of_integer_tuples is not that hard, but is not ridiculously easy either. What if it was a much more complicated string, whose validation would take hundreds of lines of code?? For something that will never happen?

What are the guidelines for this? What are the good programming practices involved?

Answer 1

The essential question is how well can you control who can modify the data within the database.

Most databases allow constraints to be defined for the different columns. In your case,¹ you may set a rule which enforces the specific tuple format, so (0,1) would be valid, while (0, 1) or (0:1) wouldn't. This guarantees that the data is necessarily using the required format, which makes it less important to have the additional checks within the application if you trust enough the database schema (and you know someone won't change it without rerunning the tests of your app).

If the format is not enforced through the schema, usually² bad data finds its way sooner or later in the database to hunt you and your applications. This data may come:

• From your application. Code changes. Developers may not be unaware of the formats which should have been used. Or they may be aware, but make a mistake. Since the format is not enforced, mistakes are very difficult to spot.

• From other applications. Different applications from different teams may access the same database, and given the lack of properly enforced rules, even clear documentation won't necessarily help.

• From manually executed commands. Someone, somewhere, needed to insert a missing value, following a high priority bug, and was unaware of the format to use, or, once again, made a typo.

When coding defensively, a popular approach is to make a separation between the application itself, assumed trustworthy, and the outside world which shouldn't be trusted.

This could apply at many levels: calls from REST clients vs. web service itself; file system vs. application code; framework vs. application; class vs. method. If you develop a single class, other classes may be considered as an outside world (this, for instance, leads to fields being replaced by properties, or to open/closed principle). For a method within a class, other methods may be considered as an outside world.

In your case, the question is should you consider your database as an outside world. Sometimes you should. In other cases, you don't have to, and additional data checks at application level wouldn't bring too much benefit, while requiring too much time spent developing and maintaining those checks.

Imagine you develop a proprietary video game. The licence explicitly prohibits any modification to the underlying database, and the database itself is encrypted to prevent unauthorized use. Now, one user decides to reverse-engineer the game to gain access to the database, and modifies some data in it. On the next launch, the game fails, because the data format is invalid. Does it really matter?

---

^{¹ One should note that if you're dealing with RDBMS, you gave a terrible example of formatted data (I suppose if it's a database such as Redis, the example would be valid, although I don't know Redis well enough). In a relational database, instead of storing tuples this way, you should store individual values. This would then make the validation as you specified it (mostly) irrelevant.}

^{² Or, according to my personal experience, in every single case.}

Answer 2

Try searching for this should never happen on github. As of this writing, people have planned for that eventuality 18,210,436 times. Why? Because if it ever does happen, and you haven't prepared for it by logging a proper error message, it's going to be crazy hard to track down. You will never think to look there until you have driven yourself mad enough to review your table manually row by row. Or worse, your important client's table row by row because you can't reproduce it locally. It costs you practically nothing to validate it on both ends now.

I agree with MainMa though, this is what schemas are for. Store it properly as separate numbers and you avoid a whole bunch of future issues.

Answer 3

It depends.

As a business rule, is it OK for invalid data to live in the database? Do users have the ability to edit the data directly? If they enter invalid data, do you need to persist it and allow them to edit it later?

If so: You need to validate every read, every time, and determine what to do with the data based on that validation -- as a business rule.

If not: Only validate once, when the data goes into the database.