Chilkat HOME .NET Core C# Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi ActiveX Delphi DLL Go Java Lianja Mono C# Node.js Objective-C PHP ActiveX PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift 2 Swift 3,4,5... Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(SQL Server) XML Signature Multiple External ReferencesDemonstrates how to add multiple external references to an XML digital signature.
-- 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) -- This example requires the Chilkat API to have been previously unlocked. -- See Global Unlock Sample for sample code. -- This example creates the following signed XML with multiple external references: -- <?xml version="1.0" encoding="UTF-8"?> -- <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#" Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4"> -- <ds:SignedInfo> -- <ds:CanonicalizationMethod Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/> -- <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/> -- <ds:Reference Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref0" URI="https://isus.ezdrowie.gov.pl/fhir/Patient/123/_history/456"> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </ds:Reference> -- <ds:Reference Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref1" URI="https://isus.ezdrowie.gov.pl/fhir/Encounter/124/_history/22"> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </ds:Reference> -- <ds:Reference Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref2" URI="https://isus.ezdrowie.gov.pl/fhir/Condition/125/_history/1"> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </ds:Reference> -- <ds:Reference Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref3" URI="https://isus.ezdrowie.gov.pl/fhir/Procedure/126/_history/1"> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </ds:Reference> -- <ds:Reference Type="http://uri.etsi.org/01903#SignedProperties" URI="#xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-signedprops"> -- <ds:Transforms> -- <ds:Transform Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/> -- </ds:Transforms> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </ds:Reference> -- </ds:SignedInfo> -- <ds:SignatureValue Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-sigvalue">...</ds:SignatureValue> -- <ds:KeyInfo> -- <ds:X509Data> -- <ds:X509Certificate>...</ds:X509Certificate> -- <ds:X509SubjectName>...</ds:X509SubjectName> -- <ds:X509IssuerSerial> -- <ds:X509IssuerName>...</ds:X509IssuerName> -- <ds:X509SerialNumber>...</ds:X509SerialNumber> -- </ds:X509IssuerSerial> -- </ds:X509Data> -- </ds:KeyInfo> -- <ds:Object> -- <xades:QualifyingProperties xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#" Target="#xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4"> -- <xades:SignedProperties Id="xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-signedprops"> -- <xades:SignedSignatureProperties> -- <xades:SigningTime>2021-06-02T07:10:13.306+02:00</xades:SigningTime> -- <xades:SigningCertificate> -- <xades:Cert> -- <xades:CertDigest> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </xades:CertDigest> -- <xades:IssuerSerial> -- <ds:X509IssuerName>...</ds:X509IssuerName> -- <ds:X509SerialNumber>...</ds:X509SerialNumber> -- </xades:IssuerSerial> -- </xades:Cert> -- <xades:Cert> -- <xades:CertDigest> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </xades:CertDigest> -- <xades:IssuerSerial> -- <ds:X509IssuerName>...</ds:X509IssuerName> -- <ds:X509SerialNumber>...</ds:X509SerialNumber> -- </xades:IssuerSerial> -- </xades:Cert> -- <xades:Cert> -- <xades:CertDigest> -- <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/> -- <ds:DigestValue>...</ds:DigestValue> -- </xades:CertDigest> -- <xades:IssuerSerial> -- <ds:X509IssuerName>...</ds:X509IssuerName> -- <ds:X509SerialNumber>...</ds:X509SerialNumber> -- </xades:IssuerSerial> -- </xades:Cert> -- </xades:SigningCertificate> -- </xades:SignedSignatureProperties> -- </xades:SignedProperties> -- </xades:QualifyingProperties> -- </ds:Object> -- </ds:Signature> -- DECLARE @success int SELECT @success = 1 DECLARE @gen int -- Use "Chilkat_9_5_0.XmlDSigGen" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT IF @hr <> 0 BEGIN PRINT 'Failed to create ActiveX component' RETURN END EXEC sp_OASetProperty @gen, 'SigLocation', '' EXEC sp_OASetProperty @gen, 'SigLocationMod', 0 -- Note: ID's simply need to be unique values in the signed XML document. They don't necessarily need to be GUID/UUID formatted. EXEC sp_OASetProperty @gen, 'SigId', 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4' EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds' EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#' EXEC sp_OASetProperty @gen, 'SigValueId', 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-sigvalue' EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'C14N' EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256' -- Create an Object to be added to the Signature. DECLARE @object1 int -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.Xml', @object1 OUT EXEC sp_OASetProperty @object1, 'Tag', 'xades:QualifyingProperties' EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'xmlns:xades', 'http://uri.etsi.org/01903/v1.3.2#' EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'xmlns:xades141', 'http://uri.etsi.org/01903/v1.4.1#' EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'Target', '#xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4' EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties', 1, 'Id', 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-signedprops' EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningTime', 'TO BE GENERATED BY CHILKAT' -- This assumes there are 3 certs in the certificate chain: The signing certificate, an intermediate root, and the root CA. -- Remove the lines with "Cert[2]" if there is no intermediate root... EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:CertDigest|ds:DigestMethod', 1, 'Algorithm', 'http://www.w3.org/2001/04/xmlenc#sha256' EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:CertDigest|ds:DigestValue', 'TO BE GENERATED BY CHILKAT' EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:IssuerSerialV2', 'TO BE GENERATED BY CHILKAT' EXEC sp_OAMethod @object1, 'GetXml', @sTmp0 OUT EXEC sp_OAMethod @gen, 'AddObject', @success OUT, '', @sTmp0, '', '' DECLARE @http int -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT DECLARE @bd int -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT DECLARE @url nvarchar(4000) SELECT @url = 'https://isus.ezdrowie.gov.pl/fhir/Patient/123/_history/456' EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, @url, @bd IF @success <> 1 BEGIN EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd RETURN END -- -------- Reference 1 -------- EXEC sp_OAMethod @gen, 'AddExternalBinaryRef', @success OUT, @url, @bd, 'sha256', '' EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, @url, 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref0' -- -------- Reference 2 -------- EXEC sp_OAMethod @bd, 'Clear', @success OUT SELECT @url = 'https://isus.ezdrowie.gov.pl/fhir/Encounter/789/_history/1' EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, @url, @bd IF @success <> 1 BEGIN EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd RETURN END EXEC sp_OAMethod @gen, 'AddExternalBinaryRef', @success OUT, @url, @bd, 'sha256', '' EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, @url, 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref1' -- -------- Reference 3 -------- EXEC sp_OAMethod @bd, 'Clear', @success OUT SELECT @url = 'https://isus.ezdrowie.gov.pl/fhir/Condition/123456/_history/1' EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, @url, @bd IF @success <> 1 BEGIN EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd RETURN END EXEC sp_OAMethod @gen, 'AddExternalBinaryRef', @success OUT, @url, @bd, 'sha256', '' EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, @url, 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref2' -- -------- Reference 4 -------- EXEC sp_OAMethod @bd, 'Clear', @success OUT SELECT @url = 'https://isus.ezdrowie.gov.pl/fhir/Procedure/123456/_history/1' EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, @url, @bd IF @success <> 1 BEGIN EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd RETURN END EXEC sp_OAMethod @gen, 'AddExternalBinaryRef', @success OUT, @url, @bd, 'sha256', '' EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, @url, 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-ref3' -- -------- Reference 5 -------- EXEC sp_OAMethod @gen, 'AddObjectRef', @success OUT, 'xmldsig-2dde88a8-def0-43e4-8570-b031015a46b4-signedprops', 'sha256', 'C14N', '', 'http://uri.etsi.org/01903#SignedProperties' -- Provide a certificate + private key. (PFX password is test123) DECLARE @cert int -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/cert_test123.pfx', 'test123' IF @success <> 1 BEGIN EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd EXEC @hr = sp_OADestroy @cert RETURN END EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1 EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data' EXEC sp_OASetProperty @gen, 'X509Type', 'IssuerSerial,SubjectName,Certificate' -- This will be an enveloping signature where the Signature element -- is the XML document root, the signed data is contained within Object -- tag(s) within the Signature. -- Therefore, pass an empty sbXml to CreateXmlDsigSb. DECLARE @sbXml int -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature' -- Sign the XML... 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 @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd EXEC @hr = sp_OADestroy @cert EXEC @hr = sp_OADestroy @sbXml RETURN END -- ----------------------------------------------- -- Save the signed XML to a file. EXEC sp_OAMethod @sbXml, 'WriteFile', @success OUT, 'qa_output/signedXml.xml', 'utf-8', 0 EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT PRINT @sTmp0 -- ---------------------------------------- -- Verify the signature we just produced... DECLARE @verifier int -- Use "Chilkat_9_5_0.XmlDSig" for versions of Chilkat < 10.0.0 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 @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd EXEC @hr = sp_OADestroy @cert EXEC @hr = sp_OADestroy @sbXml EXEC @hr = sp_OADestroy @verifier RETURN END DECLARE @verified int EXEC sp_OAMethod @verifier, 'VerifySignature', @verified OUT, 1 IF @verified <> 1 BEGIN EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT PRINT @sTmp0 EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd EXEC @hr = sp_OADestroy @cert EXEC @hr = sp_OADestroy @sbXml EXEC @hr = sp_OADestroy @verifier RETURN END PRINT 'This signature was successfully verified.' EXEC @hr = sp_OADestroy @gen EXEC @hr = sp_OADestroy @object1 EXEC @hr = sp_OADestroy @http EXEC @hr = sp_OADestroy @bd EXEC @hr = sp_OADestroy @cert EXEC @hr = sp_OADestroy @sbXml EXEC @hr = sp_OADestroy @verifier END GO |
© 2000-2024 Chilkat Software, Inc. All Rights Reserved.