Basis Data

Koneksi basis data dengan OleDb/SqlClient dan query berparameter yang aman.

Daftar isi

Program yang datanya dibagi antarpengguna atau dicari dengan cepat membutuhkan basis data, bukan sekadar berkas teks. .NET menyiapkan ADO.NET sebagai lapisan akses data bawaan, yaitu kumpulan kelas untuk membuka koneksi, mengirim perintah, dan membaca hasilnya. Topik ini memetakan tiga penyedia data (provider) yang paling sering ditemui, menjelaskan teks koneksi (connection string) beserta kredensialnya, membahas query berparameter sebagai pelindung SQL injection, dan menutupnya dengan demonstrasi lengkap yang dijalankan menggunakan Microsoft.Data.Sqlite.

Peta provider ADO.NET#

ADO.NET bekerja dengan pola kelas yang sama untuk semua sumber data, yaitu kelas koneksi, kelas perintah, kelas parameter, dan kelas pembaca hasil. Implementasi untuk satu jenis sumber data disebut provider, dan setiap provider menyalin pola itu dengan nama kelasnya sendiri. Pemilihan provider ditentukan oleh sumber data yang dituju, sedangkan struktur kode dari koneksi sampai pembacaan hasil tetap sama antarprovider. Tabel berikut merangkum tiga provider yang paling relevan untuk VB.

Provider	Bawaan atau paket	Sumber data	Penanda parameter
System.Data.OleDb	paket NuGet System.Data.OleDb	Access, Excel, dan sumber OLE DB lawas	`?` berurutan
System.Data.SqlClient atau Microsoft.Data.SqlClient	bawaan .NET Framework atau paket NuGet	SQL Server	`@nama`
Microsoft.Data.Sqlite	paket NuGet Microsoft.Data.Sqlite	satu berkas SQLite	`@nama` atau `?`

Kebutuhan kredensial ditulis di dalam teks koneksi, yaitu teks berisi pasangan kunci dan nilai yang menyebut alamat server, nama basis data, dan cara masuknya. Contoh Server=localhost;Database=Arsip;Trusted_Connection=True; memanfaatkan akun Windows, sedangkan bentuk User ID=...;Password=... menuliskan kredensialnya langsung, sehingga teks koneksi semacam itu lebih baik disimpan di berkas konfigurasi alih-alih di kode.

Koneksi dan perintah dengan SqlClient#

Fragmen berikut menampilkan pola lengkap SqlClient untuk SQL Server. Sumber datanya adalah basis data Arsip di server lokal, dan aksesnya memanfaatkan keamanan terintegrasi Windows sehingga tidak ada kata sandi di dalam kode. Eksekusinya membutuhkan SQL Server yang berjalan, sehingga fragmen ini tidak dijalankan di sandbox.

vb Contoh: pola koneksi SqlClient

' tidak-dijalankan
Imports System.Data.SqlClient

Dim strKoneksi As String =
    "Server=localhost;Database=Arsip;Trusted_Connection=True;"
Using koneksi As New SqlConnection(strKoneksi)
    koneksi.Open()
    Using perintah As New SqlCommand(
        "SELECT Id, Judul FROM Catatan WHERE Id > @batas", koneksi)
        perintah.Parameters.AddWithValue("@batas", 10)
        Using pembaca As SqlDataReader = perintah.ExecuteReader()
            Do While pembaca.Read()
                Console.WriteLine($"{pembaca.GetInt32(0)}: {pembaca.GetString(1)}")
            Loop
        End Using
    End Using
End Using

Blok Using tiga lapis menutup pembaca, perintah, dan koneksi dalam urutan yang benar meskipun exception terjadi. Metode ExecuteReader mengembalikan pembaca baris yang diproses perulangan Do While, sedangkan perintah INSERT dan UPDATE menggunakan ExecuteNonQuery yang mengembalikan jumlah baris terdampak.

