Before I ask - I do know about stored procedure is best way to prevent sql injection. We can achieve by parameterized stored procedure.

But in my application I am using dynamic queries as well in stored procedure where sql injection is possible.

Please suggest me a way to stop sql injection like any regular expression or some other way. Example :

CREATE PROCEDURE [dbo].[USP_BusinessSearch]
    @Product INT = NULL, 
    @BusinessName VARCHAR(100) = NULL

    AS

    DECLARE @AdditionalCriteria AS NVARCHAR(MAX)
    DECLARE @BaseQuery AS NVARCHAR(MAX)

    BEGIN
    SET NOCOUNT ON  
    SET @BaseQuery = 'Select * FROM Business WHERE ProductID = ' + @Product
    SET @AdditionalCriteria = ' AND BusinessName = '+@BusinessName+' '

    SET @BaseQuery = @BaseQuery + @AdditionalCriteria

    EXEC SP_EXECUTESQL @BaseQuery

    END

Thanks in advance.

Related posts

Recent Viewed