Exec stored procedure with two string parameters

Question

I wrote this procedure and defined two strings as parameters for it :

CREATE PROCEDURE [dbo].[spo_SelectTable1sDynamic]
    @WhereCondition nvarchar(500),
    @OrderByExpression nvarchar(250) = NULL
AS
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    DECLARE @SQL nvarchar(3250)

    SET @SQL = 'SELECT
                    [col1], [col2], [col3], [col4]
                FROM
                    [dbo].[Table1]
                WHERE
               ' + @WhereCondition

    IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
    BEGIN
        SET @SQL = @SQL + 'ORDER BY ' + @OrderByExpression
    END

    EXEC sp_executesql @SQL

I should pass two strings for @whereconditio and @OrderByExpression parameters.

How can I execute this stored procedure? I use this code but get an error:

EXECUTE spo_SelectTable1sDynamic N'col1='book'' , N'col1 '

Possible SQL Injection Especially @wherecondtition passed entirely as string — Lukasz Szozda
– Lukasz Szozda, Commented Jan 28, 2016 at 7:24
You mean is that I must pass value only for sp no where condition . Is right? — hmahdavi
– hmahdavi, Commented Jan 28, 2016 at 7:37

Lukasz Szozda · Accepted Answer · 2016-01-28 07:51:11Z

First of all your code is very vulnerable to SQL Injection attack.

Second SELECT * FROM tbl WHERE @condition is curse:

If you are considering to write the procedure
CREATE PROCEDURE search_sp @condition varchar(8000) AS    
SELECT * FROM tbl WHERE @condition
Just forget it. If you are doing this, you have not completed the transition to use stored procedure and you are still assembling your SQL code in the client.

Possible solution is to use Dynamic Search Conditions and avoid dynamic-SQL at all:

CREATE PROCEDURE [dbo].[spo_SelectTable1sDynamic]
    @col1 NVARCHAR(1000),   -- depending on business cases
    @col2 INT ...,
    @sortColumn SYSNAME
AS
BEGIN
  SELECT ....
  FROM table ...
  WHERE (col1 = @col1 OR @col1 IS NULL)
    AND (col2 = @col2 OR @col2 IS NULL) ...
  ORDER BY
     CASE @sortColumn
          WHEN 'col1' THEN col1 
          WHEN 'col2' THEN col2
          ...
     END
END

User can call it as:

EXEC [dbo].[spo_SelectTable1sDynamic] @col1 = 'aaa'
EXEC [dbo].[spo_SelectTable1sDynamic] @col2 = 10
EXEC [dbo].[spo_SelectTable1sDynamic] @col1 = 'bbb', @col2 = 16
-- or even
EXEC [dbo].[spo_SelectTable1sDynamic
-- to get all possible rows without any filter

Collectives™ on Stack Overflow

Exec stored procedure with two string parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related