DB Browser for SQLite

Overview

DB Browser for SQLite (sqlitebrowser) is a cross-platform desktop and command-line application for creating, designing, and editing SQLite database files. It provides a graphical user interface for working with SQLite databases, supporting schema creation, data manipulation, complex SQL queries, and database export/import operations. The tool is ideal for security professionals needing to examine application databases during assessments.

Installation

Linux (Ubuntu/Debian)

# Install from repositories
sudo apt-get update
sudo apt-get install sqlitebrowser

# Or build from source
git clone https://github.com/sqlitebrowser/sqlitebrowser.git
cd sqlitebrowser
mkdir build && cd build
cmake ..
make
sudo make install

macOS

# Install via Homebrew
brew install sqlitebrowser

# Or download DMG from official site
# https://github.com/sqlitebrowser/sqlitebrowser/releases

Windows

# Install via Chocolatey
choco install sqlitebrowser

# Or download installer from:
# https://github.com/sqlitebrowser/sqlitebrowser/releases

Docker

# Pull and run Docker image
docker pull sqlitebrowser/sqlitebrowser
docker run -it sqlitebrowser/sqlitebrowser --help

Command-Line Installation

# Verify installation
sqlitebrowser --version

# Get help
sqlitebrowser --help

GUI Usage

Opening Databases

# Open existing database
sqlitebrowser existing_database.db

# Create new database
sqlitebrowser new_database.db

# Open from command line
sqlitebrowser --sqlite-config journal_mode=WAL database.db

Creating Databases

1. File Menu → New Database
2. Enter database filename
3. Click Save
4. Start creating tables in the Table structure tab

Creating Tables

1. Right-click in table list → New Table
2. Enter table name
3. Define columns:
   - Column name
   - Data type (INTEGER, TEXT, REAL, BLOB, NULL)
   - Primary Key (check if applicable)
   - Not Null constraint
   - Default value
4. Click OK

Command-Line Operations

Creating and Opening Databases

# Create new database from command line
sqlite3 newdb.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);"

# Open with sqlitebrowser
sqlitebrowser newdb.db

# Open database in read-only mode
sqlitebrowser --read-only database.db

# Open with specific settings
sqlitebrowser --line-comment database.db

Executing SQL Commands

# Via sqlite3 CLI
sqlite3 database.db

# Inside sqlite3 prompt
> .tables
> .schema users
> SELECT * FROM users;
> .quit

# Direct execution
sqlite3 database.db "SELECT * FROM users WHERE id = 1;"

# Batch execution
sqlite3 database.db < commands.sql

Common Operations

Task	Description
Create Database	File → New Database
Add Table	Right-click → New Table
Add Column	Table → Edit Table → Add Field
Insert Data	Browse Data → Insert Row
Query Data	Execute SQL tab
Export Data	File → Export → Select format
Import Data	File → Import → Select file
Backup Database	File → Export → Database to SQL
Search Data	Edit → Find & Replace
View Schema	View → Database Structure

Data Management

Inserting Data

# Insert via GUI
1. Click "Browse Data" tab
2. Select table
3. Click "Insert New Row"
4. Enter values
5. Save

# Insert via SQL
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
INSERT INTO users (id, name, email) VALUES (1, 'Jane Smith', 'jane@example.com');

# Bulk insert
INSERT INTO users (name, email) VALUES 
  ('User1', 'user1@example.com'),
  ('User2', 'user2@example.com'),
  ('User3', 'user3@example.com');

Updating Data

# Update via SQL
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
UPDATE users SET last_login = datetime('now') WHERE name = 'John';
UPDATE users SET status = 'active' WHERE status IS NULL;

# Update via GUI
1. Browse Data tab
2. Click cell to edit
3. Enter new value
4. Click outside cell to save

Deleting Data

# Delete via SQL
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive';
DELETE FROM users;  -- Delete all rows

# Delete via GUI
1. Browse Data tab
2. Right-click row
3. Delete record

SQL Query Execution

Running Queries

# Execute via GUI
1. Click "Execute SQL" tab
2. Type query
3. Click "Execute"
4. View results below

# Execute multiple statements
SELECT * FROM users;
SELECT * FROM logs;
SELECT COUNT(*) FROM sessions;

Common Query Patterns

# Basic selection
SELECT * FROM users;
SELECT id, name FROM users WHERE active = 1;

# Joins
SELECT u.name, l.action FROM users u 
  JOIN logs l ON u.id = l.user_id;

# Aggregation
SELECT COUNT(*) FROM users;
SELECT status, COUNT(*) FROM sessions GROUP BY status;

