(SQL Server) Extract SignatureValue from Signed XML

See more XML Digital Signatures Examples

Demonstrates how to extract the signature value from signed XML.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example will work with the following signed XML, which as been reformatted for readability.

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    --     <SOAP-ENV:Header>
    --         <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" SOAP-ENV:mustUnderstand="1">
    --             <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
    --                 <ds:SignedInfo>
    --                     <ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
    --                         <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="wsse SOAP-ENV"/>
    --                     </ds:CanonicalizationMethod>
    --                     <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/>
    --                     <ds:Reference URI="#Body">
    --                         <ds:Transforms>
    --                             <ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
    --                         </ds:Transforms>
    --                         <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --                         <ds:DigestValue>OwgHPZNfDkXnZsjpfzXqAcT3RV3HzmTsEy2bP44FJ0M=</ds:DigestValue>
    --                     </ds:Reference>
    --                 </ds:SignedInfo>
    --                 <ds:SignatureValue>C+7FWngU....DJFBcdg==</ds:SignatureValue>
    --                 <ds:KeyInfo>
    --                     <ds:KeyValue>
    --                         <ds:RSAKeyValue>
    --                             <ds:Modulus>sXeRhM55P13.....NAcibRw==</ds:Modulus>
    --                             <ds:Exponent>AQAB</ds:Exponent>
    --                         </ds:RSAKeyValue>
    --                     </ds:KeyValue>
    --                 </ds:KeyInfo>
    --             </ds:Signature>
    --         </wsse:Security>
    --     </SOAP-ENV:Header>
    --     <SOAP-ENV:Body xmlns:SOAP-SEC="http://schemas.xmlsoap.org/soap/security/2000-12" SOAP-SEC:id="Body">
    --         <z:FooBar xmlns:z="http://example.com"/>
    --     </SOAP-ENV:Body>
    -- </SOAP-ENV:Envelope>

    -- -----------------------------------------------------------------------------------------
    -- There are a number of ways we can get the contents of the  <ds:SignatureValue>...</ds:SignatureValue>

    DECLARE @filePath nvarchar(4000)
    SELECT @filePath = 'qa_data/xml_dsig_testing/sample_to_show_how_to_extract_sig.xml'

    -- If you know there will only be a single SignatureValue in the XML, you don't need to parse XML.
    -- Just do quick and simple string search..
    -- For example:
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @sb, 'LoadFile', @success OUT, @filePath, 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load file.'
        EXEC @hr = sp_OADestroy @sb
        RETURN
      END

    DECLARE @base64Sig nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetBetween', @base64Sig OUT, 'SignatureValue>', '</'

    PRINT 'base64Sig = ' + @base64Sig

    -- -----------------------------------------------------------------------------------------
    -- If the XML might contain multiple signatures, but the signatures will always be located in the 
    -- same place, then you can (beforehand) copy a sample signed XML into Chilkat's online tool
    -- at Generate Parsing Code from XML
    -- to get the path to the SignatureValue.  For example, the line of code generated for the SignatureValue
    -- for the above XML is:
    -- string ds_SignatureValue = xml.GetChildContent("SOAP-ENV:Header|wsse:Security|ds:Signature|ds:SignatureValue");

    -- So you can simply use that path..
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, @filePath
    -- Assume success..

    EXEC sp_OAMethod @xml, 'GetChildContent', @base64Sig OUT, 'SOAP-ENV:Header|wsse:Security|ds:Signature|ds:SignatureValue'

    PRINT 'base64Sig = ' + @base64Sig

    -- -----------------------------------------------------------------------------------------
    -- Or perhaps you don't know where the signature is located in the XML.
    -- You can search for the tag..
    DECLARE @xSigVal int
    EXEC sp_OAMethod @xml, 'SearchForTag', @xSigVal OUT, @xml, '*:SignatureValue'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        EXEC sp_OAGetProperty @xSigVal, 'Content', @sTmp0 OUT
        PRINT 'base64Sig = ' + @sTmp0
        EXEC @hr = sp_OADestroy @xSigVal

      END
    ELSE
      BEGIN

        PRINT 'No SignatureValue found.'
      END

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @xml


END
GO