Eksekusi SQL dengan PDO PHP

Untuk pembelajaran di sini, kita akan menggunakan variabel $db di dalam sambung.php yang merupakan objek PDO dari topik sebelumnya. Serta tabel toko.barang yang juga telah dibuat pada topik sebelumnya. Mengeksekusi perintah pada PDO bisa dengan dua cara. Dengan method PDO::exec(), atau dengan cara prepared statement menggunakan PDOStatement::execute(). Dikatakan bahwa metode prepared statement telah aman dari SQL Injection. Akan tetapi, saya sendiri lebih cenderung menggunakan cara PDO::exec(). Berikut contoh dan penjelasan kedua cara tersebut:

Eksekusi SQL dengan PDO::exec()

Tiga hal yang perlu diperhatikan untuk menggunakan cara ini:

Gunakan method PDO::exec() : $db->exec($sql); untuk mengeksekusi perintah SQL,
Bungkus kode tersebut dengan try catch untuk jaga-jaga bila terjadi error,
Jika diperlukan, gunakan fungsi PDO::quote() untuk pengamanan terhadap SQL Injection.

Berikut contoh penggunaan yang sederhana. Contoh ini menggunakan tabel toko.barang dari topik sebelumnya:

<?php
include "sambung.php";
$id = $db->quote(1);
$tipe = $db->quote('Chitato');
$kategori = $db->quote('Snack');
$harga = $db->quote(7000);
try {
  $db->exec("REPLACE INTO barang VALUES ($id,$tipe,$kategori,$harga);");
} catch (Exception $ex) {
  echo $ex->getMessage();
}
?>

Fungsi PDO::quote() akan mengganti karakter-karakter khusus ke dalam escape string-nya, sekaligus membungkus string tersebut dengan tanda petik satu. Misalkan kita memiliki string:

<?php
include "sambung.php";
$kalimat = "Hari jum'at kemarin kita libur";
echo $kalimat;
echo "<br />";
echo $db->quote($kalimat);
?>

Output:

Hari jum'at kemarin kita libur
'Hari jum\'at kemarin kita libur'

Dari keluaran program di atas, kita dapat melihat bahwa tanda petik satu ' pada kalimat diganti menjadi \'. Dan di awal dan di akhir kalimat juga telah ditambahkan tanda petik satu. Sehingga kita dapat langsung menambahkan variabel ini ke dalam perintah sql, seperti pada contoh tabel barang di atas.

Hasil dari perintah PDO::quote() bergantung dengan database. Jika menggunakan mssql, maka tanda petik satu ' di dalam kalimat akan diganti menjadi petik satu dobel ''.

Dengan konsep yang sama, berikut contoh yang lebih kompleks, menggunakan array dan class. Data hasil dari contoh ini akan digunakan pada topik berikutnya:

<?php
include "sambung.php";
class Barang {
  public $id, $tipe, $kategori, $harga;
  public function __construct($id, $tipe, $kategori, $harga) {
    $this->id=$id; $this->tipe=$tipe; $this->kategori=$kategori; $this->harga=$harga;
  }
  public function insertString($db) {
    foreach ($this as $k=>$v) $$k = $db->quote($v);
    return "REPLACE INTO barang VALUES ($id,$tipe,$kategori,$harga)";
  }
}
$barangs = [
    new Barang(2, 'Mr. P', 'Snack', 4500),
    new Barang(3, 'Silver Queen Chunky Bar', 'Snack', 9000),
    new Barang(4, 'Sprite', 'Minuman', 7500),
    new Barang(5, 'Fanta', 'Minuman', 7500),
    new Barang(6, 'Coca Cola', 'Minuman', 7500),
    new Barang(7, 'Gudang Garam Surya', 'Rokok', 17000),
    new Barang(8, 'Dji Sam Soe', 'Rokok', 13500)
];
try {
  foreach ($barangs as $b) { $db->exec($b->insertString($db)); }
} catch (Exception $ex) {
  echo $ex->getMessage();
}
?>

Program di atas tidak menampilkan apapun ke layar. Hanya menyimpan data ke database. Untuk melihat data yang tersimpan, dapat melalui phpMyAdmin.

Eksekusi SQL dengan PDO::prepare

Cara ini menggunakan teknologi prepared statement. Perintah sql diketik dalam sebuah template, kemudian nilai dari parameter di dalam perintah tersebut dikirim, dan baru pada akhirnya perintah tersebut dieksekusi.

