Chapter SQL Injection menutup dengan satu janji: parameter $1 menutup celahnya karena strukturnya terkunci sebelum data tiba. Mekanisme itu punya nama resmi — prepared statement — dan keunggulannya tidak berhenti di keamanan. Sebuah query yang dijalankan berulang kali dengan nilai berbeda hanya perlu di-parse dan direncanakan sekali; eksekusi berikutnya tinggal memakai ulang hasilnya. Kombinasi dua manfaat inilah yang membuat prepared statement menjadi standar de facto aplikasi produksi: aman dari injeksi sekaligus hemat kerja server. Chapter ini membedah siklus hidupnya secara penuh di SQL — PREPARE, EXECUTE, DEALLOCATE — lengkap dengan alat bacanya, percobaan injeksi lewat parameter, dan perilaku cache rencana eksekusi yang berganti mode secara otomatis setelah lima kali eksekusi.

PREPARE dan EXECUTE #

PREPARE mendaftarkan sebuah statement bernama di sesi berjalan: teks SQL lengkap dengan lubang parameter $1, $2, dan seterusnya, plus daftar tipe parameternya. EXECUTE lalu menjalankan statement itu dengan nilai konkret. File berikut membuat dua tabel bahan latihan lebih dulu, lalu mendaftarkan dan menjalankan pencarian pesanan dengan dua nilai pelanggan yang berbeda:

sql siapkan-pesanan.sql 
-- Bahan latihan prepared statement: pesanan dan pelanggan
-- Toko Maju Jaya.
CREATE TABLE pesanan (
    id        integer PRIMARY KEY,
    pelanggan text NOT NULL,
    total     numeric(10,2) NOT NULL
);

INSERT INTO pesanan (id, pelanggan, total) VALUES
    (1, 'Budi', 150000.00),
    (2, 'Siti', 275000.00),
    (3, 'Budi', 99000.00);

CREATE TABLE pelanggan (
    id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama  text NOT NULL,
    email text NOT NULL UNIQUE
);

INSERT INTO pelanggan (nama, email) VALUES
    ('Budi', '[email protected]'),
    ('Siti', '[email protected]'),
    ('Ani',  '[email protected]');

Output yang Dihasilkan:

text 
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
sql prepare-dasar.sql 
-- Siklus dasar: PREPARE mendaftarkan statement bernama,
-- EXECUTE menjalankannya dengan nilai parameter.
PREPARE cari_pesanan(text) AS
    SELECT id, pelanggan, total
    FROM pesanan
    WHERE pelanggan = $1
    ORDER BY id;

EXECUTE cari_pesanan('Budi');

EXECUTE cari_pesanan('Siti');

-- Statement yang masih hidup di sesi ini bisa dibaca kembali.
SELECT name, parameter_types, result_types, from_sql
FROM pg_prepared_statements
ORDER BY name;

Output yang Dihasilkan:

text 
PREPARE
 id | pelanggan |   total   
----+-----------+-----------
  1 | Budi      | 150000.00
  3 | Budi      |  99000.00
(2 rows)

 id | pelanggan |   total   
----+-----------+-----------
  2 | Siti      | 275000.00
(1 row)

     name     | parameter_types |      result_types      | from_sql 
--------------+-----------------+------------------------+----------
 cari_pesanan | {text}          | {integer,text,numeric} | t
(1 row)

Mari kita bedah baris-baris pentingnya:

  1. PREPARE cari_pesanan(text) AS ... — nama statement diikuti daftar tipe parameter dalam kurung; di sini satu text. Teks SQL menyimpan lubang $1 yang menunggu nilai, bukan nilai itu sendiri.
  2. EXECUTE cari_pesanan('Budi') dan ('Siti') — dua eksekusi dengan nilai berbeda atas statement yang sama: hasil pertama dua baris (id 1 dan 3), hasil kedua satu baris (id 2). Struktur query tidak pernah ditulis ulang.
  3. Query pg_prepared_statements — view yang mencatat seluruh prepared statement di sesi berjalan. Kolom parameter_types menampilkan {text}, result_types menampilkan {integer,text,numeric} — tiga kolom hasil SELECT — dan from_sql bernilai t karena statement ini dibuat lewat perintah SQL PREPARE, bukan lewat protokol dari driver aplikasi.