# Filtering
SELECT * FROM users WHERE created_date > '2024-01-01';
SELECT * FROM logs WHERE action IN ('login', 'logout');

# Sorting
SELECT * FROM users ORDER BY created_date DESC;
SELECT * FROM logs ORDER BY timestamp ASC LIMIT 10;

# Complex queries
SELECT u.name, COUNT(l.id) as login_count
FROM users u
LEFT JOIN logs l ON u.id = l.user_id AND l.action = 'login'
GROUP BY u.id, u.name
HAVING COUNT(l.id) > 5
ORDER BY login_count DESC;

Table Structure Management

Viewing Schema

# GUI method
1. Select table in list
2. Click "Table structure" tab
3. View column details

# SQL method
PRAGMA table_info(users);

# Export schema
.schema users > schema.sql

Modifying Tables

# Add column via GUI
1. Right-click table → Edit Table
2. Click "Add Field"
3. Enter column details
4. Click OK

# Add column via SQL
ALTER TABLE users ADD COLUMN phone_number TEXT;

# Rename column (SQLite 3.25.0+)
ALTER TABLE users RENAME COLUMN email TO email_address;

# Drop column (SQLite 3.35.0+)
ALTER TABLE users DROP COLUMN phone_number;

# Create index
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

Data Export

Export Formats

# SQL dump export
1. File → Export → Database to SQL
2. Select export options
3. Save as .sql file

# CSV export
1. Right-click table
2. Export table as CSV
3. Select delimiter (comma, tab, pipe)
4. Save file

# JSON export (via command line)
sqlite3 -json database.db "SELECT * FROM users;" > users.json

# Excel export
1. Export to CSV
2. Open in Excel/LibreOffice

Command-Line Export

# Export to SQL
sqlite3 database.db ".dump" > database.sql

# Export to CSV
sqlite3 database.db -header -csv "SELECT * FROM users;" > users.csv

# Export specific table
sqlite3 database.db -csv "SELECT * FROM logs;" > logs.csv

# Export with custom delimiter
sqlite3 database.db -separator "|" "SELECT * FROM users;" > users.txt

Data Import

Importing Data

# Import CSV via GUI
1. File → Import → Table from CSV file
2. Select CSV file
3. Configure column mappings
4. Select import options
5. Click OK

# Import SQL via GUI
1. File → Import → Execute SQL from file
2. Select .sql file
3. Click Open

# Import via command line
sqlite3 database.db < backup.sql

# Import CSV via CLI
sqlite3 database.db ".mode csv" ".import data.csv users"

Import Options

# Create table if doesn't exist
sqlite3 database.db ".mode csv" ".import data.csv new_table"

# Append to existing table
sqlite3 database.db ".mode csv" ".import more_data.csv users"

# Specify column order
sqlite3 database.db ".mode csv" ".import --skip 1 data.csv users"

# Handle headers
# CSV file should have headers that match column names

Security Assessment Scenarios

Examining Application Databases

# Find databases used by application
find /opt/app -name "*.db" -o -name "*.sqlite"

# Open application database
sqlitebrowser /opt/app/data/app.db

# Examine users table
SELECT * FROM users;

# Check password storage
SELECT username, password FROM users LIMIT 10;

# Identify sensitive data
SELECT column_name FROM pragma_table_info('users')
  WHERE column_name LIKE '%password%' OR column_name LIKE '%secret%';

Analyzing Application Data

# List all tables
SELECT name FROM sqlite_master WHERE type='table';

# View table structure
PRAGMA table_info(users);

# Find sensitive columns
SELECT * FROM pragma_table_info('users')
  WHERE name LIKE '%password%' OR name LIKE '%key%';

# Count records
SELECT COUNT(*) FROM users;
SELECT table_name, COUNT(*) FROM (
  SELECT 'users' as table_name FROM users
  UNION ALL
  SELECT 'logs' as table_name FROM logs
) GROUP BY table_name;

Data Extraction

# Extract user data
SELECT username, email FROM users;

# Get all unique values
SELECT DISTINCT role FROM users ORDER BY role;

# Find patterns
SELECT * FROM audit_logs 
  WHERE action LIKE '%delete%' ORDER BY timestamp DESC;

# Correlate data across tables
SELECT u.username, COUNT(l.id) as action_count
FROM users u
LEFT JOIN audit_logs l ON u.id = l.user_id
GROUP BY u.id
ORDER BY action_count DESC;

Advanced Features

Creating Views

