Friday, December 18, 2009

Forray into xsl

So, this isn't going to be very pretty, but I just wanted to document it somewhere, and it certainly may not be the best way to go about it.  I just don't have a lot of XML experience so I'm happy I got the job done pretty quickly with some research.  I'll add more explanation when I have time.

I created an Access DB that I needed to convert to XML.  Here's the DB diagram.





Here's what the test data looked like after exporting the DB to xml:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="arb_UploadingCompany.xsd" generated="2009-12-18T15:22:05">
<arb:UploadingCompany>
<UploadingCompanyID>1</UploadingCompanyID>
<arb:Trucks>
<TrucksID>1</TrucksID>
<UploadingCompanyID>1</UploadingCompanyID>
<arb:Truck>
<TruckID>1</TruckID>
<TrucksID>1</TrucksID>
<arb:RequestForLabel>true</arb:RequestForLabel>
<arb:Compliance>
<ComplianceID>1</ComplianceID>
<TruckID>1</TruckID>
<arb:ModelYr2005-2006>Y</arb:ModelYr2005-2006>
</arb:Compliance>
<arb:ComplianceStatus>
<ComplianceStatusID>1</ComplianceStatusID>
<TruckID>1</TruckID>
<arb:Status>something</arb:Status>
<arb:ThroughDate>something</arb:ThroughDate>
</arb:ComplianceStatus>
<arb:Engine>
<EngineID>1</EngineID>
<TruckID>1</TruckID>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName/>
<arb:ModelYear>2007</arb:ModelYear>
</arb:Engine>
<arb:SystemMessage>
<SystemMessageID>1</SystemMessageID>
<TruckID>1</TruckID>
<arb:Code>1234561</arb:Code>
<arb:Message>something</arb:Message>
</arb:SystemMessage>
<arb:Vehicle>
<VehicleID>1</VehicleID>
<TruckID>1</TruckID>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2007</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<LicenseID>1</LicenseID>
<VehicleID>1</VehicleID>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Oregon</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
</arb:Truck>
<arb:Truck>
<TruckID>2</TruckID>
<TrucksID>1</TrucksID>
<arb:RequestForLabel>true</arb:RequestForLabel>
<arb:Compliance>
<ComplianceID>2</ComplianceID>
<TruckID>2</TruckID>
<arb:RegistrationOnly>Y</arb:RegistrationOnly>
</arb:Compliance>
<arb:Engine>
<EngineID>2</EngineID>
<TruckID>2</TruckID>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName>SomeFamily</arb:FamilyName>
<arb:ModelYear>2006</arb:ModelYear>
</arb:Engine>
<arb:Vehicle>
<VehicleID>2</VehicleID>
<TruckID>2</TruckID>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2006</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<LicenseID>2</LicenseID>
<VehicleID>2</VehicleID>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Alabama</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
</arb:Truck>
</arb:Trucks>
<arb:CompanyProfile>
<CompanyProfileID>1</CompanyProfileID>
<UploadingCompanyID>1</UploadingCompanyID>
<arb:CompanyName>Con-way</arb:CompanyName>
<arb:Address>
<AddressID>1</AddressID>
<CompanyProfileID>1</CompanyProfileID>
<arb:US-Address>
<US-AddressID>1</US-AddressID>
<AddressID>1</AddressID>
<arb:Line1>xxxxxx</arb:Line1>
<arb:City>Portland</arb:City>
<arb:State>Oregon</arb:State>
<arb:Zip>97202</arb:Zip>
</arb:US-Address>
</arb:Address>
<arb:Contact>
<ContactID>1</ContactID>
<CompanyProfileID>1</CompanyProfileID>
<arb:FirstName>Kevin</arb:FirstName>
<arb:LastName>Ellis</arb:LastName>
<arb:Email>ellis.kevin@xxxxxx.com</arb:Email>
</arb:Contact>
<arb:Phone>
<PhoneID>1</PhoneID>
<CompanyProfileID>1</CompanyProfileID>
<arb:US-Phone>123-456-7890</arb:US-Phone>
</arb:Phone>
<arb:TaxId>
<TaxIdID>1</TaxIdID>
<CompanyProfileID>1</CompanyProfileID>
<arb:USTaxId>12-3456789</arb:USTaxId>
</arb:TaxId>
</arb:CompanyProfile>
</arb:UploadingCompany>
</dataroot>