Dua batasan desain perlu diingat sejak awal. Prepared statement berjangka hidup sesi: begitu koneksi ditutup, seluruh statement ikut hilang — tidak ada yang tersimpan di database secara permanen. Dan statement hanya dikenali namanya: PREPARE dengan nama yang sudah terpakai di sesi yang sama ditolak dengan error, sehingga penamaan yang deskriptif sekaligus menjalankan DEALLOCATE lebih dulu adalah kebiasaan yang baik pada skrip panjang.

Injeksi Lewat Parameter Gagal Total #

Bukti kekuatan parameter paling meyakinkan adalah melemparkan payload jahat langsung ke dalamnya. File ini mendaftarkan statement INSERT dua parameter, lalu mengirim payload klasik Budi'; DROP TABLE pelanggan;-- sebagai nilai nama:

sql injeksi-lewat-parameter.sql 
-- Percobaan injeksi LEWAT parameter: payload masuk sebagai nilai,
-- disimpan apa adanya, tabel tetap utuh.
PREPARE simpan_pelanggan(text, text) AS
    INSERT INTO pelanggan (nama, email) VALUES ($1, $2);

EXECUTE simpan_pelanggan('Budi''; DROP TABLE pelanggan;--',
                         '[email protected]');

-- Payload klasik kini hanya sebuah nama pelanggan yang aneh.
SELECT id, nama, email
FROM pelanggan
ORDER BY id;

Output yang Dihasilkan:

text 
PREPARE
INSERT 0 1
 id |              nama              |         email         
----+--------------------------------+-----------------------
  1 | Budi                           | [email protected]
  2 | Siti                           | [email protected]
  3 | Ani                            | [email protected]
  4 | Budi'; DROP TABLE pelanggan;-- | [email protected]
(4 rows)

Hasilnya adalah kebalikan dari versi rawan di chapter sebelumnya. Baris INSERT 0 1 membuktikan pernyataan berhasil; query penutup memperlihatkan baris id 4 dengan kolom nama berisi persis Budi'; DROP TABLE pelanggan;-- — tanda kutip, DROP TABLE, komentar, semuanya — sebagai teks. Tabel pelanggan tetap berdiri dengan empat barisnya. Mekanisme di baliknya sederhana sekaligus kokoh: saat PREPARE dijalankan, parser sudah selesai membaca struktur SQL; nilai yang datang lewat EXECUTE tidak pernah melewati parser lagi, sehingga tidak ada cara apapun bagi nilai untuk berubah menjadi kode.

DEALLOCATE #

Siklus hidup prepared statement ditutup oleh DEALLOCATE. Tanpa argumen nama, perintah ini menghapus satu statement; DEALLOCATE ALL menghapus semuanya sekaligus — pilihan yang tepat di awal skrip panjang agar sisa statement dari percobaan sebelumnya tidak mengganggu. File berikut mendaftarkan, menghitung, menghapus, lalu membuktikan statement itu benar-benar tiada:

sql deallocate.sql 
-- DEALLOCATE menghapus prepared statement dari sesi.
PREPARE hitung_pesanan(text) AS
    SELECT count(*) AS jumlah
    FROM pesanan
    WHERE pelanggan = $1;

EXECUTE hitung_pesanan('Siti');

SELECT count(*) AS statement_hidup
FROM pg_prepared_statements;

DEALLOCATE hitung_pesanan;

SELECT count(*) AS statement_hidup
FROM pg_prepared_statements;

-- Statement sudah tidak ada: eksekusi berhenti dengan error.
EXECUTE hitung_pesanan('Siti');

Output yang Dihasilkan:

text 
psql:deallocate.sql:18: ERROR:  prepared statement "hitung_pesanan" does not exist

Baris statement_hidup berubah dari 1 menjadi 0 setelah DEALLOCATE — view pg_prepared_statements adalah cermin langsung dari isinya sesi. Eksekusi terakhir lalu gagal dengan ERROR: prepared statement "hitung_pesanan" does not exist: nama tanpa statement hanyalah nama, dan PostgreSQL menolak menebak. Error inilah yang muncul sebagai kejutan umum di skrip yang lupa urutan DEALLOCATE–PREPARE ulang setelah perubahan skema.

Kenapa Cepat: Parse Sekali, Eksekusi Berkali-kali #

