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.

8 thoughts on “Interacting Directly With SQLite Databases”

  1. Great post! It’s interesting that the password field was stored in plain text. I guess that makes sense though since you can’t send a hash as a password. Another good reason to secure your home directory!

    1. Yes, keeping your home directory secure is very important. A lot of sensitive info is stored in your home directory (your ssh key and pgp keys for example).

  2. I had no clue what you were writing about, but I love the line: “The output is a little raw for easy reading…” You have a good writing style even though the information is over my head!

  3. I figured out how to do this before I found this article, but I was looking for more info on an error I get when I do the following:

    jeremiah@FYODER:/media/disk/chaser/webapp/data$ sqlite3 data.db
    SQLite version 3.4.2
    Enter “.help” for instructions
    sqlite> .tables
    Error: file is encrypted or is not a database

    I know the file is a sqlite database because I’m pulling it right off the server. Any ideas on this last error?

  4. One more note. when I open the database in eclipse, it’s sqlite 2.1. Maybe there are big differences in the two version??

  5. Jeremiah,

    Yes, I believe there are big differences between versions. I don’t know any background about the differences between sqlite 2.x and sqlite 3.x, but I’ve found it necessary to open version 2.x sqlite databases with the sqlite program and version 3.x sqlite databases with the sqlite3 program.

    Both sqlite and sqlite3 are in the Ubuntu repositories and both can be installed at the same time. I would try opening the file using the sqlite program (instead of the sqlite3 program) and see if you have better luck reading the database.

  6. Daniel,

    Thanks for the input. I did get it all figured out with a little googling.

    The solution is to dump the database and import using the appropriate sqlite version like so…

    echo .dump | sqlite3 data.db | sqlite data2.db

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.