diskusi.tech (beta) Community

loading...
Cover image for Prepared Statement di Go

Prepared Statement di Go

mimindeeptech profile image Mimin Deep Tech ・6 min read

Ditulis oleh Kiki Luqman Hakiem

Salah satu keuntungan menggunakan bahasa pemrograman Go adalah pustaka standarnya cukup lengkap, mulai dari klien dan server untuk HTTP, kriptografi, koneksi ke basis data, termasuk didalamnya pooling dan prepared statement, dan sebagainya. Artikel ini akan difokuskan pada penggunaan prepared statement di Go.

Sebelum melangkah ke inti pembahasan, terlebih dahulu akan dijelaskan konsep prepared statement secara umum, bagaimana dia dijalankan dan keuntungan menggunakannya.

Konsep Prepared Statement

Prepared statement adalah sebuah fitur yang umum dijumpai di sistem manajemen basis data (Database Management System DBMS) yang memungkinkan kita untuk mengeksekusi perintah SQL (Structured Query Language) yang mirip (hanya berbeda parameter) dengan efisiensi tinggi.

Tahapan mengeksekusi perintah SQL menggunakan prepared statement adalah:

1. Prepare (persiapan).
Klien basis data, umumnya sebuah aplikasi, mengirimkan semacam template perintah SQL beserta placeholder parameter ke basis data.

2. Optimisasi.
Perintah SQL yang diterima oleh basis data akan diurai dan dioptimisasi, menghasilkan query plan. Query plan ini disimpan dan diikat ke satu koneksi, untuk kemudian digunakan oleh klien basis data. Prepared statement yang dihasilkan biasanya diberi pengenal/ID.

3. Eksekusi.
Klien basis data bisa menggunakan koneksi yang telah terasosiasi dengan prepared statement di atas untuk mengeksekusi perintah SQL berkali-kali. Pada tahap ini, klien basis data hanya perlu mengirimkan ID prepared statement dan parameter-parameter yang dibutuhkan melalui koneksi tersebut.

Alt Text
Prepared statement (kiri) dan unprepared (kanan)

Keuntungan Menggunakan Prepared Statement

Efisiensi tinggi dalam mengeksekusi perintah SQL menggunakan prepared statement bisa dicapai karena proses optimisasi perintah hanya dilakukan sekali dan perintah teroptimisasi tersebut bisa dipakai berulang kali. Hal ini bisa berdampak pada peningkatan performa perintah SQL. Berbeda dengan perintah SQL langsung (tanpa prepared statement), tahap optimisasi akan tetep dilakukan setiap kali perintah SQL dieksekusi.

Selain itu, parameter-parameter yang dikirim di tahap eksekusi akan disubstitusikan dengan placeholder di sisi basis data. Parameter-parameter ini tidak akan diurai/dikompilasi sebagaimana parameter pada perintah SQL langsung. Hal ini membuat perintah SQL dengan prepared statement aman dari injeksi SQL.

