SQL Server
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
-- 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.
-- 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