Validating sql stored procedures speed dating events inverness
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.
The answer, as you might expect from a consultant, is “it depends.”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.
Also, if user access to the database is only ever permitted via stored procedures, permission for users to directly access data doesn't need to be explicitly granted on any database table.
Even when using parameterized stored procedures, Web applications should still validate and sanitize all data inputs, whether users or authenticated customers supply them or they are read from a cookie.
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.
NET objects, which can result in faster development time.
In this episode of the Notes from the Field series database expert Andrew Duthie explains about Validation Rules and where they should be implemented. 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.
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.
The major disadvantage of using stored procedures for validation, speaking as an app developer, is the basic impedance mismatch between . 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.
Using normal request channels such as form data, cookies, scripts and URLs, hackers can pass malicious SQL queries and commands to a database if they are not thoroughly checked first.