SQL Server

Extract SignatureValue from Signed XML

See more XML Digital Signatures Examples

Demonstrates how to extract the signature value from signed XML. 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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  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
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    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
    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