Author Archives: Greg Gaughan

The Third Manifesto Implementers’ Workshop

Earlier this month I went to the Third Manifesto Implementers’ Workshop at Northumbria University in Newcastle. A group of us discussed recent developments in implementing the relational data model.

The relational data model was proposed by E. F. Codd in 1969 in response to the complex, hierarchical, data storage solutions of the time which required programs to be written and compiled for each database query. It was a powerful abstraction, but unfortunately SQL and its implementations missed out on important features, and broke it in fundamental ways. In response to this problem, and the industry’s approach towards object-databases, Chris Date and Hugh Darwen wrote “The Third Manifesto” (TTM) to put forward their ideas on how future database systems should work. I urge you to read their books (even if you’re not interested in the subject) – the language is amazing: precise, concise, comprehensive and easy to read – other technical authors don’t come close.

The relational model treats data as sets of logical propositions and allows them to be queried, manipulated and constrained declaratively. It abstracts away from physical storage and access issues which is why it will still be used a hundred years from now (and why NoSQL discussions like these http://wiki.apache.org/couchdb/EntityRelationship http://www.cmlenz.net/archives/2007/10/couchdb-joins are retrograde). If you’re writing loops to query your data, or having to navigate prescribed connection paths, then your abstractions are feeble and limited.

At the workshop, I talked about my project, Dee, which implements the relational ideas from TTM in Python. You can see my slides here (or here if you have an older browser).

Erwin Smout gave a couple of talks about implementing transition constraints and dispensing with data definition language.

David Livingstone walked us through the RAQUEL architecture – a layered approach along the lines of the OSI network model.

Hugh Darwen discussed the features and implementation of IBM’s Business System 12, one of the first ever relational database systems which had some surprisingly dynamic features, including key inferencing, so that view definitions could keep tabs on their underlying constraints.

Chris Date took us through his latest thoughts on how to update relational views in a generic way. The aim is for database users to be able to treat views and base tables in the same way, for both reading and writing. Lots to think about here, and my to-do list for Dee has grown another section.

Adrian Hudnott discussed a couple of research projects around optimising multiple relational assignments and tracking the source of updates so that transition constraints could be more effective.

Renaud de Landtsheer gave an insight into the work he’s been doing implementing first-order-logic constraints within Oracle databases.

I sat next to Toon Koppelaars (whose name went down well with the Geordies) and then I realised I had his book (Applied Mathematics for Database Professionals) waiting to be read on my desk at work, thanks to the eclectic Isotoma library (and Wes).

It was a packed couple of days with plenty of food for thought. Thank you to David Livingstone and Safwat Mansi for organising and hosting such an enjoyable and interesting event.

Tamper-protection for Bank Transactions

If you need to send electronic transactions to Swedish banks, you’ll be required to add anti-tampering seals to the files. The banks recommend you use a third-party system to create the HMAC SHA256-128 seals, but that could involve a fair amount of expensive server software and maintenance contracts (some linked to the number of people who work in your company).

Instead, you can do it yourself in Python like this:

import hmac
import hashlib
import string

NORMALISE = string.maketrans(
    '\xC9\xC4\xD6\xC5\xDC\xE9\xE4\xF6\xE5\xFC' + ''.join(
    [chr(x) for x in range(0,32)]) + ''.join(
    [chr(x) for x in range(127,256)
     if x not in (201,196,214,197,220,233,
                  228,246,229,252)]),
    '\x40\x5B\x5C\x5D\x5E\x60\x7B\x7C\x7D\x7E' + ''.join(
    [chr(195) for x in range(0,32)]) + ''.join(
    [chr(195) for x in range(127,256)
     if x not in (201,196,214,197,220,233,
                  228,246,229,252)]))

def hex_to_bytes(hexs):
    """Convert string of hex into bytes"""
    return ''.join(['%s' % chr(int(hexs[i:i+2], 16))
                    for i in range(0, len(hexs), 2)])

def get_signature(contents, key):
    """Calculate the HMAC SHA256-128 signature

       contents - an iso-8859-1 (latin-1) encoded string
       key - a string of hex characters

       Returns a 32 char string of hex characters (128 bits)
    """
    key = hex_to_bytes(key)

    #Normalise the contents
    contents = contents.translate(NORMALISE, '\r\n')

    dig = hmac.new(key, msg=contents,
                   digestmod=hashlib.sha256).digest()
    return ''.join(['%02X' % ord(x) for x in dig[:16]])

And then to calculate the signature for a file:

>>> print get_signature(open('bankfile.dat').read(),
...                     '1234567890abcdef1234567890abcdef')
25122AE4179BD51DC87AD6EA08D16D45

Black Box BlackBerry

Debugging software is best done using the scientific method: gather evidence about the effects of the bug, conjure up hypotheses to explain the behaviour, experiment to test the hypotheses and modify the code to change the behaviour. Rinse and repeat. If you can’t consistently reproduce the bug though, it can get tricky.

Recently, while developing a site targeted at mobile devices, we came across an intermittent problem when using a BlackBerry device. Testing mobile sites with desktop browsers and emulators can only take you so far. Eventually you reach the point where real devices begin to exhibit their own peccadillos and so we use DeviceAnywhere to access a whole host of remote-controlled physical devices.

Using the BlackBerry Curve, occasionally, our login page wouldn’t proceed to the home page after successful authentication. But we could never reproduce the this in our development environments, only on live; sometimes.
One major difference between the two environments was that the live one had dozens of servers behind a load-balancer which used a URL parameter for session affinity (we couldn’t assume all mobile devices would support cookies), whereas the development environment was a single server. We also had a staging environment which closely reproduced the live environment, although there were only a couple of servers behind its load-balancer. Initial tests on the staging environment indicated that the problem didn’t appear there either.

To rule out the mobile network provider, we installed the excellent Opera Mini browser on the BlackBerry and it worked every time. This also ruled out any issues with pages being cached by Akamai, the content delivery network. So we were now looking for a problem with our code interacting with the BlackBerry browser, but only behind our live load-balancer; sometimes.

After painstakingly tracing through the live Apache logs we closed in on the unexpected cause: a bug in the BlackBerry browser. When a server tells a browser to redirect it sends the full URL, including in our case the all-important session parameter. This URL was being tampered with before the browser navigated to it. The parameter name was being converted to lower-case (if it wasn’t preceded by a slash). This meant that the load-balancer didn’t use it for server affinity so the home page server probably didn’t have a logged-in session, and so it would bounce back to the login page.

The reason this problem had been so hard to reproduce was that in development there was only one server so affinity wasn’t an issue and the server software didn’t care about the case of the session parameter. Also the site URL was different and so the session parameter always had a preceding slash which didn’t trigger the BlackBerry URL tampering, so it never appeared as lower-case in the development logs. And on the staging environment, because there were only two servers, the device would hit the same server, notwithstanding any affinity failure caused by the lower-casing, half of the time by chance alone. The live environment was more likely to fail, but even it gave a sizeable probability of hitting the same server successively by chance alone.

We built a test server and, using some black box reverse-engineering (because the BlackBerry browser is closed-source), we reckon the logic inside the browser’s redirect code goes something like this: “lower-case all the characters in the location URL up to the first slash” presumably with the intention of making the DNS name lower-case. But it should be: “… up to the first slash or ?” to preserve the case of any query parameters.

Googling for this issue returns a number of other sites having redirect and login issues with BlackBerrys. I wonder how many are caused by this subtle, case-sensitive bug?

We’ve since searched our logs and found the bug across this wide range of BlackBerry devices/versions:BlackBerry8100/4.2.0

  • BlackBerry8100/4.5.0.52
  • BlackBerry8110/4.3.0
  • BlackBerry8120/4.5.0.52
  • BlackBerry8310/4.2.2
  • BlackBerry8700/4.2.1
  • BlackBerry8800/4.2.1
  • BlackBerry8820/4.2.2
  • BlackBerry8830/4.2.2
  • BlackBerry8900/4.6.1.101
  • BlackBerry8900/4.6.1.109
  • BlackBerry9000/4.6.0.125
  • BlackBerry9000/4.6.0.221

We’ve logged it with BlackBerry. I’ll post an update if we receive any response.

Good Spam

Following on from this bright idea to use a spamming tool to create a blog from an Open University course feed, the OU asked us to build a plugin for WordPress MU that will automatically create a bunch of blogs from Open University feeds. We also built a WordPress widget, to implement another bright idea, that can then deliver the blog entries for a course in periodic chunks.

So you can consume the course in your feed-reader at a pace that suits. This should be released soon, meanwhile, the testing is very educational.

Every day this week I’ve been mostly learning about James Clerk Maxwell.