Setiap query biasa menempuh empat tahap di server: parse (membaca struktur SQL), analyze (menyimpulkan tipe), plan (memilih rencana eksekusi), dan execute. Tiga tahap pertama bergantung pada teks query, bukan nilainya — persis bagian yang prepared statement kerjakan satu kali untuk selamanya. Sisanya, cache rencana, punya dinamika yang menarik sejak PostgreSQL 12: lima eksekusi pertama selalu direncanakan custom — planner tahu nilai parameternya dan memilih rencana terbaik untuk nilai itu. Setelah lima kali, planner mulai membandingkan biaya rata-rata rencana custom dengan biaya generic — rencana yang memperlakukan parameter sebagai nilai tak dikenal sehingga bisa dipakai untuk parameter apa pun; bila generic tidak lebih mahal, planner beralih ke sana.

File berikut membesarkan tabel pesanan menjadi 102 baris (101 milik Budi) supaya pilihan rencananya kelihatan, lalu mengeksekusi enam kali dan membaca kedua penghitungnya:

sql rencana-eksekusi.sql 
-- Cache rencana eksekusi: lima eksekusi pertama memakai rencana
-- custom, eksekusi keenam beralih ke rencana generic.
INSERT INTO pesanan (id, pelanggan, total)
SELECT g, 'Budi', 50000.00
FROM generate_series(4, 102) AS g;

CREATE INDEX idx_pesanan_pelanggan ON pesanan (pelanggan);

PREPARE hitung_pesanan(text) AS
    SELECT count(*) AS jumlah
    FROM pesanan
    WHERE pelanggan = $1;

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

-- Eksekusi keenam: biaya rata-rata rencana custom sudah bisa
-- dibandingkan dengan rencana generic.
EXECUTE hitung_pesanan('Budi');

SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
ORDER BY name;

-- Rencana generic memperlakukan $1 sebagai nilai tak dikenal.
EXPLAIN (COSTS OFF) EXECUTE hitung_pesanan('Siti');

Output yang Dihasilkan:

text 
INSERT 0 99
CREATE INDEX
PREPARE
 jumlah 
--------
    101
(1 row)

 jumlah 
--------
    101
(1 row)

 jumlah 
--------
    101
(1 row)

 jumlah 
--------
    101
(1 row)

 jumlah 
--------
    101
(1 row)

 jumlah 
--------
    101
(1 row)

      name      | generic_plans | custom_plans 
----------------+---------------+--------------
 hitung_pesanan |             1 |            5
(1 row)

            QUERY PLAN            
----------------------------------
 Aggregate
   ->  Seq Scan on pesanan
         Filter: (pelanggan = $1)
(3 rows)

Tiga pengamatan dari hasilnya:

  1. Enam baris jumlah = 101 — enam eksekusi atas statement yang sama, satu hasil identik. Parse dan analisis hanya terjadi sekali di PREPARE.
  2. custom_plans = 5, generic_plans = 1 — persis batas yang dijanjikan: lima rencana custom, lalu rencana generic untuk eksekusi keenam. Kolom ini tersedia di pg_prepared_statements sejak PostgreSQL 12.
  3. Filter: (pelanggan = $1) pada EXPLAIN — rencana yang dipakai memang generic: $1 ditampilkan sebagai parameter, bukan diganti nilainya. Untuk data ini rencana generic memilih Seq Scan — keputusan yang wajar karena 101 dari 102 baris bernilai Budi.

Prepared Statement di Sisi Aplikasi #

Perintah PREPARE di psql adalah wajah manual dari mekanisme yang lebih dalam: protokol extended query. Driver aplikasi tidak mengetik PREPARE sebagai teks — driver mengirim pesan Parse/Bind/Execute terpisah lewat protokol, dan hasilnya identik: struktur terkirim sekali, nilai mengalir per eksekusi. Sebagian driver bahkan menyiapkannya otomatis (psycopg dan pgx menyimpan statement bernama setelah beberapa eksekusi nilai yang sama), sehingga aplikasi mendapat manfaat cache rencana tanpa satu baris konfigurasi.

Dua catatan praktis menutup topik ini. Pertama, karena prepared statement hidup per sesi, pola connection pooling menuntut perhatian: statement bernama yang tidak dibersihkan bisa menetap di koneksi yang dipakai bergantian — DEALLOCATE ALL juga dikirim otomatis oleh sebagian pooler tepat untuk ini. Kedua, jangan menyiapkan ribuan statement berbeda dalam satu sesi; masing-masing menyimpan teks dan rencananya di memori server, dan yang tak terpakai lagi memang sebaiknya DEALLOCATE.

