Sejauh ini setiap query di course ini ditulis lengkap sebelum dijalankan: nama tabel, nama kolom, dan nilainya sudah diketahui saat mengetik. Sebagian pekerjaan nyata tidak semurah itu — nama tabel dibaca dari tabel konfigurasi, filter pencarian datang dari form yang kolomnya opsional, dan trigger audit harus mencatat tabel apa pun yang menempelinya. PostgreSQL menjawab kebutuhan itu lewat dynamic SQL: string SQL dirangkai saat runtime lalu dijalankan dengan EXECUTE di PL/pgSQL. Kekuatan ini datang dengan satu tagihan yang tidak bisa ditawar — setiap nilai yang masuk dari luar harus dikutip dengan benar, karena string yang dirangkai sembarangan adalah pintu masuk klasik SQL injection. Bab ini membangun SQL dinamis langkah demi langkah: format() dengan placeholder %I/%L/%s, EXECUTE ... INTO dan EXECUTE ... USING, lalu dua penerapan produksi — pembangun WHERE untuk pencarian multi-filter dan generic audit trigger yang membaca nama tabelnya sendiri.

Dataset: Produk, Event Log, dan Tabel Konfigurasi #

Lima tabel menyiapkan seluruh panggung. produk dan event_log adalah tabel bisnis yang akan diakses secara dinamis. tabel_terpantau menyimpan daftar tabel yang harus diproses job maintenance — isinya data, bukan kode, dan job membacanya saat runtime. hasil_demo menampung hasil DO block (yang tidak bisa mengembalikan result set langsung), dan log_audit menunggu demo trigger di akhir chapter:

sql buat-tabel.sql 
-- Tabel latihan chapter Dynamic SQL: produk, log event harian, tabel
-- konfigurasi yang dipantau, tabel penampung hasil demo DO block, dan
-- tabel audit untuk demo generic trigger.
CREATE TABLE produk (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga numeric(10,2) NOT NULL
);

INSERT INTO produk (id, nama, harga) VALUES
    (1, 'Tiket Seminar Database', 750000.00),
    (2, 'Tiket Workshop Data',    1250000.00),
    (3, 'Modul Video SQL',         400000.00),
    (4, 'Sertifikat Digital',      150000.00),
    (5, 'Bundel Webinar',          990000.00);

CREATE TABLE event_log (
    id         integer PRIMARY KEY,
    jenis      text NOT NULL,
    kanal      text NOT NULL,
    tanggal    date NOT NULL,
    keterangan text NOT NULL
);

INSERT INTO event_log (id, jenis, kanal, tanggal, keterangan) VALUES
    ( 1, 'page_view',   'web',      DATE '2026-01-05', 'beranda'),
    ( 2, 'page_view',   'web',      DATE '2026-01-05', 'katalog'),
    ( 3, 'add_to_cart', 'web',      DATE '2026-01-05', 'bundel webinar'),
    ( 4, 'page_view',   'aplikasi', DATE '2026-01-06', 'beranda'),
    ( 5, 'purchase',    'web',      DATE '2026-01-06', 'tiket seminar'),
    ( 6, 'page_view',   'web',      DATE '2026-01-07', 'katalog'),
    ( 7, 'checkout',    'aplikasi', DATE '2026-01-07', 'tiket workshop'),
    ( 8, 'page_view',   'web',      DATE '2026-01-08', 'beranda'),
    ( 9, 'purchase',    'aplikasi', DATE '2026-01-08', 'modul video'),
    (10, 'page_view',   'web',      DATE '2026-01-09', 'katalog'),
    (11, 'add_to_cart', 'web',      DATE '2026-01-09', 'sertifikat digital'),
    (12, 'checkout',    'web',      DATE '2026-01-10', 'bundel webinar');

-- Tabel konfigurasi: nama tabel yang harus diproses job maintenance.
-- Isinya data, bukan kode -- job membaca nama tabel dari sini saat runtime.
CREATE TABLE tabel_terpantau (
    id         integer PRIMARY KEY,
    skema      text NOT NULL,
    nama_tabel text NOT NULL,
    keterangan text NOT NULL
);

INSERT INTO tabel_terpantau (id, skema, nama_tabel, keterangan) VALUES
    (1, 'public', 'produk',    'master produk dan harga'),
    (2, 'public', 'event_log', 'log event harian');

-- Penampung hasil demo DO block: DO tidak bisa mengembalikan result set,
-- jadi hasil EXECUTE ditangkap lewat INTO lalu dicatat ke tabel ini.
CREATE TABLE hasil_demo (
    id         integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    demo       text NOT NULL,
    keterangan text NOT NULL
);

-- Tabel audit untuk demo generic trigger di akhir chapter.
-- Kolom waktu diisi now() oleh trigger tetapi tidak pernah ditampilkan
-- agar output contoh tetap deterministik.
CREATE TABLE log_audit (
    id       integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tabel    text NOT NULL,
    operasi  text NOT NULL,
    id_baris integer NOT NULL,
    data     jsonb NOT NULL,
    waktu    timestamp NOT NULL DEFAULT now()
);

