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.