x

how to validate incoming data using stored procedures.

I have heard that we can validate incoming data from a source using stored procedures. can so one explain this in a broader way?
more ▼

asked Dec 09, 2012 at 12:53 PM in Default

karz gravatar image

karz
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

There are three levels of validation that I'm aware of. First, the data type itself is a validation. If the parameters within your stored procedure are INTEGER, DATE, FLOAT, then the data passed to them must be a valid number, a real date, and a number with decimals. Secondly, assuming you have referential integrity, then the system will validate that the relationships you have within your data is valid. Lastly, you can write code to check certain things are true. With functions from CLR you can even write code that uses regular expressions to do validations.

But, in general, I would expect that last type of validation to be better done at the code level. But you can do it within SQL Server.
more ▼

answered Dec 11, 2012 at 10:42 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(comments are locked)
10|1200 characters needed characters left

@karz do you mean that the incoming data is populated by an external system making calls to a stored procedure?

If that's the case, then your SP can be written to perform data validation and verification as the SP is called. Your SP can also be written to split data out into multiple locations, to ensure that all or nothing is done; but there's not much here that couldn't be done using triggers.
This answer expands on the final option in @Grant Fritchey's answer, above, in which he talks about "writing code to check certain things are true".

more ▼

answered Dec 11, 2012 at 05:27 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left

Grant's answer is fantastic and I second everything he said. But some people have referred to checking for SQL Injection as validation, or at least a part of validation.

So, if you are looking at validation in terms of preventing SQL injection I would add a few things:

  1. Avoid dynamic SQL unless you need it.
  2. If you do use dynamic sql, use properly parameterized queries with sp_executesql instead of just building a sql string.

If you do those two, then it will handle most cases and there is little reason to try to parse the string looking for injection attacks (though it could be useful for logging purposes, especially if you want to be able to do a post mortem on failed attacks.)

Also, just to expand on what he said about "code to check that certain things are true", you certainly can do this in a stored procedure, but CHECK and other constraints can help with this and they can catch things that are inserted without going through the stored procedure. Of course, they aren't always the best approach, especially for complicated validations, but they can be useful at times.
more ▼

answered Dec 11, 2012 at 05:28 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x407

asked: Dec 09, 2012 at 12:53 PM

Seen: 1431 times

Last Updated: Dec 11, 2012 at 05:28 PM