# Create view via SQL
CREATE VIEW active_users AS
SELECT id, username, email, created_date
FROM users
WHERE status = 'active';

# Query view
SELECT * FROM active_users;

# Drop view
DROP VIEW active_users;

Working with Indexes

# View existing indexes
SELECT name FROM sqlite_master WHERE type='index';

# Create index for performance
CREATE INDEX idx_user_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);

# Index multiple columns
CREATE INDEX idx_user_status ON users(status, created_date);

# Remove index
DROP INDEX idx_user_email;

Transactions and PRAGMA

# Begin transaction
BEGIN TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
COMMIT;

# Rollback if needed
BEGIN;
DELETE FROM users;
ROLLBACK;  -- No data deleted

# Check constraints
PRAGMA foreign_keys = ON;

# View database info
PRAGMA database_list;
PRAGMA page_count;
PRAGMA page_size;

Command-Line Tools

sqlite3 CLI

# Start interactive shell
sqlite3 database.db

# Common commands in CLI
.help                    # Show all commands
.tables                  # List tables
.schema                  # Show schema
.schema table_name       # Show table structure
.mode csv/json/column    # Set output mode
.headers on/off          # Toggle headers
.output file.txt         # Redirect output to file
.quit                    # Exit

# Sample session
sqlite3 app.db
> .tables
> .schema users
> SELECT * FROM users;
> .quit

One-Liners

# Get database info
sqlite3 database.db ".tables"
sqlite3 database.db ".schema users"
sqlite3 database.db "SELECT COUNT(*) FROM users;"

# Dump entire database
sqlite3 database.db ".dump" > backup.sql

# Quick query
sqlite3 database.db "SELECT username FROM users LIMIT 5;"

# Count rows in all tables
sqlite3 database.db "SELECT name, (SELECT COUNT(*) FROM [name]) as count FROM sqlite_master WHERE type='table';"

Performance Optimization

Query Optimization

# Analyze query performance
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

# Use EXPLAIN for query analysis
EXPLAIN QUERY PLAN SELECT u.name, COUNT(l.id)
FROM users u
LEFT JOIN logs l ON u.id = l.user_id
GROUP BY u.id;

# Create indexes for frequently queried columns
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_user_id ON logs(user_id);

Database Optimization

# Analyze database for optimization suggestions
ANALYZE;

# Vacuum to optimize database size
VACUUM;

# Reindex after bulk operations
REINDEX;

# Check database integrity
PRAGMA integrity_check;

Troubleshooting

Common Issues

# Database is locked
sqlite3 database.db ".open" --nofts database.db

# Corrupt database
sqlite3 corrupted.db ".recover" | sqlite3 recovered.db

# Permission denied
sudo chown $USER database.db
sudo chmod 644 database.db

# Too large result set
SELECT * FROM large_table LIMIT 1000;

Debugging

# Enable verbose mode
sqlite3 -cmd ".eqp on" database.db

# Check for foreign key violations
PRAGMA foreign_keys = ON;
-- Try problematic operations

# View all triggers
SELECT name FROM sqlite_master WHERE type='trigger';

# Check for corrupted indexes
REINDEX;
PRAGMA integrity_check;

Best Practices

1. Backup Regularly: Export database before major changes
2. Use Transactions: Wrap related changes in transactions
3. Create Indexes: Index frequently searched columns
4. Use Views: Simplify complex queries with views
5. Document Schema: Keep track of table purposes
6. Validate Data: Use constraints and validation
7. Version Control: Track schema changes
8. Performance: Use EXPLAIN QUERY PLAN for optimization
9. Security: Restrict database file permissions
10. Integrity: Run PRAGMA integrity_check periodically

Resources

• Official Website: https://sqlitebrowser.org/
• GitHub Repository: https://github.com/sqlitebrowser/sqlitebrowser
• SQLite Documentation: https://www.sqlite.org/docs.html
• SQLite Query Optimizer: https://www.sqlite.org/eqp.html

Keyboard Shortcuts

Shortcut	Action
Ctrl+N	New Database
Ctrl+O	Open Database
Ctrl+S	Save
Ctrl+E	Execute SQL
Ctrl+F	Find
Ctrl+H	Replace
Ctrl+W	Close Window
Ctrl+Q	Quit
F5	Refresh

Summary

DB Browser for SQLite is an essential tool for database management and analysis. Whether for development, data analysis, or security assessment, it provides both GUI and command-line capabilities for comprehensive SQLite database operations. Understanding its features enables efficient database examination and manipulation during authorized assessments.