Dynamic SQL in stored procedure RSS

• • Reply
  

  AspRoMar

  Member

  2 Points

  8 Posts

  Dynamic SQL in stored procedure

  Nov 11, 2012 09:39 PM|LINK

  Hi guys,

  I'm having trouble with executing stored procedure. Procedure use parameters in statement and I can't make it work, here is the code:

  ALTER PROCEDURE [dbo].[PodaciStranka]
  	(
  	@sidx nvarchar(50),
  	@sord nvarchar(50),
  	@searchTerm nvarchar(50) = NULL,
  	@start int = NULL,
  	@limit int = NULL,
  	@search nvarchar(50) = NULL,
  	@searchTerm2 nvarchar(50) = NULL,
  	@search2 nvarchar(50) = NULL,
  	@searchTerm3 nvarchar(50) = NULL,
  	@search3 nvarchar(50) = NULL
  	)
  	AS
  
  	EXEC ('WITH StrankaPagging AS (SELECT StrankaID, Ime, Prezime, Mjesto, PostanskiBroj, Ulica, Telefon, Mobitel, Faks, Email, Opaska, Oib, KontaktOsoba, ROW_NUMBER() OVER (ORDER BY ime asc) AS BrojRedova
  	FROM Stranka INNER JOIN Mjesto ON Mjesto.MjestoID=Stranka.MjestoID WHERE '+@search+' like '+@searchTerm+
  	' SELECT * FROM StrankaPagging WHERE BrojRedova BETWEEN 1 AND 10)')

  Thanks in advance
  
  

• • Reply
  

  spapim

  Contributor

  2686 Points

  393 Posts

  Re: Dynamic SQL in stored procedure

  Nov 11, 2012 09:50 PM|LINK

  Hi,

  I suspect that the closing parenthesis of CTE is in the wrong place:

  ') SELECT * FROM StrankaPagging WHERE BrojRedova BETWEEN 1 AND 10')

  Hope this helps. 

  www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial
  www.imobiliariasemsuzano.com.br
• • Reply
  

  sandeepmitta...

  Contributor

  6957 Points

  1082 Posts

  Re: Dynamic SQL in stored procedure

  Nov 12, 2012 04:00 AM|LINK

  ALTER PROCEDURE [dbo].[PodaciStranka] (
      @sidx nvarchar(50),
      @sord nvarchar(50),
      @searchTerm nvarchar(50) = NULL,
      @start int = NULL,
      @limit int = NULL,
      @search nvarchar(50) = NULL,
      @searchTerm2 nvarchar(50) = NULL,
      @search2 nvarchar(50) = NULL,
      @searchTerm3 nvarchar(50) = NULL,
      @search3 nvarchar(50) = NULL
  )
  AS
  begin
  	declare @query nvarchar(max)
  	set @query = ';WITH StrankaPagging AS (
  		SELECT StrankaID, Ime, Prezime, Mjesto, PostanskiBroj, Ulica, Telefon, Mobitel, Faks, Email, Opaska, Oib, KontaktOsoba, ROW_NUMBER() OVER (ORDER BY ime asc) AS BrojRedova
  		FROM Stranka INNER JOIN Mjesto ON Mjesto.MjestoID=Stranka.MjestoID
  		WHERE ' + @search + ' = ''' + @searchTerm + ''')
  		SELECT * FROM StrankaPagging WHERE BrojRedova BETWEEN 1 AND 10)'
         exec (@query)
  end

  If looking for exact match, then use "=" like done in above query, but if looking for @searchTerm in @search column then only use "like". In that case, replace

  WHERE ' + @search + ' = ''' + @searchTerm + ''')

  with

  WHERE ' + @search + ' like ''%' + @searchTerm + '%'')

  in the above stored proc

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Nov 12, 2012 04:00 AM
  • Edited by sandeepmittal11 on Nov 12, 2012 04:01 AM
  • Edited by sandeepmittal11 on Nov 12, 2012 04:01 AM
  • Edited by sandeepmittal11 on Nov 12, 2012 04:02 AM