Prepared statement menutup celah injeksi dengan mengunci struktur query. Tetapi bila suatu saat struktur itu sendiri terkalahkan — fungsi yang lupa parameter, query dinamis yang tak terhindarkan — kerusakan yang bisa dilakukan penyerang ditentukan oleh hal terakhir di kolom pertahanan: hak akses role yang menjalankan query. Chapter penutup section ini, Least Privilege, menyusunnya dari nol: pemisahan role baca-tulis-DDL, batas antara privilege dan kepemilikan, sampai daftar periksa pengerasan server produksi.

Referensi #

Berkas Sumber

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

deallocate.sql

Unduh berkas
sql 
-- DEALLOCATE menghapus prepared statement dari sesi.
PREPARE hitung_pesanan(text) AS
    SELECT count(*) AS jumlah
    FROM pesanan
    WHERE pelanggan = $1;

EXECUTE hitung_pesanan('Siti');

SELECT count(*) AS statement_hidup
FROM pg_prepared_statements;

DEALLOCATE hitung_pesanan;

SELECT count(*) AS statement_hidup
FROM pg_prepared_statements;

-- Statement sudah tidak ada: eksekusi berhenti dengan error.
EXECUTE hitung_pesanan('Siti');

injeksi-lewat-parameter.sql

Unduh berkas
sql 
-- Percobaan injeksi LEWAT parameter: payload masuk sebagai nilai,
-- disimpan apa adanya, tabel tetap utuh.
PREPARE simpan_pelanggan(text, text) AS
    INSERT INTO pelanggan (nama, email) VALUES ($1, $2);

EXECUTE simpan_pelanggan('Budi''; DROP TABLE pelanggan;--',
                         '[email protected]');

-- Payload klasik kini hanya sebuah nama pelanggan yang aneh.
SELECT id, nama, email
FROM pelanggan
ORDER BY id;

prepare-dasar.sql

Unduh berkas
sql 
-- Siklus dasar: PREPARE mendaftarkan statement bernama,
-- EXECUTE menjalankannya dengan nilai parameter.
PREPARE cari_pesanan(text) AS
    SELECT id, pelanggan, total
    FROM pesanan
    WHERE pelanggan = $1
    ORDER BY id;

EXECUTE cari_pesanan('Budi');

EXECUTE cari_pesanan('Siti');

-- Statement yang masih hidup di sesi ini bisa dibaca kembali.
SELECT name, parameter_types, result_types, from_sql
FROM pg_prepared_statements
ORDER BY name;

rencana-eksekusi.sql

Unduh berkas
sql 
-- Cache rencana eksekusi: lima eksekusi pertama memakai rencana
-- custom, eksekusi keenam beralih ke rencana generic.
INSERT INTO pesanan (id, pelanggan, total)
SELECT g, 'Budi', 50000.00
FROM generate_series(4, 102) AS g;

CREATE INDEX idx_pesanan_pelanggan ON pesanan (pelanggan);

PREPARE hitung_pesanan(text) AS
    SELECT count(*) AS jumlah
    FROM pesanan
    WHERE pelanggan = $1;

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

EXECUTE hitung_pesanan('Budi');

-- Eksekusi keenam: biaya rata-rata rencana custom sudah bisa
-- dibandingkan dengan rencana generic.
EXECUTE hitung_pesanan('Budi');

SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
ORDER BY name;

-- Rencana generic memperlakukan $1 sebagai nilai tak dikenal.
EXPLAIN (COSTS OFF) EXECUTE hitung_pesanan('Siti');

siapkan-pesanan.sql

Unduh berkas
sql 
-- Bahan latihan prepared statement: pesanan dan pelanggan
-- Toko Maju Jaya.
CREATE TABLE pesanan (
    id        integer PRIMARY KEY,
    pelanggan text NOT NULL,
    total     numeric(10,2) NOT NULL
);

INSERT INTO pesanan (id, pelanggan, total) VALUES
    (1, 'Budi', 150000.00),
    (2, 'Siti', 275000.00),
    (3, 'Budi', 99000.00);

CREATE TABLE pelanggan (
    id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama  text NOT NULL,
    email text NOT NULL UNIQUE
);

INSERT INTO pelanggan (nama, email) VALUES
    ('Budi', '[email protected]'),
    ('Siti', '[email protected]'),
    ('Ani',  '[email protected]');