Home Forums Article Discussions Article Discussions by Author Discuss content posted by Paulo de Jesus A Simple Introduction to Dynamic SQL RE: A Simple Introduction to Dynamic SQL

  • February 12, 2018 at 4:31 am

    #1979399

    julian.fletcher - Monday, February 12, 2018 2:16 AM

    You quite rightly say

    When placing your Dynamic SQL code into production (typically in stored procedures), be careful about concatenating alphanumeric parameters directly because of SQL injection.

    but a few lines previously, you've done exactly that, ending up with a

    EXEC (@SQL)

    Shouldn't you be recommending the use of sp_executesql from the outset?

    Also, there's nothing about sp_executesql that makes it immune to SQL injection. sp_executesql allows for dynamic SQL to be parameterised, but not everything can be parameterised.

    Specifically database name can't.
    SELECT @SQL ='SELECT COUNT(1) ' +'FROM [' + @DBName + '].[dbo].[CommonTable] ' +'WHERE [InsertDate] = ''' + CAST(@Date AS VARCHAR) + ''''

    Hence, without some whitelisting, that will still be vulnerable, even if run with sp_executesql.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass