SQL Server 2016中如何基于传入Schema从JSON生成行数据？

Let's break down your problem: you're storing heterogeneous JSON data in SQL Server 2016, with an optional schema field to parse the JSON into a tabular format, but you're concerned about SQL injection risks with your current sp_executesql approach. Here are some practical strategies and alternatives to address this:

1. Secure Your Dynamic SQL Against Injection #

Your current approach works, but the key is to validate and sanitize the @jsonOutputSchema parameter before using it to build your dynamic SQL. Since the schema should only contain valid field definitions (name, data type, JSON path), you can enforce this with strict checks:

Example: Schema Validation Function #

Create a helper function to verify the schema string only contains allowed characters and valid syntax:

CREATE FUNCTION dbo.ValidateJsonOutputSchema(@schema NVARCHAR(MAX))
RETURNS BIT
AS
BEGIN
    -- Check for forbidden characters/keywords that indicate injection attempts
    IF PATINDEX('%[;--/*EXEC]%', @schema) > 0
        RETURN 0;
    
    -- Check that each line matches the expected format: [FieldName] [DataType] '$.Json.Path'
    DECLARE @line NVARCHAR(MAX);
    DECLARE schema_cursor CURSOR FOR
        SELECT value FROM STRING_SPLIT(@schema, CHAR(10)) WHERE TRIM(value) <> '';
    
    OPEN schema_cursor;
    FETCH NEXT FROM schema_cursor INTO @line;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Regex pattern for valid field definition (adjust based on your allowed data types)
        IF PATINDEX('%[a-zA-Z0-9_]+ [a-zA-Z0-9()]+ ''$.[a-zA-Z0-9_.]+''%', TRIM(@line)) = 0
        BEGIN
            CLOSE schema_cursor;
            DEALLOCATE schema_cursor;
            RETURN 0;
        END
        FETCH NEXT FROM schema_cursor INTO @line;
    END
    
    CLOSE schema_cursor;
    DEALLOCATE schema_cursor;
    RETURN 1;
END
GO

Then use this function before executing your dynamic SQL:

IF dbo.ValidateJsonOutputSchema(@jsonOutputSchema) = 1
BEGIN
    DECLARE @sql NVARCHAR(4000), @parmlist NVARCHAR(4000)
    SELECT @sql = N' SELECT * FROM OPENJSON ( @z_json ) WITH ( ' + @jsonOutputSchema + ')'
    SELECT @parmlist = N' @z_json nvarchar(max) '
    EXEC sp_executesql @sql, @parmlist, @json
END
ELSE
BEGIN
    RAISERROR('Invalid schema format detected.', 16, 1);
END

2. Store Schemas as Structured Data (Instead of Raw Strings) #

Instead of passing raw schema strings, store your valid schemas in a structured table. This way, you only reference schema IDs in your functions/procedures, eliminating direct user input of schema syntax:

Step 1: Create a Schema Storage Table #

CREATE TABLE dbo.JsonSchemas (
    SchemaID INT PRIMARY KEY IDENTITY(1,1),
    SchemaName NVARCHAR(100) NOT NULL,
    FieldName NVARCHAR(100) NOT NULL,
    DataType NVARCHAR(50) NOT NULL,
    JsonPath NVARCHAR(200) NOT NULL
);

Step 2: Populate with Your Existing Schemas #

For example, add your sample schema:

INSERT INTO dbo.JsonSchemas (SchemaName, FieldName, DataType, JsonPath)
VALUES
('OrderDetails', 'Number', 'varchar(200)', '$.Order.Number'),
('OrderDetails', 'Date', 'datetime', '$.Order.Date'),
('OrderDetails', 'Customer', 'varchar(200)', '$.AccountNumber'),
('OrderDetails', 'Quantity', 'int', '$.Item.Quantity');

Step 3: Generate Schema Dynamically from the Table #

Modify your procedure to accept a SchemaID instead of a raw schema string:

CREATE PROCEDURE dbo.ParseJsonWithSchema
    @json NVARCHAR(MAX),
    @SchemaID INT = NULL
AS
BEGIN
    IF @SchemaID IS NULL
    BEGIN
        -- Return raw JSON if no schema is specified
        SELECT @json AS RawJson;
        RETURN;
    END

    -- Generate the WITH clause from the structured schema table
    DECLARE @jsonOutputSchema NVARCHAR(MAX);
    SELECT @jsonOutputSchema = STRING_AGG(
        CONCAT(QUOTENAME(FieldName), ' ', DataType, ' ''', JsonPath, ''''),
        ', ' + CHAR(10)
    )
    FROM dbo.JsonSchemas
    WHERE SchemaID = @SchemaID;

    IF @jsonOutputSchema IS NULL
    BEGIN
        RAISERROR('Schema ID not found.', 16, 1);
        RETURN;
    END

    -- Execute the safe dynamic SQL (schema is generated from trusted source)
    DECLARE @sql NVARCHAR(4000), @parmlist NVARCHAR(4000)
    SELECT @sql = N' SELECT * FROM OPENJSON ( @z_json ) WITH ( ' + @jsonOutputSchema + ')'
    SELECT @parmlist = N' @z_json nvarchar(max) '
    EXEC sp_executesql @sql, @parmlist, @json
END
GO

This approach completely eliminates injection risks because the schema is built from a trusted, controlled table—users can't input arbitrary SQL syntax.

3. Use Schema-Specific Table-Valued Functions #

Since you only have 7 schemas (and growing slowly), create a dedicated table-valued function for each schema. This avoids dynamic SQL entirely and provides type safety:

Example Function for Order Details Schema #

CREATE FUNCTION dbo.ParseOrderDetailsJson(@json NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN (
    SELECT * FROM OPENJSON(@json) WITH (
        Number varchar(200) '$.Order.Number',
        Date datetime '$.Order.Date',
        Customer varchar(200) '$.AccountNumber',
        Quantity int '$.Item.Quantity'
    )
);
GO

Then, in your procedure, check which schema to use and call the corresponding function:

CREATE PROCEDURE dbo.ParseJsonWithSchema
    @json NVARCHAR(MAX),
    @SchemaName NVARCHAR(100) = NULL
AS
BEGIN
    IF @SchemaName IS NULL
    BEGIN
        SELECT @json AS RawJson;
        RETURN;
    END

    -- Call the appropriate function based on schema name
    IF @SchemaName = 'OrderDetails'
        SELECT * FROM dbo.ParseOrderDetailsJson(@json);
    ELSE IF @SchemaName = 'AnotherSchema'
        SELECT * FROM dbo.ParseAnotherSchemaJson(@json);
    -- Add more ELSE IF blocks for additional schemas
    ELSE
        RAISERROR('Unknown schema name.', 16, 1);
END
GO

The downside is that you need to create a new function for each new schema, but this is a small tradeoff for maximum security and performance (SQL Server can optimize these functions better than dynamic SQL).

Final Notes #

• Your original sp_executesql approach is viable if you add strict input validation, but the structured schema storage or schema-specific functions are more robust long-term.
• SQL Server 2016 doesn't have built-in support for parameterizing the OPENJSON WITH clause, so dynamic SQL (with safeguards) or schema-specific functions are your best bets.

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