PHP Script for Adding Entries to ConnectBot

Here's a quick little script that lets you add entries via a PHP script to your ConnectBot Android application.  This really helps if you are like me and have over 50 hosts that you want to add.  Using this is easier if you have root, but it is still possible to use even if you don't have root.  First you'll have to either hardcode your connections into the connection_entries array in the script, or modify the script to load your entries rom a csv, database, etc.  For best results, make sure ConnectBot isn't running when you update the hosts database or your changes might not be saved.  The Connectbot hosts database has the following columns:

• username
• Self explanatory, your username on the host.
• port
• 22 for ssh, etc.
• hostkeyalgo
• ssh-rsa, ssh-dsa, etc.
• color
• Row color for this new entry (different colors don't seem to work to well when you have a large number of hosts though.)
• usekeys
• true or false based on whether you are using a ssh key to login or a password.
• use authagent
• yes or no based on whether or not you want to enable the auth agent forwarding (yes, strange it isn't true/false also.)
• pubkeyid
• integer representing the key from "Manage Pubkeys" (if any) - is in the same order as the keys are displayed in "Manage Pubkeys", starting with 1.
• delkey
• 'del' or other key to use as delete key.
• fontsize
• 10 or other integer.
• wantsession
• true unless you are using this entry for port forwarding.
• compression
• false (use true if you are on a slow network.)
• encoding
• 'UTF-8' or any of the others supported by ConnectBot.
• stayconnected
• 0 or 1 based on if you want ConnectBot to attempt to reconnect when disconnected.

Instructions with root:

• Run "php connectbot_hosts_update.php get hosts" to retrieve the ConnectBot connections database.  (Must have adb.)
• Run "php connectbot_hosts_update.php view hosts" to view the existing connection entries.
• Make a backup of the hosts file :-).
• Run "php connectbot_hosts_update.php update hosts" to update the database.
• Run "php connectbot_hosts_update.php put hosts" to add the new entries to Connectbot.  (Must have adb.)

Instructions without root:

• Via terminal on Android device or adb, run the command "cat /data/data/org.connectbot/databases/hosts > /sdcard/hosts" then "adb pull /sdcard/hosts hosts".
• Run "php connectbot_hosts_update.php view hosts" to view the existing connection entries.
• Make a backup of the hosts file :-).
• Run "php connectbot_hosts_update.php update hosts" to update the database.
•  Run the command "adb push hosts /sdcard/hosts" then (via terminal on Android device or adb,) run the command "cat /sdcard/hosts > /data/data/org.connectbot/databases/hosts".

OK, if that hasn't scared you off yet, then here's the code!

#
# connectbot_hosts.php - manage connectbot hosts from PHP
#
# 2010 - Alex Shillington
#
# Note: doesn't support port forwards (yet)

$action     = $argv[1];
$dbfile     = $argv[2];

if ($action == 'view' && $dbfile != '') {
    viewData($dbfile);
} else if ($action == 'update' && $dbfile != '') {
    updateData($dbfile);
} else if ($action == 'get') {
    getHosts($dbfile);
} else if ($action == 'put' && $dbfile != '') {
    putHosts($dbfile);
} else {
    print "Usage:\nphp connectbot_hosts.php view|update|get|put HOST_DBFILE\n";
    print "(HOST_DBFILE will default to 'hosts' if not specified for get and put options.)\n";
}

