(SQL Server) Hungary NAV Invoicing Signature Calculation

Demonstrates the requestSignature calculation for the Hungarian NAV Online Invoicing System REST API v2.0.

Note: This example requires Chilkat v9.5.0.83 or greater.

For more information, see https://onlineszamla.nav.gov.hu/api/files/container/download/Online%20Szamla_Interfesz%20specifik%C3%A1ci%C3%B3_EN_v2.0.pdf

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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 assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example performs the following calculation:

    -- 1.5.1 Calculations for manageInvoice and manageAnnulment operations
    -- 
    -- For the manageInvoice and manageAnnulment operations comprising the core of requestSignature, 
    -- requestSignature is calculated using partial authentication, concatenating the hash values for 
    -- indices 1-100 as well as additional SHA3-512 hash operations. The partial authentication can be 
    -- derived by concatenating the following values:
    -- 
    --   - requestId value
    --   - UTC timestamp tag value using a YYYYMMDDhhmmss mask
    --   - string literal of the technical user�s signature key
    -- 
    -- When concatenating, the date and time separators as well as the time zone must be removed for timestamp masking.
    -- 
    -- The index hash value can be derived from the uppercase SHA3-512 hash values following the concatenation of the 
    -- operation and base64 content for the individual indices:
    -- 
    --   - literal value of invoiceOperation or annulmentOperation
    --   - base64 content in the invoiceData or invoiceAnnulment tag
    -- 
    -- The calculated hash values should be appended after the partial hash, in the order corresponding to the indices. 
    -- The SHA3-512 hash result (capitals) of the string thus concatenated yields the requestSignature value.
    -- 
    -- A fictitious example for request data:
    -- 
    -- 	- requestId = TSTKFT1222564
    -- 	- timestamp = 2017-12-30T18:25:45.000Z
    -- 	- technical user�s signature key = ce-8f5e-215119fa7dd621DLMRHRLH2S
    -- 	- the index#1 invoice data items
    -- 	- invoiceOperation = CREATE
    -- 	- invoiceData = QWJjZDEyMzQ=
    -- 	- the index#2 invoice data items
    -- 	- invoiceOperation = MODIFY
    -- 	- invoiceData = RGNiYTQzMjE=
    -- 	- partial authentication value = TSTKFT122256420171230182545ce-8f5e-215119fa7dd621DLMRHRLH2S
    -- 	- the first index hash =
    -- 		- hash base = CREATEQWJjZDEyMzQ=
    -- 		- lowercase hash = 4317798460962869bc67f07c48ea7e4a3afa301513ceb87b8eb94ecf92bc220a89c480f87f0860e85e29a3b6c0463d4f29712c5ad48104a6486ce839dc2f24cb
    -- 		- uppercase hash = 4317798460962869BC67F07C48EA7E4A3AFA301513CEB87B8EB94ECF92BC220A89C480F87F0860E85E29A3B6C0463D4F29712C5AD48104A6486CE839DC2F24CB
    -- 	- the second index hash =
    -- 		- hash base = MODIFYRGNiYTQzMjE=
    -- 		- lowercase hash = a881218238933f6ffb9e167445cb4daa9749bcf484fde48ab7649fd25e8b634a4736a65a7c4a8e2831119f739837e006566f97370415aad55e268605206f2a6c
    -- 		- uppercase hash = A881218238933F6FFB9E167445CB4DAA9749BCF484FDE48AB7649FD25E8B634A4736A65A7C4A8E2831119F739837E006566F97370415AAD55E268605206F2A6C
    -- 
    -- Thus, the base for the entire requestSignature is:
    -- 	TSTKFT122256420171230182545ce-8f5e-215119fa7dd621DLMRHRLH2S4317798460962869BC67F07C48EA7E4A3AFA301513CEB87B8EB94ECF92BC220A89C480F87F0860E85E29A3B6C0463D4F29712C5AD48104A6486CE839DC2F24CBA881218238933F6FFB9E167445CB4DAA9749BCF484FDE48AB7649FD25E8B634A4736A65A7C4A8E2831119F739837E006566F97370415AAD55E268605206F2A6C
    -- 
    -- Value of requestSignature after SHA3-512 hashing and converting to uppercase:
    -- 	60BC80609EE3B8F42FE904200A49A1921A1DADA08D55319ACD40C59F626514B74EEA49011D372600A10DBCF8199D590DA9C2841D987308F2D83DAE17C2470C42
    -- 
    -- -------------------
    DECLARE @success int

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    -- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater.  The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha3-512'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'

    DECLARE @requestId nvarchar(4000)
    SELECT @requestId = 'TSTKFT1222564'
    DECLARE @timestamp nvarchar(4000)
    SELECT @timestamp = '2017-12-30T18:25:45.000Z'
    DECLARE @signatureKey nvarchar(4000)
    SELECT @signatureKey = 'ce-8f5e-215119fa7dd621DLMRHRLH2S'

    -- Step 1: Concatenate requestId, UTC timestamp, and user's signature key.
    DECLARE @sbFinalHashBase int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbFinalHashBase OUT

    -- First append the timestamp because we are going to remove certain chars/parts.
    EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @timestamp
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, '.000Z', ''
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, '-', ''
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, ':', ''
    EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, 'T', ''

    -- Prepend the requestId and append the signatureKey
    EXEC sp_OAMethod @sbFinalHashBase, 'Prepend', @success OUT, @requestId
    EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @signatureKey

    -- Step 2: Calculate first index hash.
    DECLARE @invoiceOperation nvarchar(4000)
    SELECT @invoiceOperation = 'CREATE'
    DECLARE @invoiceData nvarchar(4000)
    SELECT @invoiceData = 'QWJjZDEyMzQ='

    DECLARE @sbHashBase int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHashBase OUT

    EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceOperation
    EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceData

    EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
    PRINT 'first hash base = ' + @sTmp0

    DECLARE @firstHash nvarchar(4000)
    EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @firstHash OUT, @sTmp0

    PRINT 'first hash = ' + @firstHash

    -- Output should be:
    -- first hash base = CREATEQWJjZDEyMzQ=
    -- first hash = 4317798460962869BC67F07C48EA7E4A3AFA301513CEB87B8EB94ECF92BC220A89C480F87F0860E85E29A3B6C0463D4F29712C5AD48104A6486CE839DC2F24CB

    -- Step 3: Calculate second index hash.
    SELECT @invoiceOperation = 'MODIFY'
    SELECT @invoiceData = 'RGNiYTQzMjE='

    EXEC sp_OAMethod @sbHashBase, 'Clear', NULL
    EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceOperation
    EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceData

    EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
    PRINT 'second hash base = ' + @sTmp0

    DECLARE @secondHash nvarchar(4000)
    EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @secondHash OUT, @sTmp0

    PRINT 'second hash = ' + @secondHash

    -- Output should be:
    -- second hash base = MODIFYRGNiYTQzMjE=
    -- second hash = A881218238933F6FFB9E167445CB4DAA9749BCF484FDE48AB7649FD25E8B634A4736A65A7C4A8E2831119F739837E006566F97370415AAD55E268605206F2A6C

    -- Step 4: Append the uppercase hex hashes to the sbFinalHashBase
    EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @firstHash
    EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @secondHash

    EXEC sp_OAMethod @sbFinalHashBase, 'GetAsString', @sTmp0 OUT
    PRINT 'sbFinalHashBase = ' + @sTmp0

    -- Step 5: SHA3-512 hash to get the requestSignature
    DECLARE @requestSignature nvarchar(4000)
    EXEC sp_OAMethod @sbFinalHashBase, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @requestSignature OUT, @sTmp0

    PRINT 'requestSignature = ' + @requestSignature

    -- Output should be:
    -- requestSignature = 60BC80609EE3B8F42FE904200A49A1921A1DADA08D55319ACD40C59F626514B74EEA49011D372600A10DBCF8199D590DA9C2841D987308F2D83DAE17C2470C42

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbFinalHashBase
    EXEC @hr = sp_OADestroy @sbHashBase


END
GO