Thursday, March 03, 2011

Fixing KOBO's malformed database

This is likely to be one of many posts on the subject.  I have an interest since the malformed database often rears its ugly head when using Calibre.  Since I wrote the Kobo driver for Calibre and I do not believe Calibre causes the issue I have an interest in knowing more about the cause and potential fixes.

Dumping the database

For my purposes I use sqliteman to dump the database to a file.  I have not tested the script simply using the sqlite3 command but I imagine it would work.

the script

The script below lacks some (a lot) of features and has hard coded file names, etc but it does the job.  I used python because I might want to actually use some sqlite connections at some point.

The script works by attempting to interpret each row in the dump file as Unicode.  If it fails the associated line and the related sql command is dropped.

#!/usr/bin/env python

import re
import string
import binascii

file = open("kobo-corrupt-nick.sql", "rb")
err_file = open("error.log", "w")
out_file = open("out.log", "w")

new_stmt = False
prev_row = ""
err_line = ""
corrupt = False
prev_corrupt = False
firstrow = True

while 1:
    line = file.readline()

    if not line:
        err_line = line

        if re.match("[I][N][S][E][R][T] *", line):
            new_stmt = True
            row = line
        elif re.match("[D][R][O][P] *", line):
            new_stmt = True
            row = line
        elif re.match("[C][R][E][A][T][E] *", line):
            new_stmt = True
            row = line
        elif re.match("[P][R][A][G][M][A] *", line):
            new_stmt = True
            row = line
        elif re.match("[B][E][G][I][N] *", line):
            new_stmt = True
            row = line
        elif re.match("[C][O][M][M][I][T] *", line):
            new_stmt = True
            row = line
            new_stmt = False
            row = row + line

        if new_stmt == True:
            if prev_corrupt == False:
                if prev_row != "":
                if prev_row != "":                     err_file.write(prev_row)         prev_row = row         unicode(line, 'utf-8')         prev_corrupt = corrupt     except UnicodeDecodeError:         prev_corrupt = True         prev_row = row         print line         continue     except:         raise if new_stmt == True:     if prev_corrupt == False:         if prev_row != "":             out_file.write(prev_row)     else:         if prev_row != "":             err_file.write(prev_row) file.close() err_file.close() out_file.close()

Importing the file

Importing the file is straight forward, simply run:

sqlite3 KoboReader.sqlite < out.log

Validating the data
This is the kicker.  A lack of understanding of the data relationships may prevent being able to sort out the data.  I have started on some scripts that may help.  For instance:

    vs.volumeid, c.numshortcovers  - count(vs.volumeid) diff
    volume_shortcovers vs inner join content c on
        c.contentid = vs.volumeid 
    c.bookid is Null
group by 
    c.title,vs.volumeid, c.numshortcovers 
having diff <> 0

This script looks for issues where the number of rows in volume_shortcovers for each book is not the number specified by numshortcovers in the book record in the content table.

Does it mean anything?  Who knows as my Kobo that has no issues also as differences.

Another sql statement attempts to rebuild those missing rows:

insert into volume_shortcovers (volumeid, shortcoverid, VolumeIndex)
    c.bookid, c.contentid, c.volumeindex
from content c left outer  join volume_shortcovers vs on 
    c.bookid = vs.volumeid and 
    c.contentid = vs.shortcoverid and 
    c.volumeindex = vs.volumeindex 
    c.bookid is not Null and
    vs.volumeid is Null
order by c.bookid, c.volumeindex 
Is that safe?  No idea but I am working on it...  Running it on a database that had issues reduced the number of rows returned by the first sql query.

As I learn more I will post it here.

Wednesday, March 10, 2010

Automatically Resize Photos for Users

What it does
This is a script I wrote some time ago to automatically resize photos for some users. With this script, they can simply copy their digital camera photos to a directory in their home directory. When the script runs via cron it will use ImageMagick's convert utility to shrink the photo to the default size (1024x768) with a quality of 75 and delete the original.

It will automatically create the required directories if they do not exist


