SQL Server
SQL Server
Extract TSTInfo from RFC3161 Timestamp Reply
See more HTTP Examples
Sends an RFC 3161 timestamp request to a TSA (Timestamp Authority) server and converts the timestamp reply to XML, and then extracts the TSTInfo from the XML and converts it to XML.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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Note: Requires Chilkat v9.5.0.75 or greater.
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First sha-256 hash the data that is to be timestamped.
-- In this example, the data is the string "Hello World"
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
DECLARE @base64Hash nvarchar(4000)
EXEC sp_OAMethod @crypt, 'HashFileENC', @base64Hash OUT, 'qa_data/hamlet.xml'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @requestToken int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @requestToken OUT
DECLARE @optionalPolicyOid nvarchar(4000)
SELECT @optionalPolicyOid = ''
DECLARE @addNonce int
SELECT @addNonce = 0
DECLARE @requestTsaCert int
SELECT @requestTsaCert = 1
-- Create a time-stamp request token
EXEC sp_OAMethod @http, 'CreateTimestampRequest', @success OUT, 'sha256', @base64Hash, @optionalPolicyOid, @addNonce, @requestTsaCert, @requestToken
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
RETURN
END
-- Send the time-stamp request token to the TSA.
-- This is the equivalent of the following CURL command:
-- curl -H "Content-Type: application/timestamp-query" --data-binary '@file.tsq' http://timestamp.digicert.com > file.tsr
DECLARE @tsaUrl nvarchar(4000)
SELECT @tsaUrl = 'http://timestamp.digicert.com'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpBd', @success OUT, 'POST', @tsaUrl, @requestToken, 'application/timestamp-query', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Get the timestamp reply from the HTTP response object.
DECLARE @timestampReply int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @timestampReply OUT
EXEC sp_OAMethod @resp, 'GetBodyBd', @success OUT, @timestampReply
-- Convert the binary timestamp reply to XML
DECLARE @asn int
EXEC @hr = sp_OACreate 'Chilkat.Asn', @asn OUT
EXEC sp_OAMethod @asn, 'LoadBd', @success OUT, @timestampReply
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @asn, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @timestampReply
EXEC @hr = sp_OADestroy @asn
RETURN
END
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @asn, 'AsnToXml', @sTmp0 OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
-- Extract the TSTInfo from the XML.
-- The TSTInfo is this base64 encoded ASN.1
-- TSTInfo ::= SEQUENCE {
-- version INTEGER { v1(1) },
-- policy TSAPolicyId,
-- messageImprint MessageImprint,
-- -- MUST have the same value as the similar field in
-- -- TimeStampReq
-- serialNumber INTEGER,
-- -- Time-Stamping users MUST be ready to accommodate integers
-- -- up to 160 bits.
-- genTime GeneralizedTime,
-- accuracy Accuracy OPTIONAL,
-- ordering BOOLEAN DEFAULT FALSE,
-- nonce INTEGER OPTIONAL,
-- -- MUST be present if the similar field was present
-- -- in TimeStampReq. In that case it MUST have the same value.
-- tsa [0] GeneralName OPTIONAL,
-- extensions [1] IMPLICIT Extensions OPTIONAL }
DECLARE @tstInfoBase64 nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetChildContent', @tstInfoBase64 OUT, 'sequence[1]|contextSpecific|sequence|sequence|contextSpecific|octets'
DECLARE @bdTstInfo int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdTstInfo OUT
EXEC sp_OAMethod @bdTstInfo, 'AppendEncoded', @success OUT, @tstInfoBase64, 'base64'
DECLARE @asnTstInfo int
EXEC @hr = sp_OACreate 'Chilkat.Asn', @asnTstInfo OUT
EXEC sp_OAMethod @asnTstInfo, 'LoadBd', @success OUT, @bdTstInfo
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @asnTstInfo, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @timestampReply
EXEC @hr = sp_OADestroy @asn
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bdTstInfo
EXEC @hr = sp_OADestroy @asnTstInfo
RETURN
END
DECLARE @xmlTstInfo int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlTstInfo OUT
EXEC sp_OAMethod @asnTstInfo, 'AsnToXml', @sTmp0 OUT
EXEC sp_OAMethod @xmlTstInfo, 'LoadXml', @success OUT, @sTmp0
EXEC sp_OAMethod @xmlTstInfo, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- Here's the TSTInfo XML:
-- <?xml version="1.0" encoding="utf-8"?>
-- <sequence>
-- <int>01</int>
-- <oid>2.16.840.1.114412.7.1</oid>
-- <sequence>
-- <sequence>
-- <oid>2.16.840.1.101.3.4.2.1</oid>
-- <null/>
-- </sequence>
-- <octets>4sRRyWOzC7EOic4fQ9+Op1pa10DbgoBGjBvkq09LZmE=</octets>
-- </sequence>
-- <int>00AD2C86E49872597B60F87D5C54BCFFAE</int>
-- <universal tag="24" constructed="0">MjAyMzAzMTYxMTQ5NTJa</universal>
-- </sequence>
-- The genTime (GeneralizedTime) is contained in the final "universal" XML element and is
-- in base64. It is the time at which the time-stamp token has been created by
-- the TSA. After decoding from base64, it is:
--
-- The syntax is: YYYYMMDDhhmmss[.s...]Z
-- Example: 19990609001326.34352Z
DECLARE @sbGenTime int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbGenTime OUT
EXEC sp_OAMethod @xmlTstInfo, 'GetChildContent', @sTmp0 OUT, 'universal'
EXEC sp_OAMethod @sbGenTime, 'DecodeAndAppend', @success OUT, @sTmp0, 'base64', 'utf-8'
EXEC sp_OAMethod @sbGenTime, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Result:
-- 20230316115718Z
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @requestToken
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @timestampReply
EXEC @hr = sp_OADestroy @asn
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bdTstInfo
EXEC @hr = sp_OADestroy @asnTstInfo
EXEC @hr = sp_OADestroy @xmlTstInfo
EXEC @hr = sp_OADestroy @sbGenTime
END
GO