suhe arie's pages

Programmer juga manusia

Statement vs PreparedStatement vs Batch, Security and Performance

Jika kita membuat aplikasi yang memanipulasi database dengan plain JDBC, Statement dan PreparedStatement object mempunyai peranan yang penting. Keduanya sama-sama dapat digunakan untuk mengirim perintah query ke database. Sekarang, kita akan coba melihat apa yang membuat mereka berbeda.

Note: Source code untuk class DBManager bisa didapat disini.

1. Security attack: SQL Injection

Yang pertama, dilihat dari sudut pandang keamanan, khususnya terhadap serangan yang disebut SQL injection. Penyerang biasanya mencoba untuk menembus sistem dengan cara merusak SQL query yang kita buat. Triknya adalah dengan menggunakan susunan karakter, yang bila digabungkan dengan query sebenarnya akan menghasilkan nilai yang berbeda.

Berikut adalah tabel yang akan kita gunakan untuk percobaan.

CREATE TABLE `myblog`.`user_security` (
  `username` VARCHAR(50) NOT NULL DEFAULT '',
  `pswd` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY(`username`)
)
ENGINE = InnoDB;

Isi dengan sample data:

Dengan kode berikut, kita akan mencoba untuk menjalankan test menggunakan Statement dan PreparedStatement dengan input yang sama, yaitu:

username: aaa
pswd: bbb’ OR ‘1’=’1

package suhearie.blog.sql_injection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import suhearie.blog.jdbc.DBManager;

public class TesSqlInjection {

    private DBManager manager;

    private Connection conn;

    public TesSqlInjection() throws Exception {
        manager = new DBManager();
        conn = manager.getConnection();
    }

    private void testStatement(String user, String pswd) throws Exception {
        System.out.println("Test menggunakan Statement");
        String sql = "SELECT * FROM user_security WHERE username = '"+user+"' AND pswd = '"+pswd+"'";
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        if (rs.next()) {
            System.out.println("User: "+user+", Pswd:"+pswd+" BENAR, login valid");
        }
        else {
            System.out.println("User: "+user+", Pswd:"+pswd+" SALAH, login invalid");
        }
    }

    private void testPreparedStatement(String user, String pswd) throws Exception {
        System.out.println("Test menggunakan PreparedStatement");
        String sql = "SELECT * FROM user_security WHERE username = ? AND pswd = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, user);
        preparedStatement.setString(2, pswd);
        ResultSet rs = preparedStatement.executeQuery();
        if (rs.next()) {
            System.out.println("User: "+user+", Pswd:"+pswd+" BENAR, login valid");
        } else {
            System.out.println("User: "+user+", Pswd:"+pswd+" SALAH, login invalid");
        }
    }

    public static void main(String[] args) throws Exception {
        TesSqlInjection test = new TesSqlInjection();
        String user = "aaa";
        String pswd = "bbb' OR '1'='1";
        test.testStatement(user, pswd);
        test.testPreparedStatement(user, pswd);
    }

}

Output dari test di atas adalah:

Test menggunakan Statement
User: aaa, Pswd:bbb’ OR ‘1’=’1 BENAR, login valid
Test menggunakan PreparedStatement
User: aaa, Pswd:bbb’ OR ‘1’=’1 SALAH, login invalid

Mengapa hasil dari test Statement bisa demikian ?
Jawabannya adalah karena bila query awal dan input user digabung, query akan menjadi:

SELECT * FROM user_security WHERE username = 'aaa' AND pswd = 'bbb' OR '1'='1'

Karena ada OR 1=1, yang akan selalu bernilai TRUE, maka nilai klausa WHERE akan menjadi TRUE, dan query akan mengembalikan resultset, meskipun username dan password yang diberikan SALAH.

Dari test tersebut, jelas bahwa Statement sangat rentan terhadap SQL injection, karena input dari user akan di menjadi bagian dari query. Sementara jika kita menggunakan PreparedStatement, input dari user akan menjadi parameter saja, sehingga tidak mempengaruhi hasil query.

2. Performance

Disini kita akan menguji performance dari 3 metode tersebut untuk melakukan manipulasi data di database. Saya sudah melakukan percobaan untuk melakukan operasi insert pada tabel sederhana berikut:

