SQLite

Introduction

SQLite is a database engine that directly uses a file, for example named db.sqlite. There is therefore no need to authenticate the database users in order for them to access the data: everything is managed by the access control of the file itself. More precisely, when modifying the database, the directory that stores the database file needs also to be writable by the engine, in order to create and remove a journal file.

SQLite can be used by applications to store settings, user information, messages, etc. Its engine is also used by projects such as OSQuery in order to benefit from a SQL interpreter.

Basic commands

In order to dump the content of a database file named db.sqlite from the command line:

sqlite3 < /dev/null -bail -batch -cmd .dump db.sqlite

From the interactive prompt given by sqlite3, it is possible to run SQL statements as well as special SQLite commands, prefixed with a dot.

# Show available commands and some help
.help

# Show the version
.version
SELECT SQLITE_VERSION();

# Quit
.exit

# List tables from the database
.tables
# Dump the schema of every table of the database
.schema

# List tables and dump the schema, for tables which names start with "user"
.tables user%
.schema user%

# Change the output mode of SELECT statement (default one is pretty):
# * Each cell in a new line
.mode line
# * Comma-separated values
.mode csv
# * Fixed-wicth columns
.mode column
.width 10 20 50 50 5
# * Pretty tables (the default one in OSQuery)
.mode pretty
# * Pipe-separated values (the default one in SQLite)
.mode list

SQL statements

SQLite supports standard SQL statements: SELECT, INSERT INTO, UPDATE, DELETE, etc.

Several SELECT statements can be merged together using:

• UNION ALL to concatenate the results ;

• UNION to concatenate the results, removing the duplicated rows ;

• INTERSECT to only keep the rows in common ;

• EXCEPT to exclude some rows.

It is also possible to join tables (LEFT JOIN, INNER JOIN, etc.) using conditions expressed as:

SELECT * FROM users LEFT JOIN user_groups ON users.uid = user_groups.uid;
SELECT * FROM users LEFT JOIN user_groups USING (uid);

When performing a SELECT statement, it is possible to group some rows with a common value using GROUP BY column. In order to filter results before grouping, it is possible to use a WHERE condition clause. To filter results after grouping, the clause to use is HAVING condition.

The filters may use LIKE to match cells according to a pattern. The pattern can use _ as a wildcard for a single character and % as a wildcard for multiple characters. In order to escape _, engines such as MSSQL use a syntax like [_], but SQLite uses something else:

SELECT * FROM objects WHERE name LIKE 'prefix\_%' ESCAPE '\';

To print timestamps as a human-readable date, there exists a function, DATETIME. For example:

SELECT DATETIME(users.last_update_time, 'unixepoch') FROM users;

Schema table

According to the documentation, every SQLite database contains a single “schema table” named sqlite_master that stores the schema for that database.

sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

To understand its content in actual database, let’s create a new table in an empty database.

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` TEXT NOT NULL,
  `email` VARCHAR(255)  UNIQUE NOT NULL,
  `password` VARCHAR(110) NOT NULL,
  `admin` TINYINT(1) NOT NULL DEFAULT '0',
  `created` DATETIME NOT NULL
);

After executing these statements, the schema table contains the CREATE TABLE statement and other objects:

sqlite> SELECT * FROM sqlite_master;
table|users|users|2|CREATE TABLE `users` (
  `uid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` TEXT NOT NULL,
  `email` VARCHAR(255)  UNIQUE NOT NULL,
  `password` VARCHAR(110) NOT NULL,
  `admin` TINYINT(1) NOT NULL DEFAULT '0',
  `created` DATETIME NOT NULL
)
index|sqlite_autoindex_users_1|users|3|
table|sqlite_sequence|sqlite_sequence|4|CREATE TABLE sqlite_sequence(name,seq)

From a SQL injection vulnerability, this table can be obtained using queries such as:

-- Concatenate all fields and select the 1st entry (using COALESCE to support NULL values)
SELECT type||';'||name||';'||tbl_name||';'||rootpage||';'||COALESCE(sql,'') FROM sqlite_master
    LIMIT 0,1;

-- Concatenate all fields of all rows
SELECT GROUP_CONCAT(type||';'||name||';'||tbl_name||';'||rootpage||';'||COALESCE(sql,''),'^')
    FROM sqlite_master;

To exfiltrate the characters of such a string, functions HEX, SUBSTR and LENGTH (documented in Built-In Scalar SQL Functions) can be used. For example:

sqlite> SELECT HEX('hello');
68656C6C6F
sqlite> SELECT LENGTH(HEX('hello'));
10
sqlite> SELECT SUBSTR(HEX('hello'),1,1)='6';
1
sqlite> SELECT SUBSTR(HEX('hello'),1,1)>'9';
0