SQL Server
SQL Server
Get PDF DSS (Document Security Store)
See more PDF Signatures Examples
This example demonstrates how to extract the information from a PDF's DSS (Document Security Store), if a /DSS exists. (Just because a PDF is signed does not mean a /DSS will exists. In fact, the /DSS is typically created at the point of adding the 2nd or greater signature because the /DSS contains LTV (long term validation) information about the previous signature at the time of adding an additional signature.)Note: This example requires Chilkat v9.5.0.85 or greater.
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
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @pdf int
EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/sign_testing_1/helloSigned2.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @pdf
RETURN
END
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @pdf, 'GetDss', @success OUT, @json
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- The document security store contains certificates, OCSP responses, and CRLs.
-- The following JSON is a sample of what the /DSS can contain.
-- Unfortunately, our sample contains /Certs and /OCSPs, but no /CRLs.
-- It's no problem because whatever JSON you get back, you can use the
-- following online tool to generate code to parse.
-- Generate Parsing Code from JSON
-- The code generated by the online tool for this JSON is shown below..
-- {
-- "/VRI": {
-- "/EC5BB34CC1F8A0C5FE674427E16E313A08C80808": {}
-- },
-- "/Certs": [
-- {
-- "serial": "02",
-- "validFrom": "2011-08-07T19:00:00-05:00",
-- "validTo": "2021-08-10T23:59:59-05:00",
-- "expired": false,
-- "subject": {
-- "CN": "XYZ Corporation",
-- "O": "XYZ"
-- },
-- "issuer": {
-- "CN": "XYZ Corporation",
-- "O": "XYZ"
-- },
-- "keyType": "RSA",
-- "keySize": "2048",
-- "der": "MIIDz...Q4Zt"
-- },
-- {
-- "serial": "01",
-- "validFrom": "2011-08-07T19:00:00-05:00",
-- "validTo": "2021-08-10T23:59:59-05:00",
-- "expired": false,
-- "subject": {
-- "CN": "XYZ Corporation",
-- "O": "XYZ"
-- },
-- "issuer": {
-- "CN": "XYZ Corporation",
-- "O": "XYZ"
-- },
-- "keyType": "RSA",
-- "keySize": "2048",
-- "der": "MIID...jXYFc="
-- },
-- {
-- "serial": "0AA125D6D6321B7E41E405DA3697C215",
-- "validFrom": "2016-01-07T06:00:00-06:00",
-- "validTo": "2031-01-07T12:00:00-06:00",
-- "expired": false,
-- "subject": {
-- "CN": "DigiCert SHA2 Assured ID Timestamping CA",
-- "OU": "www.digicert.com",
-- "O": "DigiCert Inc",
-- "C": "US"
-- },
-- "issuer": {
-- "CN": "DigiCert Assured ID Root CA",
-- "OU": "www.digicert.com",
-- "O": "DigiCert Inc",
-- "C": "US"
-- },
-- "keyType": "RSA",
-- "keySize": "2048",
-- "der": "MIIF...OUg=="
-- },
-- {
-- "serial": "04CD3F8568AE76C61BB0FE7160CCA76D",
-- "validFrom": "2019-09-30T19:00:00-05:00",
-- "validTo": "2030-10-17T00:00:00-05:00",
-- "expired": false,
-- "subject": {
-- "CN": "TIMESTAMP-SHA256-2019-10-15",
-- "O": "DigiCert, Inc.",
-- "C": "US"
-- },
-- "issuer": {
-- "CN": "DigiCert SHA2 Assured ID Timestamping CA",
-- "OU": "www.digicert.com",
-- "O": "DigiCert Inc",
-- "C": "US"
-- },
-- "keyType": "RSA",
-- "keySize": "2048",
-- "der": "MIIG...MJtPc="
-- },
-- {
-- "serial": "0CE7E0E517D846FE8FE560FC1BF03039",
-- "validFrom": "2006-11-09T18:00:00-06:00",
-- "validTo": "2031-11-10T00:00:00-06:00",
-- "expired": false,
-- "subject": {
-- "CN": "DigiCert Assured ID Root CA",
-- "OU": "www.digicert.com",
-- "O": "DigiCert Inc",
-- "C": "US"
-- },
-- "issuer": {
-- "CN": "DigiCert Assured ID Root CA",
-- "OU": "www.digicert.com",
-- "O": "DigiCert Inc",
-- "C": "US"
-- },
-- "keyType": "RSA",
-- "keySize": "2048",
-- "der": "MIIDt...FL6Lw8g=="
-- },
-- {
-- "serial": "E4D34D01798BE686424AF7F6F0C3BF41",
-- "validFrom": "2015-03-24T10:58:16-05:00",
-- "validTo": "2039-12-31T23:59:59-06:00",
-- "expired": false,
-- "subject": {
-- "CN": "www.xyz.com"
-- },
-- "issuer": {
-- "CN": "www.xyz.com"
-- },
-- "keyType": "RSA",
-- "keySize": "1024",
-- "der": "MIIB9DCCAWG...UR10lz"
-- }
-- ],
-- "/OCSPs": [
-- {
-- "responseStatus": 0,
-- "responseTypeOid": "1.3.6.1.5.5.7.48.1.1",
-- "responseTypeName": "ocspBasic",
-- "response": {
-- "responderIdChoice": "KeyHash",
-- "responderKeyHash": "Reuir/SSy4IxLVGLp6chnfNtyA8=",
-- "dateTime": "20201005173921Z",
-- "cert": [
-- {
-- "hashOid": "1.3.14.3.2.26",
-- "hashAlg": "SHA-1",
-- "issuerNameHash": "98S+C0C1w0QzPT+uuU1uONr67FE=",
-- "issuerKeyHash": "Reuir/SSy4IxLVGLp6chnfNtyA8=",
-- "serialNumber": "0AA125D6D6321B7E41E405DA3697C215",
-- "status": 0,
-- "thisUpdate": "20201005173921Z",
-- "nextUpdate": "20201012173921Z"
-- }
-- ]
-- }
-- },
-- {
-- "responseStatus": 0,
-- "responseTypeOid": "1.3.6.1.5.5.7.48.1.1",
-- "responseTypeName": "ocspBasic",
-- "response": {
-- "responderIdChoice": "KeyHash",
-- "responderKeyHash": "9LbhIB3+Ka7S5GGlsqIlssgXNW4=",
-- "dateTime": "20201006114501Z",
-- "cert": [
-- {
-- "hashOid": "1.3.14.3.2.26",
-- "hashAlg": "SHA-1",
-- "issuerNameHash": "+YYA+KSr7NIxRSxCjUNQo25SyD0=",
-- "issuerKeyHash": "9LbhIB3+Ka7S5GGlsqIlssgXNW4=",
-- "serialNumber": "04CD3F8568AE76C61BB0FE7160CCA76D",
-- "status": 0,
-- "thisUpdate": "20201006114501Z",
-- "nextUpdate": "20201013110001Z"
-- }
-- ]
-- }
-- }
-- ]
-- }
DECLARE @responseDateTime int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @responseDateTime OUT
DECLARE @thisUpdate int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @thisUpdate OUT
DECLARE @nextUpdate int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @nextUpdate OUT
DECLARE @serial nvarchar(4000)
DECLARE @validFrom nvarchar(4000)
DECLARE @validTo nvarchar(4000)
DECLARE @expired int
DECLARE @subjectCN nvarchar(4000)
DECLARE @subjectO nvarchar(4000)
DECLARE @issuerCN nvarchar(4000)
DECLARE @issuerO nvarchar(4000)
DECLARE @keyType nvarchar(4000)
DECLARE @keySize nvarchar(4000)
DECLARE @der nvarchar(4000)
DECLARE @subjectOU nvarchar(4000)
DECLARE @subjectC nvarchar(4000)
DECLARE @issuerOU nvarchar(4000)
DECLARE @issuerC nvarchar(4000)
DECLARE @responseStatus int
DECLARE @responseTypeOid nvarchar(4000)
DECLARE @responseTypeName nvarchar(4000)
DECLARE @responseResponderIdChoice nvarchar(4000)
DECLARE @responseResponderKeyHash nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @hashOid nvarchar(4000)
DECLARE @hashAlg nvarchar(4000)
DECLARE @issuerNameHash nvarchar(4000)
DECLARE @issuerKeyHash nvarchar(4000)
DECLARE @serialNumber nvarchar(4000)
DECLARE @status int
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, '/Certs'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @serial OUT, '/Certs[i].serial'
EXEC sp_OAMethod @json, 'StringOf', @validFrom OUT, '/Certs[i].validFrom'
EXEC sp_OAMethod @json, 'StringOf', @validTo OUT, '/Certs[i].validTo'
EXEC sp_OAMethod @json, 'BoolOf', @expired OUT, '/Certs[i].expired'
EXEC sp_OAMethod @json, 'StringOf', @subjectCN OUT, '/Certs[i].subject.CN'
EXEC sp_OAMethod @json, 'StringOf', @subjectO OUT, '/Certs[i].subject.O'
EXEC sp_OAMethod @json, 'StringOf', @issuerCN OUT, '/Certs[i].issuer.CN'
EXEC sp_OAMethod @json, 'StringOf', @issuerO OUT, '/Certs[i].issuer.O'
EXEC sp_OAMethod @json, 'StringOf', @keyType OUT, '/Certs[i].keyType'
EXEC sp_OAMethod @json, 'StringOf', @keySize OUT, '/Certs[i].keySize'
EXEC sp_OAMethod @json, 'StringOf', @der OUT, '/Certs[i].der'
EXEC sp_OAMethod @json, 'StringOf', @subjectOU OUT, '/Certs[i].subject.OU'
EXEC sp_OAMethod @json, 'StringOf', @subjectC OUT, '/Certs[i].subject.C'
EXEC sp_OAMethod @json, 'StringOf', @issuerOU OUT, '/Certs[i].issuer.OU'
EXEC sp_OAMethod @json, 'StringOf', @issuerC OUT, '/Certs[i].issuer.C'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, '/OCSPs'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'IntOf', @responseStatus OUT, '/OCSPs[i].responseStatus'
EXEC sp_OAMethod @json, 'StringOf', @responseTypeOid OUT, '/OCSPs[i].responseTypeOid'
EXEC sp_OAMethod @json, 'StringOf', @responseTypeName OUT, '/OCSPs[i].responseTypeName'
EXEC sp_OAMethod @json, 'StringOf', @responseResponderIdChoice OUT, '/OCSPs[i].response.responderIdChoice'
EXEC sp_OAMethod @json, 'StringOf', @responseResponderKeyHash OUT, '/OCSPs[i].response.responderKeyHash'
EXEC sp_OAMethod @json, 'DtOf', @success OUT, '/OCSPs[i].response.dateTime', 0, @responseDateTime
SELECT @j = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_j OUT, '/OCSPs[i].response.cert'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
EXEC sp_OAMethod @json, 'StringOf', @hashOid OUT, '/OCSPs[i].response.cert[j].hashOid'
EXEC sp_OAMethod @json, 'StringOf', @hashAlg OUT, '/OCSPs[i].response.cert[j].hashAlg'
EXEC sp_OAMethod @json, 'StringOf', @issuerNameHash OUT, '/OCSPs[i].response.cert[j].issuerNameHash'
EXEC sp_OAMethod @json, 'StringOf', @issuerKeyHash OUT, '/OCSPs[i].response.cert[j].issuerKeyHash'
EXEC sp_OAMethod @json, 'StringOf', @serialNumber OUT, '/OCSPs[i].response.cert[j].serialNumber'
EXEC sp_OAMethod @json, 'IntOf', @status OUT, '/OCSPs[i].response.cert[j].status'
EXEC sp_OAMethod @json, 'DtOf', @success OUT, '/OCSPs[i].response.cert[j].thisUpdate', 0, @thisUpdate
EXEC sp_OAMethod @json, 'DtOf', @success OUT, '/OCSPs[i].response.cert[j].nextUpdate', 0, @nextUpdate
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @responseDateTime
EXEC @hr = sp_OADestroy @thisUpdate
EXEC @hr = sp_OADestroy @nextUpdate
END
GO