SQL Server
SQL Server
Create/Verify XML Signature with External Text 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 a text file located at the following URL: https://www.chilkatsoft.com/helloWorld.txtChilkat 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 into the following XML:
-- <?xml version="1.0" encoding="UTF-8" standalone="no"?>
-- <abc>
-- <xyz>
-- <name>helloWorld.txt</name>
-- <url>https://www.chilkatsoft.com/helloWorld.txt</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.txt'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'xyz|url', 'https://www.chilkatsoft.com/helloWorld.txt'
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 text data to be referenced.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @sbExternalTxt int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbExternalTxt OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/helloWorld.txt', @sbExternalTxt
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 @sbExternalTxt
RETURN
END
EXEC sp_OAMethod @gen, 'AddExternalTextRef', @success OUT, 'https://www.chilkatsoft.com/helloWorld.txt', @sbExternalTxt, 'utf-8', 0, 'sha256', ''
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 @sbExternalTxt
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 @sbExternalTxt
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 @sbExternalTxt
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.txt</name>
-- <url>https://www.chilkatsoft.com/helloWorld.txt</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/helloWorld.txt">
-- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
-- <ds:DigestValue>f4OxZX/x/FO5LcGBSKHWXfwtSx+j1ncoSt3SABJtkGk=</ds:DigestValue>
-- </ds:Reference>
-- </ds:SignedInfo>
-- <ds:SignatureValue>syFSl...pZDiFQ==</ds:SignatureValue>
-- <ds:KeyInfo>
-- <ds:X509Data>
-- <ds:X509Certificate>MIIHAz...GwnUZWCaDE=</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 @sbExternalTxt
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 @sbExternalTxt
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @sbExternalData
RETURN
END
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 @sbExternalTxt
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 @sbExternalTxt
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 @sbExternalTxt
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
EXEC @hr = sp_OADestroy @sbExternalData
END
GO