15

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jeff
  • 13,943
  • 11
  • 55
  • 103
  • 2
    It should most certainly be at the backend - and if it is feasible and help your app, then also do validation in the frontend. But never rely on just front-end validation - never trust any data before inserting it into your database - **always** validate at the database level (referential integrity, CHECK constraints etc.) - no exceptions. – marc_s Jan 27 '11 at 17:42

2 Answers2

13

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.

MrEyes
  • 13,059
  • 10
  • 48
  • 68
  • Then you would never execute a database query that threw an error (non-application errors aside, like connectivity problems). Essentially, your application does all the validation that needs to happen. Does that sound right? – Jeff Jan 27 '11 at 17:46
  • 1
    You can never be absolutely certain that your DB query isn't going to throw an error due to bad data, so your app should handle that. However you can do a lot to prevent this sort of thing from happening. Generally databases can do basic validation (constraints, datatypes etc) but it is more complex to handle anything beyond that while being able to gracefully handle validation failures. Additionally why load up the database with queries that contain un-validated data, your app can handle that and it is much easier to scale an application than it is to scale a database. – MrEyes Jan 27 '11 at 17:49
3

it should be done:

  • at the point it is first entered
  • anywhere along the way it is changed/manipulated
  • anywhere along the way it could cause an error or incorrect data

so in a database driven web forms app, for instance, you would have client-side javascript validation, probably some server side validation in the business logic, and then further constraints in the database, ranging from datatype to check constraints.

ian
  • 12,003
  • 9
  • 51
  • 107
  • Do you duplicate all the rules in each layer? If not, how do you decide which rules go into which layer? – Jeff Jan 27 '11 at 17:47
  • if you follow the guidelines above, and it means you have to add validation to each layer then add validation to each layer. It's on merit, not on some rule that might not fit your app. – ian Jan 27 '11 at 17:48
  • +1 for best description of biz logic as a cross-cutting concern. – Ken Downs Jan 27 '11 at 19:37
  • 1
    @Jeff, I think I misunderstood what you were asking slightly. You don't have to exactly duplicate all the rules in each layer, just enough to make sure that each layer validates to the point that is required. For example, a datatype in a database explicitly sets a type of validation (e.g. int or string) but this may just be implied in another layer due to its nature (it may not matter if it's represented as an int 9 or a string "9" elsewhere). – ian Jan 28 '11 at 01:09