Prepared Statement SQLite: prepare, bind, step

Apa Sebenarnya Prepared Statement Itu

Saat kamu memberikan sebuah string SQL ke SQLite, SQLite harus melakukan pekerjaan nyata sebelum ada baris yang berpindah: memecahnya menjadi token, mem-parse-nya, memeriksa bahwa tabel dan kolomnya ada, merencanakan cara mengeksekusinya, dan mengompilasi rencana itu menjadi bytecode untuk mesin virtual SQLite. Baru setelah itu query-nya benar-benar berjalan.

Prepared statement adalah hasil yang kamu dapat jika berhenti di tahap "dikompilasi menjadi bytecode" lalu menyimpan hasilnya. Program yang sudah dikompilasi itu punya slot, yaitu placeholder, tempat nilai-nilai sebenarnya akan diisi nanti. Kamu bisa menjalankan program terkompilasi yang sama berkali-kali dengan nilai yang berbeda, dan kamu bisa menjalankannya dengan aman meskipun nilainya berasal dari input yang tidak tepercaya.

Bayangkan perbedaannya seperti memberi seseorang resep untuk dibacakan keras-keras setiap kali memasak, dibandingkan mengajarinya resep itu sekali lalu cukup menyebutkan bahan-bahannya di hari memasak.

Siklusnya: prepare, bind, step, finalize

Setiap driver SQLite di setiap bahasa membungkus empat panggilan C API yang sama. Mengetahui namanya membantu meskipun kamu tidak akan pernah menulis C, karena pesan error dan dokumentasi memakai istilah-istilah ini:

1. sqlite3_prepare_v2: mengompilasi string SQL menjadi statement handle.
2. sqlite3_bind_*: mengisi nilai-nilai placeholder (satu fungsi untuk setiap tipe).
3. sqlite3_step: menjalankan programnya. Untuk SELECT, panggil berulang kali untuk menelusuri baris. Untuk INSERT/UPDATE/DELETE, satu panggilan sudah menyelesaikan pekerjaannya.
4. sqlite3_finalize: melepaskan program terkompilasi saat kamu sudah selesai.

Di antara langkah-langkah itu, sqlite3_reset memutar ulang statement yang sudah selesai sehingga kamu bisa mengikat ulang nilai dan menjalankannya lagi tanpa melakukan prepare ulang.

Placeholder di Dalam SQL

Di dalam string SQL, kamu menandai setiap tempat nilai dengan placeholder, bukan menyisipkan nilainya langsung. SQLite mendukung beberapa bentuk:

-- Anonymous, positional:
INSERT INTO users (name, email) VALUES (?, ?);

-- Numbered:
INSERT INTO users (name, email) VALUES (?1, ?2);

-- Named:
INSERT INTO users (name, email) VALUES (:name, :email);
INSERT INTO users (name, email) VALUES (@name, @email);
INSERT INTO users (name, email) VALUES ($name, $email);

? paling umum dipakai di kode tingkat driver. Placeholder bernama (:name) lebih enak dibaca jika parameternya banyak atau jika nilai yang sama muncul lebih dari sekali. Pilih satu gaya per proyek dan konsisten memakainya.

Yang tidak boleh dilakukan adalah membangun query dengan menggabungkan string:

-- DO NOT DO THIS:
"INSERT INTO users (name) VALUES ('" + user_input + "')"

Itu jalan menuju SQL injection, dan cara itu juga menghilangkan manfaat pemakaian ulang bytecode yang akan kamu baca sebentar lagi.

Contoh Lengkap di SQL

Untuk melihat mekanismenya tanpa bahasa host, berikut padanan prepare/bind/step yang hanya memakai fitur SQL dari SQLite. Siapkan tabel lalu masukkan baris dengan placeholder bergaya parameter yang diisi literal:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Boris', 'boris@example.com');

SELECT id, name, email FROM users ORDER BY id;

Di aplikasi sungguhan, kamu tidak akan menulis nilainya langsung. Kamu akan melakukan prepare pada INSERT sekali dengan placeholder ?, ?, lalu bind pasangan nama dan email untuk setiap pengguna, lalu step. Bytecode terkompilasinya sama persis untuk setiap panggilan; hanya nilai yang diikat yang berubah.

Memakai Ulang Statement (Keuntungan Performanya)

Inilah pola yang bisa kamu tulis dengan driver-mu. Ini pseudocode, karena setiap bahasa menuliskannya sedikit berbeda, tapi bentuknya universal:

-- prepared once:
INSERT INTO users (name, email) VALUES (?, ?);

-- then, in a loop:
--   bind(1, name)
--   bind(2, email)
--   step()
--   reset()

