xml

How to sign XML documents using XMLDSig (XML Signature)

Tagged xmldsig, xml, signature, wtf, hl7fi, kanta, xmlsec1  Languages bash, xml

Install xmlsec1

sudo apt-get install xmlsec1

Create document

<?xml version="1.0" encoding="UTF-8"?>
<document>
  <hello>All XML is doomed to fail.</hello>
  <!-- Signature contains the signature definition -->
  <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
    <SignedInfo>
      <CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
      <SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/>
      <Reference>
        <Transforms>
          <Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/>
          <Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
        </Transforms>
        <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/>
        <DigestValue />
      </Reference>
      </SignedInfo>
    <SignatureValue />
    <KeyInfo>
      <X509Data />
    </KeyInfo>
  </Signature>
</document>

Sign document

xmlsec1 --sign --privkey-pem xxx.com.key,xxx.com.cer --output signed.xml tosign.xml

This example uses test certificates issued by VRK.

Verify document

xmlsec1 --verify --trusted-pem vrkthsp.pem --trusted-pem vrktestc.pem signed.xml

Note that a concatenated PEM file, i.e. cat vrkthsp.pem vrktestc.pem > concat.pem, does not work with xmlsec1.

How to specify which elements to sign with ds:Reference

Add one or more ds:Reference elements to specify which elements should be signed. Each element should have a unique ID in the URI attribute. The ID should be prefixed with a hash, e.g., #your-id:

<ds:Reference URI="#secret-xml-sauce">

Make sure your document contains an element having the exact ID without the hash prefix:

<Dog ID="secret-xml-sauce" name="Christian" />

Next, use the “—id-attr” switch to specify the element and attribute name:

xmlsec1 --sign --privkey-pem signing.key,signing.pem --id-attr:ID Dog --id-attr:ID structuredBody --output signed.xml tosign.xml

Note that “id” is the default attribute name. You only need —id-attr switch if you have the ID in an attribute having a different name.

How to sign multiple elements

Just add another “—id-attr: ” switch:

xmlsec1 --sign --privkey-pem signing.key,signing.pem --id-attr:ID signatureTimestamp --id-attr:ID structuredBody --output signed.xml tosign.xml

Then add another element having the given ID.

Troubleshooting

  • This error means you don’t have the correct trusted pem
certificate issuer check failed:err=2;msg=unable to get issuer certificate;issuer=/C=FI/ST=Finland/O=Vaestorekisterikeskus TEST/OU=Certification Authority Services/OU=Varmennepalvelut/CN=VRK TEST Root CA

To fix the error, take a hard look at the Issuer and Subject of all certificates in the certificate chain. For example:

openssl x509 -inform DER -in vrktestc.crt -text | grep "Issuer\|Subject"
  • xmlsec1 fails to find element containing ID
func=xmlSecXPathDataExecute:file=xpath.c:line=273:obj=unknown:subj=xmlXPtrEval:error=5:libxml2 library function failed:expr=xpointer(id('ID_OF_ELEMENT_TO_SIGN'))

Note that the XPATH queries are case sensitive. This means you might have to specify both the name of the element and the name of the ID attribute like this:

xmlsec1 sign --id-attr:ID elementThatYouWantToSign ...

For more solutions to issues, see sgros.blogspot.com: http://sgros.blogspot.com/2013/01/signing-xml-document-using-xmlsec1.html

Storing, querying, and indexing XML with Postgres

Tagged postgres, xbrl, xml, xpath  Languages sql, xml

Create table

CREATE TABLE xbrl_reports
(
  id serial primary key NOT NULL,
  doc xml NOT NULL,
  cik varchar(255) NOT NULL
);

Create function for importing XML

-- http://tapoueh.org/blog/2009/02/05-importing-xml-content-from-file
create or replace function xml_import(filename text)
  returns xml
  volatile
  language plpgsql as
$f$
    declare
        content bytea;
        loid oid;
        lfd integer;
        lsize integer;
    begin
        loid := lo_import(filename);
        lfd := lo_open(loid,262144);
        lsize := lo_lseek(lfd,0,2);
        perform lo_lseek(lfd,0,0);
        content := loread(lfd,lsize);
        perform lo_close(lfd);
        perform lo_unlink(loid);
 
        return xmlparse(document convert_from(content,'UTF8'));
    end;
$f$;

Import XML

-- Import XML file into Postgres
insert into xbrl_reports(doc, cik) values(xml_import('/Users/Christian/Downloads/2016q3/ibm-20160930.xml'), '1');

XML namespaces:

<xbrl
  xmlns="http://www.xbrl.org/2003/instance"
  xmlns:dei="http://xbrl.sec.gov/dei/2014-01-31"

Query data

-- Check if dei::TradingSymbol exists => t
SELECT xpath('//xbrl:xbrl/dei:TradingSymbol/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}') from xbrl_reports;

-- Extract dei:TradingSymbol by declaring dei namespace => {IBM}
SELECT xpath('//xbrl:xbrl/dei:TradingSymbol/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}') from xbrl_reports;

-- Extract dei:TradingSymbol by adding ((...)[1]::text) => IBM
SELECT ((xpath('//xbrl:xbrl/dei:TradingSymbol/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}'))[1]::text) from xbrl_reports;

Index data

-- Create index for faster lookups
create index xbrl_reports_ticker_idx on xbrl_reports using btree ((( xpath('//xbrl:xbrl/dei:TradingSymbol/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}') )[1]::text)); 

Materialized views for performance

CREATE MATERIALIZED VIEW company_reports AS SELECT
  ((xpath('//xbrl:xbrl/dei:TradingSymbol/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}'))[1]::text) as ticker,
  ((xpath('//xbrl:xbrl/dei:EntityRegistrantName/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}'))[1]::text) as name,
  ((xpath('//xbrl:xbrl/dei:DocumentType/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}'))[1]::text) as document_type,
  ((xpath('//xbrl:xbrl/dei:DocumentPeriodEndDate/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}'))[1]::text) as quarter,
  ((xpath('//xbrl:xbrl/dei:EntityCommonStockSharesOutstanding/text()', doc, '{{xbrl,http://www.xbrl.org/2003/instance},{dei,http://xbrl.sec.gov/dei/2014-01-31}}'))[1]::text) as shares_outstanding
FROM xbrl_reports;