SELECT id, skema, nama_tabel, keterangan
FROM tabel_terpantau
ORDER BY id;

SELECT id, nama, harga
FROM produk
ORDER BY id;

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 12
CREATE TABLE
INSERT 0 2
CREATE TABLE
CREATE TABLE
 id | skema  | nama_tabel |       keterangan        
----+--------+------------+-------------------------
  1 | public | produk     | master produk dan harga
  2 | public | event_log  | log event harian
(2 rows)

 id |          nama          |   harga    
----+------------------------+------------
  1 | Tiket Seminar Database |  750000.00
  2 | Tiket Workshop Data    | 1250000.00
  3 | Modul Video SQL        |  400000.00
  4 | Sertifikat Digital     |  150000.00
  5 | Bundel Webinar         |  990000.00
(5 rows)

format() dan Tiga Placeholder: %I, %L, %s #

format() adalah cara standar modern merangkai string SQL: satu template dengan placeholder, bukan rantai || yang mengutip manual. Tiga placeholder yang perlu dibedakan: %I memformat nilai sebagai identifier — nama tabel atau kolom — dengan tanda kutip ganda otomatis bila perlu; %L memformat sebagai literal dengan kutip tunggal dan penggandaan kutip di dalamnya; %s menempelkan nilai mentah tanpa pengutipan apa pun. Sekali lagi: %s hanya untuk bagian template yang sepenuhnya Anda kendalikan — potongan SQL yang Anda tulis sendiri — bukan untuk input dari luar. Fungsi quote_ident() dan quote_literal() adalah pasangan lama yang mengerjakan pekerjaan yang sama untuk satu nilai:

sql format-vs-injection.sql 
-- format() dan pengutipan: tiga placeholder yang wajib dipahami sebelum
-- menyusun SQL dinamis, lalu demo injeksi nyata (rentan vs aman).

-- (1) Tiga placeholder: %I mengutip identifier (nama tabel/kolom),
-- %L mengutip literal (nilai), %s menempel apa adanya (hanya untuk
-- bagian yang Anda kendalikan penuh, bukan input pengguna).
SELECT format('SELECT %I FROM %I WHERE %I = %L',
              'nama', 'produk', 'id', 3) AS contoh_aman;

SELECT format('SELECT %I FROM %I WHERE %I = %s',
              'nama', 'produk', 'id', 3) AS contoh_angka;

-- (2) Pasangan lama: quote_ident dan quote_literal mengutip satu nilai
-- satu-satu -- %I dan %L versi format() memakai keduanya di belakang layar.
SELECT quote_ident('Order Detail') AS ident_berspasi,
       quote_ident('select') AS ident_kata_kunci,
       quote_literal('O''Brien') AS literal_bertanda_kutip;

