Archive for January, 2009

Stupid Excel Trick – amuse your friends & bore your enemies

What’s 1-2?

Today, MS Excel tells me it’s +39815. (Tomorrow it will tell me something different.)

Some while back a signup sheet went around the office for our annual lamb roast. Since every problem (nail) in an office environment has a preinstalled office-suite hammer, the signup list was an Excel spreadsheet. Once everyone has entered their number of guests, it totals up how many total guests. Here’s what I sent around before figuring out Excel’s flavor of crack.

Say you don’t know exactly how many guests you will have for the BBQ. Type a range (e.g. “1-2”) in and see what Excel computes for the total numbers of guests/folks.

For “1-2” guests by my name I get exactly 39084 total guests. Fixed by just saying “1”, but now I’m curious. How is it computing this number?

I can see that being parsed as “one minus two” and yielding a negative number of guests, but I can’t figure out what particular flavor of crack Excel is smoking to get 39,084. Anyone?

I mean, if it had returned NaN or +32767 (or some other signed-unsigned integer conversion failure), I might have understood.

An Excel guru eventually figured it out: The field was hard-set as a numeric field (no auto). Still, it parsed the expression, not as “one minus two” but as a date – “February 1, current year”, and since it was a numeric field, represented this numerically the best way it knew how: the number of days since January 1st, 1900.

The flavor is strawberry.

Cypherpunk’s Wet Dream meta-entry

I once said that this blog would eventually reach a point where any possible entry could be expressed as a sum of references to previous entries. In this case, it’s this one, this one and this one.

I have maintained that a point will be reached where plain old ordinary Web sites will be forced to turn on SSL encryption by default, or otherwise resort to client-side validation to ensure the page content hasn’t been tampered with during transit. Not because they are running online shops or otherwise dealing with sensitive information – to ensure their users view the original site as it was meant to be seen, protect their users against malware injected by man-in-the-middle attacks, protect/ensure their ad sales, and protect themselves from liability (lost sales from angry customers, frivolous ADA/etc. lawsuits, computer repair bills) arising from unauthorized third-party “enhancements” to their site. And I figured the detonator for all of this (besides Comcast’s broken BitTorrent filter) would be local-yokel small-town ISPs, where bored and too-clever midnight admins sit, Perl Cookbook in hand, trying to make a few bucks on the side by replacing random Web sites’ ads with their own, or injecting other forms of malware into customer HTTP streams to gather saleable profiling data.

Nope. It’s the big boys. Among them: Charter Communications, one of the world’s largest ISPs, and British Telecom have secretly tested, or intend to test (respectively) technologies against their paying customers which do exactly that. According to an internal British Telecom memo (fulltext PDF via Wikileaks), the company partnered with online marketing company Phorm, which specializes in consumer profiling and delivery of targeted advertising. According to Wired,

“From late September to early October 2006, British Telecom secretly partnered with Phorm to let the company monitor and track 18,000 of the BT’s customers. Phorm installed boxes on BT’s network that redirected web requests through their proxy server.

Those boxes inserted JavaScript code into every web page downloaded by the users. That script then reported back to Phorm the contents of the web page, which Phorm used to create ad profiles of a user.”

The report goes on to detail the ability of the Phorm proxy box to intercept requested pages and replace the site’s advertising with its own, based on the collected profile for that customer. The report also indicates several deleterious side-effects of this injection, such as flickering problems on some Web pages (which led users to believe their PCs were infected with spyware), frequent browser crashes, and insertion of the rogue code when users tried to post to Web forums. However, they concluded that the test was “successful” since no user was able to successfully pin the blame on BT/Phorm:

“The operation of the system does have noticeable side effects, which included web-page tag insertion and navigation bar flutter.

From the postings, no user correctly determined the source of these effects and users did not post that the system was causing poor performance.

However all postings suspected that their machines had a virus, a malware or a spyware infection.”

*sigh* Remember kids, you (probably) heard it here first. Let’s hope that in the brave new world of encrypt-everything-to-avoid-getting-fucked-by-ISPs, Firefox 4 doesn’t continue to perform that tired 5-warning song and dance every time you visit a non-corporate Web site.

Firefox 3, SSL and self-signed certificates