Prepare mem-parse dan mengompilasi SQL-nya sekali. Setiap iterasi hanya menjalankan bytecode dan menyalin nilai ke slot. Untuk bulk insert (misalnya mengimpor 100.000 baris), cara ini jauh lebih cepat daripada mengeksekusi 100.000 pernyataan yang di-parse terpisah, sering kali sampai sepuluh kali lipat, terutama jika dibungkus dalam satu transaksi.

Jebakan yang umum: orang membuat perulangan dan memanggil prepare di dalam perulangan itu. Itu membuang seluruh manfaatnya. Lakukan prepare di luar perulangan, lalu bind dan step di dalamnya.

Kenapa Ini Cara yang Aman

Parameter yang diikat bukanlah string yang disisipkan ke SQL. Parameter itu adalah nilai yang diberikan ke program bytecode lewat slot bertipe: slot integer, slot teks, slot blob. SQLite tidak pernah mem-parse ulang nilai itu sebagai SQL, jadi tidak ada nilai yang bisa mengubah struktur query.

Bandingkan:

-- Vulnerable. If user_input is:  '); DROP TABLE users;--
-- the query becomes destructive.
"SELECT * FROM users WHERE name = '" + user_input + "'"

-- Safe. user_input is bound as a TEXT value and only ever
-- compared as a string, no matter what it contains.
SELECT * FROM users WHERE name = ?;

Bentuk kedua tetap aman meskipun user_input berisi '); DROP TABLE users;--. SQLite akan dengan patuh mencari pengguna yang namanya persis string (aneh) itu, tidak menemukan siapa pun, dan mengembalikan nol baris. Tidak ada bagian dari struktur query yang bisa bergeser karena nilainya.

Kita akan membahas injection lebih dalam di dokumen berikutnya, tapi intinya: prepared statement bukan sekadar salah satu pertahanan terhadap SQL injection, melainkan pertahanan utamanya.

Statement yang Mengembalikan Baris

Untuk SELECT, step mengembalikan satu baris setiap kali dipanggil. Driver biasanya melakukan perulangan sampai hasilnya "selesai":

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Notebook', 4.50),
    ('Pen',      1.20),
    ('Eraser',   0.80);

SELECT name, price FROM products WHERE price < 2.00 ORDER BY price;

Di kode aplikasi, driver akan melakukan prepare pada SELECT itu dengan ? di tempat 2.00, mengikat nilai batasnya, lalu memanggil step dalam perulangan, membaca satu baris per panggilan. Setelah baris terakhir, step melaporkan bahwa prosesnya selesai, lalu driver melakukan reset pada statement itu (untuk menjalankannya lagi dengan batas baru) atau finalize.

Jangan Lupa Finalize

Prepared statement adalah alokasi kecil di dalam SQLite. Membiarkannya bocor akan menghabiskan memori, dan yang lebih penting, memegang kunci internal pada database yang bisa memblokir penulis lain. Setiap driver memberimu cara untuk membersihkannya secara otomatis, misalnya context manager di Python, blok using di C#, RAII di C++, dan kamu sebaiknya memakainya:

• sqlite3 milik Python melakukan finalize saat cursor dibersihkan garbage collector, tapi cursor.close() yang eksplisit lebih rapi.
• better-sqlite3 (Node) melakukan finalize saat Statement dibersihkan garbage collector; prepared statement yang berumur panjang tidak masalah.
• Di C mentah, kamu memanggil sqlite3_finalize sendiri. Lupa melakukannya adalah bug sungguhan.

Patokannya: jika kamu melakukan prepare, harus ada yang melakukan finalize.

Kapan Kamu Mungkin Tidak Perlu Melakukannya Sendiri

Kamu jarang akan memanggil sqlite3_prepare_v2 secara langsung. Driver tingkat tinggi mengubah connection.execute("SELECT ... WHERE id = ?", (42,)) menjadi prepare/bind/step/finalize untukmu. Alasan memahami siklusnya adalah:

• Kamu akan mengenali apa yang terjadi saat melihat error "statement is busy" atau "cannot operate on a finalized statement".
• Kamu akan tahu kapan perlu menyimpan cache prepared statement yang berumur panjang saat memasukkan data dalam perulangan yang ketat.
• Kamu akan menulis query berparameter secara naluriah, bahkan saat menggabungkan string terlihat menggoda.

ORM dan query builder membawanya lebih jauh lagi. Keduanya membangun SQL, mengelola prepared statement, dan memberimu hasil yang bertipe. Di baliknya, semuanya adalah empat panggilan yang sama.

Selanjutnya: Mengikat Parameter

Kita sudah membahas placeholder secara abstrak. Berikutnya kita akan melihat sisi binding secara detail: parameter posisional vs bernama, penanganan tipe, NULL, dan jebakan-jebakan kecil yang muncul saat kamu mulai memasukkan data aplikasi sungguhan ke dalam query.