Where should data validation occur?

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.

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.