CODEWITHSUNDEEP
stored-proceduressql-injectionparameterized
Yeasin Abedin
Yeasin Abedin

Reputation: 2443

SQL injection in parametrized stored procedure

I have a stored procedure : 

USE AdventureWorks2012;
GO
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    SET NOCOUNT ON;

    SELECT 
        FirstName, LastName, Department
    FROM 
        HumanResources.vEmployeeDepartmentHistory
    WHERE 
        FirstName = @FirstName AND LastName = @LastName
        AND EndDate IS NULL;
GO

It will cause SQL injection if I supply @LastName = '' OR '1' = '1' --''

I am new to SQL injection. I need some advice on how can i protect it

Upvotes: 1

Views: 171

Answers (1)

Gumbo
Gumbo

Reputation: 655229

There is crucial a difference whether you have a stored procedure which contains a parameterized statement:

CREATE PROCEDURE Proc @Param NVARCHAR(50) AS
    SELECT * FROM Table WHERE Col = @Param;
GO

Or a stored procedure that builds the statement dynamically in a string:

CREATE PROCEDURE Proc @Param NVARCHAR(50) AS
    DECLARE @Query AS NVARCHAR(500)
    SET @Query = 'SELECT * FROM Table WHERE Col = ''' + @Param + '''';
    EXEC @Query;
GO

With the former stored procedure, the data parameter is clearly distinguishable from the code.

However, with the latter stored procedure, the value of @Param is incorporated into the resulting SQL code:

SELECT * FROM Table WHERE Col = '<value of @Param>'

And if the value of @Param isn’t formatted properly, it may end the string literal prematurely and parts of the parameter value may not be interpreted as intended, i. e., not as a string.

Upvotes: 2

Related Questions