Dalam sistem otentikasi, lapisan ORM sering menyamarkan SQL di balik API nyaman. Namun, seperti pelajaran dari "What ORMs have taught me: just learn SQL", memahami SQL murni memberi bekal kritis untuk menutup celah proteksi: validasi input, penyimpanan session yang konsisten, rate limit berbasis data, dan audit query untuk deteksi abuse. Artikel ini langsung menyorot praktik SQL yang konkret untuk memperkuat auth, bukan sekadar teori.

SQL sebagai dasar validasi input auth

ORM dapat membantu mencegah SQL injection, tetapi nilai tambah nyata muncul ketika Anda tahu bagaimana query seharusnya bekerja. Validasi otentikasi paling dasar adalah memastikan credential atau token tidak hanya difilter di aplikasi, tetapi diperiksa langsung di database sebelum diterima.

Contoh validasi sederhana dengan query parameterized:

SELECT user_id
FROM auth_users
WHERE username = $1
  AND password_hash = crypt($2, password_hash)
  AND is_active = TRUE;

Dengan menggunakan fungsi seperti crypt di PostgreSQL atau HASHBYTES di SQL Server, Anda memvalidasi password di dalam database tanpa memindahkan hash kemana-mana. Ini menjaga agar secret handling tetap konsisten.

Tips tambahan:

  • Gunakan VIEW atau FUNCTION untuk memaketkan validasi; jika bisnis berubah (penambahan kolom gaya login), Anda cukup memperbarui fungsi SQL tanpa mengubah banyak kode aplikasi.
  • Validasi format token/bounded length sebelum insert; tambahkan CHECK (length(token) = 36) pada tabel token untuk mencegah data corrupt yang bisa dieksploitasi.
  • Gunakan schema terpisah untuk statement auth agar audit log bisa memantau akses pada schema khusus.

Mendukung session store dan konsistensi auth

Session dan refresh token harus dicatat dalam tabel yang mendukung status, waktu habis, dan foreign key untuk user. SQL mempermudah mekanisme konsistensi.

Struktur tabel sederhana:

CREATE TABLE session_store (
  session_id UUID PRIMARY KEY,
  user_id BIGINT REFERENCES auth_users(user_id),
  created_at TIMESTAMPTZ DEFAULT now(),
  last_seen TIMESTAMPTZ DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL,
  client_ip INET,
  refresh_token_hash TEXT NOT NULL
);

Beberapa aturan operasi:

  • Update last_seen hanya via single query: UPDATE session_store SET last_seen = now() WHERE session_id = $1 AND expires_at > now();. Ini memastikan sesi yang kedaluarsa tidak dapat diperpanjang tanpa log tambahan.
  • Gunakan constraint untuk menghindari sesi duplikat. Contoh: UNIQUE (user_id, client_ip) jika Anda ingin satu sesi per IP.

Dengan SQL, Anda bisa membangun stored procedure untuk menolak sesi baru saat user memiliki sesi aktif yang belum kadaluarsa:

CREATE FUNCTION start_session(p_user_id BIGINT, p_client_ip INET)
RETURNS UUID LANGUAGE plpgsql AS $$
DECLARE
  existing UUID;
BEGIN
  SELECT session_id INTO existing
  FROM session_store
  WHERE user_id = p_user_id
    AND expires_at > now()
    AND client_ip = p_client_ip
  LIMIT 1;

  IF existing IS NOT NULL THEN
    RAISE EXCEPTION 'Sesi aktif ditemukan';
  END IF;

  RETURN (INSERT INTO session_store(user_id, expires_at, client_ip, refresh_token_hash)
          VALUES (p_user_id, now() + interval '1 hour', p_client_ip, gen_random_uuid())
          RETURNING session_id);
END;
$$;

Langsung mengeksekusi logika ini di database mengurangi race condition karena dikelola di satu titik.

Rate limit berbasis SQL untuk endpoint auth

Proteksi auth yang lemah memicu brute-force. Daripada mengandalkan cache yang bisa dipakai attacker memaksa, gunakan tabel SQL untuk menghitung percobaan.

Ilustrasi tabel:

CREATE TABLE auth_attempts (
  ip INET,
  user_agent TEXT,
  attempt_time TIMESTAMPTZ DEFAULT now()
);

Query deteksi cepat:

SELECT count(*)
FROM auth_attempts
WHERE ip = $1
  AND attempt_time > now() - interval '5 minutes';

Jika hasilnya melewati batas (misalnya 10), hentikan proses otentikasi dan response 429. Gunakan transaction kecil untuk insert+cek agar tidak ada gap.

Optimasi:

  • Tambah CREATE INDEX ON auth_attempts (ip, attempt_time DESC); untuk query count cepat.
  • Gunakan DELETE FROM auth_attempts WHERE attempt_time < now() - interval '1 day'; via job rutin agar tabel tidak membengkak.
  • Untuk rate limit per user, gabungkan user_id (jika diketahui) dan ip.

Trade-off: tabel rate limit memerlukan storage, tetapi memberi audit trail yang bisa dianalisis jika ada percobaan abuse.

Audit query dan tracing abuse

SQL memungkinkan Anda menyimpan log otentikasi yang diperlukan untuk investigasi keamanan. Gabungkan tabel audit dengan foreign key ke session atau user.

CREATE TABLE auth_audit (
  audit_id SERIAL PRIMARY KEY,
  user_id BIGINT NULL,
  session_id UUID NULL,
  event_type TEXT NOT NULL,
  details JSONB,
  occurred_at TIMESTAMPTZ DEFAULT now()
);

Saat sesi gagal atau sukses, catat:

INSERT INTO auth_audit(user_id, session_id, event_type, details)
VALUES ($1, $2, 'LOGIN_FAIL', jsonb_build_object('reason', 'password_mismatch', 'ip', $3));

Audit di SQL punya keuntungan:

  • Relasi dengan session atau user memudahkan query untuk pola suspicious.
  • JSONB menyimpan metadata (user agent, headers) tanpa merombak schema.
  • Dapat diintegrasikan dengan sistem SIEM melalui pipeline ETL dari basis data.

Secret handling dan debugging tanpa ORM

Meminimalkan ketergantungan ORM berarti Anda bertanggung jawab atas connection string, parameter binding, dan rotate secret. Beberapa praktik:

  • Environment variable read-only dari service mesh atau secret manager. Jangan hardcode secret DB di repo.
  • Gunakan prepared statements yang disimpan di DB, lalu anda memanggilnya melalui nama fungsi. Ini memberi lapisan validasi di sisi DB.
  • Log query dengan parameter masker saat debug. Catat [REDACTED] untuk header Authorization, agar log tetap berguna tanpa memaparkan token.

Debugging tip: ketika ada isu auth, jalankan query audit langsung di DB, misalnya SELECT * FROM auth_audit WHERE user_id = $1 ORDER BY occurred_at DESC LIMIT 5;. Mengetahui event terakhir membantu menentukan apakah rate limit, failure policy, atau bug session yang menyebabkan masalah.

Kesimpulan

Belajar SQL tidak hanya untuk CRUD; ia memperkuat proteksi otentikasi melalui validasi input, manajemen sesi, rate limit berbasis data, dan audit. Mengingat pelajaran “just learn SQL”, manfaatkan teknik query parameterized, constraint, dan fungsi bawaan untuk menjaga konsistensi auth. Dengan pendekatan ini, Anda menangkal abuse lebih awal, mempercepat debugging keamanan, dan mengurangi ketergantungan pada ORM yang sering menyembunyikan detail penting.