SQL Server
SQL Server
Create XML Signature with External XML Reference
See more XML Digital Signatures Examples
Demonstrates how to create an XML digital signature where the referenced data is external. In this case, the data is an XML file located at the following URL: https://www.chilkatsoft.com/data/helloWorld.xmlChilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- This example inserts an XML signature in the following XML:
-- <?xml version="1.0" encoding="UTF-8" standalone="no"?>
-- <abc>
-- <xyz>
-- <name>helloWorld.xml</name>
-- <url>https://www.chilkatsoft.com/data/helloWorld.xml</url>
-- </xyz>
-- </abc>
-- Build the above XML to be signed.
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @xml, 'Tag', 'abc'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'xyz|name', 'helloWorld.xml'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'xyz|url', 'https://www.chilkatsoft.com/data/helloWorld.xml'
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/test_secret.pfx', 'secret'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @gen int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT
-- Indicate the location within the XML the Signature will be inserted.
EXEC sp_OASetProperty @gen, 'SigLocation', 'abc|xyz'
-- Get the content of the XML data to be referenced.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @sbExternalXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbExternalXml OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/data/helloWorld.xml', @sbExternalXml
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
RETURN
END
EXEC sp_OAMethod @gen, 'AddExternalXmlRef', @success OUT, 'https://www.chilkatsoft.com/data/helloWorld.xml', @sbExternalXml, 'sha256', 'EXCL_C14N', ''
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
RETURN
END
-- Provide the private key for signing via the certificate, and indicate that
-- we want the base64 of the certificate embedded in the KeyInfo.
EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
EXEC sp_OASetProperty @gen, 'X509Type', 'Certificate'
DECLARE @bUsePrivateKey int
SELECT @bUsePrivateKey = 1
EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, @bUsePrivateKey
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
RETURN
END
-- Indicate we want an indented signature for readability.
-- This can be removed after debugging is finished..
EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'
-- Now create and insert the Signature
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
EXEC sp_OAMethod @xml, 'GetXmlSb', @success OUT, @sbXml
EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
EXEC @hr = sp_OADestroy @sbXml
RETURN
END
-- Examine the XML with the digital signature inserted
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Here is the output:
-- <?xml version="1.0" encoding="utf-8"?>
-- <abc>
-- <xyz>
-- <name>helloWorld.xml</name>
-- <url>https://www.chilkatsoft.com/data/helloWorld.xml</url>
-- <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#"/>
-- <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/>
-- <ds:Reference URI="https://www.chilkatsoft.com/data/helloWorld.xml">
-- <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>H/s775OZhel6wdBhe02l4LK6m0lkplOisKxAbVwWxp0=</ds:DigestValue>
-- </ds:Reference>
-- </ds:SignedInfo>
-- <ds:SignatureValue>UruwnBqh..Ll4IzUA==</ds:SignatureValue>
-- <ds:KeyInfo>
-- <ds:X509Data>
-- <ds:X509Certificate>MIIHAz...nUZWCaDE=</ds:X509Certificate>
-- </ds:X509Data>
-- </ds:KeyInfo>
-- </ds:Signature></xyz>
-- </abc>
-- Let's verify the signature...
DECLARE @verifier int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @verifier OUT
EXEC sp_OAMethod @verifier, 'LoadSignatureSb', @success OUT, @sbXml
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
RETURN
END
-- Let's examine the external references, and fetch the data for each..
DECLARE @sbExternalData int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbExternalData OUT
DECLARE @numRefs int
EXEC sp_OAGetProperty @verifier, 'NumReferences', @numRefs OUT
DECLARE @i int
SELECT @i = 0
WHILE @i < @numRefs
BEGIN
DECLARE @bExternal int
EXEC sp_OAMethod @verifier, 'IsReferenceExternal', @bExternal OUT, @i
IF @bExternal = 1
BEGIN
DECLARE @uri nvarchar(4000)
EXEC sp_OAMethod @verifier, 'ReferenceUri', @uri OUT, @i
-- We're assuming the URI is an https:// or http:// URL...
-- Let's also assume we know that the referenced data is text and we want the utf-8 byte representation.
EXEC sp_OAMethod @sbExternalData, 'Clear', NULL
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, @uri, @sbExternalData
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @sbExternalData
RETURN
END
-- Because the referenced external data was explicitly XML, it was the canonicalized XML that was digested.
-- We need to canonicalize the XML in the same way as when signed.
DECLARE @canonXml nvarchar(4000)
EXEC sp_OAMethod @sbExternalData, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @verifier, 'CanonicalizeXml', @canonXml OUT, @sTmp0, 'EXCL_C14N', 0
EXEC sp_OAMethod @sbExternalData, 'SetString', @success OUT, @canonXml
EXEC sp_OAMethod @verifier, 'SetRefDataSb', @success OUT, @i, @sbExternalData, 'utf-8'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @sbExternalData
RETURN
END
END
SELECT @i = @i + 1
END
-- Now that we have the external data available, we can verify the reference digest(s) and the signature.
DECLARE @bVerified int
EXEC sp_OAMethod @verifier, 'VerifySignature', @bVerified OUT, 1
IF @bVerified <> 1
BEGIN
EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @sbExternalData
RETURN
END
PRINT 'Signature verified!'
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbExternalXml
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @sbExternalData
END
GO