This is what the data needed to look like to send it to the vendor:

<?xml version="1.0" encoding="utf-8"?>
<arb:ARBER arb:SchemaVersion="1.7" xmlns:arb="http://www.arb.ca.gov/arber/batch/arber_schema_v1.7" xsi:schemaLocation="http://www.arb.ca.gov/arber/batch/arber_schema_v1.7 file:///C:/Users/sysadmin/Desktop/Arber/Arber/arber_schema_v1.7.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<arb:UploadingCompany>
<arb:CompanyProfile>
<arb:CompanyName>Con-way</arb:CompanyName>
<arb:TaxId>
<arb:USTaxId>12-3456789</arb:USTaxId>
</arb:TaxId>
<arb:Phone>
<arb:US-Phone>123-456-7890</arb:US-Phone>
</arb:Phone>
<arb:Address>
<arb:US-Address>
<arb:Line1>xxx</arb:Line1>
<arb:City>Portland</arb:City>
<arb:State>Oregon</arb:State>
<arb:Zip>97202</arb:Zip>
</arb:US-Address>
</arb:Address>
<arb:Contact>
<arb:FirstName>Kevin</arb:FirstName>
<arb:LastName>Ellis</arb:LastName>
<arb:Email>ellis.kevin@xxxxxx.com</arb:Email>
</arb:Contact>
</arb:CompanyProfile>
<arb:Trucks>
<arb:Truck>
<arb:Vehicle>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2007</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Oregon</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
<arb:Engine>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName/>
<arb:ModelYear>2007</arb:ModelYear>
</arb:Engine>
<arb:Compliance>
<arb:ModelYr2005-2006>Y</arb:ModelYr2005-2006>
</arb:Compliance>
<arb:RequestForLabel>true</arb:RequestForLabel>
</arb:Truck>
<arb:Truck>
<arb:Vehicle>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2006</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Alabama</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
<arb:Engine>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName>SomeFamily</arb:FamilyName>
<arb:ModelYear>2006</arb:ModelYear>
</arb:Engine>
<arb:Compliance>
<arb:RegistrationOnly>Y</arb:RegistrationOnly>
</arb:Compliance>
<arb:RequestForLabel>true</arb:RequestForLabel>
</arb:Truck>
</arb:Trucks>
</arb:UploadingCompany>
</arb:ARBER>



Here's the xsl I needed to write to get it there:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<arb:ARBER arb:SchemaVersion="1.7" xmlns:arb="http://www.arb.ca.gov/arber/batch/arber_schema_v1.7">
<arb:UploadingCompany>
<xsl:for-each select="dataroot/arb:UploadingCompany/arb:CompanyProfile">
<arb:CompanyProfile>
<xsl:copy-of select="arb:CompanyName"/>
<xsl:for-each select="arb:TaxId">
<arb:TaxId>
<xsl:copy-of select="arb:USTaxId"/>
</arb:TaxId>
</xsl:for-each>
<xsl:for-each select="arb:Phone">
<arb:Phone>
<xsl:copy-of select="arb:US-Phone"/>
</arb:Phone>
</xsl:for-each>
<xsl:for-each select="arb:Address">
<arb:Address>
<xsl:for-each select="arb:US-Address">
<arb:US-Address>
<xsl:copy-of select="arb:Line1"/>
<xsl:copy-of select="arb:City"/>
<xsl:copy-of select="arb:State"/>
<xsl:copy-of select="arb:Zip"/>
</arb:US-Address>
</xsl:for-each>
</arb:Address>
</xsl:for-each>
<xsl:for-each select="arb:Contact">
<arb:Contact>
<xsl:copy-of select="arb:FirstName"/>
<xsl:copy-of select="arb:LastName"/>
<xsl:copy-of select="arb:Title"/>
<xsl:copy-of select="arb:Email"/>
</arb:Contact>
</xsl:for-each>
</arb:CompanyProfile>
</xsl:for-each>
<xsl:for-each select="dataroot/arb:UploadingCompany/arb:Trucks">
<arb:Trucks>
<xsl:for-each select="arb:Truck">
<arb:Truck>
<xsl:for-each select="arb:Vehicle">
<arb:Vehicle>
<xsl:copy-of select="arb:MfrName"/>
<xsl:copy-of select="arb:ModelYear"/>
<xsl:copy-of select="arb:VIN"/>
<xsl:for-each select="arb:License">
<arb:License>
<xsl:copy-of select="arb:PlateNum"/>
<xsl:copy-of select="arb:LicenseState-US"/>
</arb:License>
</xsl:for-each>
</arb:Vehicle>
</xsl:for-each>
<xsl:for-each select="arb:Engine">
<arb:Engine>
<xsl:copy-of select="arb:MfrName"/>
<xsl:copy-of select="arb:FamilyName"/>
<xsl:copy-of select="arb:Model"/>
<xsl:copy-of select="arb:ModelYear"/>
</arb:Engine>
</xsl:for-each>
<xsl:for-each select="arb:Compliance">
<arb:Compliance>
<xsl:if test="arb:RegistrationOnly">
<xsl:copy-of select="arb:RegistrationOnly"/>
</xsl:if>
<xsl:if test="arb:ModelYr2004">
<xsl:copy-of select="arb:ModelYr2004"/>
</xsl:if>
<xsl:if test="arb:ModelYr2005-2006">
<xsl:copy-of select="arb:ModelYr2005-2006"/>
</xsl:if>
<xsl:if test="arb:ModelYr2007orAbove">
<xsl:copy-of select="arb:ModelYr2007orAbove"/>
</xsl:if>
</arb:Compliance>
</xsl:for-each>
<xsl:copy-of select="arb:RequestForLabel"/>
</arb:Truck>
</xsl:for-each>
</arb:Trucks>
</xsl:for-each>
</arb:UploadingCompany>
</arb:ARBER>
</xsl:template>
</xsl:stylesheet>