CREATE TABLE  `myblog`.`st_vs_pst` (
  `COL1` int(10) unsigned NOT NULL default '0',
  `COL2` int(10) unsigned NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Test ini akan menghapus tabel, kemudian melakukan operasi insert sebanyak 1000 row dan menggunakan parameter untuk mengaktifkan atau mematikan option autocommit pada database.

package suhearie.blog.stmt_vs_pstmt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

import suhearie.blog.jdbc.DBManager;

public class TestPerformance {

private DBManager manager;

private Connection conn;

private static final String TABLE_NAME = “ST_VS_PST”;

private static final int TOTAL = 1000;

private static final int INTERVAL = 200;

public TestPerformance() throws Exception {
manager = new DBManager();
conn = manager.getConnection();
}

private void truncateTable() throws Exception {
System.out.println(“Truncating table”);
Statement statement = conn.createStatement();
statement.executeUpdate(“TRUNCATE TABLE “+TABLE_NAME);
}

private void testStatement(boolean turnOffAutoCommit) throws Exception {
System.out.println();
System.out.println(“Starting testStatement, turnOffAutoCommit:”+turnOffAutoCommit);
truncateTable();
long start = System.currentTimeMillis();
if (turnOffAutoCommit) {
conn.setAutoCommit(false);
}
Statement statement = conn.createStatement();
for (int i=0; i<=TOTAL; i++) { statement.executeUpdate("INSERT INTO "+TABLE_NAME+" (COL1,COL2) VALUES ("+i+","+i*2+")"); if (i%INTERVAL == 0) { System.out.print("Row:"+i+"; "); } } if (turnOffAutoCommit) { conn.commit(); conn.setAutoCommit(true); } long diff = System.currentTimeMillis() - start; System.out.println("testStatement finish: "+diff+" ms"); } private void testPreparedStatement(boolean turnOffAutoCommit) throws Exception { System.out.println(); System.out.println("Starting testPreparedStatement, turnOffAutoCommit:"+turnOffAutoCommit); truncateTable(); long start = System.currentTimeMillis(); if (turnOffAutoCommit) { conn.setAutoCommit(false); } PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO "+TABLE_NAME+" (COL1,COL2) VALUES (?,?)"); for (int i=0; i<=TOTAL; i++) { preparedStatement.setInt(1, i); preparedStatement.setInt(2, i*2); preparedStatement.executeUpdate(); if (i%INTERVAL == 0) { System.out.print("Row:"+i+"; "); } } if (turnOffAutoCommit) { conn.commit(); conn.setAutoCommit(true); } long diff = System.currentTimeMillis() - start; System.out.println("testPreparedStatement finish: "+diff+" ms"); } private void testBatch(boolean turnOffAutoCommit) throws Exception { System.out.println(); System.out.println("Starting testBatch, turnOffAutoCommit:"+turnOffAutoCommit); truncateTable(); long start = System.currentTimeMillis(); if (turnOffAutoCommit) { conn.setAutoCommit(false); } PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO "+TABLE_NAME+" (COL1,COL2) VALUES (?,?)"); for (int i=0; i<=TOTAL; i++) { preparedStatement.setInt(1, i); preparedStatement.setInt(2, i*2); preparedStatement.addBatch(); if (i%INTERVAL == 0) { System.out.print("Row:"+i+"; "); preparedStatement.executeBatch(); } } if (turnOffAutoCommit) { conn.commit(); conn.setAutoCommit(true); } long diff = System.currentTimeMillis() - start; System.out.println("testBatch finish: "+diff+" ms"); } public static void main(String[] args) throws Exception { TestPerformance test = new TestPerformance(); test.testStatement(true); test.testStatement(false); test.testPreparedStatement(true); test.testPreparedStatement(false); test.testBatch(true); test.testBatch(false); } } [/sourcecode] Output dari program tersebut adalah sebagai berikut: [sourcecode language='java'] Starting testStatement, turnOffAutoCommit:true Truncating table Row:0; Row:200; Row:400; Row:600; Row:800; Row:1000; testStatement finish: 297 ms Starting testStatement, turnOffAutoCommit:false Truncating table Row:0; Row:200; Row:400; Row:600; Row:800; Row:1000; testStatement finish: 24203 ms Starting testPreparedStatement, turnOffAutoCommit:true Truncating table Row:0; Row:200; Row:400; Row:600; Row:800; Row:1000; testPreparedStatement finish: 250 ms Starting testPreparedStatement, turnOffAutoCommit:false Truncating table Row:0; Row:200; Row:400; Row:600; Row:800; Row:1000; testPreparedStatement finish: 23250 ms Starting testBatch, turnOffAutoCommit:true Truncating table Row:0; Row:200; Row:400; Row:600; Row:800; Row:1000; testBatch finish: 219 ms Starting testBatch, turnOffAutoCommit:false Truncating table Row:0; Row:200; Row:400; Row:600; Row:800; Row:1000; testBatch finish: 23297 ms [/sourcecode]

Dari output yang dihasilkan, tampak bahwa performance Batch > PreparedStatement > Statement. Alasannya:

1. Statement
Setiap SQL statement yang dieksekusi dikirim secara utuh ke database.

2. PreparedStatement
SQL query dikirim hanya satu kali, selanjutnya yang dikirim ke database hanya parameted querynya saja.

3. PreparedStatement Batch:
Seperti halnya PreparedStatement, tapi kita bisa mengakumulasi parameter-parameter dengan method addBatch() kemudian mengirim kumpulan parameter itu sekaligus dengan method executeBatch().

Tapi perbedaan paling signifikan yang memeberikan perbedaan performance paling besar didapat ketika kita mematikan option autocommit di database. Dari test di atas, kecepatannya bisa mencapai 100 kali lipat !!

