Query Berparameter

Prepared statement sebagai benteng utama pencegahan SQL injection.

Daftar isi

Topik ini membahas query berparameter (prepared statement), yaitu mekanisme pengiriman query ke server MySQL dengan memisahkan teks query dari nilai yang dikirimkannya. Pembahasan dimulai dari celah yang dibuka oleh perangkaian string, dilanjutkan perbaikannya pada driver pymysql dan mysql2, cara kerja mekanisme ini pada driver dan server, serta batasannya. Setelah menyelesaikannya, query yang menerima masukan pengguna dapat ditulis dengan aman pada kedua bahasa tersebut.

Perangkaian string yang membuka celah#

Fungsi masuk anggota merupakan contoh kecil yang menerima dua nilai dari pengguna, yaitu nama dan kode_akses. Query pemeriksaannya disusun dengan menyambung string secara langsung, sehingga kedua nilai itu menempel pada teks SQL. Cara ini terlihat sederhana, tetapi menyebabkan server menafsirkan isi masukan sebagai bagian dari perintahnya. Basis data latihan berikut dipakai untuk menunjukkan akibatnya.

bash

# menyiapkan basis data latihan dari keadaan kosong
mysql -u root -e "DROP DATABASE IF EXISTS perpustakaan;"
mysql -u root -e "CREATE DATABASE perpustakaan;"
mysql -u root perpustakaan <<'SQL'
CREATE TABLE anggota (
  id INT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kode_akses VARCHAR(40) NOT NULL
);
INSERT INTO anggota (id, nama, kode_akses) VALUES
  (1, 'Ali', 'ali123'),
  (2, 'Budi', 'budi456'),
  (3, 'Citra', 'citra789'),
  (4, 'Dewi', 'dewi000');
SQL

Tabel anggota menyimpan nama beserta kode aksesnya dalam teks polos. Penyimpanan seperti ini dipilih semata-mata untuk memudahkan demonstrasi, sedangkan kode akses yang riil selalu disimpan dalam bentuk hash.

String injeksi yang klasik adalah ' OR '1'='1, yaitu masukan yang menutup kutip pembuka, menambahkan perbandingan yang selalu benar, lalu membuka kutipan baru. Apabila string ini disambungkan ke teks query pada kedua nilai, kondisi yang terbentuk perlu dibaca menurut urutan operator SQL, yaitu AND dievaluasi lebih dahulu daripada OR. Bagian '1'='1' yang selalu benar membuat kondisi keseluruhan terpenuhi untuk setiap baris tabel.

Menunjukkan celah dan perbaikannya di Python#

Bentuk query yang terbentuk dari perangkaian string ditampilkan berikut ini agar strukturnya terlihat jelas.

sql Query yang terbentuk dari perangkaian string

-- tidak-dijalankan
SELECT id, nama FROM anggota
WHERE nama = '' OR '1'='1' AND kode_akses = '' OR '1'='1';

Skrip berikut menjalankan dua versi query yang sama, yaitu versi perangkaian string dan versi berparameter, dengan masukan yang identik.

python berparameter.py

# tidak-dijalankan
import pymysql

masukan = "' OR '1'='1"

koneksi = pymysql.connect(host="127.0.0.1", user="root", database="perpustakaan")

try:
    with koneksi.cursor() as kursor:
        query_rentan = (
            "SELECT id, nama FROM anggota"
            " WHERE nama = '" + masukan + "'"
            " AND kode_akses = '" + masukan + "'"
        )
        print("SQL perangkaian:", query_rentan)
        kursor.execute(query_rentan)
        print("Baris kembali:", kursor.fetchall())

        query_aman = "SELECT id, nama FROM anggota WHERE nama = %s AND kode_akses = %s"
        kursor.execute(query_aman, (masukan, masukan))
        print("Baris kembali:", kursor.fetchall())
finally:
    koneksi.close()

