Search Sqlite Database - All Tables and Columns

Question

Is there a library or open source utility available to search all the tables and columns of an Sqlite database? The only input would be the name of the sqlite DB file.

I am trying to write a forensics tool and want to search sqlite files for a specific string.

score 15 · Answer 1 · answered Dec 19 '20 at 19:10

15

Just dump the db and search it.

% sqlite3 file_name .dump | grep 'my_search_string'

You could instead pipe through less, and then use / to search:

% sqlite3 file_name .dump | less

answered Dec 19 '20 at 19:10

Chris Rees

301
2
5

This is good for windows too. You can use select-string in powershell instead of grep. – js2010 Feb 27 '22 at 15:46

unutbu · Accepted Answer · 2014-11-01T19:58:49.863

12

You could use "SELECT name FROM sqlite_master WHERE type='table'" to find out the names of the tables in the database. From there it is easy to SELECT all rows of each table.

For example:

import sqlite3
import os

filename = ...
with sqlite3.connect(filename) as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()    
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    for tablerow in cursor.fetchall():
        table = tablerow[0]
        cursor.execute("SELECT * FROM {t}".format(t = table))
        for row in cursor:
            for field in row.keys():
                print(table, field, row[field])

edited Nov 01 '14 at 19:58

answered Nov 22 '12 at 14:27

unutbu

842,883
184
1,785
1,677

Fantastic! I just had one more add on question --- Lets say I am searching for a string "sms" in the tables, once found - how can I print the column name + table name in the above code snippet? – ST-User Nov 22 '12 at 15:13
I've changed the code to use a `sqlite3.Row` row factory. This makes the `cursor` return dict-like rows. The row keys are the column names. – unutbu Nov 22 '12 at 16:59

score 8 · Answer 3 · answered Dec 20 '18 at 20:21

I know this is late to the party, but I had a similar issue but since it was inside of a docker image I had no access to python, so I solved it like so:

for X in $(sqlite3 database.db .tables) ; do sqlite3 database.db "SELECT * FROM $X;" | grep >/dev/null 'STRING I WANT' && echo $X; done

This will iterate through all tables in a database file and perform a select all operation which I then grep for the string. If it finds the string, it prints the table, and from there I can simply use sqlite3 to find out how it was used.

Figured it might be helpful to other who cannot use python.

score 5 · Answer 4 · answered Dec 19 '19 at 09:52

@MrWorf's answer didn't work for my sqlite file (an .exb file from Evernote) but this similar method worked:

Open the file with DB Browser for SQLite sqlitebrowser mynotes.exb
File / Export to SQL file (will create mynotes.exb.sql)
grep 'STRING I WANT" mynotes.exb.sql

Search Sqlite Database - All Tables and Columns

4 Answers4

Linked