0

When I want to use dynamic SQL I can use:

DECLARE @DBName AS VARCHAR(40)='abc'
DECLARE @DynSQL AS VARCHAR(4000)
SET @DynSQL='ALTER DATABASE ['+@DBName+']'+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
EXECUTE @DynSQL

That works fine. But in order to prevent injection, I want to use the SP and tried this code:

DECLARE @DBName AS VARCHAR(40)='abc'
DECLARE @DynSQL AS VARCHAR(4000)
SET @DynSQL='ALTER DATABASE [@MyDBName] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
EXECUTE sp_executesql @DynSQL, N'@MyDBName VARCHAR(99)',@MyDBName=@DBName

Here, it never replaces the @MyDBName. Do I have to somehow escape the brackets or how can I use that? When I remove the brackets [] it works fine but then I can run into issue with strange DB names.

Chris
  • 143
  • 7
  • 1
    you can't do it like that for the same reason you can't just use a variable as an object name in any SQL statement, you are right with the first version except you should use `quotename(@DBName)` – Stu Apr 17 '23 at 15:13
  • ok thanks. Will use that then – Chris Apr 17 '23 at 15:16

0 Answers0