SQL Server

Verify DKIM-Signature Headers in Downloaded Email

See more DKIM / DomainKey Examples

Downloads email from an IMAP server and verifies the DKIM-Signature header(s) in each email, if present. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    DECLARE @imap int
    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
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.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

    DECLARE @dkim int
    EXEC @hr = sp_OACreate 'Chilkat.Dkim', @dkim OUT

    --  Download a max of 10 emails and verify any DKIM-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 > 10
      BEGIN
        SELECT @n = 10
      END

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    --  To verify DKIM-Signature headers, we need the exact unmodified MIME bytes of each email.
    DECLARE @mimeData int
    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

        --  Get the number of DKIM-Signature headers.
        DECLARE @numDkim int
        EXEC sp_OAMethod @dkim, 'NumDkimSigs', @numDkim OUT, @mimeData

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

            PRINT '------ DKIM Signature ' + @j

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

                PRINT 'Not valid.'
              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