允許創建表（動態名稱）並插入但僅此而已

我正在嘗試找到一種方法，允許應用程序在 SQL Server 2019 上創建表並將數據插入其中，同時防止注入攻擊，以防應用程序憑據洩漏。在編寫可以並行執行的程式碼和編寫不受 sql 注入攻擊的動態 sql 時，我的經驗是有限的。

表名基於來自應用程序的輸入，即如果輸入為“nds”，則表名應為lake.nds_raw_log。

據我了解，沒有辦法通過直接授予此應用程序的角色權限來執行此操作，因為創建表與刪除或更改它們沒有分開。

我想出的是作為 dbo 執行儲存過程。當然它不長，但我有兩個問題：

• 感覺很做作，根據我的經驗，有一種更簡單的方法。
• 我相信如果我在查詢新創建的表時檢索到錯誤的表，我需要將它作為可序列化執行以避免孤立表。這實際上不應該是一個大問題，因為它不會在第一次開始生產後經常發生，所以也許我不應該關心它。

   create procedure [lake].[create_terminal_raw_log_table]
   (
       @terminal_name nvarchar(100)
   )
   with execute as 'dbo'
   as
       begin try
           set transaction isolation level serializable
           begin transaction
               --create table
               declare @dynamic_sql nvarchar(1000) =
                   'create table [lake].' + quotename(@terminal_name) + '
                   (
                       id bigint not null,
                       [timestamp] datetime2(3) not null,
                       cmd varbinary(max) not null
                   );'
               exec sp_executesql @dynamic_sql
   
               /*get name of new table, this is why I believe that I need serializable isolation
               since other tables can be created in parallel*/
               declare @table_name nvarchar(100) =
               (
                   select top 1
                       [name] as table_name
                   from sys.tables
                   order by create_date desc
               )
       
               --rename table
               declare
                   @old_name nvarchar(100) = '[lake].' + @table_name,
                   @new_name nvarchar(100) = @table_name + '_raw_log'
               begin try
                   exec sp_rename
                       @objname = @old_name,
                       @newname = @new_name
               end try
               begin catch
                   set @dynamic_sql = 'drop table ' + @old_name
                   exec sp_executesql @dynamic_sql
                   ;throw
               end catch
   
               --create primary key
               set @dynamic_sql = 'alter table [lake].' + @new_name + ' add constraint pk__' + @new_name + ' primary key(id)'
               exec sp_executesql @dynamic_sql
   
           commit transaction
       end try
       begin catch
           rollback --I thought a rollback would occur when I throw after dropping the table but that doesn't seem to be the case
           ;throw
       end catch

所以我想這可以歸結為3個問題：

• 這個儲存過程實際上可以免受 SQL 注入攻擊嗎？
• 有更簡單的方法嗎？
• 將事務級別設置為可序列化將保護程式碼在從 sys.tables 中選擇時不會選擇錯誤的表是否正確？

除非我遺漏了業務需求或程式碼中的細節，否則我認為您正在使您的程序變得比必要的複雜得多。

不需要創建表，然後查找表的名稱，然後重命名表，然後添加主鍵約束，所有這些都作為單獨的步驟，包裝在事務中以確保一致性。相反，您可以一步完成。

關於您的程式碼的其他一些程式碼審查類型註釋：

• 您正在使用nvarchar變數來支持 unicode，但使用“正常”單引號進行分配。要支持 unicode 字元串，您需要使用N'前綴來引用 unicode 字元串。
• 有一個內聯約束創建是可能的CREATE TABLE
• 引用對象時應盡可能使用模式前綴，包括sys在sp_executesql.
• 永遠不要依賴儲存過程的順序位置。在傳遞參數時顯式命名參數。大多數開發人員（包括用於系統儲存過程的 Microsoft）都避免更改參數的位置——但如果他們這樣做，如果您假設該位置，它將破壞您的程式碼。命名參數從來沒有這個問題。

這是我的程序版本：

CREATE OR ALTER PROCEDURE [lake].[create_terminal_raw_log_table]
   (
       @terminal_name nvarchar(100)
   )
WITH EXECUTE AS 'dbo'
AS
   DECLARE @table_name nvarchar(128);
   DECLARE @dynamic_sql nvarchar(1000);
   
   -- We want the table name to be the input value with `_raw_log` appended:
   -- I could skip even using this variable, 
     -- and just use `@terminal_name + N'_raw_log'` 
     -- in the two spots I reference @table_name
     -- but if you use the table name a bunch of times, 
     -- this is easier.
   SET @table_name = @terminal_name + N'_raw_log';

   --Use dynamic SQL to create the table
       --With the PK Constraint created in-line.
   SET @dynamic_sql = N'create table [lake].' + QUOTENAME(@table_name) + N'
                   (
                       id bigint not null,
                       [timestamp] datetime2(3) not null,
                       cmd varbinary(max) not null,
                       CONSTRAINT ' + QUOTENAME(N'PK_' + @table_name) + N' 
                           PRIMARY KEY CLUSTERED (id)
                   );';
   EXEC sys.sp_executesql @stmt = @dynamic_sql;
GO

確保你測試！

您需要進行一些快速的健全性測試，以確保您的程序確實有效。我喜歡確保使用 unicode 字元（我總是使用表情符號）和任何其他特定問題（如 SQL 注入、對象名稱中的空格、最小或最大長度等）進行測試。

例如：

EXEC [lake].[create_terminal_raw_log_table] @terminal_name = N'nds';
EXEC [lake].[create_terminal_raw_log_table] @terminal_name = N'amtwo';
EXEC [lake].[create_terminal_raw_log_table] @terminal_name = N'; DROP PROCEDURE [lake].[create_terminal_raw_log_table];';
EXEC [lake].[create_terminal_raw_log_table] @terminal_name = N'It Works!! 🎉🎉';

SELECT 
   TableName = o.[name]
FROM sys.objects AS o 
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE s.name = N'lake'
AND o.type = 'U';

返回這些結果：

TableName
-------------------------------------------------------------------
nds_raw_log
amtwo_raw_log
; DROP PROCEDURE [lake].[create_terminal_raw_log_table];_raw_log
It Works!! 🎉🎉_raw_log

(4 rows affected)

引用自：https://dba.stackexchange.com/questions/307467