Safety and backups

A database holds the data people depend on: orders, accounts, health records, wallets. One wrong statement can destroy it, and one leaked file can expose it. This lesson collects the habits that prevent both. They are short, and every one of them comes from someone else's bad day.

Stay safe: the rules

Rule	Why
Never experiment on production	Mistakes there hurt real people
Look read-only first	You cannot break what you cannot write
Back up before every change, and test the restore	A backup you have never restored is a hope, not a backup
Use the smallest account that does the job	A mistake, or an attacker, can only do what the account can
Never build SQL from user input	That is how SQL injection happens
Encrypt databases and backups that hold secrets	A copied file should be useless without the key
Back up a running database with the right tool	A plain file copy can be broken

The rest of the page explains each one.

Never experiment on production

Production is the real system: the database behind the live website or app, with real customers' data. Everything else (your laptop, a test server, a copy) is a place to experiment.

• Try new queries, schema changes and scripts on a copy first. A restored backup makes a perfect copy, and it tests your backup at the same time.
• Make production connections look different. In Database Manager, give them a name that stands out, such as PROD - orders; the name is shown in the title bar while you work. See Edit data safely.
• Do not keep a production connection open while you work on something else. Close it when you are done.
• When you must change production, do it in a transaction, with a SELECT first, and a backup taken minutes before.

Read-only first

Most work on a database is looking. Look with an account, or a mode, that cannot write:

• Ask for a read-only database user for browsing and reporting. The database then refuses every change, whatever you type.
• If a database tool only needs to read, give it only read access. For example, Database Manager opens an LMDB folder read-only when it cannot write to it, and edits then fail with Environment was opened read-only.
• Before any UPDATE or DELETE, run the same WHERE as a SELECT and count the rows (lesson 2).

Least privilege: the smallest account that works

Every server database (MySQL, MariaDB, PostgreSQL, MongoDB) has users with permissions. Give each person and program only what it needs:

Account	Needs	Should not have
A reporting tool, or you browsing	Read (SELECT)	Any write, any schema change
The web app	Read and write its own tables	Dropping tables, creating users, other databases
A migration script	Change the schema	Day-to-day use
The administrator account	Everything	Use by apps or for daily work

A read-only user in MySQL or MariaDB:

CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'choose-a-strong-password';
GRANT SELECT ON shopdb.* TO 'reporter'@'localhost';

And in PostgreSQL:

CREATE ROLE reporter LOGIN PASSWORD 'choose-a-strong-password';
GRANT CONNECT ON DATABASE shopdb TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

(ALL TABLES covers the tables that exist now; tables created later need their own grant.)

SQLite and LMDB have no users. Anyone who can read the file can read the data, and anyone who can write it can change it. Their protection is file permissions and encryption.

SQL injection

SQL injection happens when a program builds a SQL statement by gluing user input into the text. The input can then change the statement itself.

A shop's search box looks up a customer by email. The unsafe way, in Python:

import sqlite3

db = sqlite3.connect("shop.sqlite")
typed = "x' OR '1'='1"          # what someone types into a search box

# Unsafe: the typed text becomes part of the SQL
unsafe = f"SELECT name, email FROM customers WHERE email = '{typed}'"
print("unsafe:", len(db.execute(unsafe).fetchall()), "rows")

# Safe: the ? placeholder sends the text as a value, never as SQL
safe = "SELECT name, email FROM customers WHERE email = ?"
print("safe:  ", len(db.execute(safe, (typed,)).fetchall()), "rows")

Run against the practice shop, it prints:

unsafe: 30 rows
safe:   0 rows

The unsafe statement the database received was:

SELECT name, email FROM customers WHERE email = 'x' OR '1'='1'

'1'='1' is always true, so the "search" returned every customer's name and email. With a different input, an attacker could read other tables or change data.

The safe version uses a parameterised query (also called a prepared statement): the SQL has a placeholder, ?, and the value is sent separately. The database never reads the value as SQL, so x' OR '1'='1 is an odd email address that matches nobody.

• Always use placeholders for values. Every database library has them: ? in SQLite and MySQL drivers, $1 or %s in PostgreSQL drivers, depending on the library.
• Do not try to fix it by escaping quotes yourself. One missed case is enough for an attacker.
• Placeholders work for values, not for table or column names. If a name must come from input, check it against a fixed list of allowed names.

If you test web apps, test for this only on apps you own or have written permission to test.

Back up before changes, and test the restore

A backup is a copy of the database you can go back to. Two rules make it worth having:

1. Take one before every risky change: a schema change, a bulk UPDATE, an import, an upgrade.
2. Restore it somewhere and look inside, at least once and then regularly. Many people discover their backups were empty, broken or missing a table only on the day they need them.

