Preventing incorrect data entry

Question

Is it good to delegate data validation solely to database restrictions?

Validating data from an application does not prevent invalid insertion from other software (possibly written in a different language by another command). By using database restrictions, you reduce the points at which you need to worry about invalid input.

If you put a check in both the database and the application, the service becomes boring, because you need to update the code for those who know how many applications, increasing the likelihood of human errors.

I just don’t see it being done very hard, looking at the code from free software projects.

Answer 1

It is best, where possible, to have your own validation rules specified in your database, and use or write a framework that makes these rules depend on your interface. ASP.NET dynamic data helps with this, and there are some commercial libraries that make it even easier.

This can be done both for a simple input check (for example, a number or date), and for the corresponding data, such as foreign key constraints.

Thus, the idea is to define the rules in one place (the database in most cases) and have code in other layers that will apply these rules.

Answer 2

Check during input. Repeat the test before you put it in the database. And have database restrictions to prevent bad input. And you can bet, despite all this, bad data will continue to flow into your database, so repeat it again when you use it.

It seems like every day some kind of web application is hacked because they did all their checking in form or worse using Javascript, and people found a way around it. You must defend yourself against this.

Paranoid? Me? No, just experienced.

Answer 3

The disadvantage of leaving logic in the database is that you increase the load on this particular server. Web and application servers are relatively easy to scale outward, but the database requires special methods. As a rule, it is recommended to add as much computational logic to the application layer and as simple as possible to maintain interaction with the database.

With that said, it is possible that your application does not need to worry about such issues with great scalability. If you are sure that loading the database server will not be a problem in the foreseeable future, then go to the database restrictions. You are absolutely right that this improves the organization and simplicity of your system as a whole, keeping the verification logic in a central place.

Answer 4

There are other issues besides SQL injection with input. You must be extremely protective when accepting user input. For example, a user can enter a link to an image in a text box, which is actually a PHP script that runs something unpleasant.

If you have well developed your application, you will not need to carefully check all the input data. For example, you can use the forms API, which takes care of most of the work for you, and the database tier, which does the same.

This is a good resource for basic vulnerability checking:

http://ha.ckers.org/xss.html

Answer 5

It is too late by the time the data gets into your database to provide meaningful validation for your users and applications. You do not want your database to perform all the validation, as this will slow down and the database does not express logic so clearly. Similarly, as you grow, you will write application-level transactions to complement database transactions.

Answer 6

I would say this is a potentially bad practice, depending on what happens when the request fails. For example, if your database might cause an error that was reasonably handled by the application, then you might be fine.

On the other hand, if you do not apply any validation in your application, you may have some bad data, but you might think that they entered data that is not saved.

Answer 7

Embed as much data as you can at the end of the database without compromising other goals. For example, if speed is a problem, you may need to not use foreign keys, etc. In addition, some data verification can only be performed on the application side, for example, so that email addresses have valid domains.

Answer 8

Another disadvantage for checking data from the database is that often you do not check the same way in each case. In fact, this often depends on the application logic (user role), and sometimes you can bypass the check altogether (cron jobs and maintenance scripts).

Answer 9

I found that doing validation in the application, not the database, works well. Of course, all interaction should go through your application. If you have other applications that work with your data, your application will need to support some kind of API (hopefully REST).

Answer 10

I do not think there is one correct answer, it depends on your use.

If you have a very heavily used system, it is possible that the database performance will become a bottleneck, then you may need to shift the responsibility for checking to an external interface, where it is easier to scale using multiple servers.

If you have multiple applications that interact with the database, you may not want to replicate and support validation rules in multiple applications, so the database might be better.

You may need a slicker input screen that doesn’t just hit the user with validation warnings when they try to save the record, maybe you want to check the field after entering data and losing focus; or even by type of user, changing the font color, because the check is not performed / passes.

Also associated with restrictions, these are warnings about suspicious data. In my application, I have hard limits in the database (for example, someone cannot start working before the date of birth), but then there are warnings in the interface for data that may be correct but suspicious (for example, eight years -old getting started).