(SQL Server) Verify DomainKey-Signature Headers in Downloaded Email

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Connect to an IMAP server, login, select mailbox..
    -- Use TLS 
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    DECLARE @success int
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.someMailServer.com'
    IF @success = 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myLogin', 'myPassword'
        IF @success = 1
          BEGIN
            EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
          END
      END
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Note: DKIM-Signatures are much more common than DomainKey-Signature
    -- See DKIM-Signature Verify Sample.

    DECLARE @dkim int
    -- Use "Chilkat_9_5_0.Dkim" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Dkim', @dkim OUT

    -- Download a max of 10 emails and verify any DomainKey-Signature headers
    -- that are present.

    -- Download emails by sequence numbers (not UIDs).
    DECLARE @bUid int
    SELECT @bUid = 0
    DECLARE @seqNum int

    DECLARE @j int

    DECLARE @n int
    EXEC sp_OAGetProperty @imap, 'NumMessages', @n OUT
    IF @n > 50
      BEGIN
        SELECT @n = 50
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- To verify DomainKey-Signature headers, we need the exact unmodified MIME bytes of each email.
    DECLARE @mimeData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @mimeData OUT

    SELECT @seqNum = 1
    WHILE @seqNum <= @n
      BEGIN
        -- The FetchSingleBd method was introduced in v9.5.0.76
        EXEC sp_OAMethod @imap, 'FetchSingleBd', @success OUT, @seqNum, @bUid, @mimeData
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @imap
            EXEC @hr = sp_OADestroy @dkim
            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @mimeData
            RETURN
          END

        -- Note: DKIM-Signatures are much more common than DomainKey-Signature
        -- See DKIM-Signature Verify Sample.

        -- Get the number of DomainKey-Signature headers.
        DECLARE @numSigs int
        EXEC sp_OAMethod @dkim, 'NumDomainKeySigs', @numSigs OUT, @mimeData

        -- Verify each..
        SELECT @j = 0
        WHILE @j < @numSigs
          BEGIN

            PRINT '------ DomainKey Signature ' + @j

            EXEC sp_OAMethod @dkim, 'DomainKeyVerify', @success OUT, @j, @mimeData
            IF @success <> 1
              BEGIN

                PRINT 'Not valid.'
                EXEC sp_OAGetProperty @dkim, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
              END
            ELSE
              BEGIN

                PRINT 'valid.'
              END

            -- Show the additional information about the signature verification
            EXEC sp_OAGetProperty @dkim, 'VerifyInfo', @sTmp0 OUT
            EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
            EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
            PRINT @sTmp0

            -- The JSON contains information such as this:

            -- 	{
            -- 	  "domain": "amazonses.com",
            -- 	  "selector": "7v7vs6w47njt4pimodk5mmttbegzsi6n",
            -- 	  "publicKey": "MIGfMA0GCSqG...v2GvWPqGHz6uqeQIDAQAB",
            -- 	  "canonicalization": "relaxed/simple",
            -- 	  "algorithm": "rsa-sha256",
            -- 	  "signedHeaders": "Subject:From:To:Date:Mime-Version:Content-Type:References:Message-Id:Feedback-ID",
            -- 	  "verified": "yes"
            -- 	}

            SELECT @j = @j + 1
          END
        SELECT @seqNum = @seqNum + 1
      END

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @dkim
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @mimeData


END
GO