A common rule of thumb is 3-2-1: three copies of the data, on two different kinds of storage, one of them away from the building.

Testing a SQLite backup takes a minute. Open the copy, then:

PRAGMA integrity_check;

A healthy file returns one row, ok. Then compare row counts with the original:

SELECT COUNT(*) FROM orders;

In Database Manager, Backup in the title bar writes a timestamped copy, and you test it by adding the backup file as a new connection. There is no restore button: see Restore a backup. You will do both in the lab.

Back up a running database with the right tool

Copying the database file with your file manager or cp while a program is using it is risky:

time ──►
program writes:    [ page 1 ][ page 2 ][ page 3 ]      one change, three pages
cp reads:                [ page 1 ]          [ page 3 ]
                          old       ──────►   new       = a mix that never existed

• A change touches several places in the file. A copy taken in the middle can hold half of a change: a file that is damaged, or quietly wrong.
• SQLite may keep recent changes in a second file next to the database (shop.sqlite-wal in WAL mode, or a -journal file during a write). Copy only the main file and you lose those changes, or worse.
• LMDB's data.mdb has the same problem while it is being written.

A plain copy is only safe when no program has the database open. For a database that is running, use a tool that copies a consistent snapshot:

Database	Safe way to back up while it runs
SQLite	VACUUM INTO, the .backup command of the sqlite3 tool, or the backup API of your language's SQLite library
MySQL / MariaDB	mysqldump
PostgreSQL	pg_dump
MongoDB	mongodump
LMDB	mdb_copy, or lmdb-lws-backup, which copies through a read-only snapshot

For SQLite, VACUUM INTO writes a consistent, compacted copy to a new file, even while the database is open:

VACUUM INTO 'shop-2026-11-09.sqlite';

The target file must not exist yet; if it does, SQLite stops with output file already exists. It also cannot run inside an open transaction. Database Manager's Backup button makes this kind of consistent, compacted copy of a SQLite file.

With the sqlite3 command-line tool:

sqlite3 shop.sqlite ".backup shop-copy.sqlite"

From Python, with the standard library only:

import sqlite3

src = sqlite3.connect("shop.sqlite")
dst = sqlite3.connect("shop-copy.sqlite")
src.backup(dst)      # SQLite's online backup: safe while others use the file
dst.close()
src.close()

Server databases

For MySQL, PostgreSQL and MongoDB, Database Manager's Backup is a convenient snapshot of tables and rows, not a full server backup. For a database you must be able to rebuild exactly, also use the database's own tools. See Export and back up.

Encryption at rest

Encryption at rest means the data is encrypted while it sits on disk, so a stolen laptop, a lost USB stick or a leaked backup file shows only noise without the key. (Encryption in transit protects data on the network: the Use SSL / TLS switch for server connections in Database Manager.)

• SQLite with SQLCipher. SQLCipher is an encrypted form of SQLite: the whole file is encrypted with a password. Database Manager includes it. It opens encrypted files, and its padlock button encrypts a plain file, changes the password or removes it. See Password-protect a SQLite file.
• Backups need it too. A backup of a database full of secrets is as secret as the database. A Database Manager backup of an encrypted SQLite file stays encrypted with the same password. lmdb-lws-backup encrypts every LMDB backup as it writes it, so no plain copy ever lands on disk.
• The key is everything. A forgotten SQLCipher password, or a lost lmdb-lws-backup key, cannot be recovered. Keep it in a password manager, and test that the stored copy works. See Keys and security.
• Saved passwords are a weak spot. Database Manager saves connection passwords in plain text on your computer. For important databases turn on Ask for the password every time. See How passwords are kept.

Encryption at rest does not protect data from someone who has the password, or from a program that has the database open. It is one layer, alongside accounts, permissions and backups.

Personal data

• Test with made-up data, like the practice shop. Do not copy real customers into a test database.
• Do not paste real rows into tickets, chats or screenshots. Remove names, emails and anything secret first.
• Delete exports and old copies you no longer need. A forgotten CSV on a shared drive is a leak waiting to happen.

Check yourself

QuizSafety and backups

Question 1 of 6

You want to try a new bulk UPDATE script. Where should you run it first?

Score: 0

What you learned

• Experiment on copies, look read-only first, and give every account only the rights it needs.
• Build SQL with placeholders, never by gluing user input into the text.
• Back up before every change, and restore the backup to prove it works.
• A plain file copy of a running database can be broken; use VACUUM INTO, .backup, the dump tools, mdb_copy or lmdb-lws-backup.
• Encrypt databases and backups that hold secrets, and guard the key.

Next steps

• Lab: explore a shop database: put the whole track into practice
• Edit data safely
• lmdb-lws-backup: encrypted, verified backups of a running LMDB database