Interacting Directly With SQLite Databases

Recently, I was looking for information, a password actually, that I knew I had stored in an application I no longer use (Gnome-RDP, if you must know). To make matters worse, I used the app on a computer that I no longer have.

There were, thankfully, two things in my favor:

1. Gnome-RDP stores saved connections in a SQLite database.
2. I have backups. 🙂

After locating the correct backup set I found the ‘.gnome-rdp.db’ file (Gnome-RDP stores it in your $HOME folder) and copied it to my local home directory. At this point, I could have just done a:

sudo apt-get install gnome-rdp

and run the app and looked up the info I wanted, but where is the fun in that? Instead, I wanted to get to the data I needed without leaving the command line.

The sqlite package comes with a command-line interface to SQLite databases, similar to MariaDB‘s “mysql” command-line client. There are differences though.

One big difference is that non-SQL commands (a.k.a. SQLite specific commands) all begin with a ‘.’ (dot).

Helpfully, when you start the client, it tells you about the ‘.help’ command. So when I loaded up the .gnome-rdp.db file this is what I saw and did:

me@computer:~$ sqlite .gnome-rdp.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> .help
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in a text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line(s)", "column(s)",
"insert", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.nullvalue STRING      Print STRING instead of nothing for NULL data
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode
sqlite>

Of the commands, the most useful to me immediately were the ‘.tables’ and ‘.schema’ commands. The first listed the tables in the .gnome-rdp.db like so:

sqlite> .tables
session  version
sqlite>

Once I knew the tables I used the ‘.schema’ command to find out about the ‘session’ table:

sqlite> .schema session
CREATE TABLE session (id INTEGER PRIMARY KEY, parentid INTEGER,
iscategory BOOL, sessionname VARCHAR(30), protocol INTEGER,
computer VARCHAR(30), user VARCHAR(30), password VARCHAR(30),
domain VARCHAR(30), srvtype INTEGER, colordepth INTEGER,
screenresolutionx INTEGER, screenresolutiony INTEGER,
soundredirection INTEGER, keyboardlang VARCHAR(10),
connectiontype INTEGER, windowmode INTEGER, terminalsize INTEGER,
compressionlevel INTEGER, imagequality INTEGER);
sqlite>

The output is a little raw for easy reading, but I can read SQL enough to know that the computer, user, and password columns were the ones I wanted. So I issued a simple SQL query to get the data I was looking for:

sqlite> SELECT computer, user, password from session;
alpha|Administrator|ziep3eequa
bravo|Administrator|sheejo7oph
charlie|Administrator|ohshait9ie
delta|Administrator|ze7xaitoot
echo|Administrator|iek4aithaw

Oh, and before you get any bright ideas, the computer names and passwords have been changed and don’t apply to ANY machines I know or have EVER had access to.

Now, the output of the above SQL query is easy for a machine to parse, but not that easy to read. This is by design, but the developers know that it’s not always a machine that will be mucking around inside SQLite databases, so if you want prettier output, SQLite allows you to do that with the ‘.headers’ and ‘.mode’ commands:

sqlite> .mode columns
sqlite> .headers on
sqlite> SELECT computer, user, password from session;
computer    user           password
----------  -------------  ------------
alpha       Administrator  ziep3eequa
bravo       Administrator  sheejo7oph
charlie     Administrator  ohshait9ie
delta       Administrator  ze7xaitoot
echo        Administrator  iek4aithaw
sqlite>

One final thing: you may have noticed that the ‘user’ column just barely fit the ‘Administrator’ user names. You can increase the width of columns using the ‘.width’ command.

The long and short of the story is that once I located the SQLite database that had the data I needed, I was quickly able to find the information I needed. Sure the interface to SQLite is not as advanced or pretty as what you get with MariaDB, but once you get past the differences, you’re still issuing SQL commands to get at the data you want. I like that.