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;

How to parse OBX segments from an ORU-01 HL7 message using Java and HAPI

Tagged hapi, hl7, java, obx, oru  Languages java, xml

Tested with HAPI 2.3:

import ca.uhn.hl7v2.model.v23.message.ORU_R01;
import ca.uhn.hl7v2.model.v23.group.*;
import ca.uhn.hl7v2.model.v23.segment.OBX;
import ca.uhn.hl7v2.model.v23.segment.OBR;
import ca.uhn.hl7v2.model.Varies;
import org.junit.BeforeClass;
import ca.uhn.hl7v2.HL7Exception;
import ca.uhn.hl7v2.model.Message;
import ca.uhn.hl7v2.parser.PipeParser;
import ca.uhn.hl7v2.util.Terser;

/*
 * An HL7 message has the following components:
 *
 * MSH - Message Header Segment
 * RESPONSE (repeating)
 *   PATIENT
 *     PID - Patient Identification Segment
 *     PV1 – Patient Visit Segment
 *   ORDER OBSERVATION (repeating)
 *     OBR - Observation Request Segment
 *       ORC - Common Order Segment
 *       OBR - Observation Request Segment
 *       OBX – Observation Segment (repeating)
 *
 */

PipeParser pipeParser = new PipeParser();
pipeParser.setValidationContext(new ca.uhn.hl7v2.validation.impl.NoValidation());
Message message = pipeParser.parse(m);
ORU_R01 oru = (ORU_R01) message;
MSH msh = oru.getMSH();
String sendingApp = msh.getSendingApplication().encode();
String sendingFacility = msh.getSendingFacility().encode();
//
// ORU_R01_RESPONSE group structure (a Group object)
//
// 1: ORU_R01_PATIENT (a Group object) optional
// 2: ORU_R01_ORDER_OBSERVATION (a Group object) repeating
//
// See https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/group/ORU_R01_RESPONSE.html
//
for (ORU_R01_RESPONSE response : oru.getRESPONSEAll()) {
  //
  // ORU_R01_ORDER_OBSERVATION group structure (a Group object)
  //
  // 1: ORC (Common order segment) optional
  // 2: OBR (Observation request segment)
  // 3: NTE (Notes and comments segment) optional repeating
  // 4: ORU_R01_OBSERVATION (a Group object) repeating
  // 5: CTI (Clinical Trial Identification) optional repeating
  //
  // See https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/group/ORU_R01_ORDER_OBSERVATION.html
  //
  for (ORU_R01_ORDER_OBSERVATION orderObservation : response.getORDER_OBSERVATIONAll()) {
    OBR obr = orderObservation.getOBR();
    String fillerOrderNumber = obr.getObr3_FillerOrderNumber().encode();
    //
    // ORU_R01_OBSERVATION group structure (a Group object)
    //
    // 1: OBX (Observation segment) optional
    // 2: NTE (Notes and comments segment) optional repeating
    //
    // See https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/group/ORU_R01_OBSERVATION.html
    //
    for (ORU_R01_OBSERVATION observation : orderObservation.getOBSERVATIONAll()) {
      //
      // HL7 OBX message segment (Observation segment)
      //
      // https://hapifhir.github.io/hapi-hl7v2/v23/apidocs/src-html/ca/uhn/hl7v2/model/v23/segment/OBX.html
      //
      OBX obx = observation.getOBX();
      String type = obx.getObx3_ObservationIdentifier().getCe2_Text().getValue();
      String status = obx.getObservResultStatus().getValue();
      for (Varies varies : obx.getObx5_ObservationValue()) {
        String value = varies.encode();
        log.info("value {} type {} status {}", value, type, status);
      }
    }
  }
}
    <dependency>
      <groupId>ca.uhn.hapi</groupId>
      <artifactId>hapi-base</artifactId>
      <version>${hapi.version.stable}</version>
    </dependency>
    <dependency>
      <groupId>ca.uhn.hapi</groupId>
      <artifactId>hapi-structures-v23</artifactId>
      <version>${hapi.version.stable}</version>
    </dependency>

Use the HAPI test panel to inspect and find the names of elements, e.g. PID-3-4: https://hapifhir.github.io/hapi-hl7v2/hapi-testpanel/install.html