Mengapa bisa begitu? Sekedar analisa/asumsi logika sederhana, jika kita menggunakan fitur autocommit, setiap kita melakukan insert satu baris, maka database akan langsung melakukan perubahan permanen pada tabel. Sebaliknya, jika database tidak melakukan autocommit, maka kita bisa melakukan ribuan operasi insert, dan mungkin database akan memprosesnya di memory/buffer. Kemudian pada saat kita meng-commit perubahan, database akan melakukan proses tersebut untuk ribuan bari sekaligus.

Kesimpulannya, jika Anda mau melakukan insert atau update dalam jumlah yang besar, pilihan terbaik adalah menggunakan PreparedStatement Batch, dan menonaktifkan option autocommit.

Advertisements

August 21, 2008 - Posted by | Database, Java | , , , , , ,

12 Comments »

  1. Thank You !!

    Comment by vyor | October 22, 2008 | Reply

  2. matur nuwun mas, atas infonya
    bisa nggak ditambah materi-materi lain tentang java mas

    Comment by Tuwuh Pitoyo | February 10, 2010 | Reply

  3. Nice info..
    Thanks

    Comment by hijra | August 8, 2010 | Reply

  4. Maaf pak, saya mau bertanya tentang penggunaan PreparedStatement. Saya sedang membuat sebuah aplikasi database dengan menggunakan java (IDE Netbeans). Dalam program tersebut, saya ingin menampilkan isi database berdasarkan data yang di isi pada jTextField atau data yang dipilih lewat ComboBox. Berikut potongan programnya :

    String cek1 = txtBulan.getText();
    try{
    Connection hubung = DriverManager.getConnection(db_url, db_user, db_pass);
    sql = “SELECT * FROM balok WHERE no=1 AND bln=?”;
    PreparedStatement perintah = hubung.prepareStatement(sql);
    perintah.setString(1, cek1);
    ResultSet hasil = perintah.executeQuery();

    while(hasil.next()){
    txNo.setText(hasil.getString(“no”));
    txSoal.setText(hasil.getString(“soal”));

    cek1 adalah variabel yang isinya diambil dari sebuah ComboBox yang dipilih. Akan tetapi, program tidak berhasil memanggil isi database berdasarkan no dan bln sesuai perintah SQL yang di buat. Potongan kode program tersebut saya letakkan dibawah initComponent(). Jika syarat “bln” saya hapus (dalam perintah SQL, ada 2 syarat yaitu : “no” dan “bln”) dengan catatan tidak ada nomor yang sama, maka data dari database akan ditampilkan sesuai dengan no yang di isi/di pilih oleh user. Saya tidak menggunakan public void seperti yang bapak gunakan pada contoh diatas : “private void testPreparedStatement(String user, String pswd) throws Exception {” . Saya mohon masukan dari bapak untuk program saya, Terima kasih.

    Comment by Dori | September 19, 2010 | Reply

    • Kodenya sepertinya sudah benar. Tidak ada hubungannya dengan method modifier (private/public). Mungkin ada error/exception lainnya.

      Comment by suhearie | October 23, 2010 | Reply

  5. Thanks atas artikelnya gan
    Ane dulu cm tau pake Statement doank, trus ada yg bilang kalau itu rentan sama SQL injection.
    Ternyata simulasinya kyk gt toh
    Ijin save page yya

    Comment by bluething kaskus | December 14, 2010 | Reply

    • OK.. silahkan gan, asal cantumkan link ke sumber aslinya πŸ™‚

      Comment by suhearie | September 19, 2011 | Reply

  6. mas , adakah kerugiannya menggunakan prepared statement ?
    soalnya aplikasi yang sudah dibuat mengalami masalah penggunaan resource memori yang sangat besar saat upload data part dan solusinya menurut developernya adalah pengurangan prepare statement
    sebenarnya benarkah masalah performance disebabkan terlalu banyak menggunakan prepared statement ?

    Terima Kasih

    Comment by isni | July 23, 2012 | Reply

    • Hallo.. Saya belum pernah denger pemakaian prepared statement bisa menyebabkan masalah performance. Sejauh ini saya jg belum pernah mengalami itu. Kamu kena out of memory error? Ada banyak faktor yang bisa menyebabkan masalah memory, misalnya prepared statement yg tidak di ‘close’, atau object lain yang tidak ‘dibersihkan’ setelah digunakan. Salah satu cara untuk mengetahuinya adalah menggunakan java profiler.

      Comment by suhearie | July 24, 2012 | Reply

      • Tadinya tiap transaksi menggunakan 1 preparedStatement , akhirnya setiap transaksi prepared statementnya dijadikan satu
        dan itu harusnya mengalami signifikan pengurangan penggunaan memori saat transaksi .
        itu kata developer yang memberi tahu saya

        apakah itu sebenarnya berpengaruh ya mas ?
        masalah utama aplikasi yang terlalu memakan resource memori dan umumnya terjadi itu apa ya mas?
        saya masih bingung –‘ maaf banyak tanya , πŸ™‚

        Comment by isni | July 31, 2012

  7. I am interested in your article. Could you please translate this to English too. This will benefit other too.

    Comment by krishna | April 14, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: