CODEWITHSUNDEEP
sql-serversql-injection
richa verma
richa verma

Reputation: 287

How to write a safe stored procedure

I want to write a safe simple stored procedure which will select some columns from database table based on some parameters given to stored procedure.

I have tried two ways of writing the stored procedure.

1st way as:

CREATE PROCEDURE SPBasic
    @id int,
    @value int
AS
BEGIN
    SELECT Id, name, design 
    FROM SimpleTable
    WHERE ID = @id AND value = @value;
END
GO

2nd way as:

CREATE PROCEDURE SPBasic
    @id int,
    @value int
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'SELECT Id, name, design FROM SimpleTable
                 WHERE ID = @id AND value = @value';
    EXEC sp_executesql @sql, N'@id int, @value int', @id,@value;
END
GO

For both the cases I run the script as:

DECLARE @return_value int

EXEC    @return_value = SPBasic
        @id = N'11',
        @value = 1;drop table test

SELECT  'Return Value' = @return_value

And in both the cases the temporary table test which I created for test purpose is getting deleted.

So, I would like to know what could be the safe code for this purpose?

Upvotes: 1

Views: 365

Answers (1)

George Barwood
George Barwood

Reputation: 219

Since both your stored procedures take integer parameters, there is no possibility of an SQL injection attack. If the 2nd procedure took a string ( varchar ) parameter, then an SQL injection attack would be a possibility.

Upvotes: 1

Related Questions