Some users are not comfortable with using ImageMagick and few of the available gui tools are as fast at resizing multiple files. With this script, the users simply drop the files in place, wait a few minutes and the cron job resizes the files for them.

It also has the added benefit of reducing the storage requirements for photos

Potential issues

The script automatically deletes the original. This is by design don't complain to me if you lose original work.

The script currently meets my needs but I would probably add the ability for the user to specify a default size, quality, photo directory and the option of keeping the originals via a config file in their home directory

 This same script is available at as it was originally posted there.


# Copyright (C) 2006  Timothy Legge

# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.

# Resize all the files in a directory when it runs.  The assumption is
# that this will be run via cron so the files dropped in the directory
# will be automatically resized at regular intervals

# Checking for root privelages to call script:
ROOT_UID=0 # Root has $UID 0.
if [[ "$UID" -eq "$ROOT_UID" ]] # Check to ensure user is root..

NEW_SIZE=1024x768       # New Size
QUALITY=75              # Default jpg quality


# Exclude the following directories

#echo "This script will process photos in all users ~/photos directory and"
#echo "DELETE all the original photos"; echo
#echo "YOU HAVE BEEN WARNED"; echo
#echo "Comment the following line if you know what you are doing"

#exit 0 # Comment or delete line to use script

for name in $(awk 'BEGIN{FS=":"}{if ($3>500) print $1;}' < "$PASSWORD_FILE" )
        if [[ ! -e "$HOME_DIR" ]];
                continue        # User does not have a home directory
        RESIZE=$PHOTO_DIR/$NEW_SIZE             # Resize to most monitor resolitions


        # Check to see whether the current directory is
        # in the list of excluded directories
        for exclude in $exclude_dirs
                if [[ "$exclude" = "$HOME_DIR" ]]
                #       echo "Exclude : $exclude"
                        break   # Directory is excluded break out of the loop
        # Check to see whether the directory should be skipped (in excluded list)
        if [[ "$skip_dir" = "1" ]]
                continue        # Exclude directory go to next directory
        else    # Process the photos in the directories as normal
                # Check to see whether the $PHOTO_DIR exists
                # Create it if it does not
                if [[ ! -e "$PHOTO_DIR" ]];
                        echo "Photo resize directory does not exist"
                        echo "    Creating $PHOTO_DIR"
                        if mkdir "$PHOTO_DIR" 2>/dev/null;
                                chown $name:$name $PHOTO_DIR
                                echo "        $PHOTO_DIR successfully created"
                                # Create the $RESIZE_DIR since its parent did not exist
                                if mkdir "$RESIZE" 3>/dev/null
                                        chown $name:$name $RESIZE
                                        echo "        $RESIZE successfully created"
                                        echo "        ERROR: unable to create $RESIZE"
                                echo "    ERROR: unable to create $PHOTO_DIR"

                # Check to see whether the location for the resized files exists
                # Create it if it does not
                if [[ ! -e "$RESIZE" ]];
                        echo "Location for resized photos does not exist"
                        echo "    Creating $RESIZE"
                        if mkdir "$RESIZE" 2>/dev/null;
                                chown $name:$name $RESIZE
                                echo "        $RESIZE successfully created"
                                echo "        ERROR: unable to create $RESIZE"

                # Start Processing files
                echo "Begin processing pictures in $PHOTO_DIR"

                # Loop through each file in the $PHOTO_DIR
                for file in $PHOTO_DIR/*
                        if [[ ! -e "$file" ]];  # Check to see if any files exist
                                echo "$file does not exist."

                        # Only process regular file types (not directories)
                        if [[ -f "$file" ]]
                                echo "    convert "$file" -resize $NEW_SIZE -quality 75 $RESIZE/`basename "$file"`"
                                if convert "$file" -resize $NEW_SIZE -quality 75 "$RESIZE/`basename "$file"`" 2>/dev/null
                                       if rm -f "$file" 2>/dev/null
                                               echo "        Successfully deleted $file"
                                                echo "        Error attempting to delete $file"
                                        echo "        Error attempting to resize $file"
                                        echo "            Original file not deleted"
                chown $name:$name $RESIZE/*
                # Finish Processing files
                echo "No resizable pictures remaining in $PHOTO_DIR"

        fi      # Directory not excluded - Process photos
else    # not root
        echo "Script must be run as root"
exit 0

Sunday, November 15, 2009

Running Multiple SSD Daemons in Ubuntu


There are times when running multiple sshd daemons makes sense. One of those times is when you have a server that has both a public/external interface and an private/internal interface. A LTSP server is a perfect example of this.

LTSP servers typically have an internal network that the thin clients are on and an external network that connects to the Internet. Often, the internal users are not using strong passphrases and allowing direct ssh connection from the Internet would put you system at risk.

The solution is to split up the sshd configuration by interface so you can use more secure settings for the public interface.

In this setup I also create a sshd daemon for localhost ( as it is used for NOMACHINE's  nxServer and client.


Create custom files:
cp /etc/ssh/sshd_config /etc/ssh/sshd_config_internal
cp /etc/ssh/sshd_config /etc/ssh/sshd_config_localhost
cp /etc/init.d/ssh cp /etc/init.d/ssh_internal
cp /etc/init.d/ssh cp /etc/init.d/ssh_localhost
cp /etc/default/ssh /etc/default/ssh_internal
cp /etc/default/ssh /etc/default/ssh_localhost
cp /etc/ssh/ssh_host_rsa_key /etc/ssh/ssh_host_internal_rsa_key
cp /etc/ssh/ssh_host_rsa_key /etc/ssh/ssh_host_localhost_rsa_key
cp /etc/ssh/ssh_host_dsa_key /etc/ssh/ssh_host_internal_dsa_key
cp /etc/ssh/ssh_host_dsa_key /etc/ssh/ssh_host_localhost_dsa_key

Minimal Config File Changes
These minimum changes are required to simply make the three daemons configuration different enough to run.  Later you can make modifications to increase the security of the public interface.
Edit /etc/ssh/sshd_config
  1. Edit ListenAddress to make it the ip address of the public interface
  2. Add PidFile /var/run/
Edit /etc/ssh/sshd_config_internal
  1. Edit ListenAddress to make it the ip address of the private interface
  2. Add PidFile /var/run/
  3. Edit HostKey /etc/ssh/ssh_host_internal_rsa_key
  4. Edit HostKey /etc/ssh/ssh_host_internal_dsa_key
Edit /etc/ssh/sshd_config_localhost
  1. Edit ListenAddress to make it
  2. Add PidFile /var/run/
  3. Edit HostKey /etc/ssh/ssh_host_localhost_rsa_key
  4. Edit HostKey /etc/ssh/ssh_host_localhost_dsa_key
Init Script Changes
Here, it is easier to modify one of the scripts first and then do a search and replace to create the second script but I will show all the changes:

Edit /etc/init.d/ssh_internal

sed -i 's/usr.sbin.sshd/usr\/sbin\/sshd_internal/g' ssh_internal_temp
sed -i 's/\/run\/sshd_internal/g' ssh_internal_temp
sed -i 's/"sshd"/"sshd_internal"/g' ssh_internal_temp
sed -i 's/etc.default.ssh/etc\/default\/ssh_internal/g' ssh_internal_temp
sed -i 's/Provides:\t\tsshd/Provides:\t\tsshd_internal/g' ssh_internal_temp

Edit /etc/init.d/ssh_localhost

sed -i 's/usr.sbin.sshd/usr\/sbin\/sshd_localhost/g' ssh_localhost_temp
sed -i 's/\/run\/sshd_localhost/g' ssh_localhost_temp
sed -i 's/"sshd"/"sshd_localhost"/g' ssh_localhost_temp
sed -i 's/etc.default.ssh/etc\/default\/ssh_localhost/g' ssh_localhost_temp
sed -i 's/Provides:\t\tsshd/Provides:\t\tsshd_localhost/g' ssh_localhost_temp

Note that there are no capital Vs in the above.  It is a backslash \ followed by a forwardslash / as in "\/"

Create links to sshd

ln -s /usr/sbin/sshd /usr/sbin/sshd_internal
ln -s /usr/sbin/sshd /usr/sbin/sshd_localhost

Set the new init scripts to start automatically
update-rc.d sshd_internal defaults
update-rc.d sshd_localhost defaults

Wednesday, October 14, 2009

Dansguardian, ident and Windows clients

I manage a Ubuntu, LTSP setup and recently there was an issue where the Windows XP clients that use the Dansguardian web proxy stopped working. Since the issue was noticed some days after the change that casued it I did not realize the connection.

At some point I had enabled ident in the /etc/dansguardian/dansguardian.conf setting in order to record the user name of the person using the proxy. This worked as planned on the LTSP clients however the Windows XP clients stopped working. I figured it out when I noticed that the clients did not totally stop working but were in fact really slow.

The Dansguardian proxy was attempting to connect to Windows XP on port 113/tcp and the firewall was blocking the access. As a workaround I simply added a port exception in the Windows XP firewall to allow the proxy to connect to port 113/tcp. Since nothing was listening on that port Dansguardian instantly received a reply that the connection was refused and the proxy worked fine (with the exception of not being able to identify the user.

I now need to either find a Windows ident server or figure out how the ntlm authentication works in Dansguardian.

Monday, September 21, 2009

Ubuntu and kvm virtualization - know who you are and where it runs

Maybe I should have read the documentation. Might have saved me a few confused hours. I doubt it though as my issue seemed to be something straight forward enough that it is probably not documented.

I had created a virtual machine using the Virtual Machine Manager GUI. It was a CentOS system, minimally configure that I simply wanted to clone as I needed a couple of machines to test Openfire. Unfortunately, all my vast knowledge (Google) ;-) seemed to let me down.

The command that should have cloned the system
sudo virt-clone -o CentOSServer -n CentOSServer2 -f /home/user/CentOS2.img --connect=qemu:///system
failed with an error:

ERROR Domain CentOSServer is not found
Yet, the virtual machine that I was attempting to clone existed. I connected to it with the Virtual Machine Manager and was able to start it and access it without any issues.

I tried various alternatives including specifying --connect=qemu:///session but nothing seemed to work.

I tried virsh to connect in an attempt to use the "list -all" command. I was thinking that the gui could find the virtual machine so it had to be somewhere:
sudo virsh --connect=qemu:///session list --all

Unfortunately, that did not list any virtual machines.

The answer was fairly simple once I noticed that the GUI had specified "localhost (User)" in the name of the host to which I had connected and that the virtual machines were part of. I realized that by running the commands using sudo, I was connecting with root's profile while I was running the Virtual Machine Manager as my own user profile.

Once I realized that I should connect as my own user the "virsh list --all" command showed the virtual machine that I wanted to clone. Then, the following command cloned the system:

sudo virt-clone -o CentOSServer -n CentOSServer2 -f /home/user/CentOS2.img --connect=qemu:///session

I need to do a little research to understand the difference between the session and the system but it worked. Now back to Openfire

Friday, January 16, 2009

Lock Firefox Proxy and other Settings

Locking Firefox settings in a multi-user Linux system can be a bit difficult and the information out there varies in quality. A while ago I found a script written by Andy Rabagliati of that automatically created the correctly formated config file and updated the Firefox settings to import the config file.

Recently when I used it on Ubuntu I ran into issues so I modified the script to account for the way Ubuntu separates the browser from xulrunner.

Here is a link to the updated script

To run the script simply pass it the ip address of the proxy server as follows:
sudo ./
There are numerous additional settings that can be enabled at the bottom of the script. Currently the following settings are enabled:
  1. Set the cache size to 10 megs
  2. Do not hide the tabs if only one is open
  3. Proxy settings
To reverse the settings simply open the all.js files that are modified (modified files are listed when the scrip runs) and remove or comment out the last line in the file or copy the backup version over the modified file,

Sunday, August 31, 2008

The Ultimate Hacker Key

The Ultimate Hacker Key (aka who needs a key chain)

While listening to PaulDotCom Security Weekly 114 I "discovered" UNetbootin - Universal Netboot Installer. Paul talked about installing Backtrack 3 on his USB key and how easy UNetbootin was to use. A colleague of mine recently expressed an interest in having Backtrack running on a USB Key so I thought I would take a look.

Installing Backtrack 3

Getting Backtrack installed and ready to boot from usb is so simple using UNetbootin it almost does not require directions. But here they are:
  1. Goto
  2. Click Download (for windows or Linux)
  3. Insert your USB Key
  4. Run UNetBootin
  5. Select BackTrack from the Distribution Download
  6. The Version populates automatically
  7. Select the drive that is your USB key (hint: it is probably not C:\)
  8. Click Okay
  9. Wait
  10. Reboot and if USB is a boot option the Backtrack menu should appear
So who needs a Key Chain

On several PaulDotCom Security Weekly podcasts, Larry referenced and described his key chain. It has some pretty cool things but who needs a key chain when you have a 8 GB Kingston USB key ($19.99 CDN at FutureShop last week). It got me thinking, whether I could get all those things on one device.

First up - Ophcrack

Ophcrack is a free Windows password cracker based on rainbow tables. So, lets get it installed:
  1. Plug in the USB Key
  2. Download the ophcrack LiveCD iso from
  3. Mount the iso image (mount -o loop ophcrack-xp-livecd-2.0.1.iso /mnt/cdrom)
  4. Copy the main directory to the USB key (cp -ra /mnt/cdrom/ophcrack /media/usbkey/)
  5. Copy and rename the boot directory (cp -ra /mnt/cdrom/boot /media/usbkey/bootoph)
  6. umount /mnt/cdrom
  7. Edit the syslinux.cfg from Backtrack 3 (vim /media/usbkey/boot/syslinux/syslinux.cfg)
  8. Comment out any extra Backtrack 3 boot images that you don't plan to use
  9. Add in the boot section from /media/usbkey/bootoph/ophcrack.cfg like:
    LABEL xconf
    MENU LABEL Ophcrack Graphic mode
    KERNEL /bootoph/vmlinuz
    APPEND initrd=/bootoph/initrd.gz ramdisk_size=6666 root=/dev/ram0 rw autoexec=xconf;startx changes=/slax/
  10. Note that the /boot/ references were changed to /bootoph/
  11. Reboot

Second - Offline NT Password & Registry Editor

This is a utility to (re)set the password of any user that has a valid (local) account on your Windows NT/2k/XP/Vista etc system. You do not need to know the old password to set a new one.

This is a very small live CD so putting it on its own USB key seems like a waste.
  1. Download the CD image from
  2. Unzip the zip the zip file to obtain the cd080802.iso
  3. mount the iso file (mount -o loop cd080802.iso /mnt/cdrom)
  4. create a boot directory called bootnpwd on the USB key (mkdir /media/usbkey/bootpwd)
  5. Copy all files froom the iso to the new directory (cp -ra /mnt/cdrom/* /media/usbkey/bootnpwd)
  6. umount /mnt/cdrom
  7. Edit the syslinux.cfg from Backtrack 3 (vim /media/usbkey/boot/syslinux/syslinux.cfg)
  8. Add in the boot section from /media/usbkey/bootnpwd/syslinux.cfg like:
    LABEL bootnwd
    MENU LABEL Offline NT Password and Registry Editor
    KERNEL /bootnpwd/vmlinuz
    APPEND rw vga=1 initrd=/bootnpwd/initrd.cgz,/bootnpwd/scsi.cgz
  9. Note that the /boot/ references were changed to /bootnpwd/
  10. Reboot
To Do List
  1. Add extra RainBow tables for OphCrack
  2. Add Ubuntu or some other General Purpose Distro
  1. For Windows users there are a number of utilities to mount an iso image as a drive. Do a Google Search or get a real OS ;-)
  2. The instructions above are from memory and while I have read it several times to remove obvious errors some may still exist. Leave feedback with corrections but use it has a guide...