c# – 加密由SqlDependency创建的存储过程

发布时间：2020-12-15 07:45:00 所属栏目：百科来源：网络整理

导读：我创建了一个 SqlDependency,以便在特定查询的结果发生变化时触发事件. // Create a commandSqlConnection conn = new SqlConnection(connectionString);string query = "SELECT MyColumn FROM MyTable;";SqlCommand cmd = new SqlCommand(query,conn)cmd.Co

我创建了一个 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;

执行此代码时,将自动创建一个名为的存储过程

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

此程序未加密,违反了我的要求.我有两个选择：

>说服客户,自动生成的程序未加密并不重要,因为它只进行清理工作并且不包含任何真实信息(感谢ScottChamberlain指出这一点).
>找到一种方法来加密SqlDependency生成的存储过程.

我怎样才能完成选项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

解决方法

创建一个DDL触发器,检查是否正在创建名为“SqlQueryNotificationStoredProcedure-”的过程,如果是,请立即使用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;

免责声明：未针对实际依赖性通知进行测试,但基本思路是合理的.它非常脆弱,因为它取决于程序的确切形式,当然 – 使其更加健壮是可能的,但是单调乏味.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!