[Note from Pinal]: This is a 54th episode of Notes from the Field series. Where do we blame for a mistake or error in the system? Well, developer blames DBA and DBA blame developers. Sometimes there is no solution to the catch 22 situation. I have been there and I am very sure that you have been there too. Well, this is an amazing and beautiful article by G. Andrew Duthie. He has attempted to demystify the problem which we all face every day.
In this episode of the Notes from the Field series database expert Andrew Duthie explains about Validation Rules and where they should be implemented. Read the experience of Andrew in his own words.
If you’re a DBA dealing with developers, you may run into the question of whether it’s better to allow the developers to write validation rules in their .NET app layer, or whether you should put your foot down and insist that the validation be implemented in stored procedures. The answer, as you might expect from a consultant, is “it depends.”
Advantages of Code-based Validation
One of the things that might inform your decision on what to use for validation is the skills possessed by the development team. If you’ve got a developer or developers who are well-versed in C# and LINQ, but don’t have a lot of experience writing stored procedures, you may want to cut them a break and let them use the tools they’re more familiar with.
Writing validation rules in code at the application layer allows developers to stay within the realm of .NET objects, which can result in faster development time.
Disadvantages of Code-based Validation
While there are probably more that could be discussed, I’ll mention just two of the significant disadvantages to writing validation rules in code.
First, if the code for the validation rules is using LINQ, particularly if the rules are complex, there’s the possibility of queries that generate sub-optimal SQL under the covers. This can be mitigated by profiling the queries to make sure that any performance hogs are caught as early as possible, but it’s certainly a valid concern.
Second, from a maintainability standpoint, having rules in the app means that adding rules requires the app to be recompiled and redeployed. For some apps and environments, this may not be a big deal, but in others, it could definitely be a deal-breaker.
Advantages of Stored Procedure-based Validation
Using stored procedures for validation provides some key advantages. One is proximity to the data. Unlike code-based validation, which may require pumping significant amounts of data over the wire from the database to the app tier, stored procedure-based validation keeps the logic on the DB tier, so performance may be significantly better.
Another advantage is that with a good execution design (for example, a master stored procedure that executes a list of validation rules in a specified order based on a configuration table), it can be relatively easy to introduce new rules with less disruption than having to recompile and redeploy an entire application.
Disadvantages of Stored Procedure-based Validation
The major disadvantage of using stored procedures for validation, speaking as an app developer, is the basic impedance mismatch between .NET code (C# or Visual Basic) and T-SQL. While it’s certainly possible for developers to master both, there’s a mental cost in switching between these environments, and a potential for mistakes when transitioning from one to the other.
The other downside of stored procedures is the mixing of application logic between the app tier and the database tier. While validation close to the data can, as noted, improve performance, if some parts of the application logic live in both the app and database tiers, this could make for more costly maintenance down the road.
Consistency is Key
One additional point I’d like to make is that it’s probably wise to choose one option or the other…not both. If you have multiple applications in development (or even in maintenance mode), having a mix of app-based or sproc-based validation will likely give you headaches at some point. So get your team together and have a discussion about how you’re currently handling things, and whether there might be a better way.
Summary
The short answer to “which is better” is really “either.” It all depends on the skills of your developers, the performance you need from your app, and the other factors I’ve discussed. Although I’m coming at this from the perspective of an app developer, I’ve recently become more comfortable with the idea of stored procedure-based validation, particularly in instances where more than one app may be targeting the same database, since this can help reduce redundancy, and centralize management of rules.
I’d love to get your feedback on how you’ve handled validation rules in your environment, so feel free to share a comment below.
If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
There’s also the question, “Is the database platform capable of validating the input?” Case in point, a web application which allows user input. If that user input can have HTML elements embedded, SQL Server is not where you want to build your validation logic. It doesn’t have an HTML parser, and that’s just the start of the issues.
Good point, Brian.
Of course, if we’re talking about input validation from a website, there really aren’t any good ways to validate that there’s nothing bad in the input, particularly if HTML content is allowed. Filtering for known bad input will always leave you at risk of missing the unknown.
Better to only allow known safe content (whitelisting), though even that can be dangerous, if you’ve made a mistake about whether a given bit of content is truly safe.
For these reasons, if it’s user input we’re talking about, it should always be considered unsafe, and should be encoded before rendering in a web app, regardless of what kind of validation is being done, or where.
But to clarify, when I discuss validation rules above, I’m speaking more of rules that are intended to validate specific bits of data, based on business requirements, not necessarily about input validation, which is another topic entirely.
Thanks for your feedback!
@Brian,
Thanks for the reply…I agree that input validation shouldn’t be in the DB, and I probably should have been clearer that that’s not the kind of validation I was referring to.
An important additional point on input validation, while we’re on the topic…whether you’re validating user input by disallowing certain content (blacklisting) or by only allowing certain content (whitelisting), it’s critical when dealing with user-provided input that will be displayed in a browser to always encode that input before displaying it again, because there’s always the possibility that you missed something during validation. (I’m sure that you know all this, but wanted to point it out for the benefit of any folks reading this who may not)
I appreciate the feedback!