Blok pertama menyusun teks SQL dengan menyambung masukan secara langsung, dan teks itulah yang tercetak pada baris pertama keluaran. Kondisi WHERE yang terbentuk memuat perbandingan yang selalu benar, sehingga server mengembalikan seluruh baris, termasuk baris anggota lain yang kode aksesnya tidak diketahui. Inilah SQL injection, yaitu penyusunan query yang tidak terkendali karena data pengguna diperlakukan sebagai kode. Blok kedua mengirim query dengan penanda %s, sedangkan nilainya dikirim sebagai pasangan terpisah pada execute(). Karena string injeksi kini dicari sebagai nilai literal yang tidak ada pada tabel, server tidak mengembalikan baris apa pun dan fetchall() mengembalikan ().

bash

# menjalankan demo injeksi dan demo berparameter
python3 berparameter.py

text Keluaran

SQL perangkaian: SELECT id, nama FROM anggota WHERE nama = '' OR '1'='1' AND kode_akses = '' OR '1'='1'
Baris kembali: ((1, 'Ali'), (2, 'Budi'), (3, 'Citra'), (4, 'Dewi'))
Baris kembali: ()

Empat baris anggota kembali pada percobaan pertama meskipun kode akses yang diketahui tidak ada satu pun, sedangkan percobaan kedua mengembalikan hasil kosong. Perbedaannya bukan pada isi query, melainkan pada cara nilai dikirim ke server.

Menunjukkan celah dan perbaikannya di Node.js#

Versi Node.js menampilkan dua metode yang berbeda. Metode query() mengirim teks SQL apa adanya sehingga dipakai untuk memperlihatkan perilaku perangkaian string, sedangkan metode execute() memakai prepared statement sebenarnya dan mengirim nilai secara terpisah.

js berparameter.js

// tidak-dijalankan
const mysql = require("mysql2/promise");

async function utama() {
  const koneksi = await mysql.createConnection({
    host: "127.0.0.1",
    user: "root",
    database: "perpustakaan",
  });

  try {
    const masukan = "' OR '1'='1";

    const queryRentan =
      "SELECT id, nama FROM anggota" +
      " WHERE nama = '" + masukan + "'" +
      " AND kode_akses = '" + masukan + "'";
    console.log("SQL perangkaian:", queryRentan);
    const [rentan] = await koneksi.query(queryRentan);
    console.log("Baris kembali:", rentan.length);

    const queryAman = "SELECT id, nama FROM anggota WHERE nama = ? AND kode_akses = ?";
    const [aman] = await koneksi.execute(queryAman, [masukan, masukan]);
    console.log("Baris kembali:", aman.length);
  } finally {
    await koneksi.end();
  }
}

utama();

Keluaran versi Node.js menyatakan jumlah baris alih-alih mencetak isi tiap baris, tetapi kesimpulannya sama dengan versi Python. Percobaan perangkaian string mengembalikan empat baris, sedangkan percobaan berparameter mengembalikan nol baris.

bash

# menjalankan demo injeksi dan demo berparameter dari Node.js
node berparameter.js

text Keluaran

SQL perangkaian: SELECT id, nama FROM anggota WHERE nama = '' OR '1'='1' AND kode_akses = '' OR '1'='1'
Baris kembali: 4
Baris kembali: 0

Cara kerja prepared statement pada driver dan server#

Server MySQL memang menyediakan mekanisme prepared statement yang sesungguhnya, yaitu pernyataan PREPARE untuk mengurai template beserta penanda parameternya dan pernyataan EXECUTE untuk mengirim nilainya secara terpisah. Karena nilai tidak pernah melewati pengurai SQL, tanda kutip maupun kata kunci di dalamnya tidak dapat mengubah struktur query. Rencana eksekusi hasil PREPARE juga dapat dipakai ulang bila pernyataan yang sama dieksekusi berkali-kali dalam satu sesi.

Driver menawarkan pola yang sama melalui dua jalan. mysql2 dengan metode execute() benar-benar memakai mekanisme dua tahap di server, sedangkan pymysql membentuk query di sisi klien dengan melakukan escaping terhadap setiap nilai secara konsisten sebelum mengirimnya. Kedua jalan menutup celah yang sama, yaitu aplikasi tidak lagi merangkai teks SQL dengan data mentah, sehingga masukan pengguna tidak pernah diperlakukan sebagai kode. PDO pada PHP secara bawaan mengemulasi prepared statement di sisi klien dan dapat disetel agar memakai mekanisme aslinya melalui atribut PDO::ATTR_EMULATE_PREPARES.

