加密 SqlDependency 创建的存储过程

本文关键字：存储过程 创建 SqlDependency 加密 | 更新日期: 2023-09-27 18:33:10

我创建了一个 SqlDependency ，以便在特定查询的结果更改时触发事件。

// Create a command
SqlConnection conn = new SqlConnection(connectionString);
string query = "SELECT MyColumn FROM MyTable;";
SqlCommand cmd = new SqlCommand(query, conn)
cmd.CommandType = CommandType.Text;
// Register a dependency
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += DependencyOnChange;

执行此代码时，将自动创建一个存储过程，其名称如下

SqlQueryNotificationStorageProcedure-82ae1b92-21c5-46ae-a2a1-511c4f849f76

此过程未加密，这违反了我提出的要求。我有两个选择：

  1. 说服客户自动生成的过程未加密并不重要，因为它只执行清理工作，不包含任何真实信息（感谢 ScottChamberlain 指出这一点）。
  2. 找到一种方法来加密 SqlT依赖项生成的存储过程。

如何完成选项 2？

相关存储过程的内容：

CREATE PROCEDURE [dbo].[SqlQueryNotificationStoredProcedure-b124707b-23fc-4002-aac3-4d52a71c5d6b]
AS
BEGIN
    BEGIN TRANSACTION;
    RECEIVE TOP (0) conversation_handle
    FROM [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];
    IF (
        SELECT COUNT(*)
        FROM [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b]
        WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
    ) > 0
    BEGIN
        IF (
            ( 
                SELECT COUNT(*)
                FROM sys.services
                WHERE NAME = 'SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b'
            ) > 0
        )
        DROP SERVICE [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];
        IF (OBJECT_ID('SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b', 'SQ') IS NOT NULL)
            DROP QUEUE [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];
        DROP PROCEDURE [SqlQueryNotificationStoredProcedure-b124707b-23fc-4002-aac3-4d52a71c5d6b];
    END
    COMMIT TRANSACTION;
END
GO

加密 SqlDependency 创建的存储过程

创建一个 DDL 触发器，用于检查是否正在创建名称为"SqlQueryNotificationStorageProcedure-"的过程，如果是，请立即将其更改WITH ENCRYPTION改为：

CREATE TRIGGER [TR_EncryptQueryNotificationProcedures] 
ON DATABASE
AFTER CREATE_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    SET ARITHABORT ON;
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;
    -- For debugging purposes only
    PRINT CONVERT(NVARCHAR(MAX), EVENTDATA());
    DECLARE @DatabaseName NVARCHAR(128);
    SET @DatabaseName = EVENTDATA().value(
        '(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Schema NVARCHAR(128);
    SET @Schema = EVENTDATA().value(
        '(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Name NVARCHAR(128);
    SET @Name = EVENTDATA().value(
        '(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Definition NVARCHAR(MAX);
    SELECT @Definition = 
        OBJECT_DEFINITION(
            OBJECT_ID(
                QUOTENAME(@DatabaseName) + '.' + 
                QUOTENAME(@Schema) + '.' + 
                QUOTENAME(@Name),
                'P'
            )
        )
    ;
    -- If the sproc is already encrypted, we can't do anything with it
    IF @Definition IS NULL RETURN;  
    SELECT @Definition = STUFF(
        @Definition, 
        CHARINDEX('CREATE', @Definition), 
        LEN('CREATE'), 
        'ALTER'
    );
    IF 
        @Name LIKE 'SqlQueryNotificationStoredProcedure-%' AND
        -- this should always be false since we can't read encrypted definitions, 
        -- but just to make sure 
        @Definition NOT LIKE '%WITH ENCRYPTION AS BEGIN%' 
    BEGIN;
        SET @Definition = REPLACE(
            @Definition, 'AS' + CHAR(13) + CHAR(10) + 'BEGIN', 
            'WITH ENCRYPTION AS BEGIN'
        );
        EXEC (@Definition);
    END;
END;
GO
ENABLE TRIGGER [TR_EncryptQueryNotificationProcedures] ON DATABASE;

免责声明：未针对实际的依赖关系通知进行测试，但基本思想是合理的。它非常脆弱，因为它取决于程序的确切形式，当然 - 使其更强大是可能的，但很乏味。

相关文章：