Perhatikan contoh kode perintah SQL langsung di bawah ini (kode #1):

rows, err := db.Query("select id, name from users where id = " + id)
Enter fullscreen mode Exit fullscreen mode

Jika variabel id bernilai 1, maka query yang dihasilkan (kode #2):

select id, name from users where id = 1
Enter fullscreen mode Exit fullscreen mode

Bayangkan jika nilai variabel id berasal dari masukan eksternal, misal masukan dari pengguna melalui form, pengguna tersebut memasukkan nilai berupa penggalan perintah SQL, misal 0 OR 1 = 1; drop table users, kecuali jika kita membersihkan (sanitize) variabel id tersebut di aplikasi kita, query yang dihasilkan (kode #3):

select id, name from users where id = 0 or 1 = 1; drop table users
Enter fullscreen mode Exit fullscreen mode

Query tersebut akan diurai oleh basis data dan dieksekusi.

Sementara itu, pada query berparameter (prepared statement biasanya berupa perintah SQL berparameter), nilai variabel id diletakkan pada placeholder. Placeholder hanya bisa diisi nilai yang sesuai dengan tipe data tertentu (kode #4).

rows, err := db.Query("select id, name from users where id = $1", id)
Enter fullscreen mode Exit fullscreen mode

Dengan asumsi bahwa kolom id mempunyai tipe data integer, nilai variabel id yang berupa penggalan perintah SQL di atas akan dianggap sebagai nilai yang tidak valid oleh basis data. Alih-alih perintah SQL tersebut dieksekusi, basis data akan mengembalikan error.

Untuk kolom dengan tipe data varchar misalnya, nilai variabel tersebut akan dibungkus dengan tanda petik. Query yang dihasilkan akan menjadi seperti (kode #5):

select id, name from users where id = "0 OR 1 = 1; drop table users"
Enter fullscreen mode Exit fullscreen mode

Query tersebut hanya akan mengembalikan set kosong karena tidak ditemukan user dengan id yang diminta.

Prepared Statement di Go

Pustaka standar Go (database/sql) mengabstraksikan akses ke basis data sehingga pengembang perangkat lunak tidak perlu menghadapi kerumitan dalam menangani pool koneksi dan prepared statement.

Semua perintah SQL akan dieksekusi menggunakan pool koneksi. Artinya kita tidak perlu membuka-tutup koneksi sebelum dan sesudah mengeksekusi perintah SQL. Koneksi yang tidak lagi dipakai akan dikembalikan ke pool dan bisa digunakan kembali untuk mengeksekusi perintah SQL lain. Dengan demikian jumlah koneksi ke basis data bisa dibatasi dan kebocoran koneksi bisa dihindari.

Demikian halnya dengan prepared statement, secara otomatis digunakan jika perintah SQL yang akan dieksekusi mengandung parameter (tergantung implementasi driver basis data, tapi hampir semua basis data yang kita gunakan menerapkan ini, termasuk Postgre). Perintah di bawah ini akan dieksekusi tanpa menggunakan prepared statement (kode #6):

for _, userID := range userIDs {
    rows, err := db.Query("select purchase_date, amount from orders where user_id = " + userID)
    // ...
}
Enter fullscreen mode Exit fullscreen mode

Sedangkan untuk perintah di bawah ini, di belakang layar, Go akan membuat koneksi prepared statement dan mengeksekusi perintah tersebut di dalam koneksi tersebut (kode #7):

for _, userID := range userIDs {
    rows, err := db.Query("select purchase_date, amount from orders where user_id = $1", userID)
    // ...
}
Enter fullscreen mode Exit fullscreen mode

Prepared statement digunakan tanpa secara eksplisit memanggil fungsi db.Prepare() atau tx.Prepare().

Namun dengan cara di atas, hanya satu dari dua manfaat prepared statement yang didapat, yaitu perlindungan terhadap SQL injection. Untuk mendapatkan manfaat prepared statement yang lain, yaitu peningkatan performa, kode di atas bisa diperbaiki menjadi (kode #8):

Aplikasi Prepared Statement di Dunia Nyata

Umumnya stmt.Query() dipanggil di suatu fungsi (misal: SelectOrdersByUserID), yang mana fungsi tersebut dipanggil di suatu controller, sehingga userID didapatkan dari permintaan HTTP yang berbeda-beda (kode #9):

func SelectOrdersByUserID(userID string) ([]Order, err) {
    rows, err := stmt.Query(userID)
    // ...
}
Enter fullscreen mode Exit fullscreen mode

Tentu tidak mungkin memanggil db.Prepare di fungsi tersebut karena tiap permintaan HTTP akan menghasilkan koneksi prepared statement yang berbeda. stmt.Query() akan dieksekusi di koneksi yang berbeda pula. Peningkatan performa yang diharapkan tidak akan tercapai.

Oleh karena itu, prepared statement bisa diset sebagai variabel global untuk memastikan bahwa tiap permintaan HTTP akan menggunakan prepared statement yang sama (kode #10):

// File: repository/order.go
import "database/sql"
​
var selectOrderByUserIDStmt *sql.Stmt
​
func InitOrderPreparedStatements(db *sql.DB) {
    selectOrderByUserIDStmt = db.Prepare("select purchase_date, amount from orders where user_id = $1")
}
​
func SelectOrdersByUserID(userID string) ([]Order, err) {
    rows, err := selectOrderByUserIDStmt.Query(userID)
    // ...
}
Enter fullscreen mode Exit fullscreen mode

Kita pastikan bahwa prepared statement hanya diinisialisasi sekali, yaitu pada saat aplikasi dimulai:

/ File: main.go
import "repository"
​
func main() {
    // initialize db
    // ...
    order.InitOrderPreparedStatements(db)
}
Enter fullscreen mode Exit fullscreen mode

Prepared Statement dalam Transaksi Basis Data

Suatu koneksi akan terikat pada satu transaksi basis data (transaction) hingga tx.Commit() atau tx.Rollback() dipanggil. Perintah-perintah SQL yang dijalankan dalam transaksi (tx.Query(), tx.Exec(), dan lain-lain) bisa dipastikan berjalan di koneksi yang sama.

Hal tersebut di atas juga berlaku untuk prepared statement. Prepared statement yang diinisialisasi dalam suatu transaksi, akan terikat pada koneksi yang sama dengan koneksi yang mengikat transaksi tersebut. Contoh penggunaan prepared statement dalam transaksi (kode #11):

tx, err := db.Begin()
// handle error
defer tx.Rollback()
​
stmt, err := tx.Prepare("insert into order_details ...")
// handle error
defer stmt.Close()
​
for _, detail := range orderDetails {
    stmt.Exec(detail.orderID, detail.qty)
}
​
// execute another SQL
​
err := tx.Commit()
Enter fullscreen mode Exit fullscreen mode

Kekurangan Prepared Statement di Go

Seperti kita ketahui bahwa prepared statement terikat pada satu koneksi basis data, sehingga stmt.Query() akan dieksekusi di koneksi yang sama. Go menggunakan pool koneksi, dan mengalokasikan koneksi secara dinamis. Tidak ada jaminan bahwa suatu koneksi akan selamanya terikat hanya pada satu prepared statement.

Objek Stmt, yang dikembalikan sewaktu db.Prepare() atau tx.Prepare() dieksekusi, akan 'mengingat' koneksi mana yang telah diikat dengan prepared statement. Lalu, bagaimana jika koneksi yang tadinya terikat dengan suatu prepared statement, karena semua koneksi di pool terpakai, sehingga koneksi tersebut dipakai untuk mengeksekusi perintah SQL lain?

Yang menarik adalah, ketika suatu koneksi di pool telah tersedia, Go akan mengambilnya, menginisialisasi kembali prepared statement pada koneksi tersebut, sehingga prepared statement bisa digunakan. Hal ini merupakan keunggulan, sekaligus kekurangan. Keunggulannya adalah tidak diperlukan untuk menginisialisasi kembali prepared statement secara eksplisit di kode kita.

Kekurangannya, di aplikasi yang menangani permintaan yang sangat besar dengan jumlah prepared statement yang sangat banyak, bisa menimbulkan waktu tambahan (overhead) yang sangat besar karena prepared statement harus diinisialisasi ulang terlalu sering. Penurunan performa juga bisa ditimbulkan oleh prepared statement yang jarang digunakan, atau diinisialisasi dan dieksekusi sekali saja (seperti pada kode #6). Oleh karena itu, jumlah prepared statement harus dibatasi hanya untuk perintah SQL yang sangat sering dieksekusi.

Menghindari Prepared Statement

Perintah SQL tanpa parameter secara otomatis dieksekusi tanpa prepared statement. Kita bisa hindari prepared statement yaitu dengan cara menggabungkan (concat — kode #1) atau interpolasi string (menggunakan fmt.Sprintf() — kode #12):

func DeleteOrderByUserIDAndSKU(db *sql.DB, userID, sku string) error {
    // sanitize userID & sku
    query := fmt.Sprintf(`delete from orders where user_id = '%s' and sku = '%s'`, userID, sku)
    db.Exec(query)
}
Enter fullscreen mode Exit fullscreen mode

Tentunya pastikan parameter sudah dibersihkan (sanitized) terlebih dahulu, untuk menghindari serangan injeksi SQL.

Kesimpulan

Prepared statement adalah fitur yang umum dijumpai di sistem manajemen basis data. Prepared statement bisa digunakan untuk meningkatkan performa aplikasi, khususnya dari segi akses ke basis data.

Pustaka standar Go mendukung prepared statement. Dengan mengenali karakteristik dan mengetahui perilakunya, kita bisa menggunakan prepared statement secara efektif.

Discussion

pic
Editor guide
Collapse
smilelikeshit profile image
imam m

mantappp terima kasih