Ketiga driver pada modul ini menyediakan bentuk pemanggilan yang berbeda, dan tabel berikut merangkumnya.

Bahasa dan driver	Penanda	Pengiriman nilai
Python, pymysql	`%s`	`kursor.execute(sql, (p1, p2))`
Node.js, mysql2	`?`	`koneksi.execute(sql, [p1, p2])`
PHP, PDO	`?` atau `:nama`	`$stmt = $pdo->prepare(sql)` lalu `$stmt->execute([p1, p2])`

Batasan parameter dan praktik yang perlu dijaga#

Parameter hanya berlaku untuk nilai, yaitu literal string, angka, tanggal, dan sejenisnya. Nama tabel, nama kolom, dan arah pengurutan tidak dapat dijadikan parameter karena bagian itu sudah diurai sejak penyusunan rencana eksekusi. Apabila bagian struktur query harus dinamis, nilai yang masuk diverifikasi terhadap daftar putih (whitelist), yaitu senarai nama yang diizinkan, sebelum digabungkan ke teks query.

Praktik lain yang perlu dijaga adalah mengirim seluruh nilai dinamis melalui parameter tanpa kecuali, termasuk angka yang tampak aman, karena kebiasaan menyambung string kembali tumbuh dari pengecualian kecil. Query yang sudah berparameter juga tidak perlu ditambah pengutipan manual, karena lapisan ganda itu mengubah isi datanya.

Kesalahan umum#

Urutan parameter yang tertukar tidak menimbulkan error. Query tetap sah dan server mengembalikan hasil kosong, sehingga kesalahannya baru terasa pada perilaku aplikasi. Contoh berikut menukar nilai nama dan kode_akses lalu memperbaikinya.

bash

# menunjukkan akibat urutan parameter yang tertukar
python3 <<'PY'
import pymysql
koneksi = pymysql.connect(host="127.0.0.1", user="root", database="perpustakaan")
with koneksi.cursor() as kursor:
    kursor.execute(
        "SELECT id, nama FROM anggota WHERE nama = %s AND kode_akses = %s",
        ("budi456", "Budi"),
    )
    print("Urutan terbalik:", kursor.fetchall())
    kursor.execute(
        "SELECT id, nama FROM anggota WHERE nama = %s AND kode_akses = %s",
        ("Budi", "budi456"),
    )
    print("Urutan benar:", kursor.fetchall())
koneksi.close()
PY

text Keluaran

Urutan terbalik: ()
Urutan benar: ((2, 'Budi'),)

Kesalahan seperti ini tidak terdeteksi driver karena tipe datanya masih cocok. Memberi nama variabel yang jelas dan menguji query dengan data yang diketahui adalah penangkalnya.

Menganggap parameter mengamankan seluruh query. Sebagaimana dijelaskan pada bagian sebelumnya, nama tabel dan kolom tetap harus berasal dari kode program. Query yang menyambung nama tabel dari masukan pengguna tetap rentan meskipun nilainya sudah dikirim berparameter.

Ringkasan#

Perangkaian string membuat server menafsirkan masukan pengguna sebagai bagian dari SQL, dan string ' OR '1'='1 mengubah kondisi pemeriksaan agar selalu benar.
Query berparameter memisahkan teks query dari nilai, sehingga masukan tidak pernah mengubah struktur query baik di server maupun di sisi driver.
pymysql menandai parameter dengan %s, sedangkan mysql2 menandainya dengan ?, dan keduanya menerima nilai sebagai pasangan terpisah pada pemanggilan eksekusinya.
Parameter hanya mencakup nilai, sedangkan nama tabel, kolom, dan klausa dinamis ditangani dengan daftar putih pada kode aplikasi.

Referensi#

SQL Prepared Statements — Oracle — Mekanisme PREPARE dan EXECUTE pada server MySQL 8.0.
Prepared Statements and Stored Procedures — The PHP Group — Perilaku prepared statement pada PDO, termasuk pemilihan emulasinya.
PyMySQL Cursors — PyMySQL Project — Kontrak execute() beserta penanda parameter %s.
SQL Injection Prevention Cheat Sheet — OWASP Foundation — Prinsip pencegahan SQL injection dengan query berparameter.