ter Smitten's

A few weeks ago I was upgrading / migrating / reinstalling my old server. This was a nice opportunity to finally play around with Let’s Encrypt, so I could setup https for all my sites. After reading the documentation it looked like I needed to install all sorts of dependencies on my machine. That was something a wasn’t to happy about. Digging some further revealed that it was also possible to use Docker to run all the commands, so I decided to do that:

sudo docker run -it --rm --name letsencrypt \
  -v $PWD/files/letsencrypt/etc/letsencrypt:/etc/letsencrypt \
  -v $PWD/files/letsencrypt/var/lib/letsencrypt:/var/lib/letsencrypt \
  quay.io/letsencrypt/letsencrypt:latest \
  certonly \
  --manual \
  --email mischa@tersmitten.nl \
  --agree-tos \
  -d blog.tersmitten.nl \
;

This way I could run all the commands, without installing the dependencies (on any machine). Also I was able to pick any location to store the letsencrypt files.

Sometimes you just want to send an email with a (binary) attachment, this is how you do that:

apt-get install sharutils

uuencode < input-attachment.bin output-attachment.bin | mail foo@bar

References:

• How To Send an Email With Attachment and Body from Linux

To change the owner trust value of a given public (GPG) key you would normally use the gpg --edit-key 8A581CE7. This presents us a menu which enables you to do all key related tasks:

root@ubuntu-1404:~# gpg --edit-key 8A581CE7
gpg (GnuPG) 1.4.16; Copyright (C) 2013 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Secret key is available.

pub  4096R/8A581CE7  created: 2015-04-30  expires: never       usage: SCEA
                     trust: ultimate      validity: ultimate
sub  4096R/968AB157  created: 2015-04-30  expires: never       usage: SEA 
[ultimate] (1). Duplicity Backup <root@foo.bar>

gpg> trust
pub  4096R/8A581CE7  created: 2015-04-30  expires: never       usage: SCEA
                     trust: ultimate      validity: ultimate
sub  4096R/968AB157  created: 2015-04-30  expires: never       usage: SEA 
[ultimate] (1). Duplicity Backup <root@foo.bar>

Please decide how far you trust this user to correctly verify other users' keys
(by looking at passports, checking fingerprints from different sources, etc.)

  1 = I don't know or won't say
  2 = I do NOT trust
  3 = I trust marginally
  4 = I trust fully
  5 = I trust ultimately
  m = back to the main menu

Your decision? 5
Do you really want to set this key to ultimate trust? (y/N) y

pub  4096R/8A581CE7  created: 2015-04-30  expires: never       usage: SCEA
                     trust: ultimate      validity: ultimate
sub  4096R/968AB157  created: 2015-04-30  expires: never       usage: SEA 
[ultimate] (1). Duplicity Backup <root@foo.bar>

But that requires interaction. What if we would need to do it from a shell script for instance?

Method 1, shell magic

The output of gpg --export-ownertrust looks like this:

# List of assigned trustvalues, created Thu 30 Apr 2015 08:56:18 PM UTC
# (Use "gpg --import-ownertrust" to restore them)
A38FBA1F60F422597F6441D5E1C4C3898A581CE7:6:

So let’s see how we can recreate that without using gpg --export-ownertrust:

Get the fingerprint of key 8A581CE7:

gpg --list-keys --fingerprint | grep 8A581CE7 -A 1 | tail -1

Remove all the spaces and get the right part:

tr -d '[:space:]' | awk 'BEGIN { FS = "=" } ; { print $2 }'

final version, feeded to gpg --import-ownertrust:

echo "$( \
  gpg --list-keys --fingerprint \
  | grep 8A581CE7 -A 1 | tail -1 \
  | tr -d '[:space:]' | awk 'BEGIN { FS = "=" } ; { print $2 }' \
):6:" | gpg --import-ownertrust;

Method 2, expect

Expect is a program that “talks” to other interactive programs according to a script. Following the script, Expect knows what can be expected from a program and what the correct response should be.

Install expect:

apt-get install expect

Create an expect script:

root@ubuntu-1404:~# cat set-trust.exp 
#!/usr/bin/expect

set timeout 10

spawn /usr/bin/gpg --edit-key $argv 0 --yes trust quit

expect "Your decision? " { send "5\r" }
expect "Do you really want to set this key to ultimate trust? (y/N) " { send "y\r" }

interact

Run the expect script (with argument):

chmod 0755 ./set-trust.exp

./set-trust.exp 8A581CE7

Due to some nasty bugs in PHP 5.3.2 we needed to update our Ubuntu 10.04 machines to 5.3.10 (still nasty, but better). This is how we accomplished that:

Remove the current APC version:

sudo pecl uninstall apc;

Add the brianmercer/php5 PPA repository:

sudo apt-get install python-software-properties;

sudo apt-add-repository ppa:brianmercer/php5;
sudo apt-get update;
sudo apt-get upgrade;

Reinstall APC:

sudo pecl install apc;

Although I should be ashame of running such an old version of Ubuntu, sometimes you’ll just have to deal with it. This particular machine needed a 3.x kernel for improved EXT4 filesystem performance.

It seems that in Ubuntu 10.04 there’re a couple of back-ported kernels available:

