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:
- Gnome-RDP stores saved connections in a SQLite database.
- 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.