Using a prepared statement

[Jenn Person]

Hi,

I have a class with a method takes in a connection object and a string called criteria that contains search parameters. I used to have the method findInstructors() working using a simple select statement, but this leaves it vulnerable to SQL and Javascript injection. So I want to use a prepared statement, but I'm have a little trouble.

Here is my original working method:

public ArrayList findInstructors(Connection connection, String criteria) throws SQLException
{
Statement statement = connection.createStatement();
String query = "SELECT * FROM Instructors WHERE active = 1 AND ((Instructors.lastName) Like '%"+criteria+"%') ORDER BY Instructors.lastName";
 
ResultSet resultSet = statement.executeQuery(query);
 
ArrayList instructors = new ArrayList();
 
while(resultSet.next())
{
InstructorBean instructor = new InstructorBean();
instructor.setInstructorID(resultSet.getInt("instructorID"));
instructor.setTitle(resultSet.getString("title"));
instructor.setFirstName(resultSet.getString("firstName"));
instructor.setLastName(resultSet.getString("lastName"));
instructor.setOffice(resultSet.getString("office"));
instructor.setPhone(resultSet.getString("phone"));
instructor.setEmail(resultSet.getString("email"));
instructor.setPosition(resultSet.getInt("position"));
instructor.setHomePage(resultSet.getString("homePage"));
instructor.setImage(resultSet.getString("image"));
instructor.setInstructorType(resultSet.getInt("instructorType"));
instructor.setActive(resultSet.getBoolean("active"));
instructor.setNewFace(resultSet.getBoolean("newFace"));
instructor.setOnLeave(resultSet.getBoolean("onLeave"));
instructor.setDateUpdated(resultSet.getString("dateUpdated"));
instructors.add(instructor);
}
 
return instructors;
}

Here is my attempt with the prepared statement:

public ArrayList findInstructors(Connection connection, String criteria) throws SQLException
{
//Statement statement = connection.createStatement();
//String query = "SELECT * FROM Instructors WHERE active = 1 AND ((Instructors.lastName) Like '%"+criteria+"%') ORDER BY Instructors.lastName";
 
//ResultSet resultSet = statement.executeQuery(query);
 
ArrayList instructors = new ArrayList();
 
StringBuffer template = new StringBuffer();
 
template.append("SELECT * FROM Instructors WHERE active = 1 AND ((Instructors.lastName) Like '%?%') ORDER BY Instructors.lastName");
 
PreparedStatement statement = connection.prepareStatement(template.toString());
 
statement.setString(1, criteria);
 
ResultSet resultSet = statement.executeQuery();
 
while(resultSet.next())
{
InstructorBean instructor = new InstructorBean();
instructor.setInstructorID(resultSet.getInt("instructorID"));
instructor.setTitle(resultSet.getString("title"));
instructor.setFirstName(resultSet.getString("firstName"));
instructor.setLastName(resultSet.getString("lastName"));
instructor.setOffice(resultSet.getString("office"));
instructor.setPhone(resultSet.getString("phone"));
instructor.setEmail(resultSet.getString("email"));
instructor.setPosition(resultSet.getInt("position"));
instructor.setHomePage(resultSet.getString("homePage"));
instructor.setImage(resultSet.getString("image"));
instructor.setInstructorType(resultSet.getInt("instructorType"));
instructor.setActive(resultSet.getBoolean("active"));
instructor.setNewFace(resultSet.getBoolean("newFace"));
instructor.setOnLeave(resultSet.getBoolean("onLeave"));
instructor.setDateUpdated(resultSet.getString("dateUpdated"));
instructors.add(instructor);
}
 
return instructors;
}

I'm not getting any results with the prepared statement. How can I do this properly?

Thanks,
Jenn

[Herman Schelti]

hi Jenn,

I had a simular problem a while ago.

can you try this:
template.append("SELECT * FROM Instructors WHERE active = 1 AND ((Instructors.lastName) Like ?) ORDER BY Instructors.lastName");

and:
statement.setString(1, '%' + criteria + '%');

another thing: make sure you always close resultsets, (prep.) statements and connections (usually in a finally block)

Herman

[Jenn Person]

Ok great, I'll try this out after work and let you know of the results. Thanks so much!