SQL Server

Verify XML Signature with External URL References

See more XML Digital Signatures Examples

Demonstrates how to verify an XML digital signature that includes references to URLs where the data to be digested is on a web server. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    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.

    -- The signed XML we wish to verify contains external references such as this:

    --     <ds:Reference Id="xmldsig-e7ae7ce2-9133-4d56-bd97-0a6aef738cc2-ref0" URI="https://www.chilkatsoft.com/images/starfish.jpg">
    --       <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --       <ds:DigestValue>AOU810yJV5Np/DnO29qpObqiTSTTCDvxGsX5ayiTYXI=</ds:DigestValue>
    --     </ds:Reference>
    --     <ds:Reference Id="xmldsig-e7ae7ce2-9133-4d56-bd97-0a6aef738cc2-ref1" URI="https://www.chilkatsoft.com/hamlet.xml">
    --       <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --       <ds:DigestValue>4sRRyWOzC7EOic4fQ9+Op1pa10DbgoBGjBvkq09LZmE=</ds:DigestValue>
    --     </ds:Reference>

    DECLARE @verifier int
    EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @verifier OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- First load the signed XML
    DECLARE @sbSignedXml int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSignedXml OUT

    EXEC sp_OAMethod @sbSignedXml, 'LoadFile', @success OUT, 'qa_data/xml_dsig_verify/signedWithExternalUrlRefs.xml', 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load signed XML.'
        EXEC @hr = sp_OADestroy @verifier
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSignedXml
        RETURN
      END

    EXEC sp_OAMethod @verifier, 'LoadSignatureSb', @success OUT, @sbSignedXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @verifier
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSignedXml
        RETURN
      END

    -- Iterate over each reference.  If it is an external URL reference, download the data and provide it to the verifier.
    DECLARE @sbRefUri int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRefUri OUT

    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    DECLARE @numRefs int
    EXEC sp_OAGetProperty @verifier, 'NumReferences', @numRefs OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numRefs
      BEGIN
        EXEC sp_OAMethod @verifier, 'IsReferenceExternal', @iTmp0 OUT, @i
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @sbRefUri, 'Clear', NULL
            EXEC sp_OAMethod @verifier, 'ReferenceUri', @sTmp0 OUT, @i
            EXEC sp_OAMethod @sbRefUri, 'Append', @success OUT, @sTmp0
            EXEC sp_OAMethod @sbRefUri, 'StartsWith', @iTmp0 OUT, 'https://', 0
            IF @iTmp0 = 1
              BEGIN

                EXEC sp_OAMethod @sbRefUri, 'GetAsString', @sTmp0 OUT
                PRINT 'External URL Reference: ' + @sTmp0

                -- Download the data at the URL and provide to the verifier.
                EXEC sp_OAMethod @sbRefUri, 'GetAsString', @sTmp0 OUT
                EXEC sp_OAMethod @http, 'DownloadBd', @success OUT, @sTmp0, @bd
                IF @success = 0
                  BEGIN
                    EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
                    PRINT @sTmp0
                    EXEC @hr = sp_OADestroy @verifier
                    EXEC @hr = sp_OADestroy @http
                    EXEC @hr = sp_OADestroy @sbSignedXml
                    EXEC @hr = sp_OADestroy @sbRefUri
                    EXEC @hr = sp_OADestroy @bd
                    RETURN
                  END
                EXEC sp_OAMethod @verifier, 'SetRefDataBd', @success OUT, @i, @bd
                IF @success = 0
                  BEGIN
                    EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
                    PRINT @sTmp0
                    EXEC @hr = sp_OADestroy @verifier
                    EXEC @hr = sp_OADestroy @http
                    EXEC @hr = sp_OADestroy @sbSignedXml
                    EXEC @hr = sp_OADestroy @sbRefUri
                    EXEC @hr = sp_OADestroy @bd
                    RETURN
                  END
              END
          END
        SELECT @i = @i + 1
      END

    -- Now that we have the external data, verify the signature..
    DECLARE @bVerified int
    EXEC sp_OAMethod @verifier, 'VerifySignature', @bVerified OUT, 1
    IF @bVerified = 0
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END

    PRINT 'Signature verified = ' + @bVerified

    EXEC @hr = sp_OADestroy @verifier
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbSignedXml
    EXEC @hr = sp_OADestroy @sbRefUri
    EXEC @hr = sp_OADestroy @bd


END
GO