(SQL Server) Signing HTTP Messages

Demonstrates how to sign HTTP messages per draft-cavage-http-signatures-10

For more information, see https://tools.ietf.org/html/draft-cavage-http-signatures-10

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 @bCrlf int
    SELECT @bCrlf = 1
    DECLARE @sbPublicKeyPem int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPublicKeyPem OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, '-----BEGIN PUBLIC KEY-----', @bCrlf
    EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, 'MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDCFENGw33yGihy92pDjZQhl0C3', @bCrlf
    EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, '6rPJj+CvfSC8+q28hxA161QFNUd13wuCTUcq0Qd2qsBe/2hFyc2DCJJg0h1L78+6', @bCrlf
    EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, 'Z4UMR7EOcpfdUE9Hf3m/hs+FUR45uBJeDK1HSFHD8bHKD6kv8FPGfJTotc+2xjJw', @bCrlf
    EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, 'oYi+1hqp1fIekaxsyQIDAQAB', @bCrlf
    EXEC sp_OAMethod @sbPublicKeyPem, '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 @sbPublicKeyPem, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @pubKey, 'LoadFromString', @success OUT, @sTmp0

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

    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '-----BEGIN RSA PRIVATE KEY-----', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'MIICXgIBAAKBgQDCFENGw33yGihy92pDjZQhl0C36rPJj+CvfSC8+q28hxA161QF', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'NUd13wuCTUcq0Qd2qsBe/2hFyc2DCJJg0h1L78+6Z4UMR7EOcpfdUE9Hf3m/hs+F', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'UR45uBJeDK1HSFHD8bHKD6kv8FPGfJTotc+2xjJwoYi+1hqp1fIekaxsyQIDAQAB', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'AoGBAJR8ZkCUvx5kzv+utdl7T5MnordT1TvoXXJGXK7ZZ+UuvMNUCdN2QPc4sBiA', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'QWvLw1cSKt5DsKZ8UETpYPy8pPYnnDEz2dDYiaew9+xEpubyeW2oH4Zx71wqBtOK', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'kqwrXa/pzdpiucRRjk6vE6YY7EBBs/g7uanVpGibOVAEsqH1AkEA7DkjVH28WDUg', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'f1nqvfn2Kj6CT7nIcE3jGJsZZ7zlZmBmHFDONMLUrXR/Zm3pR5m0tCmBqa5RK95u', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '412jt1dPIwJBANJT3v8pnkth48bQo/fKel6uEYyboRtA5/uHuHkZ6FQF7OUkGogc', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'mSJluOdc5t6hI1VsLn0QZEjQZMEOWr+wKSMCQQCC4kXJEsHAve77oP6HtG/IiEn7', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'kpyUXRNvFsDE0czpJJBvL/aRFUJxuRK91jhjC68sA7NsKMGg5OXb5I5Jj36xAkEA', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'gIT7aFOYBFwGgQAQkWNKLvySgKbAZRTeLBacpHMuQdl1DfdntvAyqpAZ0lY0RKmW', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'G6aFKaqQfOXKCyWoUiVknQJAXrlgySFci/2ueKlIE1QqIiLSZ8V8OlpFLRnb1pzI', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '7U1yQXnTAEFYM560yJlzUpOb1V4cScGd365tiSMvxLOvTA==', @bCrlf
    EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '-----END RSA PRIVATE KEY-----', @bCrlf

    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 @sbPrivateKeyPem, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @privKey, 'LoadPem', @success OUT, @sTmp0

    --    All examples use this request:
    -- 
    --    POST /foo?param=value&pet=dog HTTP/1.1
    --    Host: example.com
    --    Date: Sun, 05 Jan 2014 21:31:40 GMT
    --    Content-Type: application/json
    --    Digest: SHA-256=X48E9qOokqqrvdts8nOJRJN3OWDUoyWxBf7kbu9DBPE=
    --    Content-Length: 18
    -- 
    --    {"hello": "world"}

    -- C.1.  Default Test
    -- 
    --    If a list of headers is not included, the date is the only header
    --    that is signed by default.  The string to sign would be:
    -- 
    --    date: Sun, 05 Jan 2014 21:31:40 GMT
    -- 
    --    The Authorization header would be:
    -- 
    --    Authorization: Signature keyId="Test",algorithm="rsa-sha256",
    --    signature="SjWJWbWN7i0wzBvtPl8rbASWz5xQW6mcJmn+ibttBqtifLN7Sazz
    --    6m79cNfwwb8DMJ5cou1s7uEGKKCs+FLEEaDV5lp7q25WqS+lavg7T8hc0GppauB
    --    6hbgEKTwblDHYGEtbGmtdHgVCk9SuS13F0hZ8FD0k/5OxEPXe5WozsbM="
    -- 
    --    The Signature header would be:
    -- 
    --    Signature: keyId="Test",algorithm="rsa-sha256",
    --    signature="SjWJWbWN7i0wzBvtPl8rbASWz5xQW6mcJmn+ibttBqtifLN7Sazz
    --    6m79cNfwwb8DMJ5cou1s7uEGKKCs+FLEEaDV5lp7q25WqS+lavg7T8hc0GppauB
    --    6hbgEKTwblDHYGEtbGmtdHgVCk9SuS13F0hZ8FD0k/5OxEPXe5WozsbM="
    -- 

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

    EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @dateStr nvarchar(4000)
    EXEC sp_OAMethod @dtNow, 'GetAsRfc822', @dateStr OUT, 0

    -- To duplicate the above result, we'll hard-code the date string.
    SELECT @dateStr = 'Sun, 05 Jan 2014 21:31:40 GMT'

    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, 'ImportPrivateKeyObj', @success OUT, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbPublicKeyPem
        EXEC @hr = sp_OADestroy @pubKey
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

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

    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'date: '
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @dateStr

    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
    DECLARE @b64Signature nvarchar(4000)
    EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rsa, 'SignStringENC', @b64Signature OUT, @sTmp0, 'SHA256'

    PRINT @b64Signature

    PRINT '---------------------------'

    -- The result should be:
    -- SjWJWbWN7i0wzBvtPl8rbASW ... FD0k/5OxEPXe5WozsbM=

    -- ----------------------------------------------------------------------------------------------------

    -- C.2.  Basic Test
    -- 
    --    The minimum recommended data to sign is the (request-target), host,
    --    and date.  In this case, the string to sign would be:
    -- 
    --    (request-target): post /foo?param=value&pet=dog
    --    host: example.com
    --    date: Sun, 05 Jan 2014 21:31:40 GMT
    -- 
    --    The Authorization header would be:
    -- 
    --    Authorization: Signature keyId="Test",algorithm="rsa-sha256",
    --    headers="(request-target) host date", signature="qdx+H7PHHDZgy4
    --    y/Ahn9Tny9V3GP6YgBPyUXMmoxWtLbHpUnXS2mg2+SbrQDMCJypxBLSPQR2aAjn
    --    7ndmw2iicw3HMbe8VfEdKFYRqzic+efkb3nndiv/x1xSHDJWeSWkx3ButlYSuBs
    --    kLu6kd9Fswtemr3lgdDEmn04swr2Os0="

    EXEC sp_OAMethod @sbStringToSign, 'Clear', NULL
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, '(request-target): '
    EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, 'post /foo?param=value&pet=dog', 0
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'host: '
    EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, 'example.com', 0
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'date: '
    EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @dateStr


    PRINT 'StringToSign:'
    EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0
    EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rsa, 'SignStringENC', @b64Signature OUT, @sTmp0, 'SHA256'

    PRINT @b64Signature

    PRINT '---------------------------'

    -- The result should be:
    -- qdx+H7PHHDZgy4y/Ahn ... mn04swr2Os0=

    EXEC @hr = sp_OADestroy @sbPublicKeyPem
    EXEC @hr = sp_OADestroy @pubKey
    EXEC @hr = sp_OADestroy @sbPrivateKeyPem
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @dtNow
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @sbStringToSign


END
GO