Home › Forums › MS SQL Server

Deciding on the scope of stored procedures

In creating my database for use in future potential job interviews, I've been creating stored procedures to query the data in various ways. I'm faced with the question of how to handle parameters used by those procedures. Should I assume that valid values will be passed to my procedures from some front end application or should I try to handle every possible contingency? What is the best philosophy to follow regarding this situation?

‹ Function for adding durations with the TIME data type Running balance queries ›
Tue, 08/16/2011 - 07:41 — m_sabal

Never trust your input

Always assume that the values coming into your procedure are malicious. For some fields, like a date, SQL Server's own type checking will protect you. But with varchar, it's a little more difficult. If you have logical validations (such as a phone number only having digits, parentheses, and dashes), it's best to use them. Otherwise, you can check for specific characters that are very unlikely to be used in your data fields, such as greater than and less than (common in cross-site scripting attacks). The validation may look like this:

DECLARE @okay int
SET @okay=1

IF CHARINDEX('<',@parm1)>0
SET @okay=0
IF CHARINDEX('>',@parm1)>0
SET @okay=0

IF @okay=1 BEGIN
UPDATE ......
END

Tue, 08/16/2011 - 15:01 — scott1278

Specific text values required

The procedures I'm developing for my media database require specific values from a Genre table and/or a Category table. Values other than what are stored in these tables will generate null datasets. Can I assume that a front end application working with my database will send valid values to my back end database or is it wisest to validate input via parameter values based on table values?

Tue, 08/16/2011 - 15:46 — m_sabal

Validate

You should definitely still validate at the database level. The application developer may forget, or a cracker may try to access your database without the application. If the parameters have been validated as type-safe, a properly crafted JOIN clause is likely all you need to use for validation.