Contents
Introduction to Java JDBC PreparedStatement
The PreparedStatement interface is a sub-interface of the Statement interface. The PreparedStatement object will be attached to a SQL query, so we can reuse the PreparedStatement object again and again. Moreover the PreparedStatement helps us to defend against the dark art of the SQL, the SQL injection attacks.
We will cover the simple SQL injection now. First we create a Test table again and insert new rows to it. This time we use PreparedStatement (kills two birds with one stone 😀 ):
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var queryCreate = "create table Test " +
"(Id int not null auto_increment " +
"primary key, " +
"Name varchar(50));";
//this time the string query is attached to
//the PreparedStatement
PreparedStatement psCreate = conn
.prepareStatement(queryCreate);
//execute the PreparedStatement
psCreate.execute();
var queryInsert = "insert into Test" +
"(Name) values" +
"('Krisha'), ('Prisha'), ('Pavatil');";
PreparedStatement psInsert = conn
.prepareStatement(queryInsert);
psInsert.execute();
}
}
Note that we can only add a Test table if that table does not exist in the test database. The simplest way when coding with the database is that we need to interact with the database in the terminal:
mysql -u testuser -p
The in mysql cursor, just check the Test table existence:
mysql> use test; mysql> show tables;
And we can drop table when needed:
mysql> drop table Test;
Show table content:
mysql> select * from Test;
OK, next just think that we are coding a server response from user input. For example in a app, the user inputs the string name and when the server receive that string, it will return a list of rows and sends that data back to user. We suppose that the input that user sends is Krisha:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
//We suppose that this is the input the user
//send us
String input = "Krisha";
var query = "select * from Test" +
" where Name = '" + input + "';";
PreparedStatement psQuery = conn
.prepareStatement(query);
//we use executeQuery() to retrieve data
ResultSet set = psQuery.executeQuery();
while (set.next()){
System.out.println(
"Id: " + set.getInt("Id") + " - " +
"Name: " + set.getString("Name")
);
}
}
}
Ok so far so good, the result will be the line
Id: 1 - Name: Krisha
The input string will be concatenated to the query string and the full SQL line will be:
select * from Test where Name = 'Krisha';
Thus we have the output as above.
SQL injection
But think about this, if the user inputs a name like this ‘or Name is not null or Name =’
Yes, this is very strange name, but still legitimate right? Now we change the input to this:
var input = "'or Name is not null or Name ='";
And run this code again:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var input = "'or Name is not null or Name ='";
var query = "select * from Test" +
" where Name = '" + input + "';";
PreparedStatement psQuery = conn
.prepareStatement(query);
ResultSet set = psQuery.executeQuery();
while (set.next()){
System.out.println(
"Id: " + set.getInt("Id") + " - " +
"Name: " + set.getString("Name")
);
}
}
}
Well the output is that all the table data is returned:
Id: 1 - Name: Krisha Id: 2 - Name: Prisha Id: 3 - Name: Pavatil
With the input like this, the SQL string is like:
select * from Test where Name = '' or Name is not null or Name ='';
This is legitimate SQL code and will output all the rows with non null Name. As we can see, with simple SQL injection like this, an attacker can get all the data of the table Test. Of course, this can happen in ancient time of programming (now they have more advanced techniques of dark art, so they don’t use that naive SQL injection anymore 😀 ).
PreparedStatement the right way
But if we use PreparedStatement the right way, we can avoid this kind of attack.
First we change the query to this:
var query = "select * from Test" +
" where Name = ?;";
Each time we need to add an input into the SQL query, we use the question mark ? right there. The question mark will be marked by Java as a String parameter, this parameter will be passed later, in this example by the method setString():
psQuery.setString(1, input);
The code means that we pass the variable input to the parameter at index 1, which is the first question mark in the variable query. Why is this better? Yep, that’s because the whole input string will be compared with the Name column in the where clause of the query. The whole code of Test.java:
import java.sql.*;
class Test {
static public void main(String[] args)
throws SQLException {
var dbUrl = "jdbc:mysql://localhost/test?"
+ "user=testuser&password=Test@12345";
var conn = DriverManager.getConnection(dbUrl);
var input = "'or Name is not null or Name ='";
var query = "select * from Test" +
" where Name = ?;";
PreparedStatement psQuery = conn
.prepareStatement(query);
psQuery.setString(1, input);
ResultSet set = psQuery.executeQuery();
while (set.next()){
System.out.println(
"Id: " + set.getInt("Id") + " - " +
"Name: " + set.getString("Name")
);
}
}
}
Now run the code and we will see no output. That’s what we expected because clearly there’s no row with that ugly name in the Test table :D. We use the PreparedStatement just like the Statement interface with three basic methods: execute(), executeQuery() and executeUpdate().