SQL Server Requires Chilkat v11.5.0+
SQL Server
Box: Verify a Captured V2 Webhook Signature
See more Box Examples
Verify raw payload bytes and captured headers using the configured primary and secondary signing keys. The example rejects stale/future timestamps and compares randomized HMACs of the signatures to avoid exposing a signature-prefix timing comparison. It does not host an HTTP listener. The receiver must persist/deduplicate the verified event and acknowledge promptly.
See the Box API guide for setup and the complete workflow.
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
DECLARE @iTmp1 int
DECLARE @iTmp2 int
DECLARE @iTmp3 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Requires Chilkat 11.5.0 or later and a previously unlocked Chilkat bundle.
-- See Global Unlock Sample.
-- Guide: Box API Guide.
SELECT @success = 0
-- Save the exact HTTP body bytes to this file, without JSON reserialization.
DECLARE @rawBody int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @rawBody OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @rawBody, 'LoadFile', @success OUT, 'qa_data/box_webhook_body.bin'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rawBody, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
RETURN
END
-- Read these fields case-insensitively from the incoming request headers.
-- For this offline example, enter the captured header values.
DECLARE @timestamp nvarchar(4000)
SELECT @timestamp = 'BOX_DELIVERY_TIMESTAMP'
DECLARE @primarySignature nvarchar(4000)
SELECT @primarySignature = 'BOX_SIGNATURE_PRIMARY'
DECLARE @secondarySignature nvarchar(4000)
SELECT @secondarySignature = 'BOX_SIGNATURE_SECONDARY'
DECLARE @signatureVersion nvarchar(4000)
SELECT @signatureVersion = '1'
DECLARE @signatureAlgorithm nvarchar(4000)
SELECT @signatureAlgorithm = 'HmacSHA256'
-- Retrieve configured keys from protected application configuration.
DECLARE @primaryKey nvarchar(4000)
SELECT @primaryKey = 'BOX_PRIMARY_SIGNATURE_KEY'
DECLARE @secondaryKey nvarchar(4000)
SELECT @secondaryKey = 'BOX_SECONDARY_SIGNATURE_KEY'
IF (@signatureVersion <> '1') or (@signatureAlgorithm <> 'HmacSHA256')
BEGIN
PRINT 'Unsupported signature version or algorithm.'
EXEC @hr = sp_OADestroy @rawBody
RETURN
END
DECLARE @delivered int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @delivered OUT
EXEC sp_OAMethod @delivered, 'SetFromTimestamp', @success OUT, @timestamp
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @delivered, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
RETURN
END
DECLARE @now int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @now OUT
EXEC sp_OAMethod @now, 'SetFromCurrentSystemTime', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @now, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
RETURN
END
DECLARE @age int
EXEC sp_OAMethod @now, 'GetAsUnixTime', @iTmp0 OUT, 0
EXEC sp_OAMethod @delivered, 'GetAsUnixTime', @iTmp1 OUT, 0
SELECT @age = EXEC sp_OAMethod @now, 'GetAsUnixTime', @iTmp2 OUT, 0
@ - EXEC sp_OAMethod @delivered, 'GetAsUnixTime', @iTmp3 OUT, 0
@
-- Box recommends rejecting deliveries older than ten minutes.
-- Allow at most 60 seconds of future clock skew; keep the server clock synchronized.
IF (@age > 600) or (@age < -60)
BEGIN
PRINT 'Delivery timestamp is outside the accepted window.'
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
RETURN
END
DECLARE @signedBytes int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @signedBytes OUT
EXEC sp_OAMethod @signedBytes, 'AppendBd', @success OUT, @rawBody
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @signedBytes, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
RETURN
END
EXEC sp_OAMethod @signedBytes, 'AppendString', @success OUT, @timestamp, 'utf-8'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @signedBytes, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
RETURN
END
DECLARE @mac int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @mac OUT
EXEC sp_OASetProperty @mac, 'MacAlgorithm', 'hmac'
EXEC sp_OASetProperty @mac, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @mac, 'EncodingMode', 'base64'
EXEC sp_OAMethod @mac, 'SetMacKeyString', @success OUT, @primaryKey
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
RETURN
END
DECLARE @expectedPrimary nvarchar(4000)
EXEC sp_OAMethod @mac, 'MacBdENC', @expectedPrimary OUT, @signedBytes
EXEC sp_OAGetProperty @mac, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
RETURN
END
EXEC sp_OAMethod @mac, 'SetMacKeyString', @success OUT, @secondaryKey
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
RETURN
END
DECLARE @expectedSecondary nvarchar(4000)
EXEC sp_OAMethod @mac, 'MacBdENC', @expectedSecondary OUT, @signedBytes
EXEC sp_OAGetProperty @mac, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
RETURN
END
-- Blind both sides with a fresh secret random key before equality comparison.
-- Never compare the original HMAC strings using a prefix-short-circuit comparison.
-- A host-language receiver can instead use its standard constant-time byte comparison.
DECLARE @random int
EXEC @hr = sp_OACreate 'Chilkat.Prng', @random OUT
DECLARE @comparisonKey nvarchar(4000)
EXEC sp_OAMethod @random, 'GenRandom', @comparisonKey OUT, 32, 'base64'
EXEC sp_OAGetProperty @random, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @random, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
EXEC sp_OAMethod @mac, 'SetMacKeyEncoded', @success OUT, @comparisonKey, 'base64'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
DECLARE @pExpected nvarchar(4000)
EXEC sp_OAMethod @mac, 'MacStringENC', @pExpected OUT, @expectedPrimary
EXEC sp_OAGetProperty @mac, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
DECLARE @pReceived nvarchar(4000)
EXEC sp_OAMethod @mac, 'MacStringENC', @pReceived OUT, @primarySignature
EXEC sp_OAGetProperty @mac, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
DECLARE @sExpected nvarchar(4000)
EXEC sp_OAMethod @mac, 'MacStringENC', @sExpected OUT, @expectedSecondary
EXEC sp_OAGetProperty @mac, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
DECLARE @sReceived nvarchar(4000)
EXEC sp_OAMethod @mac, 'MacStringENC', @sReceived OUT, @secondarySignature
EXEC sp_OAGetProperty @mac, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @mac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
DECLARE @validPrimary int
SELECT @validPrimary = (@primaryKey <> '') and (@primarySignature <> '') and (@pExpected = @pReceived)
DECLARE @validSecondary int
SELECT @validSecondary = (@secondaryKey <> '') and (@secondarySignature <> '') and (@sExpected = @sReceived)
IF (@validPrimary = 0) and (@validSecondary = 0)
BEGIN
PRINT 'Signature verification failed.'
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
RETURN
END
-- Parse JSON only after signature verification.
DECLARE @webhookEvent int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @webhookEvent OUT
EXEC sp_OAMethod @rawBody, 'GetString', @sTmp0 OUT, 'utf-8'
EXEC sp_OAMethod @webhookEvent, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @webhookEvent, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
EXEC @hr = sp_OADestroy @webhookEvent
RETURN
END
EXEC sp_OAMethod @webhookEvent, 'StringOf', @sTmp0 OUT, 'id'
PRINT 'Verified event ID: ' + @sTmp0
EXEC sp_OAMethod @webhookEvent, 'StringOf', @sTmp0 OUT, 'trigger'
PRINT 'Trigger: ' + @sTmp0
-- Deduplicate using event.id, not BOX-DELIVERY-ID, which changes on retries.
-- Verify the target belongs to your configured webhook before dispatching work.
EXEC @hr = sp_OADestroy @rawBody
EXEC @hr = sp_OADestroy @delivered
EXEC @hr = sp_OADestroy @now
EXEC @hr = sp_OADestroy @signedBytes
EXEC @hr = sp_OADestroy @mac
EXEC @hr = sp_OADestroy @random
EXEC @hr = sp_OADestroy @webhookEvent
END
GO