• • Reply
  

  jeeveshfulor...

  Participant

  1576 Points

  289 Posts

  Re: Dynamic SQL in stored procedure

  Nov 12, 2012 09:31 AM|LINK

  try with below code:-

  ALTER PROCEDURE [dbo].[PodaciStranka]
          (
          @sidx nvarchar(50),
          @sord nvarchar(50),
          @searchTerm nvarchar(50) = NULL,
          @start int = NULL,
          @limit int = NULL,
          @search nvarchar(50) = NULL,
          @searchTerm2 nvarchar(50) = NULL,
          @search2 nvarchar(50) = NULL,
          @searchTerm3 nvarchar(50) = NULL,
          @search3 nvarchar(50) = NULL
          )
          AS
  
          EXEC ('WITH StrankaPagging AS (SELECT StrankaID, Ime, Prezime, Mjesto, PostanskiBroj, Ulica, Telefon, Mobitel, Faks, Email, Opaska, Oib, KontaktOsoba, ROW_NUMBER() OVER (ORDER BY ime asc) AS BrojRedova
          FROM Stranka INNER JOIN Mjesto ON Mjesto.MjestoID=Stranka.MjestoID WHERE '+@search+' like '+''''+@searchTerm+ ''''+')'+
          ' SELECT * FROM StrankaPagging WHERE BrojRedova BETWEEN 1 AND 10')

  OR do like this:-

  ALTER PROCEDURE [dbo].[PodaciStranka]
          (
          @sidx nvarchar(50),
          @sord nvarchar(50),
          @searchTerm nvarchar(50) = NULL,
          @start int = NULL,
          @limit int = NULL,
          @search nvarchar(50) = NULL,
          @searchTerm2 nvarchar(50) = NULL,
          @search2 nvarchar(50) = NULL,
          @searchTerm3 nvarchar(50) = NULL,
          @search3 nvarchar(50) = NULL
          )
          AS
  		BEGIN
  				DECLARE @Sql VARCHAR(MAX)
  				SET @Sql = 'WITH StrankaPagging AS (SELECT StrankaID, Ime, Prezime, Mjesto, PostanskiBroj, Ulica, Telefon,
  				              Mobitel, Faks, Email, Opaska, Oib, KontaktOsoba, ROW_NUMBER() OVER (ORDER BY ime asc) AS BrojRedova
                                FROM Stranka INNER JOIN Mjesto ON Mjesto.MjestoID=Stranka.MjestoID WHERE '+@search+' like '+''''+
                                @searchTerm+ ''''+')'+' SELECT * FROM StrankaPagging WHERE BrojRedova BETWEEN 1 AND 10'
  			   EXEC (@Sql)
  		END

  
  
  

  • Edited by jeeveshfuloria08 on Nov 12, 2012 09:34 AM
  • Marked as answer by AspRoMar on Nov 12, 2012 10:03 PM
• • Reply
  

  RichardD

  Contributor

  3950 Points

  549 Posts

  Re: Dynamic SQL in stored procedure

  Nov 12, 2012 06:45 PM|LINK

  This is a perfect example of why stored procedures DO NOT protect against SQL injection! By using a dynamic SQL query, a simple search for ';DROP TABLE Stranka;-- will have devastating consequences.

  Assuming the @search parameter is not under user control - ie: it is one of a fixed list of values, and you have validation methods in place to ensure that it matches one of these values - you can use the sp_executesql stored procedure to minimize this risk:

  DECLARE @stmt nvarchar(max);
  DECLARE @params nvarchar(max);
  
  SET @stmt = N'WITH StrankaPagging AS
  (
     SELECT
        ...
        ROW_NUMBER() OVER (ORDER BY ime asc) AS BrojRedova
     FROM
        Stranka INNER JOIN Mjesto
        ON Mjesto.MjestoID = Stranka.MjestoID
     WHERE
        ' + QuoteName(@search) + N' Like @searchTerm
  )
  SELECT
     *
  FROM
     StrankaPagging
  WHERE
     BrojRedova BETWEEN 1 AND 10';
  
  SET @params = N'@searchTerm nvarchar(50)';
  
  EXEC sp_executesql @stmt = @stmt, @params = @params, @searchTerm = @searchTerm;

• • Reply
  

  AspRoMar

  Member

  2 Points

  8 Posts

  Re: Dynamic SQL in stored procedure

  Nov 12, 2012 10:03 PM|LINK

  Thanks to everyone, you all helped me very much.