SQL Server
SQL Server
Validate a .pkpass Archive
See more Digital Signatures Examples
Opens a .pkpass archive (which is just a .zip renamed to .pkpass) and validates the contents. The hashes in the manifest are compared with the computed hash values for each individual file. If all computed hash values match, then the signature is verified.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 @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @zip int
EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT
EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'qa_data/pkpass/invalid.pkpass'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
RETURN
END
-- Get the contents of the manifest.json file, which contains something like this:
-- {
-- "icon.png" : "0296b01347b3173e98438a003b0e88986340b2d8",
-- "logo.png" : "25de09e2d3b01ce1fe00c2ca9a90a2be1aaa05cf",
-- "icon@2x.png" : "5afd9585b08c65fdf105a90c8bd643407cba2787",
-- "pass.json" : "145ea5a5db784fff485126c77ecf7a1fc2a88ee7",
-- "strip@2x.png" : "468fa7bc93e6b55342b56fda09bdce7c829d7d46",
-- "strip.png" : "736d01f84cb73d06e8a9932e43076d68f19461ff"
-- }
DECLARE @ent int
EXEC @hr = sp_OACreate 'Chilkat.ZipEntry', @ent OUT
EXEC sp_OAMethod @zip, 'EntryOf', @success OUT, 'manifest.json', @ent
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @ent
RETURN
END
-- Get the exact content of the manifest.json for later signature verification.
DECLARE @bdManifest int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdManifest OUT
EXEC sp_OAMethod @ent, 'UnzipToBd', @success OUT, @bdManifest
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @ent, 'UnzipToString', @sTmp0 OUT, 0, 'utf-8'
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- For each file in the JSON, get the filename and hex hash value.
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha1'
DECLARE @someHashesFailed int
SELECT @someHashesFailed = 0
DECLARE @filename nvarchar(4000)
DECLARE @sbHashHex int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHashHex OUT
DECLARE @bdFileData int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdFileData OUT
DECLARE @numMembers int
EXEC sp_OAGetProperty @json, 'Size', @numMembers OUT
DECLARE @i int
SELECT @i = 0
WHILE @i < @numMembers
BEGIN
EXEC sp_OAMethod @json, 'NameAt', @filename OUT, @i
EXEC sp_OAMethod @sbHashHex, 'Clear', NULL
EXEC sp_OAMethod @json, 'StringAt', @sTmp0 OUT, @i
EXEC sp_OAMethod @sbHashHex, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @zip, 'EntryOf', @success OUT, @filename, @ent
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @ent
EXEC @hr = sp_OADestroy @bdManifest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbHashHex
EXEC @hr = sp_OADestroy @bdFileData
RETURN
END
-- Get the data for this file.
EXEC sp_OAMethod @bdFileData, 'Clear', @success OUT
EXEC sp_OAMethod @ent, 'UnzipToBd', @success OUT, @bdFileData
DECLARE @computedHashHex nvarchar(4000)
EXEC sp_OAMethod @crypt, 'HashBdENC', @computedHashHex OUT, @bdFileData
EXEC sp_OAMethod @sbHashHex, 'ContentsEqual', @iTmp0 OUT, @computedHashHex, 0
IF @iTmp0 = 0
BEGIN
PRINT 'Computed hash does not match stored hash for ' + @filename
PRINT ' computed: ' + @computedHashHex
EXEC sp_OAMethod @sbHashHex, 'GetAsString', @sTmp0 OUT
PRINT ' stored: ' + @sTmp0
SELECT @someHashesFailed = 1
END
ELSE
BEGIN
PRINT 'hash verified for ' + @filename + '(' + @computedHashHex + ')'
END
SELECT @i = @i + 1
END
IF @someHashesFailed = 1
BEGIN
PRINT 'Some hashes failed.'
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @ent
EXEC @hr = sp_OADestroy @bdManifest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbHashHex
EXEC @hr = sp_OADestroy @bdFileData
RETURN
END
-- Let's verify the signature..
-- First get the signature.
EXEC sp_OAMethod @zip, 'EntryOf', @success OUT, 'signature', @ent
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @ent
EXEC @hr = sp_OADestroy @bdManifest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbHashHex
EXEC @hr = sp_OADestroy @bdFileData
RETURN
END
DECLARE @bdSignature int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdSignature OUT
EXEC sp_OAMethod @ent, 'UnzipToBd', @success OUT, @bdSignature
-- Show the contents of the signature in base64 encoding.
PRINT 'Signature:'
EXEC sp_OAMethod @bdSignature, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
PRINT @sTmp0
PRINT '----'
-- Verify the signature against the manifest.json
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
DECLARE @verified int
EXEC sp_OAMethod @bdSignature, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @crypt, 'VerifyBdENC', @verified OUT, @bdManifest, @sTmp0
IF @verified = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
PRINT 'signature verified = ' + @verified
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @ent
EXEC @hr = sp_OADestroy @bdManifest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbHashHex
EXEC @hr = sp_OADestroy @bdFileData
EXEC @hr = sp_OADestroy @bdSignature
END
GO