Tuesday, September 22, 2009

TestDisk to the rescue!

Last night I had a serious problem with my netbook. I'm running Ubuntu on a Dell mini with most of the data on an SD card. Anyway, I think the SD card got messed up when the machine went into hibernation, and when I opened the lid I was getting cryptic messages about the drive not being able to mount. I tried the usual tools, fsck, dumpe2fs, gpart, etc. Nothing doing. Everything that was working for other people wasn't working for me... Kept researching the messages I was getting from the various tools:

Block bitmap for group 1 not in group

Bad Magic Number in Superblock

Disk doesn't contain a valid partition table

etc. etc.

The last one ended up being the clue. Found a utility called TestDisk and it had me fixed up in less than 15 minutes.

http://www.cgsecurity.org/wiki/TestDisk_Step_By_Step

It was able to figure out the partitions based on the good data still on the disk. Then it was just a matter of using the tool to rewrite the partition table and I was back in business!

Wednesday, July 22, 2009

Receiving a SAML Response with a Java servlet

As an addendum to my previous post, if you need to receive a SAML Response in a Java servlet using OpenSAML you can use this code. This is, obviously, more likely than needing to create a Response object from an XML file. The 'request' variable is just an input to the processRequest method on the servlet.

import org.opensaml.ws.message.MessageContext;
import org.opensaml.ws.transport.http.HttpServletRequestAdapter;
import org.opensaml.common.binding.BasicSAMLMessageContext;
import org.opensaml.saml2.binding.decoding.HTTPPostDecoder;
import org.opensaml.saml2.core.Response;

//get the message context
MessageContext messageContext = new BasicSAMLMessageContext();
messageContext.setInboundMessageTransport(new HttpServletRequestAdapter(request));
HTTPPostDecoder samlMessageDecoder = new HTTPPostDecoder();
samlMessageDecoder.decode(messageContext);

//get the SAML Response
Response samlResponse = (Response)messageContext.getInboundMessage();

Monday, July 6, 2009

Processing SAML in Java using OpenSAML

I'm currently doing all of my SAML 2.0 work in a .NET environment, but I wanted to verify my SAML in Java too so I created this tester utilizing OpenSAML. It certainly isn't pretty at this point, and I'm certainly not saying it represents any best-practices, but I'm going to post it in its current state because I think it illustrates some useful things. It's basically just unmarshalling xml to an OpenSAML response object, verifying the signature on the response, and decrypting an assertion. Nothing terribly mind-blowing, but it might prove useful to someone.

import org.w3c.dom.Document;
import org.w3c.dom.Element;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import java.util.List;

import javax.xml.namespace.QName;
import javax.xml.validation.Schema;

import java.security.KeyFactory;
import java.security.KeyStore;
import java.security.PublicKey;
import java.security.cert.CertificateFactory;
import java.security.cert.X509Certificate;
import java.security.interfaces.RSAPrivateKey;
import java.security.spec.X509EncodedKeySpec;