Parameter pada SqlClient ditulis dengan nama yang diawali tanda @, dan AddWithValue mencari posisinya melalui nama yang sama. Urutan pembentukannya adalah membuka koneksi, menyusun perintah, mengisi parameter, lalu mengeksekusinya. ExecuteNonQuery mengembalikan jumlah baris yang berubah sebagai Integer, sehingga pemanggilan INSERT dapat dipastikan menambah tepat satu baris. Karena itu pola Using bukan sekadar gaya penulisan, melainkan jaminan bahwa koneksi tertutup pada semua jalur eksekusi.

Parameter berposisi pada OleDb#

Provider System.Data.OleDb ditujukan untuk sumber data lawas seperti berkas Access dan Excel yang diakses melalui driver OLE DB. Struktur koneksinya mengikuti pola SqlClient, tetapi penanda parameternya berbeda, yaitu tanda tanya yang diisi sesuai urutan. Karena itu urutan penambahan parameter menjadi bagian dari logika program dan tidak boleh ditukar.

vb Contoh: parameter berposisi pada OleDb

' tidak-dijalankan
Imports System.Data.OleDb

Using koneksi As New OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=arsip.accdb")
    koneksi.Open()
    Dim tambah As New OleDbCommand(
        "INSERT INTO Catatan (Judul, Isi) VALUES (?, ?)", koneksi)
    tambah.Parameters.AddWithValue("judul", "Dokumen 'pagi'")
    tambah.Parameters.AddWithValue("isi", "Catatan pagi disusun ulang")
    tambah.ExecuteNonQuery()
End Using

Tanda tanya pertama diisi nilai judul dan tanda tanya kedua diisi nilai isi, sesuai urutan penambahannya. Parameter berposisi menuntut kedisiplinan karena compiler tidak dapat memeriksa kecocokan antara tanda tanya dan nilainya. Apabila urutan kolom pada INSERT berubah, urutan AddWithValue harus mengikutinya, dan kesalahan urutan baru terlihat saat data masuk ke kolom yang salah. Provider OleDb sendiri diperoleh melalui paket NuGet System.Data.OleDb, sehingga proyek .NET modern tetap dapat mengakses sumber lawas tanpa komponen tambahan selain drivernya.

Driver OLE DB ACE untuk Access harus terpasang di mesin Windows sebelum teks koneksi Provider=Microsoft.ACE.OLEDB.12.0;Data Source=arsip.accdb dapat dibuka. Provider ini dipilih ketika proyek harus mengambil data dari berkas Access atau Excel lama yang tidak disediakan oleh provider lain.

Query berparameter sebagai pelindung SQL injection#

Query berparameter mengirim teks SQL dan nilai parameternya secara terpisah, sehingga nilai tidak pernah dicampur ke dalam struktur perintah. Lawannya adalah penggabungan langsung yang berbahaya seperti berikut.

vb Contoh: penyusunan SQL yang rentan, jangan digunakan

' tidak-dijalankan
Dim sql As String =
    "SELECT Id, Judul FROM Catatan WHERE Judul LIKE '" & masukan & "'"

Apabila masukan berisi ' OR 1=1 --, teks SQL yang terbentuk memuat kondisi tambahan yang membuat seluruh baris lolos saringan. Teknik menanamkan teks semacam itu ke dalam perintah disebut SQL injection, dan ragam serangannya jauh lebih luas daripada contoh singkat ini. Ragam lainnya memanfaatkan komentar, operator penggabung, atau prosedur tersimpan, dan semuanya bermuara pada satu penyebab yang sama, yaitu data yang dianggap sebagai perintah. Dengan parameter, masukan yang sama hanya dicari sebagai teks biasa, karena mesin basis data menerima nilainya sebagai data dan bukan sebagai bagian perintah. Oleh karena itu setiap nilai yang berasal dari luar program, baik dari pengguna maupun dari berkas, dikirim melalui parameter.

Demonstrasi lengkap dengan Microsoft.Data.Sqlite#