-- (3) Payload injeksi dilihat sebagai TEKS dulu: baris pertama disusun
-- penyambungan mentah, baris kedua lewat %L. Bedanya satu tanda kutip
-- ganda di posisi tepat -- dan itu yang membedahkan serangan dari teks.
SELECT 'SELECT count(*) FROM event_log WHERE keterangan = '''
       || 'tiket'' OR ''1''=''1' || '''' AS teks_query_rentan;

SELECT format('SELECT count(*) FROM event_log WHERE keterangan = %L',
              'tiket'' OR ''1''=''1') AS teks_query_aman;

-- (4) Eksekusi keduanya di dalam DO block, hasil ditangkap ke hasil_demo.
-- Versi rentan: payload meledak menjadi OR '1'='1' dan mengembalikan
-- SEMUA baris. Versi aman: %L membungkus payload sebagai literal biasa,
-- pencarian mengembalikan nol baris karena tidak ada keterangan bernilai
-- persis seperti itu.
DO $$
DECLARE
    carian text := 'tiket'' OR ''1''=''1';
    jumlah integer;
BEGIN
    EXECUTE 'SELECT count(*) FROM event_log WHERE keterangan = '''
            || carian || ''''
        INTO jumlah;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('rentan', 'input jahat lolos: ' || jumlah || ' baris cocok');

    EXECUTE format('SELECT count(*) FROM event_log WHERE keterangan = %L', carian)
        INTO jumlah;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('aman', 'payload jadi teks biasa: ' || jumlah || ' baris cocok');
END $$;

SELECT id, demo, keterangan
FROM hasil_demo
ORDER BY id;

Output yang Dihasilkan:

text 
              contoh_aman               
----------------------------------------
 SELECT nama FROM produk WHERE id = '3'
(1 row)

             contoh_angka             
--------------------------------------
 SELECT nama FROM produk WHERE id = 3
(1 row)

 ident_berspasi | ident_kata_kunci | literal_bertanda_kutip 
----------------+------------------+------------------------
 "Order Detail" | "select"         | 'O''Brien'
(1 row)

                          teks_query_rentan                           
----------------------------------------------------------------------
 SELECT count(*) FROM event_log WHERE keterangan = 'tiket' OR '1'='1'
(1 row)

                             teks_query_aman                              
--------------------------------------------------------------------------
 SELECT count(*) FROM event_log WHERE keterangan = 'tiket'' OR ''1''=''1'
(1 row)

DO
 id |  demo  |               keterangan               
----+--------+----------------------------------------
  1 | rentan | input jahat lolos: 12 baris cocok
  2 | aman   | payload jadi teks biasa: 0 baris cocok
(2 rows)

Perhatikan dua teks query yang dihasilkan: versi rentan menghasilkan WHERE keterangan = 'tiket' OR '1'='1' — payload keluar dari kutip dan menjadi logika SQL, sedangkan versi %L menghasilkan WHERE keterangan = 'tiket'' OR ''1''=''1' — payload tetap terkurung sebagai satu nilai teks. Eksekusi nyatanya lalu memperlihatkan akibatnya: versi rentan mencocokkan 12 baris (semua baris!), versi aman nol baris. Pola serangan ini persis yang dibedah tuntas di chapter SQL Injection — bedanya di bab itu yang dirangai string koneksi aplikasi, di sini string di dalam database sendiri.

EXECUTE, INTO, dan USING #

EXECUTE menjalankan string SQL pada runtime. Hasil query satu baris ditangkap klausa INTO ke variabel PL/pgSQL. Untuk nilai, USING mengikat parameter $1, $2, … tanpa menyentuh string sama sekali — mekanisme yang sama dengan prepared statement di sisi aplikasi:

sql execute-dasar.sql 
-- EXECUTE di PL/pgSQL: menjalankan string SQL saat runtime, menangkap
-- hasilnya dengan INTO, mengikat parameter dengan USING, dan membaca
-- nama tabel dari tabel konfigurasi (loop).

-- (1) EXECUTE format(...) INTO: nama tabel hidup di variabel, %I
-- mengutipnya dengan benar, dan hasil agregasi masuk variabel jumlah.
DO $$
DECLARE
    nama_tabel text := 'produk';
    jumlah     integer;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I', nama_tabel)
        INTO jumlah;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('hitung', nama_tabel || ' berisi ' || jumlah || ' baris');
END $$;

-- (2) EXECUTE ... USING: nilai berpindah lewat parameter $1, bukan
-- penyambungan string -- literal tidak pernah dipegang tangan.
DO $$
DECLARE
    harga_min numeric := 500000;
    jumlah    integer;
BEGIN
    EXECUTE 'SELECT count(*) FROM produk WHERE harga >= $1'
        INTO jumlah
        USING harga_min;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('using', 'produk dengan harga >= 500000: ' || jumlah || ' baris');
END $$;

-- (3) Config-driven: job maintenance membaca daftar tabel dari
-- tabel_terpantau lalu menghitung baris tiap tabel lewat EXECUTE format.
-- Menambah tabel yang dipantau = INSERT satu baris konfigurasi, tanpa
-- menyentuh kode job sama sekali.
DO $$
DECLARE
    r      record;
    jumlah integer;
BEGIN
    FOR r IN SELECT skema, nama_tabel FROM tabel_terpantau ORDER BY id LOOP
        EXECUTE format('SELECT count(*) FROM %I.%I', r.skema, r.nama_tabel)
            INTO jumlah;
        INSERT INTO hasil_demo (demo, keterangan)
        VALUES ('konfigurasi', r.nama_tabel || ': ' || jumlah || ' baris');
    END LOOP;
END $$;

SELECT id, demo, keterangan
FROM hasil_demo
ORDER BY id;

Output yang Dihasilkan:

text 
DO
DO
DO
 id |    demo     |               keterangan               
----+-------------+----------------------------------------
  1 | rentan      | input jahat lolos: 12 baris cocok
  2 | aman        | payload jadi teks biasa: 0 baris cocok
  3 | hitung      | produk berisi 5 baris
  4 | using       | produk dengan harga >= 500000: 3 baris
  5 | konfigurasi | produk: 5 baris
  6 | konfigurasi | event_log: 12 baris
(6 rows)

Tiga DO block di file tersebut menaiki satu tangga yang sama:

  1. Block pertama menyimpan nama tabel di variabel; format('SELECT count(*) FROM %I', nama_tabel) menghasilkan SELECT count(*) FROM "produk" — tanda kutip ganda tidak mengganggu karena PostgreSQL membacanya sebagai identifier biasa.
  2. Block kedua memakai USING harga_min — nilai 500000 berpindah lewat parameter $1, tidak pernah dirangkai ke string, sehingga tidak ada peluang kutip salah.
  3. Block ketiga adalah pola config-driven: loop FOR r IN SELECT ... membaca baris tabel_terpantau, dan %I.%I mengutip skema dan tabel sekaligus. Empat baris hasil_demo terakhir mencatat perjalanan tiga block itu — termasuk produk: 5 baris dan event_log: 12 baris dari loop konfigurasi.

Ketika Penyambungan Mentah Gagal #

Demo terakhir sisi keamanan: nama tabel disambung mentah tanpa pengutipan. Petugas mengetik log event — dengan spasi — di form admin, dan string hasil penyambungan dibaca PostgreSQL sebagai FROM log event: tabel log dengan alias event. Tabel bernama log tidak pernah ada, dan EXECUTE berhenti dengan galat:

sql injection-error.sql 
-- Demo galat: nama tabel disambung mentah tanpa pengutipan. Petugas
-- mengetik "log event" (dengan spasi) di form admin; PostgreSQL membaca
-- "FROM log event" sebagai tabel log dengan alias event -- dan tabel
-- bernama log tidak pernah ada. EXECUTE berhenti dengan error; bandingkan
-- dengan format('... FROM %I', ...) yang tetap aman untuk nama apa pun.
DO $$
DECLARE
    nama_tabel text := 'log event';
BEGIN
    EXECUTE 'SELECT count(*) FROM ' || nama_tabel;
END $$;

Output yang Dihasilkan:

text 
psql:injection-error.sql:11: ERROR:  relation "log" does not exist
LINE 1: SELECT count(*) FROM log event
                             ^
QUERY:  SELECT count(*) FROM log event
CONTEXT:  PL/pgSQL function inline_code_block line 5 at EXECUTE

Galat ini “beruntung” karena langsung terlihat. Versi format('SELECT count(*) FROM %I', nama_tabel) menghasilkan FROM "log event" — nama dikutip dengan benar, dan bila tabelnya memang bernama demikian (dibuat dengan kutip ganda), query jalan; bila tidak, galat relation "log event" does not exist yang jujur muncul tanpa peluang menyelipkan SQL tambahan. Dengan input yang dirancang jahat, penyambungan mentah tidak berhenti di galat — ia menjalankan SQL yang tidak pernah Anda minta, seperti demo OR '1'='1' di atas.

Dynamic WHERE Builder: Filter Opsional dari Form #

Penerapan paling sering diminta: pencarian dengan filter opsional — pengguna boleh mengisi jenis, kanal, rentang tanggal, atau tidak mengisi apa pun. Menulis satu query per kombinasi berarti 16 cabang; menambah satu filter berarti menggandakan jumlahnya. Pembangun WHERE dinamis menutup jalur itu: setiap filter terisi menyumbang satu predikat yang sudah dikutip %L, predikat dirangkai AND lewat array_to_string, dan string final dijalankan RETURN QUERY EXECUTE:

sql where-builder.sql 
-- Dynamic WHERE builder: pencarian dengan filter opsional. Setiap filter
-- yang diisi pengguna menyumbang satu predikat (sudah dikutip %L),
-- predikat dirangkai AND, lalu query final dijalankan RETURN QUERY
-- EXECUTE. Filter yang NULL tidak menyumbang apa pun -- tidak ada
-- "WHERE 1=1" dan tidak ada cabang query terpisah per kombinasi.
CREATE FUNCTION cari_event(
    p_jenis    text DEFAULT NULL,
    p_kanal    text DEFAULT NULL,
    p_mulai    date DEFAULT NULL,
    p_selesai  date DEFAULT NULL
) RETURNS SETOF event_log
LANGUAGE plpgsql AS $$
DECLARE
    kondisi text[] := ARRAY[]::text[];
    query   text;
BEGIN
    IF p_jenis IS NOT NULL THEN
        kondisi := array_append(kondisi, format('jenis = %L', p_jenis));
    END IF;
    IF p_kanal IS NOT NULL THEN
        kondisi := array_append(kondisi, format('kanal = %L', p_kanal));
    END IF;
    IF p_mulai IS NOT NULL THEN
        kondisi := array_append(kondisi, format('tanggal >= %L', p_mulai));
    END IF;
    IF p_selesai IS NOT NULL THEN
        kondisi := array_append(kondisi, format('tanggal <= %L', p_selesai));
    END IF;

    IF cardinality(kondisi) = 0 THEN
        query := 'SELECT * FROM event_log ORDER BY id';
    ELSE
        query := format('SELECT * FROM event_log WHERE %s ORDER BY id',
                        array_to_string(kondisi, ' AND '));
    END IF;

    RETURN QUERY EXECUTE query;
END $$;

-- Tanpa filter: seluruh 12 baris.
SELECT count(*) AS semua_event
FROM cari_event();

-- Satu filter: hanya purchase.
SELECT * FROM cari_event(p_jenis => 'purchase');

-- Filter kanal.
SELECT * FROM cari_event(p_kanal => 'aplikasi');

-- Filter rentang tanggal: kombinasi dua predikat dirangkai AND.
SELECT * FROM cari_event(p_mulai => DATE '2026-01-07',
                         p_selesai => DATE '2026-01-08');

Output yang Dihasilkan:

text 
CREATE FUNCTION
 semua_event 
-------------
          12
(1 row)

 id |  jenis   |  kanal   |  tanggal   |  keterangan   
----+----------+----------+------------+---------------
  5 | purchase | web      | 2026-01-06 | tiket seminar
  9 | purchase | aplikasi | 2026-01-08 | modul video
(2 rows)

 id |   jenis   |  kanal   |  tanggal   |   keterangan   
----+-----------+----------+------------+----------------
  4 | page_view | aplikasi | 2026-01-06 | beranda
  7 | checkout  | aplikasi | 2026-01-07 | tiket workshop
  9 | purchase  | aplikasi | 2026-01-08 | modul video
(3 rows)

 id |   jenis   |  kanal   |  tanggal   |   keterangan   
----+-----------+----------+------------+----------------
  6 | page_view | web      | 2026-01-07 | katalog
  7 | checkout  | aplikasi | 2026-01-07 | tiket workshop
  8 | page_view | web      | 2026-01-08 | beranda
  9 | purchase  | aplikasi | 2026-01-08 | modul video
(4 rows)

Mari kita bedah mekanismenya:

  1. Parameter DEFAULT NULL membuat semua filter opsional — pemanggil cukup menyebut yang diisi lewat notasi bernama (p_jenis => 'purchase').
  2. Array kondisi mengumpulkan predikat yang sudah dikutip: format('jenis = %L', 'purchase') menghasilkan jenis = 'purchase' — nilai aman sejak lahir, bukan dikutip belakangan.
  3. array_to_string(kondisi, ' AND ') merangkai predikat; %s dipakai karena isinya potongan SQL yang Anda bangun sendiri, bukan input mentah.
  4. Empat pemanggilan memperlihatkan empat bentuk query yang sama: 12 baris tanpa filter, 2 baris purchase, 3 baris kanal aplikasi, dan 4 baris rentang 7–8 Januari — semuanya dari satu function, tanpa satu pun cabang query terpisah.

Generic Audit Trigger: Nama Tabel dari Trigger Context #

Penerapan penutup berbalik arah: bukan Anda yang menyuntikkan nama tabel ke string — trigger context yang menyediakannya. Function trigger yang sama dapat dipasang di banyak tabel; variabel TG_TABLE_NAME membawa nama tabel yang sedang berubah dan TG_OP membawa operasinya, sementara NEW/OLD diubah to_jsonb() menjadi data yang bisa disimpan tanpa memedulikan bentuk kolomnya:

sql pemicu-audit.sql 
-- Generic audit trigger: SATU function trigger dipasang di banyak tabel.
-- Nama tabel tidak ditulis tangan -- trigger menyediakannya lewat variabel
-- TG_TABLE_NAME, dan isi baris lewat NEW/OLD yang diubah menjadi jsonb.
-- Inilah dynamic SQL dari trigger context: function yang sama bekerja
-- untuk semua tabel ber-kolom id.
CREATE FUNCTION catat_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO log_audit (tabel, operasi, id_baris, data)
        VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
    ELSE
        INSERT INTO log_audit (tabel, operasi, id_baris, data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
    END IF;
    RETURN NULL;
END $$;

CREATE TRIGGER audit_produk
AFTER INSERT OR UPDATE OR DELETE ON produk
FOR EACH ROW EXECUTE FUNCTION catat_audit();

CREATE TRIGGER audit_event_log
AFTER UPDATE ON event_log
FOR EACH ROW EXECUTE FUNCTION catat_audit();

-- Tiga operasi pada produk dan satu pada event_log -- semuanya tercatat
-- oleh function trigger yang sama.
INSERT INTO produk (id, nama, harga) VALUES (6, 'Tiket Webinar Gratis', 0);

UPDATE produk SET harga = 1050000.00 WHERE id = 5;

DELETE FROM produk WHERE id = 4;

UPDATE event_log SET keterangan = 'katalog promo' WHERE id = 2;

-- Kolom waktu sengaja tidak ditampilkan (berisi now(), berbeda tiap
-- eksekusi); empat kolom ini deterministik dan bisa dibandingkan ulang.
SELECT tabel, operasi, id_baris, data
FROM log_audit
ORDER BY id;

SELECT id, nama, harga
FROM produk
ORDER BY id;

Output yang Dihasilkan:

text 
CREATE FUNCTION
CREATE TRIGGER
CREATE TRIGGER
INSERT 0 1
UPDATE 1
DELETE 1
UPDATE 1
   tabel   | operasi | id_baris |                                                  data                                                   
-----------+---------+----------+---------------------------------------------------------------------------------------------------------
 produk    | INSERT  |        6 | {"id": 6, "nama": "Tiket Webinar Gratis", "harga": 0.00}
 produk    | UPDATE  |        5 | {"id": 5, "nama": "Bundel Webinar", "harga": 1050000.00}
 produk    | DELETE  |        4 | {"id": 4, "nama": "Sertifikat Digital", "harga": 150000.00}
 event_log | UPDATE  |        2 | {"id": 2, "jenis": "page_view", "kanal": "web", "tanggal": "2026-01-05", "keterangan": "katalog promo"}
(4 rows)

 id |          nama          |   harga    
----+------------------------+------------
  1 | Tiket Seminar Database |  750000.00
  2 | Tiket Workshop Data    | 1250000.00
  3 | Modul Video SQL        |  400000.00
  5 | Bundel Webinar         | 1050000.00
  6 | Tiket Webinar Gratis   |       0.00
(5 rows)

Empat baris log_audit mencatat empat operasi: INSERT produk 6, UPDATE harga produk 5, DELETE produk 4, dan UPDATE keterangan event_log 2 — semuanya oleh satu function. Cabang TG_OP = 'DELETE' perlu karena NEW kosong pada operasi hapus, sehingga OLD yang harus dibaca. to_jsonb() menyimpan seluruh baris apa pun bentuk kolomnya — inilah alasan audit generik hampir selalu memilih jsonb daripada kolom tetap. Perhatikan pula pemilihan kolom di SELECT: waktu berisi now() sehingga disembunyikan demi output yang dapat dibandingkan ulang; di produksi, kolom itu justru yang paling sering dibaca.

Kendali penuh atas SQL yang dibangun runtime menuntaskan peralatan section ini. Satu hal tersisa sebelum pindah section: mengumpulkan seluruh pola yang telah dilatih — pertumbuhan bulanan, perbandingan tahunan, kumulatif, top-k, histogram, persentil, dan pivot — menjadi satu rak resep siap pakai di Query Pattern untuk Data Analisis .

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

buat-tabel.sql

Unduh berkas
sql 
-- Tabel latihan chapter Dynamic SQL: produk, log event harian, tabel
-- konfigurasi yang dipantau, tabel penampung hasil demo DO block, dan
-- tabel audit untuk demo generic trigger.
CREATE TABLE produk (
    id    integer PRIMARY KEY,
    nama  text NOT NULL,
    harga numeric(10,2) NOT NULL
);

INSERT INTO produk (id, nama, harga) VALUES
    (1, 'Tiket Seminar Database', 750000.00),
    (2, 'Tiket Workshop Data',    1250000.00),
    (3, 'Modul Video SQL',         400000.00),
    (4, 'Sertifikat Digital',      150000.00),
    (5, 'Bundel Webinar',          990000.00);

CREATE TABLE event_log (
    id         integer PRIMARY KEY,
    jenis      text NOT NULL,
    kanal      text NOT NULL,
    tanggal    date NOT NULL,
    keterangan text NOT NULL
);

INSERT INTO event_log (id, jenis, kanal, tanggal, keterangan) VALUES
    ( 1, 'page_view',   'web',      DATE '2026-01-05', 'beranda'),
    ( 2, 'page_view',   'web',      DATE '2026-01-05', 'katalog'),
    ( 3, 'add_to_cart', 'web',      DATE '2026-01-05', 'bundel webinar'),
    ( 4, 'page_view',   'aplikasi', DATE '2026-01-06', 'beranda'),
    ( 5, 'purchase',    'web',      DATE '2026-01-06', 'tiket seminar'),
    ( 6, 'page_view',   'web',      DATE '2026-01-07', 'katalog'),
    ( 7, 'checkout',    'aplikasi', DATE '2026-01-07', 'tiket workshop'),
    ( 8, 'page_view',   'web',      DATE '2026-01-08', 'beranda'),
    ( 9, 'purchase',    'aplikasi', DATE '2026-01-08', 'modul video'),
    (10, 'page_view',   'web',      DATE '2026-01-09', 'katalog'),
    (11, 'add_to_cart', 'web',      DATE '2026-01-09', 'sertifikat digital'),
    (12, 'checkout',    'web',      DATE '2026-01-10', 'bundel webinar');

-- Tabel konfigurasi: nama tabel yang harus diproses job maintenance.
-- Isinya data, bukan kode -- job membaca nama tabel dari sini saat runtime.
CREATE TABLE tabel_terpantau (
    id         integer PRIMARY KEY,
    skema      text NOT NULL,
    nama_tabel text NOT NULL,
    keterangan text NOT NULL
);

INSERT INTO tabel_terpantau (id, skema, nama_tabel, keterangan) VALUES
    (1, 'public', 'produk',    'master produk dan harga'),
    (2, 'public', 'event_log', 'log event harian');

-- Penampung hasil demo DO block: DO tidak bisa mengembalikan result set,
-- jadi hasil EXECUTE ditangkap lewat INTO lalu dicatat ke tabel ini.
CREATE TABLE hasil_demo (
    id         integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    demo       text NOT NULL,
    keterangan text NOT NULL
);

-- Tabel audit untuk demo generic trigger di akhir chapter.
-- Kolom waktu diisi now() oleh trigger tetapi tidak pernah ditampilkan
-- agar output contoh tetap deterministik.
CREATE TABLE log_audit (
    id       integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tabel    text NOT NULL,
    operasi  text NOT NULL,
    id_baris integer NOT NULL,
    data     jsonb NOT NULL,
    waktu    timestamp NOT NULL DEFAULT now()
);

SELECT id, skema, nama_tabel, keterangan
FROM tabel_terpantau
ORDER BY id;

SELECT id, nama, harga
FROM produk
ORDER BY id;

execute-dasar.sql

Unduh berkas
sql 
-- EXECUTE di PL/pgSQL: menjalankan string SQL saat runtime, menangkap
-- hasilnya dengan INTO, mengikat parameter dengan USING, dan membaca
-- nama tabel dari tabel konfigurasi (loop).

-- (1) EXECUTE format(...) INTO: nama tabel hidup di variabel, %I
-- mengutipnya dengan benar, dan hasil agregasi masuk variabel jumlah.
DO $$
DECLARE
    nama_tabel text := 'produk';
    jumlah     integer;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I', nama_tabel)
        INTO jumlah;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('hitung', nama_tabel || ' berisi ' || jumlah || ' baris');
END $$;

-- (2) EXECUTE ... USING: nilai berpindah lewat parameter $1, bukan
-- penyambungan string -- literal tidak pernah dipegang tangan.
DO $$
DECLARE
    harga_min numeric := 500000;
    jumlah    integer;
BEGIN
    EXECUTE 'SELECT count(*) FROM produk WHERE harga >= $1'
        INTO jumlah
        USING harga_min;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('using', 'produk dengan harga >= 500000: ' || jumlah || ' baris');
END $$;

-- (3) Config-driven: job maintenance membaca daftar tabel dari
-- tabel_terpantau lalu menghitung baris tiap tabel lewat EXECUTE format.
-- Menambah tabel yang dipantau = INSERT satu baris konfigurasi, tanpa
-- menyentuh kode job sama sekali.
DO $$
DECLARE
    r      record;
    jumlah integer;
BEGIN
    FOR r IN SELECT skema, nama_tabel FROM tabel_terpantau ORDER BY id LOOP
        EXECUTE format('SELECT count(*) FROM %I.%I', r.skema, r.nama_tabel)
            INTO jumlah;
        INSERT INTO hasil_demo (demo, keterangan)
        VALUES ('konfigurasi', r.nama_tabel || ': ' || jumlah || ' baris');
    END LOOP;
END $$;

SELECT id, demo, keterangan
FROM hasil_demo
ORDER BY id;

format-vs-injection.sql

Unduh berkas
sql 
-- format() dan pengutipan: tiga placeholder yang wajib dipahami sebelum
-- menyusun SQL dinamis, lalu demo injeksi nyata (rentan vs aman).

-- (1) Tiga placeholder: %I mengutip identifier (nama tabel/kolom),
-- %L mengutip literal (nilai), %s menempel apa adanya (hanya untuk
-- bagian yang Anda kendalikan penuh, bukan input pengguna).
SELECT format('SELECT %I FROM %I WHERE %I = %L',
              'nama', 'produk', 'id', 3) AS contoh_aman;

SELECT format('SELECT %I FROM %I WHERE %I = %s',
              'nama', 'produk', 'id', 3) AS contoh_angka;

-- (2) Pasangan lama: quote_ident dan quote_literal mengutip satu nilai
-- satu-satu -- %I dan %L versi format() memakai keduanya di belakang layar.
SELECT quote_ident('Order Detail') AS ident_berspasi,
       quote_ident('select') AS ident_kata_kunci,
       quote_literal('O''Brien') AS literal_bertanda_kutip;

-- (3) Payload injeksi dilihat sebagai TEKS dulu: baris pertama disusun
-- penyambungan mentah, baris kedua lewat %L. Bedanya satu tanda kutip
-- ganda di posisi tepat -- dan itu yang membedahkan serangan dari teks.
SELECT 'SELECT count(*) FROM event_log WHERE keterangan = '''
       || 'tiket'' OR ''1''=''1' || '''' AS teks_query_rentan;

SELECT format('SELECT count(*) FROM event_log WHERE keterangan = %L',
              'tiket'' OR ''1''=''1') AS teks_query_aman;

-- (4) Eksekusi keduanya di dalam DO block, hasil ditangkap ke hasil_demo.
-- Versi rentan: payload meledak menjadi OR '1'='1' dan mengembalikan
-- SEMUA baris. Versi aman: %L membungkus payload sebagai literal biasa,
-- pencarian mengembalikan nol baris karena tidak ada keterangan bernilai
-- persis seperti itu.
DO $$
DECLARE
    carian text := 'tiket'' OR ''1''=''1';
    jumlah integer;
BEGIN
    EXECUTE 'SELECT count(*) FROM event_log WHERE keterangan = '''
            || carian || ''''
        INTO jumlah;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('rentan', 'input jahat lolos: ' || jumlah || ' baris cocok');

    EXECUTE format('SELECT count(*) FROM event_log WHERE keterangan = %L', carian)
        INTO jumlah;
    INSERT INTO hasil_demo (demo, keterangan)
    VALUES ('aman', 'payload jadi teks biasa: ' || jumlah || ' baris cocok');
END $$;

SELECT id, demo, keterangan
FROM hasil_demo
ORDER BY id;

injection-error.sql

Unduh berkas
sql 
-- Demo galat: nama tabel disambung mentah tanpa pengutipan. Petugas
-- mengetik "log event" (dengan spasi) di form admin; PostgreSQL membaca
-- "FROM log event" sebagai tabel log dengan alias event -- dan tabel
-- bernama log tidak pernah ada. EXECUTE berhenti dengan error; bandingkan
-- dengan format('... FROM %I', ...) yang tetap aman untuk nama apa pun.
DO $$
DECLARE
    nama_tabel text := 'log event';
BEGIN
    EXECUTE 'SELECT count(*) FROM ' || nama_tabel;
END $$;

pemicu-audit.sql

Unduh berkas
sql 
-- Generic audit trigger: SATU function trigger dipasang di banyak tabel.
-- Nama tabel tidak ditulis tangan -- trigger menyediakannya lewat variabel
-- TG_TABLE_NAME, dan isi baris lewat NEW/OLD yang diubah menjadi jsonb.
-- Inilah dynamic SQL dari trigger context: function yang sama bekerja
-- untuk semua tabel ber-kolom id.
CREATE FUNCTION catat_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO log_audit (tabel, operasi, id_baris, data)
        VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
    ELSE
        INSERT INTO log_audit (tabel, operasi, id_baris, data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
    END IF;
    RETURN NULL;
END $$;

CREATE TRIGGER audit_produk
AFTER INSERT OR UPDATE OR DELETE ON produk
FOR EACH ROW EXECUTE FUNCTION catat_audit();

CREATE TRIGGER audit_event_log
AFTER UPDATE ON event_log
FOR EACH ROW EXECUTE FUNCTION catat_audit();

-- Tiga operasi pada produk dan satu pada event_log -- semuanya tercatat
-- oleh function trigger yang sama.
INSERT INTO produk (id, nama, harga) VALUES (6, 'Tiket Webinar Gratis', 0);

UPDATE produk SET harga = 1050000.00 WHERE id = 5;

DELETE FROM produk WHERE id = 4;

UPDATE event_log SET keterangan = 'katalog promo' WHERE id = 2;

-- Kolom waktu sengaja tidak ditampilkan (berisi now(), berbeda tiap
-- eksekusi); empat kolom ini deterministik dan bisa dibandingkan ulang.
SELECT tabel, operasi, id_baris, data
FROM log_audit
ORDER BY id;

SELECT id, nama, harga
FROM produk
ORDER BY id;

where-builder.sql

Unduh berkas
sql 
-- Dynamic WHERE builder: pencarian dengan filter opsional. Setiap filter
-- yang diisi pengguna menyumbang satu predikat (sudah dikutip %L),
-- predikat dirangkai AND, lalu query final dijalankan RETURN QUERY
-- EXECUTE. Filter yang NULL tidak menyumbang apa pun -- tidak ada
-- "WHERE 1=1" dan tidak ada cabang query terpisah per kombinasi.
CREATE FUNCTION cari_event(
    p_jenis    text DEFAULT NULL,
    p_kanal    text DEFAULT NULL,
    p_mulai    date DEFAULT NULL,
    p_selesai  date DEFAULT NULL
) RETURNS SETOF event_log
LANGUAGE plpgsql AS $$
DECLARE
    kondisi text[] := ARRAY[]::text[];
    query   text;
BEGIN
    IF p_jenis IS NOT NULL THEN
        kondisi := array_append(kondisi, format('jenis = %L', p_jenis));
    END IF;
    IF p_kanal IS NOT NULL THEN
        kondisi := array_append(kondisi, format('kanal = %L', p_kanal));
    END IF;
    IF p_mulai IS NOT NULL THEN
        kondisi := array_append(kondisi, format('tanggal >= %L', p_mulai));
    END IF;
    IF p_selesai IS NOT NULL THEN
        kondisi := array_append(kondisi, format('tanggal <= %L', p_selesai));
    END IF;

    IF cardinality(kondisi) = 0 THEN
        query := 'SELECT * FROM event_log ORDER BY id';
    ELSE
        query := format('SELECT * FROM event_log WHERE %s ORDER BY id',
                        array_to_string(kondisi, ' AND '));
    END IF;

    RETURN QUERY EXECUTE query;
END $$;

-- Tanpa filter: seluruh 12 baris.
SELECT count(*) AS semua_event
FROM cari_event();

-- Satu filter: hanya purchase.
SELECT * FROM cari_event(p_jenis => 'purchase');

-- Filter kanal.
SELECT * FROM cari_event(p_kanal => 'aplikasi');

-- Filter rentang tanggal: kombinasi dua predikat dirangkai AND.
SELECT * FROM cari_event(p_mulai => DATE '2026-01-07',
                         p_selesai => DATE '2026-01-08');