SQL Server
SQL Server
Ibanity HTTP Signature for XS2A, Isabel Connect, Ponto Connect
See more Ibanity Examples
Demonstrates how to add a Signature header for Ibanity HTTP requests.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 @sTmp1 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.
-- In order to sign your HTTP requests, you have to add 2 headers to the HTTP request: Digest: the digest of the request payload and Signature: the actual signature of the request.
-- POST /xs2a/customer-access-tokens HTTP/1.1
-- Host: api.ibanity.com
-- Content-Type: application/json
-- Digest: SHA-512=z4PhNX7vuL3xVChQ1m2AB9Yg5AULVxXcg/SpIdNs6c5H0NE8XYXysP+DGNKHfuwvY7kxvUdBeoGlODJ6+SfaPg==
-- Ibanity-Idempotency-Key: 61f02718-eeee-46e1-b5eb-e8fd6e799c2d
-- Signature: keyId="62f02718-eeee-46e1-b5eb-e8fd6e799c2e",created=1599659223,algorithm="hs2019",headers="(request-target) host digest (created) ibanity-idempotency-key",signature="SjWJWbWN7i0...zsbM="
--
-- {"data":{"type":"customerAccessToken", "attributes":{"applicationCustomerReference":"15874569"}}}
-- The payload (body) of the above HTTP request is the JSON.
-- Build the JSON above.
-- Use this online tool to generate code from sample JSON:
-- Generate Code to Create JSON
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'data.type', 'customerAccessToken'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'data.attributes.applicationCustomerReference', '15874569'
DECLARE @payload nvarchar(4000)
EXEC sp_OAMethod @json, 'Emit', @payload OUT
PRINT 'payload = ' + @payload
-- Step 1: Build the (created) virtual header
DECLARE @dtNow int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT
EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
DECLARE @created nvarchar(4000)
EXEC sp_OAMethod @dtNow, 'GetAsUnixTimeStr', @created OUT, 0
PRINT 'created = ' + @created
-- Step 2: Build the Digest header
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha512'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
DECLARE @sbDigestHdrValue int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbDigestHdrValue OUT
EXEC sp_OAMethod @sbDigestHdrValue, 'Append', @success OUT, 'SHA-512='
EXEC sp_OAMethod @json, 'Emit', @sTmp1 OUT
EXEC sp_OAMethod @crypt, 'HashStringENC', @sTmp0 OUT, @sTmp1
EXEC sp_OAMethod @sbDigestHdrValue, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Step 3: Build the (request target) virtual header
-- In order to build the signature you will need a virtual header named (request-target) (the parentheses are important).
-- The (request-target) is the string concatenation of the HTTP method (in lowercase) with the path and query parameters.
DECLARE @request_target nvarchar(4000)
SELECT @request_target = 'post /xs2a/customer-access-tokens'
-- Step 4: Build the signing string
-- The signing string is the concatenation of the signed header names (in lowercase) and values separated by a LF.
-- You must always sign the following headers: (request-target), host, (created), digest.
-- If used, you must also sign the authorization header and any ibanity-* headers, such as ibanity-idempotency-key.
DECLARE @sbSigningString int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSigningString OUT
EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, '(request-target): '
EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @request_target, 0
EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'host: '
EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, 'api.ibanity.com', 0
EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'digest: '
EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @sTmp0, 0
EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, '(created): '
EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @created, 0
EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'ibanity-idempotency-key: '
DECLARE @idempotencyKey nvarchar(4000)
EXEC sp_OAMethod @crypt, 'GenerateUuid', @idempotencyKey OUT
EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, @idempotencyKey
-- Step 5: Build the signed headers list
-- To allow Ibanity to check the signed headers, you must provide a list of the header names. They should be lowercase and in the same order used to create the signing string.
DECLARE @signed_headers_list nvarchar(4000)
SELECT @signed_headers_list = '(request-target) host digest (created) ibanity-idempotency-key'
-- Step 6: Build the Signature header
-- This is where the real signing happens. The signature header is a combination of several sub-headers -
--
-- keyId: the identifier for the application's signature certificate, obtained from the Developer Portal
-- algorithm: the digital signature algorithm used to generate the signature (must be hs2019)
-- headers: The list of HTTP headers created in step 5
-- signature: the Base64-encoded digital signature of the signing string created in step 4.
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, 'my_ibanity_signature_private_key.pem', 'pem_password'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbDigestHdrValue
EXEC @hr = sp_OADestroy @sbSigningString
EXEC @hr = sp_OADestroy @privKey
RETURN
END
DECLARE @rsa int
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OASetProperty @rsa, 'PssSaltLen', 32
EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
-- Use the RSASSA-PSS signature algorithm
EXEC sp_OASetProperty @rsa, 'PkcsPadding', 0
EXEC sp_OAMethod @rsa, 'UsePrivateKey', @success OUT, @privKey
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbDigestHdrValue
EXEC @hr = sp_OADestroy @sbSigningString
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
RETURN
END
-- Sign the signing string.
DECLARE @sigBase64 nvarchar(4000)
EXEC sp_OAMethod @sbSigningString, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rsa, 'SignStringENC', @sigBase64 OUT, @sTmp0, 'sha-256'
EXEC sp_OAGetProperty @rsa, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbDigestHdrValue
EXEC @hr = sp_OADestroy @sbSigningString
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
RETURN
END
-- Build the signature header value.
DECLARE @sbSigHeaderValue int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSigHeaderValue OUT
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, 'keyId="'
-- Use your identifier for the application's signature certificate, obtained from the Developer Portal
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '62f02718-eeee-46e1-b5eb-e8fd6e799c2e'
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '",created='
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @created
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, ',algorithm="hs2019",headers="'
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @signed_headers_list
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '",signature="'
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @sigBase64
EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '"'
EXEC sp_OAMethod @sbSigHeaderValue, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbDigestHdrValue
EXEC @hr = sp_OADestroy @sbSigningString
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @sbSigHeaderValue
END
GO