SQL statements

How to organize your database code

My advice: write a single class or a small number of classes that provide methods to do things with and to the database.

They should hide as much as possible the fact that there is a database under the covers.

Then the rest of the code uses those classes.

A query

public List<String> namesStartingWith(String start)
  throws SQLException
{
  List<String> names = new ArrayList<>();
  try (Statement stmt = connection.createStatement()) {
    String q = "select name from students where name like '" + start + "%'";
    ResultSet rs = statement.executeQuery(q);
    while(rs.next()) {
      names.add(rs.getString("name"));
    }
  }
  return names;
}

Looks good.

Let’s try it out:

foo.namesStartingWith("bobby'; DROP TABLE STUDENTS; --");

The SQL that runs:

select name from students where name like 'bobby';
DROP TABLE STUDENTS; --%'

Oopsie.

A classic

This is called “SQL injection”.

Normally this would happen where we have code like foo.namesStartingWith(formField1) where formField1 came from the user.

The fix

PreparedStatement namesLike = conn.prepareStatement(
  "select name from students where name like ?"
);

public List<String> namesStartingWith(String start)
  throws SQLException
{
  List<String> names = new ArrayList<>();
  namesLike.setString(1, start + "%");
  ResultSet rs = namesLike.executeQuery();
  while(rs.next()) {
    names.add(rs.getString("name"));
  }
  return names;
}

PreparedStatement

Faster and safer.

Database does work once to get ready to execute the prepared statement.

Then it can execute that plan many times with different arguments.

Plus the database driver is responsible for injecting your arguments into the SQL in the appropriate ways so you don’t have to worry about escaping special characters, etc.