import org.opensaml.DefaultBootstrap;
import org.opensaml.common.xml.SAMLSchemaBuilder;
import org.opensaml.saml2.core.Assertion;
import org.opensaml.saml2.core.Attribute;
import org.opensaml.saml2.core.AttributeStatement;
import org.opensaml.saml2.core.Response;
import org.opensaml.saml2.encryption.Decrypter;
import org.opensaml.xml.Configuration;
import org.opensaml.xml.XMLObject;
import org.opensaml.xml.encryption.DecryptionException;
import org.opensaml.xml.encryption.InlineEncryptedKeyResolver;
import org.opensaml.xml.io.Unmarshaller;
import org.opensaml.xml.parse.BasicParserPool;
import org.opensaml.xml.security.keyinfo.StaticKeyInfoCredentialResolver;
import org.opensaml.xml.security.x509.BasicX509Credential;
import org.opensaml.xml.signature.Signature;
import org.opensaml.xml.signature.SignatureValidator;
import org.opensaml.xml.validation.ValidationException;


public class Tester
{
public static void main(String[] args)
{
try
{
//initialize the opensaml library
DefaultBootstrap.bootstrap();

Schema schema = SAMLSchemaBuilder.getSAML11Schema();

//get parser pool manager
BasicParserPool parserPoolManager = new BasicParserPool();
parserPoolManager.setNamespaceAware(true);
parserPoolManager.setIgnoreElementContentWhitespace(true);
parserPoolManager.setSchema(schema);

//grab the xml file
File xmlFile = new File("C:\\Documents and Settings\\kgellis\\My Documents\\Eclipse\\Workspace\\MyOpenSamlTester\\Files\\Raw_AssertionEncrypted.xml");

//parse xml file
FileInputStream fileInputStream = new FileInputStream(xmlFile);
InputStream inputStream = fileInputStream;
Document document = parserPoolManager.parse(inputStream);
Element metadataRoot = document.getDocumentElement();

QName qName= new QName(metadataRoot.getNamespaceURI(), metadataRoot.getLocalName(), metadataRoot.getPrefix());

//get an unmarshaller
Unmarshaller unmarshaller = Configuration.getUnmarshallerFactory().getUnmarshaller(qName);

//unmarshall using the document root element
Response response = (Response)unmarshaller.unmarshall(metadataRoot);

//we have the xml unmarshalled to a response object
System.out.println("Response object created");
System.out.println("Issue Instant: " + response.getIssueInstant().toString());
System.out.println("Signature Reference ID: " + response.getSignatureReferenceID().toString());

//grab the certificate file
File certificateFile = new File("C:\\Documents and Settings\\kgellis\\My Documents\\Eclipse\\Workspace\\MyOpenSamlTester\\Files\\Con-wayPublicKey.cer");

//get the certificate from the file
InputStream inputStream2 = new FileInputStream(certificateFile);
CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
X509Certificate certificate = (X509Certificate)certificateFactory.generateCertificate(inputStream2);
inputStream2.close();

//pull out the public key part of the certificate into a KeySpec
X509EncodedKeySpec publicKeySpec = new X509EncodedKeySpec(certificate.getPublicKey().getEncoded());

//get KeyFactory object that creates key objects, specifying RSA
KeyFactory keyFactory = KeyFactory.getInstance("RSA");
System.out.println("Security Provider: " + keyFactory.getProvider().toString());

//generate public key to validate signatures
PublicKey publicKey = keyFactory.generatePublic(publicKeySpec);

//we have the public key
System.out.println("Public Key created");

//create credentials
BasicX509Credential publicCredential = new BasicX509Credential();

//add public key value
publicCredential.setPublicKey(publicKey);

//create SignatureValidator
SignatureValidator signatureValidator = new SignatureValidator(publicCredential);

//get the signature to validate from the response object
Signature signature = response.getSignature();

//try to validate
try
{
signatureValidator.validate(signature);
}
catch (ValidationException ve)
{
System.out.println("Signature is NOT valid.");
System.out.println(ve.getMessage());
return;
}

//no validation exception was thrown
System.out.println("Signature is valid.");

//start decryption of assertion

//grab the KeyStore file
File keyStoreFile = new File("C:\\Documents and Settings\\kgellis\\My Documents\\Eclipse\\Workspace\\MyOpenSamlTester\\Files\\WWCPrivateKey.jks");

KeyStore keyStore = KeyStore.getInstance("JKS");

//load up a KeyStore
keyStore.load(new FileInputStream(keyStoreFile), "!c3c0ld".toCharArray());

RSAPrivateKey privateKey = (RSAPrivateKey) keyStore.getKey("pvktmp:bd5ba0e0-9718-48ea-b6e6-32cd9c852d76", "!c3c0ld".toCharArray());

//we have the private key
System.out.println("Private Key created");
System.out.println("Private Key Algorithm: " + privateKey.getAlgorithm());

//create the credential
BasicX509Credential decryptionCredential = new BasicX509Credential();
decryptionCredential.setPrivateKey(privateKey);

StaticKeyInfoCredentialResolver skicr = new StaticKeyInfoCredentialResolver(decryptionCredential);

//create a decrypter
Decrypter decrypter = new Decrypter(null, skicr, new InlineEncryptedKeyResolver());

//decrypt the first (and only) assertion
Assertion decryptedAssertion;

try
{
decryptedAssertion = decrypter.decrypt(response.getEncryptedAssertions().get(0));
}
catch (DecryptionException de)
{
System.out.println("Assertion decryption failed.");
System.out.println(de.getMessage());
return;
}

System.out.println("Assertion decryption succeeded.");
System.out.println("Assertion ID: " + decryptedAssertion.getID());

//loop through the nodes to get what we want
List<AttributeStatement> attributeStatements = decryptedAssertion.getAttributeStatements();
for (int i = 0; i < attributeStatements.size(); i++)
{
List<Attribute> attributes = attributeStatements.get(i).getAttributes();
for (int x = 0; x < attributes.size(); x++)
{
String strAttributeName = attributes.get(x).getDOM().getAttribute("Name");

List<XMLObject> attributeValues = attributes.get(x).getAttributeValues();
for (int y = 0; y < attributeValues.size(); y++)
{
String strAttributeValue = attributeValues.get(y).getDOM().getTextContent();
System.out.println(strAttributeName + ": " + strAttributeValue);
}
}
}
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}

Friday, May 22, 2009

Passing a variable to Openquery in SQL Server 2005

You'd think that passing a variable to Openquery in SQL Server 2005 would be easy. It isn't. The parser won't parse a string like you think it would. Instead you need to do something like this (and those are all single-quotes):

DECLARE @TSQL varchar(8000), @VAR char(50)
SELECT @VAR = 'XXXXX'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(ConnectionName,''SELECT SomeField FROM SomeTable WHERE AnotherField = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

Ugly, but it works.

Thursday, May 14, 2009

Creating a free public/private key pair in Windows

I recently had to test something with public and private keys. You don't want to have to pay for certificates for testing. Free is better. These commands came in very handy so I'm documenting them here so I don't forget...

Go to this directory (or the equivalent depending on your VS install):
C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\

makecert -r -pe -n "cn=kevnls" -b 01/01/2008 -e 01/01/2020 -sky exchange -sv kevnls.pvk kevnls.cer

pvk2pfx -pvk kevnls.pvk -spc kevnls.cer -pfx kevnls.pfx -po somepassword

Edit: If you need a java keystore (.jks) instead of a .pvk file you can do this using the keytool in the jre (usually in C:\Program Files\Java\jre6\bin\).


./keytool -genkey -keyalg RSA -alias somealias -keystore kevnls.jks -storepass somepassword -validity 7300 -keysize 2048

./keytool -export -keystore keystore.jks -alias somealias -file kevnls.cer

Monday, March 30, 2009

Combining PDFs with Ruby in OS X

I recently had to write a fairly complex script using Ruby to match-up and combine PDF documents. I thought I'd share the command I used to do the PDF work as I matched up the files. It uses an automator action that comes default in OS X Leopard.

Here's the relevant code snippet:

filepath1 = folder1 + "/" + filename1
filepath2 = folder2 + "/" + filename2
outfilepath = folder3 + "/" + filename3

command = "python '/System/Library/Automator/Combine PDF Pages.action/Contents/Resources/join.py' -o '" + outfilepath + "' '" + filepath2 + "' '" + filepath1 + "'"

`#{ command }`

I think you can see how it works with that much code. It currently is complaining about a deprecated call somewhere in the automator action, but it doesn't affect the outcome, and it will hopefully go away in a future release.