How to run a parameterized SQL query safely

Short answer

Write ? in the SQL instead of a value and pass the values through a PreparedStatement:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE users (name TEXT, city TEXT)");
            st.executeUpdate("INSERT INTO users VALUES ('Anna', 'Paris'), ('Oleg', 'Berlin')");
        }
 
        String city = "Paris";
        try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM users WHERE city = ?")) {
            ps.setString(1, city);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    IO.println(rs.getString("name"));
                }
            }
        }
    }
}

Output:

Anna

Several parameters

Parameters are numbered from 1 in the order they appear in the query. For modifying queries executeUpdate() returns the number of affected rows:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE products (title TEXT, price REAL, stock INTEGER)");
            st.executeUpdate("INSERT INTO products VALUES ('Tea', 3.5, 10), ('Cake', 6.0, 0)");
        }
 
        try (PreparedStatement ps = conn.prepareStatement(
                "UPDATE products SET price = price * ? WHERE stock > ?")) {
            ps.setDouble(1, 0.9);
            ps.setInt(2, 0);
            IO.println("Updated: " + ps.executeUpdate());
        }
    }
}

Output:

Updated: 1

A list of values in IN

A single ? cannot stand for a list. Generate as many placeholders as there are values:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE users (id INTEGER, name TEXT)");
            st.executeUpdate("INSERT INTO users VALUES (1, 'Anna'), (2, 'Oleg'), (3, 'Mia')");
        }
 
        List<Integer> ids = List.of(1, 3);
        String marks = String.join(", ", Collections.nCopies(ids.size(), "?"));
        String sql = "SELECT name FROM users WHERE id IN (" + marks + ") ORDER BY id";
 
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < ids.size(); i++) {
                ps.setInt(i + 1, ids.get(i));
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    IO.println(rs.getString(1));
                }
            }
        }
    }
}

Output:

Anna
Mia

Common mistake

Building SQL by concatenating strings. Crafted input changes the meaning of the query — this is SQL injection:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
         Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE users (name TEXT, password TEXT)");
        st.executeUpdate("INSERT INTO users VALUES ('Anna', 'a1'), ('Oleg', 'o2')");
 
        String password = "x' OR '1'='1";
        String sql = "SELECT name FROM users WHERE password = '" + password + "'";
        try (ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                IO.println("Logged in as " + rs.getString(1));
            }
        }
    }
}

Output:

Logged in as Anna
Logged in as Oleg

With a PreparedStatement the same string is just a wrong password, and the query finds no rows. Table and column names cannot be passed as parameters: if they depend on input, pick them from a fixed list of allowed values.

Thanks for reading!

Short answer

Several parameters

A list of values in IN

Common mistake

Functions used in this recipe

Thanks for reading!