How do I retrieve a row from a SQLite database through Java's jdbc using a string variable?

Question

This should return the row where name is equal to the String variable name, however all I get is this error

[SQLITE_ERROR] SQL error or missing database (near "Wars": syntax error)

In this instance I tried to use "Star Wars" as the name variable since I'm using a movies database. What am I doing wrong?

 public void selectName(ObservableList<Movies> movieList, String name){
    String sql = "SELECT id, name, year FROM movies WHERE name = " + name + " ";

    try(Connection conn = this.Connect();
        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery(sql)){
        while(rs.next()){
            movieList.addAll(new Movies(rs.getInt("id"),rs.getString("name"),rs.getInt("year")));
        }
    }catch(SQLException ex){
        System.out.println(ex.getMessage());
    }

Answer 1

The reason you received the error is that you are executing [..] where name = Star Wars which is invalid. If you were using a literal query string (where the value is fixed and not user input), the correct query would be [..] where name = 'Star Wars'.

The simple solution would seem to be to enclose the value in single quotes, however you should never concatenate values into query strings (especially not if they are user input) as this leaves you vulnerable to SQL injection.

Instead you should use a parametrized query (a.k.a. prepared statement) and set the parameter value. This way the driver will properly handle the parameter in a way that avoids potential SQL injection risks.

Specifically, you should do something like:

try (PreparedStatemet select = connection.prepareStatement(
        "SELECT id, name, year FROM movies WHERE name = ?")) {
    select.setString(1, name);
    try (ResultSet rs = select.executeQuery() {
        // process result set
    }
}