# apt-cache search linux-image-server-lts
linux-image-server-lts-backport-maverick - Linux kernel image on Server Equipment.
linux-image-server-lts-backport-natty - Linux kernel image on Server Equipment.
linux-image-server-lts-backport-oneiric - Linux kernel image on Server Equipment.

Let’s pick the latest one (11.10, Oneiric Ocelot) install that:

sudo apt-get install ubuntu-backport-oneiric linux-headers-server-lts-backport-oneiric;

After a reboot we should be upgraded:

# uname -a
Linux server.oefenweb.nl 3.0.0-32-server #51~lucid1-Ubuntu SMP Fri Mar 22 17:53:04 UTC 2013 x86_64 GNU/Linux

Let’s say we want to list a specific range of commits in SVN. The snipped below would do the trick:

svn log -r 1342:1402;

It also seems the we can go even further by specifying a date range instead of revision numbers (not tested):

svn log -r {2010-11-01}:{2011-05-04};

On 2011-06-16 I wrote about a small PHP snippet to convert a MySQL database and all its tables to UTF-8/InnoDB. Yesterday, two years later, I was trying to accomplish the exact same thing using common_schema which I discovered recently.

According to it’s author(s) “common_schema is a framework for MySQL server administration“. In High Performance MySQL (3rd Edition), Baron Schwartz states that “common_schema is to MySQL as jQuery is to javaScript“. I don’t know about that, but it sure is really useful and fun to play with 🙂

set @script := "
  var $database := 'cacti';
  foreach ($table, $schema, $engine: table in :$database)
  {
    ALTER TABLE :$schema.:$table ENGINE=InnoDB;
    ALTER TABLE :$schema.:$table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
  }
";
call common_schema.run(@script);

Recently I had some jpg files (scans) that I wanted to combine to one pdf. With the convert utility it is as simple as:

convert *.jpg foo.pdf;

If you’re using Ubuntu (or Debian) you can get convert by installing ImageMagick:

sudo apt-get install imagemagick;

Recently I was benchmarking several methods for exporting (dumping) and importing data. One tool that I wanted to test was mydumper. I started installing it the regular way:

sudo apt-get install mydumper;

Unfortunately Ubuntu’s version (0.5.1) did not work for me. This could be related to this bug, but also could have been caused by me using Percona Server instead of MySQL. Anyway, I decided to compile the latest version of mydumper myself:

wget https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz;
tar -xzvf mydumper-0.5.2.tar.gz;
cd mydumper-0.5.2;

cmake . -DCMAKE_INSTALL_PREFIX=~/bin/mydumper;
make;

Unfortunately that didn’t work either.

make[2]: *** No rule to make target `/usr/lib/libmysqlclient_r.so', needed by `mydumper'.

At first I didn’t know what to do, but after some Googling I found a similar problem. Examining /usr/lib revealed that libmysqlclient_r.so was pointing to a non-existing file.

Missing libmysqlclient_r.so

After doing some research I decided to point libmysqlclient_r.so to libmysqlclient.so. Note that this is similar to libmysqlclient_r.a pointing to libmysqlclient.a.

cd /usr/lib;
ln -sf libmysqlclient.so libmysqlclient_r.so;

Doing so will (of course) fix the symlink.

Fixed libmysqlclient_r.so

Also the compilation of mydumper will succeed now!

make;
make install;

As I wrote before, a few months ago my N900 (touchscreen) died and without a working touchscreen it proved to be impossible to operate it. Therefore I ordered a new one which could, but did not fix my phone.

In order to recover my text messages I wrote a simple Python script which reads the el-v1.db file in comm_and_cal.zip:/home/user/.rtcom-eventlogger/backup.tgz (which is an SQLite 3 database) and writes the messages to a CVS file (which are better handled by other phones).

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import sqlite3
import os.path
import csv
from datetime import datetime

if len(sys.argv) != 3:
  print ""
  print "Usage:"
  print "%s [path to el-v1.db] [path to csv file]" % sys.argv[0]
  print ""
  sys.exit(1)

dbFileName  = sys.argv[1].strip()
csvFileName = sys.argv[2].strip()

if not os.path.exists(dbFileName):
  print ""
  print "Error:"
  print "Could not open '%s'" % dbFileName
  print ""
  sys.exit(1)

with sqlite3.connect(dbFileName) as connection, open(csvFileName, 'wb') as csvFd:
  cursor = connection.cursor()
  writer = csv.writer(csvFd, delimiter = '\t', quotechar = '"', quoting = csv.QUOTE_ALL)

  sql = "SELECT start_time, remote_uid, free_text FROM Events WHERE event_type_id = 7"
  for record in cursor.execute(sql):
    startTime, phoneNumber, textMessage = record
    
    startDateTime = datetime.fromtimestamp(startTime).strftime('%Y-%m-%d %H:%M:%S');
    phoneNumber   = phoneNumber.encode('utf-8')
    textMessage   = textMessage.encode('utf-8')

    writer.writerow([startTime, startDateTime, phoneNumber, textMessage])

connection.close()
csvFd.close()

Enjoy!

References:

• Nokia n900 mobile forensic cheat sheet