First off, for those who know what I’m talking about and are just as pissed…the fix! (sorta)
Open about:config and set the follwing settings:
browser.xul.error_pages.expert_bad_cert: true
browser.ssl_override_behavior: 2

This brings you down from five clicks to “only” two. :-/

So, a while back I got sick of the nag dialogs, caved and updated to Firefox 3. It works pretty much just as well as FF2, but one thing has been bugging me: Encrypted sites are second-class citizens. In previous FF versions (and pretty much any browser known to mankind), if you visited a site which had SSL encryption turned on but no “respectable” certificate, you would get a simple warning dialog about it, with the option to stop or continue (one click). Now however, this one-click process has been replaced by an extremely cumbersome process of navigating through several warnings, examining the certificate, and adding a special exception to the browser settings for that certificate (permanently). Now, viewing any of the Web’s increasingly “encrypted for the heck of it” pages is a giant pain in the ass.

By “respectable” certificate, I mean something that has been purchased from a certification company such as Verisign with an annual maintenance fee (currently about $400/yr), after said company has (claimed to have) performed some background checks on you to ensure that you are really who you say you are. The certificate then vouches, to any random onlooker, that your site is actually operated by you. This makes great sense if you are a bank, of course. You want your customers to know you’re really the bank, not some guy who bought a bank-like domain. For everyone who isn’t a bank, though, the main purpose of SSL is to encrypt data between the server and the user’s PC, preventing any random monkey-in-the-middle (bored local-yokel ISP admins, airport Wifi no-goodniks) from viewing or tampering with the data flowing between them. Since random hobbyists and bloggers can’t or won’t (and, ahem, shouldn’t) pay $400 a year for a CA certificate just to give away free content, this has historically been accomplished for free by a self-signed certificate, i.e. a certificate generated by the webmaster himself. Obviously, while it performs the encryption task just as well, it cannot vouch for the identify the webmaster – but if Joe’s Blog is just trying to keep some braindead ISP / censorware from “re-expressing” the site, is Joe’s SSL-enhanced blog less secure than Joe’s plain unencrypted HTTP (which does not generate alarm bells from Firefox) blog?. Duh, of course not. So why treat it as though it is?

Some more correct solutions would be:

  • Present the Dire Warning Dialog matryoshka exactly once. At the third click is the “I know what I’m doing” checkbox for advanced users which reverts behavior to that of to FF2 and most every other browser in existence (i.e. single-click dismissal for users who understand the difference between encryption and authentication). This approach worked surprisingly well on my LSP-Fix utility, which allows advanced (potentially destructive) operations with an “I know what I’m doing” checkbox and appropriate warnings about how much fun it is to reinstall one’s OS.
  • Same as #1, but with a very brief SSL test the user must pass to enable the checkbox, to prove they really understand the difference between encryption and authentication – or for that matter, between either of those and security.
  • Keep the 1-click dismissal from the start, but fix the wording for novices: WARNING: This site is no more secure than Joe Blow’s random blog. Do not submit your credit-card number or anything else you wouldn’t put on the frontpage of the New York Times.
  • Display a bright red titlebar / address bar and the familiar “broken padlock” symbol for sites with unverifiable certificates. A Bright Red Something works very well as a persistent visual reminder, hours after the dialog-clicking has faded from memory (I use this approach on any GUI logged in as administrator/root, reminding me that I have the power to really screw myself.)
  • Release the SSL behavior fix for FF3 as an extension that must be manually installed, for clueful users who want to surf Comcast-proofed sites in peace Looks like a Moz developer’s already done it, though compatibility with different FF3 versions sounds a bit hit-and-miss.

A Mozilla developer’s (non-SSL-encrypted) blog on the subject explains some of the logic behind this UX nightmare, including the (semi-sensible, I must admit) rationale behind making exceptions permanent by defaut. (This, too, should have an advanced-user override switch – I for one don’t want to permanently accept bad certs as good.) Be glad for what you got though, apparently they had initially decided not to allow self-signed pages to be accessed AT ALL.

(Yeah, apparently I’m not the first person to take offense at this behavior, though my beef is not so much in the wording but the sheer ten-click time-wastage and the implication that some encrypted sites are somehow less secure than plaintext ones.)