️ SQLite Quick Reference

Panduan cepat SQLite untuk database lokal, development, dan CTF.

Apa Itu SQLite?

SQLite adalah database engine file-based, zero-config, serverless. Tidak perlu install server terpisah - database hanyalah satu file .sqlite atau .db.

Fitur	Keterangan
Tipe	Embedded relational database
Konfigurasi	Zero config - tidak perlu daemon/server
File	Satu file = satu database
Portabilitas	File bisa di-copy ke mana saja
Cocok untuk	Aplikasi lokal, embedded, mobile, CTF, prototyping

Instalasi

# Ubuntu/Debian
sudo apt install sqlite3

# macOS
brew install sqlite3

# Cek versi
sqlite3 --version

Membuka Database

# Buka database (buat baru jika belum ada)
sqlite3 mydatabase.db

# Buka database dalam mode read-only
sqlite3 mydatabase.db -readonly

# Jalankan query langsung tanpa masuk shell
sqlite3 mydatabase.db "SELECT * FROM users;"

# Buka database in-memory (sementara)
sqlite3 :memory:

Perintah Dot (Dot Commands)

Perintah dot adalah perintah internal SQLite (bukan SQL). Semua diawali dengan titik (.).

Navigasi & Informasi

.tables                    -- Lihat semua tabel
.tables %user%             -- Cari tabel dengan pattern
.schema                    -- Lihat schema semua tabel
.schema users              -- Lihat schema tabel users
.databases                 -- Lihat database ter-attach
.indexes                   -- Lihat semua index

Format Output

.headers on                -- Tampilkan header kolom
.headers off               -- Sembunyikan header
.mode column               -- Format kolom rapi
.mode list                 -- Output pipa (|)
.mode csv                  -- Format CSV
.mode json                 -- Format JSON
.mode line                 -- Setiap field di baris baru
.mode tabs                 -- Tab-separated
.nullvalue NULL            -- Tampilkan NULL sebagai 'NULL'

Operasi Database

.dump                      -- Dump semua data sebagai SQL
.dump users                -- Dump tabel tertentu
.output file.sql           -- Redirect output ke file
.output stdout             -- Kembalikan output ke terminal
.import file.csv mytable   -- Import CSV ke tabel
.backup backup.db          -- Backup database
.restore backup.db         -- Restore dari backup

Keluar

.quit                      -- Keluar dari SQLite
.exit                      -- Keluar dengan kode
.exit 1                    -- Keluar dengan error code 1

tip

Setiap kali masuk SQLite, jalankan .headers on dan .mode column agar output lebih mudah dibaca. Simpan di ~/.sqliterc agar otomatis.

# Buat file konfigurasi agar selalu rapi
echo ".headers on" >> ~/.sqliterc
echo ".mode column" >> ~/.sqliterc

SQL Dasar di SQLite

CREATE TABLE

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

tip

SQLite tidak mewajibkan FOREIGN KEY - aktifkan dengan PRAGMA foreign_keys = ON; setiap kali koneksi.

INSERT

INSERT INTO users (username, email, password)
VALUES ('admin', '[email protected]', 'supersecret');

INSERT INTO users VALUES (NULL, 'guest', '[email protected]', 'guest123', NULL);

-- Multiple insert
INSERT INTO users (username, email) VALUES
    ('alice', '[email protected]'),
    ('bob', '[email protected]');

SELECT

SELECT * FROM users;
SELECT id, username, email FROM users;
SELECT * FROM users WHERE id = 1;

UPDATE

UPDATE users SET password = 'newpass' WHERE username = 'admin';
UPDATE posts SET content = 'Updated content' WHERE id = 5;

DELETE

DELETE FROM users WHERE username = 'guest';
DELETE FROM posts;   -- Hapus semua baris (hati-hati!)

WHERE, ORDER BY, LIMIT

-- Filter
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE id > 10 AND username IS NOT NULL;
SELECT * FROM posts WHERE user_id IN (1, 2, 3);

-- Urutkan
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, id DESC;

-- Batasi hasil
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Pagination
SELECT * FROM users LIMIT 20, 10;        -- Alternatif (offset, limit)

LIKE & Pattern Matching

