(SQL Server) Everyware API RSA Encrypt JSON

See more RSA Examples

Demonstrates how to RSA encrypt JSON using everyware.com's RSA public key.

For more information, see https://docs.everyware.com/docs/access-iframe#sample-json--encrypted-payload

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.

    DECLARE @success int

    -- First build the JSON to be encrypted.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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, 'provider_key', 'USER GUID HERE'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'menu_item', 'payment'
    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
    EXEC sp_OAMethod @dt, 'GetAsUnixTimeStr', @sTmp0 OUT, 0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'date_time', @sTmp0

    -- This build JSON like the following:

    -- {
    --     "provider_key": "USER GUID HERE",
    --     "menu_item": "payment",
    --     "date_time": "1588163411"
    -- }

    -- When we sign, we'll want to sign the most compact JSON possible
    EXEC sp_OASetProperty @json, 'EmitCompact', 1

    -- Everyware's RSA public key is at:  https://docs.everyware.com/docs/everyware-public-rsa-key
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '-----BEGIN PUBLIC KEY-----', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAxNbflxUSWQ3XJ1N9dAoh', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'k+uaiFsg3wkPi9LGS/mH8DtHBgZxKyz+oQBDtnDd9FDEo0ql7MMgCMsTAv27W5vk', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'Pu0rm6zhcTeYquWEuVCS7VtVsyTATr0Z9WhqNeZlIRurovJAXl2jRDX6QeY5dayC', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'ubwyG4lBWE4fCakGY6zlh+oaElK0rvblqjYoEg3dn4KPRCYGof8OFxLptHThD4cE', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'T30j+utVafhO0HRyJ4iR3Pigb4GXdWBtJEEEWddZJizMkjFQkyUAoYLOT8EJ2TW3', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'Tz8SvAuHBUEFcPWTSTMAG/bSp5wrYBTXaeEhx+wrYa60OruHuzgmhzKyQVuYlCNJ', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'HdbnassuIRjjSNo25o4AdSlWwpGfBZjAiyEInR+KGpHdhKTxSekJxiwiXUS0UfSG', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'prOpd5PzWaAR7DvjLsdmR9XffxvJCVxC735gLK7hDJKjCajDPHVDr8FSL8xMlrq0', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'nKxtsHeRl1yzoGrRr12+9MiQnHtpqROTNXcXdwe3v+Vh8V5k8v8oIrcgh1+/N7Bd', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'NiRsy1gFHBdu/he/KcDRT/9/acQFMPLQueGfZxUvU5As6pEONjtKX2MUg2fMF6Rc', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'sQVVrLzg0g7EcuHGfuPeKfD/716MvS8NU7rX+2soijCSQv/e18PJPMVDlcMXjnup', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'PPx1tStemesavFlj1okhS6UCAwEAAQ==', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '-----END PUBLIC KEY-----', @bCrlf

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

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @pubkey, 'LoadFromString', @success OUT, @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pubkey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @pubkey
        RETURN
      END

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

    EXEC sp_OAMethod @rsa, 'ImportPublicKeyObj', @success OUT, @pubkey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @pubkey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- We probably need a base64Url encoded encrypted key.
    -- Using straight-up base64 would potenially include chars that are not URL safe (i.e. have special meanings in URLs)
    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64url'

    DECLARE @encryptedJson nvarchar(4000)
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @rsa, 'EncryptStringENC', @encryptedJson OUT, @sTmp0, 0

    -- Build the URL
    -- Such as:  https://portal.everyware.com/Account/LoginMenu?data={Base64Url_encrypted_JSON}
    DECLARE @sbUrl int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT

    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://portal.everyware.com/Account/LoginMenu?data='
    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @encryptedJson

    EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @pubkey
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @sbUrl


END
GO