SQL Server存储过程安全传递架构名参数的非动态SQL方案

Great question—passing schema and table names safely in SQL Server stored procedures is a common pain point, especially when you want to steer clear of the SQL injection risks that come with naive dynamic SQL. Since you only have two optional schemas to work with, you’ve got some solid, safe alternatives beyond the basic dynamic SQL approach. Let’s break them down:

1. Safely Validated Dynamic SQL (Most Flexible) #

While this still uses dynamic SQL, you can eliminate injection risks by rigorously validating inputs against system catalog views and restricting schemas to your predefined options. This is the most scalable choice if you might add more schemas later.

How it works: #

• First, block any schema names that aren’t your two allowed options.
• Verify the table actually exists under the specified schema using sys.tables and sys.schemas.
• Use QUOTENAME() to escape special characters in object names, and sys.sp_executesql for execution (which keeps data parameters parameterized if you need them later).

Example Code: #

CREATE PROCEDURE dbo.GetTableData
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Reject invalid schemas upfront
    IF @SchemaName NOT IN ('SchemaA', 'SchemaB')
    BEGIN
        RAISERROR('Invalid schema name. Only SchemaA or SchemaB are allowed.', 16, 1);
        RETURN;
    END

    -- Ensure the table exists in the target schema
    IF NOT EXISTS (
        SELECT 1 
        FROM sys.tables t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE s.name = @SchemaName AND t.name = @TableName
    )
    BEGIN
        RAISERROR('Table %s.%s does not exist.', 16, 1, @SchemaName, @TableName);
        RETURN;
    END

    -- Build and execute safe dynamic SQL
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';';

    EXEC sys.sp_executesql @SQL;
END

2. Static SQL with Branching (Most Secure) #

Since you only have two possible schemas, you can avoid dynamic SQL entirely for the schema portion by using IF/ELSE branches. This is bulletproof against injection for the schema part, and you can still safely handle the table name with validation and QUOTENAME().

How it works: #

• Validate the schema is one of your allowed options.
• Check the table exists in the target schema.
• Use separate static SQL blocks for each schema, only dynamically handling the pre-validated table name.

Example Code: #

CREATE PROCEDURE dbo.GetTableData
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate inputs first
    IF @SchemaName NOT IN ('SchemaA', 'SchemaB')
    BEGIN
        RAISERROR('Invalid schema name. Only SchemaA or SchemaB are allowed.', 16, 1);
        RETURN;
    END

    IF NOT EXISTS (
        SELECT 1 
        FROM sys.tables t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE s.name = @SchemaName AND t.name = @TableName
    )
    BEGIN
        RAISERROR('Table %s.%s does not exist.', 16, 1, @SchemaName, @TableName);
        RETURN;
    END

    -- Branch to schema-specific SQL
    IF @SchemaName = 'SchemaA'
    BEGIN
        DECLARE @SQLA NVARCHAR(MAX) = N'SELECT * FROM SchemaA.' + QUOTENAME(@TableName);
        EXEC sys.sp_executesql @SQLA;
    END
    ELSE IF @SchemaName = 'SchemaB'
    BEGIN
        DECLARE @SQLB NVARCHAR(MAX) = N'SELECT * FROM SchemaB.' + QUOTENAME(@TableName);
        EXEC sys.sp_executesql @SQLB;
    END
END

3. Synonyms (Best for Consistent Table Structures) #

If the tables in both schemas have identical structures, you can use synonyms to abstract the schema/table reference. You’ll dynamically create a synonym pointing to the target object (safely validated) and then query it with static SQL.

How it works: #

• Validate schema and table existence.
• Create a session-specific synonym to avoid concurrency conflicts.
• Query the synonym with static SQL, then clean up the synonym afterward.

Example Code: #

CREATE PROCEDURE dbo.GetTableData
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate inputs
    IF @SchemaName NOT IN ('SchemaA', 'SchemaB')
    BEGIN
        RAISERROR('Invalid schema name. Only SchemaA or SchemaB are allowed.', 16, 1);
        RETURN;
    END

    IF NOT EXISTS (
        SELECT 1 
        FROM sys.tables t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE s.name = @SchemaName AND t.name = @TableName
    )
    BEGIN
        RAISERROR('Table %s.%s does not exist.', 16, 1, @SchemaName, @TableName);
        RETURN;
    END

    -- Create a session-unique synonym name
    DECLARE @SynonymName NVARCHAR(128) = N'TempTarget_' + CAST(@@SPID AS NVARCHAR(10));
    DECLARE @SQL NVARCHAR(MAX);

    -- Clean up any existing synonym for this session
    IF EXISTS (SELECT 1 FROM sys.synonyms WHERE name = @SynonymName)
    BEGIN
        SET @SQL = N'DROP SYNONYM dbo.' + QUOTENAME(@SynonymName) + N';';
        EXEC sys.sp_executesql @SQL;
    END

    -- Create synonym pointing to the target table
    SET @SQL = N'CREATE SYNONYM dbo.' + QUOTENAME(@SynonymName) + N' FOR ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';';
    EXEC sys.sp_executesql @SQL;

    -- Query the synonym
    SET @SQL = N'SELECT * FROM dbo.' + QUOTENAME(@SynonymName) + N';';
    EXEC sys.sp_executesql @SQL;

    -- Clean up the synonym
    SET @SQL = N'DROP SYNONYM dbo.' + QUOTENAME(@SynonymName) + N';';
    EXEC sys.sp_executesql @SQL;
END

Final Tips #

• Always validate inputs first: Checking that the schema is allowed and the table exists is non-negotiable for security.
• Use QUOTENAME(): It escapes special characters (like spaces or reserved words) in object names, preventing injection attempts that rely on unescaped text.
• Prefer sys.sp_executesql over EXEC(): It allows parameterization of data values if you add filters later, which is safer than concatenating user input directly into SQL strings.

内容的提问来源于stack exchange，提问作者xzk