SQL Server Requires Chilkat v11.0.0+
SQL Server
Create JPK VAT metadata XML
See more RSA Examples
Demonstrates how to create the JPK VAT metadata XML (InitUpload) that will be signed using XADES.Chilkat 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
DECLARE @iTmp0 int
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.
-- First build an InitUpload XML template
-- Use this online tool to generate the code from the sample XML below:
-- Generate Code to Create XML
-- <InitUpload xmlns="http://e-dokumenty.mf.gov.pl">
-- <DocumentType>JPK</DocumentType>
-- <Version>01.02.01.20160617</Version>
-- <EncryptionKey algorithm="RSA" encoding="Base64" mode="ECB" padding="PKCS#1">F9EhKFec...uWqAWUIg==</EncryptionKey>
-- <DocumentList>
-- <Document>
-- <FormCode schemaVersion="1-1" systemCode="JPK_VAT (3)">JPK_VAT</FormCode>
-- <FileName>JPK_VAT_3_v1-1_20181201.xml</FileName>
-- <ContentLength>8736</ContentLength>
-- <HashValue algorithm="SHA-256" encoding="Base64">JFDI1pItwh6dj/Xe1uts/x61qnjZ4DLHpkZMhmf1oKQ=</HashValue>
-- <FileSignatureList filesNumber="1">
-- <Packaging>
-- <SplitZip mode="zip" type="split"/>
-- </Packaging>
-- <Encryption>
-- <AES block="16" mode="CBC" padding="PKCS#7" size="256">
-- <IV bytes="16" encoding="Base64">z64oN9zXHt1+S3XACRSCYw==</IV>
-- </AES>
-- </Encryption>
-- <FileSignature>
-- <OrdinalNumber>1</OrdinalNumber>
-- <FileName>JPK_VAT_3_v1-1_20181201-000.xml.zip.aes</FileName>
-- <ContentLength>16</ContentLength>
-- <HashValue algorithm="MD5" encoding="Base64">5NX0q1935fvMjLFV7E1yDw==</HashValue>
-- </FileSignature>
-- </FileSignatureList>
-- </Document>
-- </DocumentList>
-- </InitUpload>
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', 'InitUpload'
EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://e-dokumenty.mf.gov.pl'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentType', 'JPK'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Version', '01.02.01.20160617'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'algorithm', 'RSA'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'encoding', 'Base64'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'mode', 'ECB'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'padding', 'PKCS#1'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'EncryptionKey', 'TO BE DETERMINED'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FormCode', 1, 'schemaVersion', '1-1'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FormCode', 1, 'systemCode', 'JPK_VAT (3)'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FormCode', 'JPK_VAT'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileName', 'JPK_VAT_3_v1-1_20181201.xml'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|ContentLength', '9999'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|HashValue', 1, 'algorithm', 'SHA-256'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|HashValue', 1, 'encoding', 'Base64'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|HashValue', 'TO BE DETERMINED'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList', 1, 'filesNumber', '1'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Packaging|SplitZip', 1, 'mode', 'zip'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Packaging|SplitZip', 1, 'type', 'split'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'block', '16'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'mode', 'CBC'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'padding', 'PKCS#7'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'size', '256'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', 1, 'bytes', '16'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', 1, 'encoding', 'Base64'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', 'TO BE DETERMINED'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|OrdinalNumber', '1'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|FileName', 'JPK_VAT_3_v1-1_20181201-000.xml.zip.aes'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|ContentLength', '9999'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', 1, 'algorithm', 'MD5'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', 1, 'encoding', 'Base64'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', 'TO BE DETERMINED'
-- ------------------------------------------------------------
-- Step 1: Load our JPK_VAT XML and update the DocumentList|Document|HashValue
-- and DocumentList|Document|ContentLength
DECLARE @bdXml int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdXml OUT
EXEC sp_OAMethod @bdXml, 'LoadFile', @success OUT, 'qa_data/xml_dsig/jpk_vat/JPK_VAT_3_v1-1_20181201-000.xml'
IF @success <> 1
BEGIN
PRINT 'Failed to load XML file.'
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bdXml
RETURN
END
EXEC sp_OAGetProperty @bdXml, 'NumBytes', @iTmp0 OUT
EXEC sp_OAMethod @xml, 'UpdateChildContentInt', NULL, 'DocumentList|Document|ContentLength', @iTmp0
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
EXEC sp_OAMethod @crypt, 'HashBdENC', @sTmp0 OUT, @bdXml
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|HashValue', @sTmp0
-- ------------------------------------------------------------
-- Step 2: Create a Zip archive containing the XML.
DECLARE @zip int
EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT
-- The filename we pass here doesn't matter because we won't actually be creating a .zip file.
EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'anything.zip'
EXEC sp_OAMethod @zip, 'AddBd', @success OUT, 'JPK_VAT_3_v1-1_20181201-000.xml', @bdXml
-- Write the .zip file to a BinData object.
DECLARE @bdZip int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdZip OUT
EXEC sp_OAMethod @zip, 'WriteBd', @success OUT, @bdZip
-- ------------------------------------------------------------
-- Step 3: Generate a random 256-bit AES key (32-bytes)
DECLARE @prng int
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @bdAesKey int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdAesKey OUT
EXEC sp_OAMethod @prng, 'GenRandomBd', @success OUT, 32, @bdAesKey
DECLARE @ivBytes nvarchar(4000)
EXEC sp_OAMethod @prng, 'GenRandom', @ivBytes OUT, 16, 'base64'
-- Store the IV (base64 string) in the XML.
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', @ivBytes
-- ------------------------------------------------------------
-- Step 4: AES encrypt our zip archive (the contents of bdZip)
EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
EXEC sp_OASetProperty @crypt, 'KeyLength', 256
EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0
EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivBytes, 'base64'
EXEC sp_OAMethod @bdAesKey, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'base64'
-- AES by definition has a block size of 16.
EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @bdZip
-- bdZip now contains the AES encrypted data.
-- Note: This is NOT the same as a zip where the contents are AES encrypted.
-- In that case, we have an unencrypted zip structure with AES encrypted files within.
-- In our case, the entire zip file image is encrypted.
-- Save the bdZip to a file. This is what will get sent to e-dokumenty.mf.gov.pl
EXEC sp_OAMethod @bdZip, 'WriteFile', @success OUT, 'qa_output/JPK_VAT_3_v1-1_20181201-000.xml.zip.aes'
EXEC sp_OAGetProperty @bdZip, 'NumBytes', @iTmp0 OUT
EXEC sp_OAMethod @xml, 'UpdateChildContentInt', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|ContentLength', @iTmp0
-- ------------------------------------------------------------
-- Step 4: RSA Encrypt the AES key using the public key certificate provided by the Ministry of Finance
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/pem/mf_public_rsa.pem'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bdXml
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @bdZip
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @bdAesKey
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @pubKey int
EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT
EXEC sp_OAMethod @cert, 'GetPublicKey', @success OUT, @pubKey
DECLARE @rsa int
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OAMethod @rsa, 'UsePublicKey', @success OUT, @pubKey
EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
EXEC sp_OASetProperty @rsa, 'LittleEndian', 0
-- in-place RSA encrypt the contents of bdAesKey.
EXEC sp_OAMethod @rsa, 'EncryptBd', @success OUT, @bdAesKey, 0
EXEC sp_OAMethod @bdAesKey, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'EncryptionKey', @sTmp0
-- Step 5: We forgot to get the MD5 hash of the AES encrypted zip.
-- (I'm assuming we need the MD5 of the encrypted zip as opposed to the MD5 of the pre-encrypted zip..)
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'md5'
EXEC sp_OAMethod @crypt, 'HashBdENC', @sTmp0 OUT, @bdZip
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', @sTmp0
-- At this point, the XML is prepared and the AES encrypted image of the zip file is written
-- to a file (and also in bdZip).
DECLARE @finalXml nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetXml', @finalXml OUT
PRINT @finalXml
EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_output/jpk_vat.xml'
PRINT 'Finished.'
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bdXml
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @bdZip
EXEC @hr = sp_OADestroy @prng
EXEC @hr = sp_OADestroy @bdAesKey
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pubKey
EXEC @hr = sp_OADestroy @rsa
END
GO