JDBC Statement SQL Injection

Categories: Java; Tagged with: Derby • Java • JDBC; @ May 7th, 2014 23:54

Here is an example to inject dirty SQL using JDBC Statement:

package com.liguoliang.j2ee;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import org.junit.Before;
import org.junit.Test;

public class TestJDBC {

	private static final String DB_URL = "jdbc:derby:C:\\Users\\Guoliang\\MyDB;create=true";
	
	Connection conn = null ;
	
	@Before
	public void before() throws ClassNotFoundException, SQLException {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
	    conn = DriverManager.getConnection(DB_URL);
	}
	
	@Test
	public void testSQLInjection() throws SQLException {
		String userName = "guoliang' OR 'a' = 'a";
		String password = "wrong-password" + new Date().toString();
		
		Statement statement = conn.createStatement();
		String sql = "SELECT * FROM USERS WHERE USER_NAME = '" + userName + "' AND PASSWORD = '" + password + "'";
		System.out.println(sql);
		ResultSet rs = statement.executeQuery(sql);
		
		int userId = -1;
		while (rs.next()) {
			userId= rs.getInt(1);
			System.out.println(" > User Id: " + userId); // Will print all user id;
		}
		assertTrue(userId != -1);
	}
	
	@Test
	public void testPreparedStatement() throws SQLException {
		String userName = "guoliang' OR 'a' = 'a";
		String password = "wrong-password";
		
		PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERS WHERE USER_NAME = ? AND PASSWORD = ?");
		ps.setString(1, userName);
		ps.setString(2, password);
		
		ResultSet rs = ps.executeQuery();
		
		int userId = -1;
		while (rs.next()) {
			userId= rs.getInt(1);
			fail("User Id: " + userId);
		}
		
		System.out.println("userId: " + userId);
	}

}

I set one of the paramters to : [guoliang ‘a’ = ‘a], the I got the SQL:

SELECT * FROM USERS WHERE USER_NAME = 'guoliang' OR 'a' = 'a' AND PASSWORD = 'wrong-passwordWed May 07 23:47:11 CST 2014'

This means, the SQL will return all records in this table.

However we can use prepared statement to solve this issue.

SQLs used by this example:

CREATE TABLE USERS (ID INT, USER_NAME VARCHAR(20), PASSWORD VARCHAR(20));
INSERT INTO USERS (ID, USER_NAME, PASSWORD) VALUES (1, 'guoliang', 'password_abc');

Running from Expo to Toa Payoh via. PCN(Park Connector Network) <-> Quick Setup a Project Using JDBC & Derby DB/Java DB