SQLite is a lightweight database library suitable for single user or low throughput databases. It is used for storing some user data in Apertis.

Summary

When to use SQLite

Even though it is lightweight for a database, SQLite is a fairly heavyweight solution to some problems. It should not be used for configuration data: GSettings should be used for that. Similarly, it is not suitable for more fully featured database systems which require support for concurrent access or advanced SQL support. It fills the middle space, and is best suited to situations where structured user data needs to be stored and fast indexing is required on various columns in the data, as that is hard to implement manually.

Before deciding to use SQLite, you must consider a vacuuming policy for the database, and whether your use case will interact well with SQLite’s vacuuming system. Vacuuming is effectively SQLite’s term for defragmenting the database — if a database is not vacuumed appropriately, performance will degrade and the database size will increase indefinitely. Read this article on vacuuming for more information; please consider it before choosing to use SQLite.

Using SQLite

Providing an SQLite tutorial is beyond the scope of this document, but a quickstart guide is available. An introduction to the API is available too, as well as the full API reference.

SQL injection

When using SQLite, queries must be constructed using SQLite’s prepared statement and value binding API, rather than by constructing SQL strings then passing them to SQLite to parse. Constructing strings makes SQL injection vulnerabilities very likely, which can give attackers access to arbitrary user data from the database.

Here is an example of using the prepared statement API:

int result;
const gchar *sql;
sqlite3_stmt *stmt = NULL;

/* Build the query. To prevent SQL injection, this string must be constant, and not constructed dynamically. */
sql = "DELETE FROM table_name WHERE id = ?1;";
result = sqlite3_prepare_v2 (db_handle, sql, -1, &stmt, NULL);

if (result != SQLITE_OK)
  {
    abort ();
  }

/* Bind parameters to it. */
if (sqlite3_bind_text (stmt, 1, id_string, -1, NULL) != SQLITE_OK)
  {
    sqlite3_finalize (stmt);
    abort ();
  }

/* Run it. */
while ((result = sqlite3_step (stmt)) == SQLITE_BUSY);

sqlite3_finalize (stmt);

if (retval != SQLITE_DONE)
  {
    abort ();
  }

External links