Provider ketiga adalah Microsoft.Data.Sqlite, yang mengoperasikan basis data SQLite dalam satu berkas dan tanpa server. Karena OleDb dan SqlClient membutuhkan mesin basis data yang tidak berjalan di sandbox, demonstrasi lengkap topik ini menggunakan SQLite. Penyiapan proyeknya hanya dua perintah setelah folder dibuat.

bash

# tidak-dijalankan
mkdir AplikasiData
cd AplikasiData
dotnet new console -lang VB
dotnet add package Microsoft.Data.Sqlite

text Transkrip terminal

$ dotnet new console -lang VB
The template "Console App" was created successfully.

Processing post-creation actions...
Restoring /home/kal/my-project/build-scratch/berkas-data__sqlite/AplikasiData/AplikasiData.vbproj:
  Determining projects to restore...
  Restored /home/kal/my-project/build-scratch/berkas-data__sqlite/AplikasiData/AplikasiData.vbproj (in 76 ms).
Restore succeeded.
$ dotnet add package Microsoft.Data.Sqlite
info : PackageReference for package 'Microsoft.Data.Sqlite' version '10.0.14' added to file '/home/kal/my-project/build-scratch/berkas-data__sqlite/AplikasiData/AplikasiData.vbproj'.
log  : Restored /home/kal/my-project/build-scratch/berkas-data__sqlite/AplikasiData/AplikasiData.vbproj (in 177 ms).

Perintah dotnet new console -lang VB membuat proyek console VB, sedangkan dotnet add package menambah paket Microsoft.Data.Sqlite versi 10.0.14 beserta dependensinya. Berkas proyeknya kemudian disunting untuk mengaktifkan Option Strict On, dan berkas kode template diganti namanya menjadi AplikasiData.vb. Isi kedua berkasnya adalah sebagai berikut.

xml AplikasiData.vbproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <RootNamespace></RootNamespace>
    <TargetFramework>net10.0</TargetFramework>
    <OptionStrict>On</OptionStrict>
    <OptionExplicit>On</OptionExplicit>
    <OptionInfer>On</OptionInfer>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite" Version="10.0.14" />
  </ItemGroup>

</Project>

Programnya membuat tabel Catatan, memasukkan tiga baris dengan INSERT berparameter, lalu mencari baris yang judulnya memuat kata tertentu dengan SELECT berparameter. Judul contoh sengaja memuat tanda kutip satu untuk membuktikan bahwa nilai berparameter tidak perlu diloloskan secara manual.

vb AplikasiData.vb

Option Strict On

Imports Microsoft.Data.Sqlite

Module Program

    Sub Main()
        ' Menghapus berkas basis data contoh agar eksekusi ulang tetap idempoten
        If IO.File.Exists("arsip.db") Then
            IO.File.Delete("arsip.db")
        End If

        Using koneksi As New SqliteConnection("Data Source=arsip.db")
            koneksi.Open()

            Dim buatTabel As New SqliteCommand(
                "CREATE TABLE Catatan (Id INTEGER PRIMARY KEY, Judul TEXT, Isi TEXT)",
                koneksi)
            buatTabel.ExecuteNonQuery()

            ' Data contoh yang memuat tanda kutip satu pada judulnya
            Dim contoh() As (Judul As String, Isi As String) = {
                ("Dokumen 'pagi'", "Catatan pagi disusun ulang"),
                ("Dokumen siang", "Perkembangan siang hari dicatat"),
                ("Laporan pagi", "Rekap pagi disiapkan")
            }

            Dim tambah As New SqliteCommand(
                "INSERT INTO Catatan (Judul, Isi) VALUES (@judul, @isi)",
                koneksi)
            For Each catatan As (Judul As String, Isi As String) In contoh
                tambah.Parameters.Clear()
                tambah.Parameters.AddWithValue("@judul", catatan.Judul)
                tambah.Parameters.AddWithValue("@isi", catatan.Isi)
                tambah.ExecuteNonQuery()
            Next
            Console.WriteLine("Tiga baris contoh dimasukkan dengan parameter")

            ' Pencarian berparameter: nilai pola tidak pernah digabung ke teks SQL
            Dim cari As New SqliteCommand(
                "SELECT Id, Judul FROM Catatan WHERE Judul LIKE @kata",
                koneksi)
            cari.Parameters.AddWithValue("@kata", "%pagi%")

            Using pembaca As SqliteDataReader = cari.ExecuteReader()
                Do While pembaca.Read()
                    Console.WriteLine(
                        $"{pembaca.GetInt32(0)}: {pembaca.GetString(1)}")
                Loop
            End Using
        End Using
    End Sub