Parameter pada PDO::prepare dapat berupa tanda tanya (?) atau parameter bernama (:nama). Parameter tanda tanya lebih mudah digunakan, akan tetapi rentan dengan kesalahan pengurutan. Sebaliknya parameter bernama lebih sulit diketik, tetapi lebih kebal terhadap kesalahan pengurutan.

PDO::prepare dengan parameter tanda tanya

Berikut di bawah ini adalah contoh penggunaan parameter tanda tanya:

<?php
include "sambung.php";
try {
  $sth = $db->prepare("REPLACE INTO barang VALUES (?,?,?,?)");
  list($id,$tipe,$kategori,$harga) = [1,'Chitato','Snack',7000];
  $sth->bindParam(1,$id);
  $sth->bindParam(2,$tipe);
  $sth->bindParam(3,$kategori);
  $sth->bindParam(4,$harga);
  $sth->execute();
} catch (Exception $ex) {
  echo $ex->getMessage();
}
?>

Pada contoh di atas, pertama-tama objek $db (PDO) menyiapkan template perintah REPLACE dan disimpan dalam objek $sth (PDOStatement). Tanda tanya di dalam template kemudian diberi nilai dengan perintah bindParam. 1, 2, 3 dan 4 pada bindParam mewakili urutan parameter di dalam template. Pada akhirnya, saat $sth->execute(), perintah SQL benar-benar dikirim dan dieksekusi. Perhatikan bahwa kita menggunakan $sth->execute(), bukan $db->exec(). Keduanya merupakan fungsi yang berbeda pada class yang berbeda.

Pada contoh di atas, $id, $tipe, $kategori dan $harga tidak perlu diproses dengan PDO::quote() penggunaan metode prepared statement otomatis telah mengamankan query dari SQL Injection.

Dengan memasukkan array sebagai parameter $sth->execute(), cara ini dapat dipersingkat lagi penggunaannya:

<?php
include "sambung.php";
try {
  $sth = $db->prepare("REPLACE INTO barang VALUES (?,?,?,?)");
  $sth->execute([1,'Chitato','Snack',7000]);
} catch (Exception $ex) {
  echo $ex->getMessage();
}
?>

Perlu diperhatikan bahwa cara parameter tanda tanya ini sangat rentan dengan kesalahan urutan parameter.

PDO::prepare dengan parameter bernama

Berikut contoh dengan bindParam seperti pada contoh parameter tanda tanya di atas:

<?php
include "sambung.php";
try {
  $sth = $db->prepare("REPLACE INTO barang VALUES (:id,:tipe,:kategori,:harga)");
  list($id,$tipe,$kategori,$harga) = [1,'Chitato','Snack',7000];
  $sth->bindParam(":id",$id);
  $sth->bindParam(":tipe",$tipe);
  $sth->bindParam(":kategori",$kategori);
  $sth->bindParam(":harga",$harga);
  $sth->execute();
} catch (Exception $ex) {
  echo $ex->getMessage();
}
?>

Dengan parameter bernama, bisa juga menggunakan array:

<?php
include "sambung.php";
try {
  $sth = $db->prepare("REPLACE INTO barang VALUES (:id,:tipe,:kategori,:harga)");
  $sth->execute([
      "id"=>1,
      "tipe"=>'Chitato',
      "kategori"=>'Snack',
      "harga"=>7000
      ]);
} catch (Exception $ex) {
  echo $ex->getMessage();
}
?>

Mendapatkan nilai auto increment PDO MySQL

Pada MySQL, terdapat fitur auto_increment, atau sequence pada PostgreSQL. Dengan kolom seperti ini, seringkali kita ingin tahu id baru yang diberikan kepada data yang kita simpan. Kita dapat melakukan select setelah insert, tetapi cara ini rentan error, karena bila tidak menggunakan table lock, bisa saja terjadi insert dua kali dari dua client yang berbeda, dan select hanya mengambil id terakhir dari kedua insert tersebut.

Agar aman dari kasus seperti di atas, dapat menggunakan perintah PDO::lastInsertId() seperti contoh di bawah ini:

<?php
include "sambung.php";
$db->exec("REPLACE INTO barang VALUES (NULL,'Barang A','Kategori',123000)");
$id = $db->lastInsertId();
echo "id Barang A: $id";
?>

Bila menggunakan database PostgreSQL, fungsi lastInsertId() harus diberi parameter berupa nama sequence yang digunakan pada tabel tersebut:

<?php
include "sambung.php";
$db->exec("INSERT INTO barang VALUES (DEFAULT,'Barang A','Kategori',123000)");
$id = $db->lastInsertId("barang_id_seq");
echo "id Barang A: $id";
?>

Pendahuluan

Dasar

Menengah

Database