Validating sql stored procedures

24-Dec-2017 09:00

Validating user input in client code is important so that you do not waste round trips to the server.

On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values.

For more information about SQL injection and how to avoid it, see "SQL Injection" in SQL Server Books Online.

For more information about validating stored procedure parameters, see "Stored Procedures ( Database Engine)" and subordinate topics in SQL Server Books Online.

There are a few options that you could use for this.

The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters: ALTER PROCEDURE [dbo].[sp_Example] @parameter1 char (8), @parameter2 char (20), @parameter3 char (20), @parameter4 char (20), @parameter5 char (20), @parameter6 char (20),...

Rewrite as follows: alter procedure [email protected] int, @personid intasset nocount onif exists (select top 1 projectmanagerid from project where projectmanagerid = @personid)begin (select top (@count) count(pr.projectmanagerid) from person p inner join project pr on p.personid = pr.projectmanagerid )endelse print 'no record found' Hi, I am geeting an error msg 4104 (The multi-part identifier "pr.projectmanagerid" could not be bound.) Please help in resolving this error.

On the other hands, I am serious sometimes, especially with my work that I always focus on.… continue reading »

Read more

These are the Post-Mortems, where we dissect a letter and dig through the remains in order to get to the heart of the issue. Many times, we’re having to liberally apply the Chair Leg of Truth to a lifetime of beliefs.… continue reading »

Read more

Facebook profile photos of nearby singles appear randomly; users respond with an easy “like” or “nope” with the swipe of their fingers.… continue reading »

Read more