SELECT * FROM users WHERE username LIKE '%admin%';
SELECT * FROM users WHERE email LIKE '_@%';  -- underscore = 1 char

GROUP BY & HAVING

SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id
HAVING post_count > 5;

SELECT status, COUNT(*), AVG(salary)
FROM employees
GROUP BY status;

JOIN

-- INNER JOIN
SELECT u.username, p.title, p.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- LEFT JOIN (semua user, meski tanpa posts)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

-- Multiple JOIN
SELECT u.username, p.title, c.body
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

-- Self JOIN
SELECT a.username AS user1, b.username AS user2
FROM users a
JOIN users b ON a.role = b.role
WHERE a.id < b.id;

Import & Export

Import CSV

sqlite3 mydb.db

.mode csv
.headers on
.import /path/to/data.csv mytable

Pastikan tabel sudah dibuat dengan kolom yang sesuai.

Export ke CSV

.headers on
.mode csv
.output export.csv
SELECT * FROM users;
.output stdout

Export ke SQL Dump

sqlite3 mydb.db .dump > backup.sql
sqlite3 mydb.db .dump users > users.sql

Restore dari SQL Dump

sqlite3 newdb.db < backup.sql

Backup Binary

sqlite3 mydb.db ".backup mydb.backup"
sqlite3 newdb.db ".restore mydb.backup"

Function Built-in

Fungsi Agregasi

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT role) FROM users;
SELECT SUM(salary) FROM employees;
SELECT AVG(price) FROM products;
SELECT MIN(age), MAX(age) FROM users;

Fungsi String

SELECT UPPER(username) FROM users;
SELECT LOWER(email) FROM users;
SELECT LENGTH(content) FROM posts;
SELECT SUBSTR(username, 1, 3) FROM users;
SELECT TRIM('  hello  ');
SELECT REPLACE(email, '@example.com', '@company.com') FROM users;
SELECT LENGTH('hello') || ' - ' || UPPER('world');

Fungsi NULL

SELECT IFNULL(null_field, 'default_value') FROM table;
SELECT COALESCE(null1, null2, 'fallback');
SELECT NULLIF(a, b);   -- NULL jika a = b

Fungsi Lain

SELECT TYPEOF(column) FROM table;    -- Cek tipe data
SELECT TOTAL(price) FROM orders;     -- SUM untuk non-integer
SELECT RANDOM();                     -- Random number (-9223372036854775808 to +9223372036854775807)
SELECT ABS(-42);                     -- Nilai absolut
SELECT MAX(10, 20, 5);              -- Nilai maksimum dari list

Date & Time

-- Current date/time
SELECT datetime('now');
SELECT date('now');
SELECT time('now');

-- Format
SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');
SELECT strftime('%d/%m/%Y', 'now');

-- Manipulasi
SELECT datetime('now', '+1 day');
SELECT datetime('now', '-1 month');
SELECT datetime('now', '+7 days', '+3 hours');

-- Julian day (untuk selisih hari)
SELECT julianday('now') - julianday('2024-01-01');

-- Konversi
SELECT strftime('%s', 'now');              -- Unix timestamp
SELECT datetime(1735689600, 'unixepoch');  -- Unix ke datetime

-- Filter berdasarkan date
SELECT * FROM users WHERE date(created_at) = date('now');
SELECT * FROM posts WHERE created_at > datetime('now', '-7 days');

CTF Use Cases

SQLite sering muncul di CTF sebagai database lokal atau file .sqlite yang bisa dieksploitasi.

1. Membaca Database SQLite

# File SQLite biasanya berekstensi .sqlite, .db, .sqlite3, atau .db3
file dump.sqlite
sqlite3 dump.sqlite

.tables
.schema
SELECT * FROM users;
SELECT * FROM admin;
SELECT * FROM flags;
SELECT * FROM secrets;

2. SQLite Injection

SQLite injection mirip dengan MySQL, tapi ada beberapa perbedaan:

-- UNION injection
' UNION SELECT 1,2,3,4 --
' UNION SELECT sql, 2, 3, 4 FROM sqlite_master WHERE type='table' --

-- Dapatkan nama tabel
sqlite_master.name
-- Kolom
PRAGMA table_info(users)

