(SQL Server) ebay: Add Digital Signature to HTTP Request

See more eBay Examples

Demonstrates how to add a digital signature to an ebay HTTP request.

For more information, see https://developer.ebay.com/develop/guides/digital-signatures-for-apis

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
    DECLARE @iTmp0 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

    -- Note: Ebay provides a Key Management API
    -- See https://developer.ebay.com/api-docs/developer/key-management/overview.html

    -- The following test keys can be used: 
    -- 
    -- Ed25519 
    -- 
    -- Private Key:
    -- 
    -- -----BEGIN PRIVATE KEY-----
    -- MC4CAQAwBQYDK2VwBCIEIJ+DYvh6SEqVTm50DFtMDoQikTmiCqirVv9mWG9qfSnF
    -- -----END PRIVATE KEY-----

    DECLARE @strPrivateKey nvarchar(4000)
    SELECT @strPrivateKey = 'MC4CAQAwBQYDK2VwBCIEIJ+DYvh6SEqVTm50DFtMDoQikTmiCqirVv9mWG9qfSnF'

    -- 
    -- Public Key:
    -- 
    -- -----BEGIN PUBLIC KEY-----
    -- MCowBQYDK2VwAyEAJrQLj5P/89iXES9+vFgrIy29clF9CC/oPPsw3c5D0bs=
    -- -----END PUBLIC KEY-----

    DECLARE @strPublicKey nvarchar(4000)
    SELECT @strPublicKey = 'MCowBQYDK2VwAyEAJrQLj5P/89iXES9+vFgrIy29clF9CC/oPPsw3c5D0bs='

    -- This example assumes you got a JWE for your given private key from the Ebay Key Management REST API.
    -- This JWE is just for example:
    DECLARE @strJwe nvarchar(4000)
    SELECT @strJwe = 'eyJ6aXAiOiJERUYiLCJlbmMiOiJBMjU2R0NNIiwidGFnIjoiSXh2dVRMb0FLS0hlS0Zoa3BxQ05CUSIsImFsZyI6IkEyNTZHQ01LVyIsIml2IjoiaFd3YjNoczk2QzEyOTNucCJ9.2o02pR9SoTF4g_5qRXZm6tF4H52TarilIAKxoVUqjd8.3qaF0KJN-rFHHm_P.AMUAe9PPduew09mANIZ-O_68CCuv6EIx096rm9WyLZnYz5N1WFDQ3jP0RBkbaOtQZHImMSPXIHVaB96RWshLuJsUgCKmTAwkPVCZv3zhLxZVxMXtPUuJ-ppVmPIv0NzznWCOU5Kvb9Xux7ZtnlvLXgwOFEix-BaWNomUAazbsrUCbrp514GIea3butbyxXLNi6R9TJUNh8V2uan-optT1MMyS7eMQnVGL5rYBULk.9K5ucUqAu0DqkkhgubsHHw'

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

    EXEC sp_OAMethod @sbBody, 'Append', @success OUT, '{"hello": "world"}'


    PRINT 'Body of request:'
    EXEC sp_OAMethod @sbBody, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- -------------------------------------------------
    -- Build the signature base string...

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

    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '"content-digest": sha-256=:'
    EXEC sp_OAMethod @sbBody, 'GetHash', @sTmp0 OUT, 'sha256', 'base64', 'utf-8'
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, ':' + CHAR(10)

    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '"x-ebay-signature-key": '
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, @strJwe
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, CHAR(10)

    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '"@method": POST' + CHAR(10)

    -- This is the path part of the URL without query params...
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '"@path": '
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '/verifysignature'
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, CHAR(10)

    -- The is the domain, such as "api.ebay.com" w/ port if the port is something unusual.
    -- In this example, we're testing against a local docker test server (see the info at https://developer.ebay.com/develop/guides/digital-signatures-for-apis)
    -- Normally, I think it would just be "api.ebay.com" instead of "localhost:8080".
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '"@authority": '
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, 'localhost:8080'
    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, CHAR(10)

    EXEC sp_OAMethod @sbSigBase, 'Append', @success OUT, '"@signature-params": '

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

    EXEC sp_OAMethod @sbSigInput, 'Append', @success OUT, '("content-digest" "x-ebay-signature-key" "@method" "@path" "@authority")'
    EXEC sp_OAMethod @sbSigInput, 'Append', @success OUT, ';created='

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

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @unixTimeNow nvarchar(4000)
    EXEC sp_OAMethod @dt, 'GetAsUnixTimeStr', @unixTimeNow OUT, 0
    EXEC sp_OAMethod @sbSigInput, 'Append', @success OUT, @unixTimeNow

    EXEC sp_OAMethod @sbSigBase, 'AppendSb', @success OUT, @sbSigInput

    -- -------------------------------------------------
    -- Sign the signature base string using the Ed25519 private key

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

    EXEC sp_OAMethod @bdPrivKey, 'AppendEncoded', @success OUT, @strPrivateKey, 'base64'

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

    EXEC sp_OAMethod @privKey, 'LoadAnyFormat', @success OUT, @bdPrivKey, ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbBody
        EXEC @hr = sp_OADestroy @sbSigBase
        EXEC @hr = sp_OADestroy @sbSigInput
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

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

    EXEC sp_OAMethod @bdToBeSigned, 'AppendSb', @success OUT, @sbSigBase, 'utf-8'

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

    DECLARE @sigBase64 nvarchar(4000)
    EXEC sp_OAMethod @eddsa, 'SignBdENC', @sigBase64 OUT, @bdToBeSigned, 'base64', @privKey
    EXEC sp_OAGetProperty @eddsa, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @eddsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbBody
        EXEC @hr = sp_OADestroy @sbSigBase
        EXEC @hr = sp_OADestroy @sbSigInput
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @bdToBeSigned
        EXEC @hr = sp_OADestroy @eddsa
        RETURN
      END


    PRINT 'sigBase64:'

    PRINT @sigBase64

    -- ----------------------------------------------------------
    -- Send the JSON POST

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

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'x-ebay-signature-key', @strJwe

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

    EXEC sp_OAMethod @sbContentDigestHdr, 'Append', @success OUT, 'sha-256=:'
    EXEC sp_OAMethod @sbBody, 'GetHash', @sTmp0 OUT, 'sha256', 'base64', 'utf-8'
    EXEC sp_OAMethod @sbContentDigestHdr, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbContentDigestHdr, 'Append', @success OUT, ':'
    EXEC sp_OAMethod @sbContentDigestHdr, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Digest', @sTmp0

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

    EXEC sp_OAMethod @sbSigHdr, 'Append', @success OUT, 'sig1=:'
    EXEC sp_OAMethod @sbSigHdr, 'Append', @success OUT, @sigBase64
    EXEC sp_OAMethod @sbSigHdr, 'Append', @success OUT, ':'
    EXEC sp_OAMethod @sbSigHdr, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Signature', @sTmp0

    EXEC sp_OAMethod @sbSigInput, 'Prepend', @success OUT, 'sig1='
    EXEC sp_OAMethod @sbSigInput, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Signature-Input', @sTmp0

    -- Add this header to make eBay actually check the signature.
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'x-ebay-enforce-signature', 'true'

    -- Set the OAuth2 access token to add the "Authorization: Bearer <access_token>" to the header.
    EXEC sp_OASetProperty @http, 'AuthToken', 'your_oauth2_access_token'

    -- The signature base string constructed above is valid if we send this POST to "http://localhost:8080/verifysignature"
    -- Normally, you'll send your POST to some api.ebay.com endpoint.
    DECLARE @url nvarchar(4000)
    SELECT @url = 'http://localhost:8080/verifysignature'

    DECLARE @resp int
    EXEC sp_OAMethod @sbBody, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostJson2', @resp OUT, 'http://localhost:8080/verifysignature', 'application/json', @sTmp0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbBody
        EXEC @hr = sp_OADestroy @sbSigBase
        EXEC @hr = sp_OADestroy @sbSigInput
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @bdPrivKey
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @bdToBeSigned
        EXEC @hr = sp_OADestroy @eddsa
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbContentDigestHdr
        EXEC @hr = sp_OADestroy @sbSigHdr
        RETURN
      END


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response status code: ' + @iTmp0

    PRINT 'Response body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @sbBody
    EXEC @hr = sp_OADestroy @sbSigBase
    EXEC @hr = sp_OADestroy @sbSigInput
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @bdPrivKey
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @bdToBeSigned
    EXEC @hr = sp_OADestroy @eddsa
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbContentDigestHdr
    EXEC @hr = sp_OADestroy @sbSigHdr


END
GO