function updateData($dbfile) {
    # change defaults as desired (view existing database to see current values)
    $defaults = array(
        'username'       => 'myusername',
        'port'           => '22',
        'hostkeyalgo'    => 'ssh-rsa',
        'color'          => 'gray',
        'usekeys'        => 'true',
        'useauthagent'   => 'no',
        'pubkeyid'       => '1', #note: this is in the same order as they are displayed in "Manage Pubkeys", starting with 1
        'delkey'         => 'del',
        'fontsize'       => 10,
        'wantsession'    => 'true',
        'compression'    => 'false',
        'encoding'       => 'UTF-8',
        'stayconnected'  => 0
    );

    $entries = array();

    # define connection entries
    $connection_entries = array();
    $connection_entries[] = array('username'=>'alex','nickname'=>'server1_remote','hostname'=>'server1.example.com','port'=>22,'pubkeyid'=>3);
    $connection_entries[] = array('username'=>'alex','nickname'=>'server1_local','hostname'=>'10.0.0.9','port'=>22,'pubkeyid'=>3);
    # etc...

    # add any other code here that you want to add to connection_entries array from other csv files, db, etc.
    # ...

    # populate entries from connection_entries and default entry values
    foreach ($connection_entries as $connection) {
        $entry = $defaults;
        foreach ($connection as $var=>$val) {
            $entry[$var] = $val;
        }
        $entries[] = $entry;
    }

    # connect to hosts db
    $dbh = connect($dbfile);

    # add entries to hosts db
    foreach ($entries as $entry) {
        $sql = "SELECT _id,hostname FROM hosts WHERE nickname = '{$entry['nickname']}'";
        if ($sth = $dbh->query($sql)) {
            $results = $sth->fetchAll(PDO::FETCH_ASSOC);
            $_id = $results[0]['_id'];
            # overwrite existing entry with same nickname
            if ($_id != '' || $entry['force'] == 1) {
                $sql = "DELETE FROM hosts WHERE _id = $_id";
                try {
                    $dbh->exec($sql);
                } catch(Exception $e) {
                    print "ERROR: " . $e->getMessage() . "\n$sql\n";
                }
            }
            $sql = "SELECT _id,hostname FROM hosts WHERE hostname = '{$entry['hostname']}'";
            if ($sth = $dbh->query($sql)) {
                $results = $sth->fetchAll(PDO::FETCH_ASSOC);
                $_id = $results[0]['_id'];
                if ($_id != '' || $entry['force'] == 1) {
                    $sql = "DELETE FROM hosts WHERE _id = $_id";
                    try {
                        $dbh->exec($sql);
                    } catch(Exception $e) {
                        print "ERROR: " . $e->getMessage() . "\n$sql\n";
                    }
                }
                if ($_id == '' || $entry['force'] == 1) {
                    print "{$entry['nickname']} is new {$entry['hostname']}\n";
                    $keystring = implode(',',array_keys($entry));
                    foreach ($entry as $key=>$val) {
                        $entry[$key] = "'" . sqlite_escape_string($val) . "'";
                    }
                    $valstring = implode(',',array_values($entry));
                    $sql = "INSERT INTO hosts ($keystring) VALUES ($valstring)";
                    try {
                        $dbh->exec($sql);
                    } catch(Exception $e) {
                        print "ERROR: " . $e->getMessage() . "\n$sql\n";
                    }
                } else {
                    print "{$entry['nickname']} is new {$entry['hostname']}, but has an existing entry with a different nickname - not adding\n";
                }
            }
        } else {
            print "Error in query:\n$sql\n";
        }
    }
    $dbh = null;

}

function viewData($dbfile) {
    $dbh = connect($dbfile);
    $sql = "SELECT * FROM hosts";
    if ($sth = $dbh->query($sql)) {
        $results = $sth->fetchAll(PDO::FETCH_ASSOC);
        print implode(',',array_keys($results[0])) . "\n";
        foreach ($results as $result) {
            print implode(',',$result) . "\n";
        }
    } else {
        print "error reading from hosts db $dbfile\n";
    }
    $dbh = null;
}

function connect($dbfile) {
    try {
        $dbh = new PDO("sqlite:$dbfile");
    } catch(Exception $e) {
        print "ERROR: " . $e->getMessage() . "\n";
        exit;
    }
    return $dbh;
}

function getHosts($dbfile = 'hosts') {
    if (!`./adb pull /data/data/org.connectbot/databases/hosts ./$dbfile`) {
        print "sorry, you need root to get the hosts file that way\n";
        print "try this from a script instead:\n";
        print "cat /data/data/org.connectbot/databases/hosts > /sdcard/hosts\n";
        print "then adb pull that file\n";
    }
}

function putHosts ($dbfile = 'hosts') {
    if (!`./adb push $dbfile /data/data/org.connectbot/databases/hosts`) {
        print "sorry, you need root to push that way\n";
        print "instead, push $dbfile to /sdcard/hosts then:\n";
        print "cat /sdcard/hosts > /data/data/org.connectbot/databases/hosts\n";
    }
    print "\nIf connectbot is running, don't save any config changes once you've pushed the new hosts database.  Also, you might need to kill connectbot or restart the phone to see the new hosts.\n";
}

?>