Does SSRS Report with Stored Procedure execute SQL string?

• danyeungw

  Old Hand

  Points: 337

  More actions

  November 12, 2024 at 5:15 pm

  #4480674

  I declare @Where based on the input parameter in the stored procedure.

  Set @SQL = 'Select * from Table1 ' + @where
  EXECUTE(@SQL)

  I created a dataset in SSRS to run the stored procedure. It returned no fields. Does SSRS Report with Stored Procedure execute SQL string?  Thanks.

   

• danyeungw

  Old Hand

  Points: 337

  More actions

  November 12, 2024 at 6:01 pm

  #4480709

  I figured it out. Add PRINT(@SQL).

  Set @SQL = 'Select * from Table1 ' + @where
  EXECUTE(@SQL)
  PRINT(@SQL)

• Zidar

  SSC Enthusiast

  Points: 138

  More actions

  November 12, 2024 at 7:29 pm

  #4480764

  Imagine if somebody passes this parameter:

  SET @Where = '1=1; DELETE TAble1, DELETE TAblee2...'

  I assume the parameter comes from a front end app. SELECT part will execute, but so will DELETE TAble parts as well. Search for SQL Injection and try something else. There is another way in T-SQL to execute SQL string, by passing and checking parameters. My Copilot gave me this:

  DECLARE @SQLString NVARCHAR(500);
  DECLARE @ParmDefinition NVARCHAR(500);
  DECLARE @City NVARCHAR(30);
  
  SET @City = 'New York';
  SET @SQLString = N'SELECT * FROM Person.Address WHERE City = @City';
  SET @ParmDefinition = N'@City NVARCHAR(30)';
  
  EXEC sp_executesql @SQLString, @ParmDefinition, @City = @City;

  Much better, parameters cannot change you SQL string by adding malicious code. It requires more work, but it is worth learning.

  Zidar's Theorem: The best code is no code at all...