Where should data validation occur?

Question

I've read conflicting philosophies on where data validation should happen and it's just confusing me more. Some say it should only be in the database. Others say that the validation rules should be mirrored in other layers like the bll or ui.

Where should the data validation live? Should rules be split across multiple layers? What are some of the actual best practices (as opposed to theory, head in the clouds type stuff) regarding when and where to validate data in an application running on top of a database.

Answer 1

My 2 cents:

Data validation should occur in two locations:

1. The point where data is acted upon, for example validating input parameters to an SQL query.

2. General validation at the point where data is submitted, for example in a web application some validation should occur on the client. The advantage being that you can quickly notify users of input issues, i.e. incorrectly formed telephone number, string too long etc. However this should not be relied upon to be a authoritative validation check as, in the case of a web application, a malicious user may bypass an client side validation.

In my opinion the database should not be performing general validation, data should be validated/escaped/sanitised before it goes into the database. That said your database schema can give you a level of abstract validation through column data types, constraints etc. That said, any data that could trigger issues with these should be 'cleaned' before it is passed into the database.

This said, there are many wrong ways but there is no right way. Validation depends on the architecture of your application, the nature of the data within in it and how that data is used.