GigaMegaBlog

One of the downsides of working with lesser known databases like IBM’s U2 is that you generally can’t solve a problem just by Googling it.

Something came up today at work that at first seemed like a tough nut to crack. A programmer was trying to port a custom decryption algorithm from U2’s native language, Pick Basic, to C#. The algorithm wasn’t resulting in a correctly decrypted string. The problem seemed to be that the Pick Basic function for returning the ASCII code for a character, SEQ(), was often returning different values than .Net’s equivalent (I thought) method, Encoding.ASCII.GetBytes(). This had never been a problem for us before, since U2 and .Net agree on the ASCII code for all the characters on the keyboard. But “unprintable” characters that show up in an encrypted string, like a right single quote (’ – ASCII code 146), were being interpreted differently.

My first thought was that the string being decrypted wasn’t really a series of characters, just a series of bits, so what did it matter if U2 used a different code page? We’d just read the U2 data as binary data and feed that into the decryption algorithm.

The documentation for the API we use to read the database, UniObjects.Net, helpfully explains the advantages of retrieving certain types of data as binary sequences rather than strings, then mentions in passing that database files have to be “Type 1 or Type 19” in order for this to work. Ours wasn’t, so we were stuck with reading the data into strings.

My second thought was that there must be some way for C# code to invoke this mysterious SEQ() function directly. Why recreate the function when we could just invoke the original? The UniObjects.Net help file explains how to invoke various Pick Basic functions, but SEQ() wasn’t among them. UniObjects.Net provides 2 other ways of invoking database commands: Call() and Exec(). We use the former to invoke our custom Pick Basic subroutines, and the latter to run the U2 equivalent of SELECT commands, but I couldn’t figure out how to persuade either of these to just invoke the SEQ() function.

Well, surely this problem had already been solved by greater Windows programming minds than my own? But Googling “U2 SEQ Windows” just resulted in the inevitable bizarrely worded references to the band, a few pages about the database that happened to contain the other search terms (including an obscure blog named GigaMegaBlog), but nothing that suggested that any Windows programmer had ever called U2’s SEQ() function before.

So it appeared that the only way to solve this problem was to unlock the mysteries of the SEQ(). If it was translating characters into different values than our Windows app, it had to to be a code page problem. What code page was it using? Our U2 installs tend to be plain vanilla, so I’d be surprised if we weren’t using the default code page for U2. Which was…?

Googling U2 and “code page” didn’t return many hits either, but browsing through U2’s documentation uncovered the fact that, in NLS mode, the CHAR function (the inverse of SEQ()) returns values from the ISO-8859-1 character set. That rang a bell — ISO-8859-1 is a common character set in the Windows world too. Although our encrypted field was clearly never intended to be a Unicode string, this was the closest I could find to an indication of what character set SEQ() might be working with. So, I changedEncoding.ASCII.GetBytes() to Encoding.GetEncoding(“iso-8859-1″).GetBytes() and tried running the encrypted text through the algorithm again. No joy. The results were different than when I used ASCII, though. And, hold on a sec, when I was using ASCII a lot of characters were being translated to the same integer: 63. What the….?

Just then the programmer dropped by to show me that he’d found a web page that listed a character map that seemed to match what SEQ() was returning. The obscure, elusive character set was, in fact… ANSI.

ANSI? As in Windows ANSI? If it was just Windows ANSI, then entering Alt-0146 on the keypad would display a ’. Which it does. And changing the code to Encoding.Ansi.GetBytes() would fix the problem. Which it did. Duh.

So, a one word fix took an hour worth of detective work to uncover. All because nowhere on the great wide Internet was there a page that described how to write the U2 SEQ() function in .Net. Until now.

As a rule, I have a preference for open source software. Being a developer, I approach most software the same way that a mechanic or engineer approaches a car: not just an easy way of getting from point A to point B, but also something to study, tinker with, and learn from.

So, when I first realized that SSH was the most secure and flexible way of getting at files on my home PC from the Internet, I went with OpenSSH. It’s a wonderful piece of open source software: well written, well documented (including the kind of in-depth documentation to feed your inner geek), preinstalled on most Linux distributions and readily available for Windows. After investing considerable time learning about public key encryption, port forwarding and the myriad configuration options for an SSH server, I was finally up and running. And it was very reliable: 99% of the time I’d fire up open an SSH shell or fire up VNC and my home PC answered.

But 99% isn’t quite good enough, because there are times when I really, really need to get at a file on my home PC. Trying to figure out what the problem was the other 1% of the time was pretty frustrating and generally futile, since I usually needed to get at my home PC to see what was wrong. If I couldn’t absolutely, positively depend on being able to access my home PC when I needed to, then I had to make sure that I copied the important stuff to my notebook or work PC, or carried it around with me in my briefcase, which pretty much defeated the purpose.

About a year ago I took the plunge and switched my Internet-facing home PC to Windows Vista. The big downside to Vista, especially back then, was software compatibility, and one of the things that hadn’t been fully ported to Vista yet was Cygwin and its version of OpenSSH. (It has now). So, I decided to try out one of the commercial implementations of SSH, WinSSHD by Bitvise. Though not open source, Bitvise’s product was reasonably priced for personal use ($40), well documented, and well supported. They also offered a free SSH client, Tunnelier, which I didn’t think I needed until I tried it.

A year later, I’m still running WinSSHD on my Vista box and I’m not even going to consider switching away from it. It’s easy to install and configure. Tunnelier makes things like public key authentication and port tunneling — somewhat of a geeky adventure with OpenSSH — so straightforward that non-geeks could easily get it working. (And they should — the fact that even Internet-savvy people generally allow their home PCs to be accessed over the Internet with just password protection is troubling.) Most importantly, WinSSHD has been 100% reliable. In fact, when playing around with one of my other home PCs and Vista is being a pri*ck about what network connections to allow, the one thing that always works is my SSH connection.

I still love playing around with open source software, but OpenSSH is no longer one of my toys. When I simply have to get from point A to point B, it’s nice to have something dead simple that just works.