Project Anvil

I am so pleased with deciding to go with SQL Server 2005 Express for Anvil. I recently learned about an extremely powerful feature that makes data validation in the database a breeze.

Whenever you use input from an untrusted source, it needs to be validated. Especially if it comes from or can be accessed by the user. The best way to handle this is to put an input sentry at any trust boundary, as it crosses from an untrusted to trusted border. Ultimately, the last line of defense will be the database, as that is where the final storage ends up... at least for our application.

You can easily apply CHECK constraints on fields in the database. But that is a very rudimentary method of validating the input, since you can typically only do basic checks.

Enter the fact that in SQL Server 2005, you can now enable CLR in the database, and write user-based functions in your favorite .NET language. And more importantly, you can CALL these functions AS constraints on fields in the database.

This is really impressive stuff. In my case, I wrote a generic regular expression validation function that allows me to do the deepest of validation checks on the data before its inserted. If the data fails the regex validation, the record will not be committed.

I decided to screencast the authoring of this powerful regular expression validation method. Feel free to use it yourself on your SQL Server 2005 databases.

And remember... always assume that input is malicious until proven to be safe.

Anvil SQL Server Data Validation Screencast [Flash ~14MB]