Post reply

Does SSRS Report with Stored Procedure execute SQL string?

  • 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.

     

  • 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)

  • 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...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply