Verify and extract OpenPGP signed data in T-SQL

Verifying OpenPGP signed content with MsSqlPGP and Transact-SQL is done in two steps:

  1. verify the validity of the digital signature against a public key
  2. extract the source embedded data contained in the signature

The output of the signature verification is an INT value that can have the following meanings:

1 – the signature was verified correctly
-1 – the signature is broken or modified
-2 – the provided public key doesn’t match the signature
-3 – the input data in not an OpenPGP signature (but it can be a detached signature or .pgp signed and encrypted)

Verify signed NVARCHAR data

We need the public .pgp key corresponding to the private key used for creating the signed content. The public key must be either in serialized ASCII armored format or as file system location on the MS SQL database server machine. In this example it will be as file path. The next example will show how to use a pre-loaded ASCII armored key.

--
-- Verifying OpenPGP signed content
--
print 'Signed check Result is :'
DECLARE @verified INT
SET @verified = dbo.PGP_Verify_String(@signed, 'c:\Projects\MsSqlPgp\Examples\Data\public_key.asc');
IF 1 = @verified
	print 'signature OK'
ELSE IF -1 = @verified
	print 'signature is invalid'
ELSE IF -2 = @verified
	print 'provided public key does not match the signature'
ELSE IF -3 = @verified
	print 'the input data is not a valid OpenPGP signature'
--
-- Extracting data from an OpenPGP signed content
--
print 'Extracted text is :'
print dbo.PGP_Extract_Signed_String(@signed)

Verify signed VARBINARY data

Here we deal with .pgp signed only data in binary format and the expected internal data that will be extracted is again in binary format:

DECLARE @public_key varbinary(MAX)
DECLARE @public_keyASCII VARCHAR(MAX)
--
-- load the public key
-- 
SELECT @public_key = BulkColumn FROM OPENROWSET(BULK'c:\MsSqlPgp\Examples\Data\public_key.asc', SINGLE_BLOB) AS X;
--
-- transform the key into ASCII armored format
--
SET @public_keyASCII = dbo.PGP_Load_Key(@public_key)
 
--
-- Verifying OpenPGP signed content
--
print 'Signed check Result is :'
DECLARE @verified INT
SET @verified = dbo.PGP_Verify_Binary(@signed, @public_keyASCII);
IF 1 = @verified
	print 'signature OK'
ELSE IF -1 = @verified
	print 'signature is invalid'
ELSE IF -2 = @verified
	print 'provided public key does not match the signature'
ELSE IF -3 = @verified
	print 'the input data is not a valid OpenPGP signature'
 
print 'Extracted data is :'
print dbo.PGP_Extract_Signed_Binary(@signed);

Verify and extract external files

There may be situations when we need to verify external .pgp signed only files. In that case we are going to use dbo.PGP_Verify_File and dbo.PGP_Extract_Signed_File:

print 'Signed check Result is :'
DECLARE @verified INT
SET @verified = dbo.PGP_Verify_File('c:\MsSqlPgp\Examples\Data\data1.pgp', 'c:\MsSqlPgp\Examples\Data\public_key.asc')
IF 1 = @verified
	print 'signature OK'
ELSE IF -1 = @verified
	print 'signature is invalid'
ELSE IF -2 = @verified
	print 'provided public key does not match the signature'
ELSE IF -3 = @verified
	print 'the input data is not a valid OpenPGP signature'
 
 
-- Extracting signed content
EXEC dbo.PGP_Extract_Signed_File 'c:\MsSqlPgp\Examples\Data\data1.pgp', 'c:\MsSqlPgp\Examples\Data\data1_out.txt';

Summary

The OpenPGP signed content is processed by MsSqlPGP in two steps: signature verification and data extraction. The product offers methods for working with NVARCHAR data in an ASCII armored (text format) signed content, VARBINARY data in binary .pgp signature and over external file system located files.

List of methods used

dbo.PGP_Verify_String Verify the signature of ASCII armored signed only text content
dbo.PGP_Verify_Binary Verify the signature of binary signed only binary content
dbo.PGP_Verify_Files Verify the signature of OpenPGP signed only external files
dbo.PGP_Extract_Signed_String Extracts the text content from ASCII armored signed only .pgp data
dbo.PGP_Extract_Signed_Binary Extracts the binary content from binary signed only .pgp data
dbo.PGP_Extract_Signed_File Extracts the content from external signed only .pgp file