Sqlite Database Search - All Tables and Columns

Question

Is there an open source library or utility for finding all tables and columns of the Sqlite database? The only input will be the sqlite database file name.

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

Answer 1

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

For instance:

 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]) 

Answer 2

I know this is late for the party, but I had a similar problem, but since it was inside the docker image, I did not have access to python, so I solved it like this:

 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 over all the tables in the database file and perform the select all operation, which I then do for the row. If he finds a row, he prints the table, and from there I can just use sqlite3 to find out how it was used.

I thought this might be useful for those who cannot use Python.