(SQL Server) Adyen HMAC Signature Calculation for Hosted Payment Pages

Demonstrates how to do the HMAC Signature Calculation for a hosted payment page (HPP) in Adyen.

For a C# ASP.NET Razor Pages example showing the HTML Form with HMAC signature code, see Adyen HMAC Signature Calculation in C#

For more information, see https://docs.adyen.com/classic-integration/hosted-payment-pages/hmac-signature-calculation/

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

    DECLARE @success int

    DECLARE @strHtml nvarchar(4000)
    SELECT @strHtml = '<table class="od"><tr><th>Description</th><th>Quantity</th><th>Amount</th></tr><tr><td>1 Digital Camera</td><td class="r">1</td><td class="r">100 GBP</td></tr><tr><td class="b">Total</td><td class="r"></td><td class="b r">100.00 GBP</td></tr></table>'

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

    DECLARE @gzOrderData nvarchar(4000)
    EXEC sp_OAMethod @gzip, 'CompressStringENC', @gzOrderData OUT, @strHtml, 'utf-8', 'base64'

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

    EXEC sp_OASetProperty @xml, 'Tag', 'keyValuePairs'

    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'orderData', @gzOrderData

    -- required, The payment deadline; the payment needs to occur within the specified time value.
    DECLARE @sessionValidity nvarchar(4000)
    SELECT @sessionValidity = '2019-08-11T10:30:00Z'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'sessionValidity', @sessionValidity

    -- optional.  Normally we'll let Adyen automatically know the country based on the IP address.
    -- By default, the payment methods offered to a shopper are filtered based on the country the shopper's IP address is mapped to. 
    -- In this way, shoppers are not offered payment methods that are not available in the country they are carrying out the transaction from. 
    -- This IP-to-country mapping is not 100% accurate, so if you have already established the country of the shopper, you can set it explicitly 
    -- in the countryCode parameter.
    DECLARE @countryCode nvarchar(4000)
    SELECT @countryCode = 'GB'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'countryCode', @countryCode

    -- optional
    DECLARE @shopperLocale nvarchar(4000)
    SELECT @shopperLocale = 'en_GB'
    -- If not specified, the locale preference is set to en_GB   by default.
    -- When it is not necessary to include the country-specific part, use only the language code.
    -- For example: it instead of it_IT to set the locale preferences to Italian.
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'shopperLocale', @shopperLocale

    -- required, A reference to uniquely identify the payment. This reference is used in all communication with you about the payment status. 
    -- We recommend using a unique value per payment; however, it is not a requirement. If you need to provide multiple references for a transaction, 
    -- you can enter them in this field. Separate each reference value with a hyphen character ("-"). This field has a length restriction: 
    -- you can enter max. 80 characters.
    DECLARE @merchantReference nvarchar(4000)
    SELECT @merchantReference = 'paymentTest1234'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'merchantReference', @merchantReference

    -- required, The merchant account identifier you want to process the (transaction) request with.
    DECLARE @merchantAccount nvarchar(4000)
    SELECT @merchantAccount = 'ChilkatSoftwareIncCOM'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'merchantAccount', @merchantAccount

    -- required.  10000 for $100.00
    DECLARE @paymentAmount nvarchar(4000)
    SELECT @paymentAmount = '10000'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'paymentAmount', @paymentAmount

    -- required, The three-character ISO currency code
    DECLARE @currencyCode nvarchar(4000)
    SELECT @currencyCode = 'GBP'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'currencyCode', @currencyCode

    -- required.
    DECLARE @skinCode nvarchar(4000)
    SELECT @skinCode = 'S7uWsvfB'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'skinCode', @skinCode

    -- optional, A unique identifier for the shopper, for example, a customer ID.
    -- We recommend providing this information, as it is used in velocity fraud checks. It is also the key in recurring payments.
    -- This field is mandatory in recurring payments.  
    DECLARE @shopperReference nvarchar(4000)
    SELECT @shopperReference = 'somebody@example.com'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'shopperReference', @shopperReference

    -- optional
    DECLARE @shopperEmail nvarchar(4000)
    SELECT @shopperEmail = 'somebody@example.com'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'shopperEmail', @shopperEmail

    -- optional, An integer value that adds up to the normal fraud score.
    -- The value can be either a positive or negative integer.
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'offset', '0'

    -- Apparently this is a required field.
    DECLARE @shipBeforeDate nvarchar(4000)
    SELECT @shipBeforeDate = '2019-06-04'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'shipBeforeDate', @shipBeforeDate

    EXEC sp_OAMethod @xml, 'SortByTag', NULL, 1

    -- Encode...
    --  "\" (backslash) as "\\"
    --  ":" (colon) as "\:"

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

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

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

    DECLARE @n int
    EXEC sp_OAGetProperty @xml, 'NumChildren', @n OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @n
      BEGIN
        IF @i > 0
          BEGIN
            EXEC sp_OAMethod @sbTags, 'Append', @success OUT, ':'
            EXEC sp_OAMethod @sbValues, 'Append', @success OUT, ':'
          END
        EXEC sp_OAMethod @xml, 'GetChild2', @success OUT, @i
        EXEC sp_OAGetProperty @xml, 'Tag', @sTmp0 OUT
        EXEC sp_OAMethod @sbTags, 'Append', @success OUT, @sTmp0

        EXEC sp_OAGetProperty @xml, 'Content', @sTmp0 OUT
        EXEC sp_OAMethod @sbContent, 'SetString', @success OUT, @sTmp0
        DECLARE @numReplaced int
        EXEC sp_OAMethod @sbContent, 'Replace', @numReplaced OUT, '\', '\\'
        EXEC sp_OAMethod @sbContent, 'Replace', @numReplaced OUT, ':', '\:'
        EXEC sp_OAMethod @sbValues, 'AppendSb', @success OUT, @sbContent
        EXEC sp_OAMethod @xml, 'GetParent2', @success OUT

        SELECT @i = @i + 1
      END

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

    EXEC sp_OAMethod @sbSigningStr, 'AppendSb', @success OUT, @sbTags
    EXEC sp_OAMethod @sbSigningStr, 'Append', @success OUT, ':'
    EXEC sp_OAMethod @sbSigningStr, 'AppendSb', @success OUT, @sbValues

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

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @crypt, 'MacAlgorithm', 'hmac'

    DECLARE @hmacKey nvarchar(4000)
    SELECT @hmacKey = '934D1E806DDD99595EB430076FD7F8E4D12D0A3F51243A4C0C3897703118E739'
    EXEC sp_OAMethod @crypt, 'SetMacKeyEncoded', @success OUT, @hmacKey, 'hex'

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @merchantSig nvarchar(4000)
    EXEC sp_OAMethod @sbSigningStr, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HmacStringENC', @merchantSig OUT, @sTmp0


    PRINT @merchantSig

    EXEC @hr = sp_OADestroy @gzip
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @sbTags
    EXEC @hr = sp_OADestroy @sbValues
    EXEC @hr = sp_OADestroy @sbContent
    EXEC @hr = sp_OADestroy @sbSigningStr
    EXEC @hr = sp_OADestroy @crypt


END
GO