End Module

text Keluaran

Tiga baris contoh dimasukkan dengan parameter
1: Dokumen 'pagi'
3: Laporan pagi

Keluaran memperlihatkan dua hasil pencarian, yaitu Dokumen ‘pagi’ dan Laporan pagi. Keduanya cocok dengan pola %pagi% yang dikirim melalui parameter @kata. Pola LIKE ditulis utuh di dalam teks SQL, sedangkan nilainya dikirim terpisah, sehingga struktur perintah tidak tersentuh oleh datanya. Pemanggilan GetInt32 dan GetString mengikuti urutan kolom pada SELECT, yaitu Id terlebih dahulu lalu Judul.

Kesalahan umum#

Kesalahan pertama adalah mengirim perintah sebelum koneksi dibuka. Pemanggilan ExecuteReader atau ExecuteNonQuery atas koneksi yang belum diberi Open memunculkan InvalidOperationException dengan pesan bahwa koneksi tidak terbuka. Buka koneksi terlebih dahulu, atau susun perintahnya dengan koneksi.CreateCommand yang mengikat perintah pada koneksi tersebut sejak awal. Urutan ini berlaku sama untuk ketiga provider karena kelas perintahnya tidak membuka koneksi sendiri.

Kesalahan kedua adalah menambah parameter yang sama berulang kali di dalam perulangan. Setiap AddWithValue menambah satu parameter baru pada koleksinya, sehingga pemanggilan kedua atas nama yang sama memicu error. Panggil Parameters.Clear() di awal setiap iterasi, atau cukup ubah nilai parameter yang sudah ada tanpa menambahnya lagi.

Kesalahan ketiga adalah menuliskan kredensial langsung di dalam kode. Teks koneksi yang memuat kata sandi ikut tersimpan di repository dan ikut terbaca dari hasil build. Simpan teks koneksi di berkas konfigurasi atau variabel lingkungan, lalu baca nilainya saat program berjalan.

Ringkasan#

ADO.NET menyeragamkan akses basis data melalui pasangan kelas koneksi, perintah, parameter, dan pembaca per provider.
OleDb melayani sumber lawas dengan parameter berposisi tanda tanya, SqlClient melayani SQL Server dengan parameter bernama, dan Microsoft.Data.Sqlite mengoperasikan basis data berkas tanpa server.
Teks koneksi menyimpan server, nama basis data, dan kredensial, dan sebaiknya tidak ditulis di dalam kode.
Query berparameter memisahkan nilai dari teks SQL sehingga tidak menyisakan celah untuk SQL injection, dan demonstrasi SQLite pada topik ini dijalankan penuh di sandbox.

Referensi#

ADO.NET Overview — Microsoft Learn — Arsitektur ADO.NET beserta konsep provider dan pola kelasnya.
OleDbConnection Class — Microsoft Learn — Kelas koneksi untuk sumber data OLE DB.
SqlConnection Class — Microsoft Learn — Kelas koneksi untuk SQL Server.
SQL injection — Microsoft Learn — Penjelasan serangan SQL injection dan pencegahannya.
Microsoft.Data.Sqlite overview — Microsoft Learn — Dokumentasi provider SQLite yang dipakai demonstrasi.
SqliteParameterCollection.AddWithValue Method — Microsoft Learn — Metode penambahan parameter bernama yang digunakan pada contoh.