-1

In my web application which returns a comma-separated string and I need to pass that string to the stored procedure, to retrieve the data from the database. To achieve that, I simply wrote the following stored procedure.

CREATE PROCEDURE [dbo].[spGetAllUserBySupervisor]
(
    @values varchar(8000) 
)
AS
BEGIN
    BEGIN TRY

    SET NOCOUNT ON;
    declare @sql nvarchar(200)

    set @sql = 'SELECT * from [user] WHERE  FkSpID in (' + @values + ')'
    execute sp_executesql @sql   

    END TRY
    BEGIN CATCH
        -- Raise an error with the details of the exception
        DECLARE @ErrMsg       VARCHAR(4000),
                @ErrSeverity  INT

        SELECT @ErrMsg = ERROR_MESSAGE(),
               @ErrSeverity = ERROR_SEVERITY()

        RAISERROR(@ErrMsg, @ErrSeverity, 1);
    END CATCH
END

This is how I execute the procedure:

EXEC [spGetAllUserBySupervisor]
        @values = N'10004,10025'

EXEC [spGetAllUserBySupervisor]
        @values = N'10004,10025,10050'

This procedure works fine but, When considering the security this is not the best way to this. Because I think someone can SQL inject a dangerous clause. So what is the most appropriate solution to do this?

Sachith Wickramaarachchi
  • 5,546
  • 6
  • 39
  • 68
  • 2
    You can take a look at `Table-Valued Parameter` https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017 – Squirrel Jun 17 '19 at 03:14
  • Sachith give me a try. if you encounter problem, post a new question – Squirrel Jun 17 '19 at 03:35

1 Answers1

0

what version of SQL are you using? from 2016 you can use STRING_SPLIT:

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Or you could write your own function to split the string into a table. This method would remove the dynamic SQL I think you're wanting to get rid of.

Rodney Ellis
  • 727
  • 2
  • 13
  • 28