Apr 112015

To whom it may concern,

There have been reports of web browser sessions from people outside China to websites inside China being hijacked and having malware injected.  Dubbed “Great Cannon”, this malware having the sole purpose of carrying out distributed denial of service attacks on websites that the Chinese Government attempts to censor from its people.  Whether it be the Government there itself doing this deliberately, or someone hijacking major routing equipment is fundamentally irrelevant here, either way the owner of the said equipment needs to be found, and a stop put to this malware.

I can understand you wish to prevent people within your borders from accessing certain websites, but let me make one thing abundantly clear.


I will not accept my web browser which is OUTSIDE China being hijacked and used as a mule for carrying out your attacks.  It is illegal for me to carry out these attacks, and I do not authorise the use of my hardware or Internet connection for this purpose.  If this persists, I will be blocking any and all Chinese-owned websites’ executable code in my browser.

This will hurt Chinese business more than it hurts me.  If you want to ruin yourselves economically, go ahead, it’ll be like old times before the Opium Wars.

Apr 102015

This afternoon, whilst waiting for a build job to complete I thought I’d do some further analysis on my annual mileage.

Now I don’t record my odometer readings daily (perhaps I should), but I do capture them every Sunday morning.  So I can possibly assume that the distance done for each day of a “run” is the total distance divided by the number of days.  I’m using a SQLite3 database to track this, question is, how do I extract this information?

This turned out to be the key to the answer.  I needed to enumerate all the days between two points.  SQLite3 has a julianday function, and with that I have been able to extract the information I need.

My database schema is simple. There are two tables:
CREATE TABLE bikes (id integer primary key not null, description varchar(64));
CREATE TABLE odometer (timestamp datetime not null default current_timestamp, action char(8) not null, bike_id integer not null, odometer real not null, constraint duplicate_log unique (timestamp, action, bike_id) on conflict replace);

Then there are the views.
CREATE VIEW run_id as select s.rowid as start_id, (select rowid from odometer where bike_id=s.bike_id and timestamp > s.timestamp and action='stop' order by timestamp asc limit 1) as stop_id from odometer as s where s.action='start';
CREATE VIEW "run" AS select start.timestamp as start_timestamp, stop.timestamp as stop_timestamp, start.bike_id as bike_id, start.odometer as start_odometer, stop.odometer as stop_odometer, stop.odometer-start.odometer as distance,julianday(start.timestamp) as start_day, julianday(stop.timestamp) as stop_day from (run_id join odometer as start on run_id.start_id=start.rowid) join odometer as stop on run_id.stop_id=stop.rowid;

The first view breaks up the start and stop events, and gives me row IDs for where each “run” starts and stops. I then use that in my run view to calculate distances and timestamps.

Here’s where the real voodoo lies, to enumerate days, I start at the very first timestamp in my dataset, find the Julian Day for that, then keep adding one day on until I get to the last timestamp. That gives me a list of Julian days that I can marry up to the data in the run view.

CREATE VIEW distance_by_day as
SELECT day_of_year, avg_distance FROM (
SELECT days.day - julianday(date(days.day,'start of year')) as day_of_year, sum(run.distance/max((run.stop_day-run.start_day),1))/count(*) as avg_distance
FROM run,
days(day) as (
SELECT julianday((select min(timestamp) from odometer))
union all
SELECT day+1 from days
limit cast(round(julianday((select max(timestamp) from odometer))-julianday((select min(timestamp) from odometer))) as int)
) SELECT day from days) as days
run.start_day < = days.day AND run.stop_day >= days.day
group by day_of_year) dist_by_doy;

This is the result.

Distance by Day Of Year

Distance by Day Of Year

Apr 062015

I’ve been a long time user of PGP, had a keypair since about 2003.  OpenPGP has some nice advantages in that it’s a more social arrangement in that verification is done by physically meeting people.  I think it is more personal that way.

However, you still can get isolated islands, my old key was a branch of the strong set, having been signed by one person who did do a lot of key-signing, but sadly thanks to Heartbleed, I couldn’t trust it anymore.  So I’ve had to start anew.

The alternate way to ensure communications is to use some third party like a certificate authority and use S/MIME.  This is the other side of the coin, where a company verifies who you are.  The company is then entrusted to do their job properly.  If you trust the company’s certificate in your web browser or email client, you implicitly trust every non-revoked valid certificate that company has signed.  As such, there is a proliferation of companies that act as a CA, and a typical web browser will come with a list as long as your arm/leg/whatever.

I’ve just set up one such certificate for myself, using StartCOM‘s CA as the authority.  If you trust StartCOM, and want my GPG key, you’ll find a S/MIME signed email with my key here.  If you instead trust my GPG signature and want my S/MIME public key, you can get that here.  If you want to throw caution to the wind, you can get the bare GPG key or S/MIME public key instead.

Update: I noticed GnuPG 2.1 has been released, so I now have an ECDSA key; fingerprint B8AA 34BA 25C7 9416 8FAE  F315 A024 04BC 5865 0CF9.  You may use it or my existing RSA key if your software doesn’t support ECDSA.