-- Subquery injection
' OR 1=1 --
' OR '1'='1
' OR 1=1 ORDER BY 1--

tip

SQLite tidak punya information_schema. Gunakan sqlite_master untuk enumerasi tabel dan PRAGMA table_info(tabel) untuk kolom.

-- Time-based (menggunakan LIKE)
' OR (SELECT CASE WHEN (substr(password,1,1)='a') THEN 1 ELSE 0 END) --

-- Boolean-based
' OR (SELECT length(password) FROM users LIMIT 1) > 5 --
' OR (SELECT substr(password,1,1) FROM users LIMIT 1) = 'a' --

-- Error-based (hilangkan dengan CASE)
' OR (SELECT CASE WHEN (1=1) THEN 1 ELSE load_extension('x') END) --

4. Dump Semua Data

sqlite3 target.db .dump
sqlite3 target.db .dump > dump.sql
grep -i "flag\|admin\|secret\|password" dump.sql

5. Cari Admin Credentials

SELECT * FROM users WHERE role = 'admin' OR username LIKE '%admin%';
SELECT * FROM config WHERE key LIKE '%pass%';
SELECT * FROM tokens;

6. Write & Read File (Jika Ekstensi Diaktifkan)

-- SQLite bisa membaca file (jarang aktif di web)
SELECT readfile('/etc/passwd');
SELECT writefile('/tmp/evil.txt', 'malicious content');

Attach Database

Attach memungkinkan Anda mengakses multiple database dalam satu session:

-- Attach database lain
ATTACH DATABASE 'other.db' AS db2;

-- Query antar database
SELECT * FROM db2.users;
SELECT * FROM main.posts;

-- Copy data antar database
INSERT INTO main.users SELECT * FROM db2.users;

-- Detach
DETACH DATABASE db2;

tip

Attach sangat berguna saat membandingkan dua database atau memindahkan data.

PRAGMA (Konfigurasi Runtime)

PRAGMA foreign_keys = ON;        -- Aktifkan foreign key constraint
PRAGMA journal_mode = WAL;       -- Write-Ahead Logging (lebih cepat)
PRAGMA synchronous = NORMAL;     -- Keseimbangan speed dan safety
PRAGMA cache_size = -8000;       -- Cache 8MB
PRAGMA page_size = 4096;         -- Ukuran halaman
PRAGMA temp_store = MEMORY;      -- Temporary table di memory

-- Cek
PRAGMA database_list;
PRAGMA integrity_check;          -- Cek integritas database
PRAGMA quick_check;              -- Cek cepat integritas

Troubleshooting

Masalah	Solusi
`database is locked`	Ada proses lain yang menulis. Tunggu atau kill proses.
`database disk image is malformed`	Database corrupt. Coba backup terakhir atau `PRAGMA integrity_check`
`unable to open database file`	Cek path, permission file, dan direktori
`no such table`	Cek `.tables` - mungkin tabel di database berbeda
`too many SQL variables`	Batasi parameter batch (max ~999)
`file is not a database`	File bukan SQLite (atau corrupt)
Permission denied	Cek ownership file dan direktori

Ringkasan Perintah Cepat

Perintah	Fungsi
`sqlite3 file.db`	Buka database
`.tables`	Lihat tabel
`.schema [tabel]`	Lihat struktur tabel
`.headers on`	Tampilkan header kolom
`.mode column`	Format kolom rapi
`.dump`	Dump seluruh DB sebagai SQL
`.import file.csv table`	Import CSV
`.backup file.db`	Backup database
`PRAGMA foreign_keys = ON`	Aktifkan foreign key
`ATTACH DATABASE`	Attach database lain
`.quit`	Keluar

Kesimpulan

SQLite adalah database yang sederhana, ringan, dan sangat berguna untuk:

Development - prototyping tanpa setup database server
Testing - database sementara untuk unit test
CTF - membaca file database, injection, enumerasi
Aplikasi mobile/desktop - penyimpanan lokal
Data analysis - query file CSV dengan SQL

Dengan memahami perintah dot, SQL dasar, dan fitur khusus SQLite, Anda bisa memanfaatkannya secara maksimal. Ingat: .headers on + .mode column = hidup lebih rapi!