PreparedStatement and SQL Injection Prevention in Java

How injection happens

// Never write this
String sql = "SELECT * FROM users WHERE email = '" + email + "' AND active = 1";
Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery(sql);

With an ordinary address the query is fine. With a crafted one it is not:

email = anything' OR '1'='1

SELECT * FROM users WHERE email = 'anything' OR '1'='1' AND active = 1

The input stopped being data and became part of the query. That is the whole of SQL injection, and it is why filtering quotes is not a solution: it treats a structural problem as a formatting one.

The fix

String sql = "SELECT id, name FROM users WHERE email = ? AND active = 1";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, email);      // sent separately from the query text

    try (ResultSet rows = statement.executeQuery()) {
        while (rows.next()) {
            System.out.println(rows.getString("name"));
        }
    }
}

The database parses the SQL before the values arrive. A parameter can therefore never change the structure of the statement, no matter what it contains. This is not escaping; the query and the data travel separately.

Setting parameters

statement.setString(1, title);
statement.setLong(2, categoryId);
statement.setInt(3, views);
statement.setBoolean(4, published);
statement.setBigDecimal(5, amount);
statement.setObject(6, LocalDate.now());
statement.setNull(7, Types.VARCHAR);           // an explicit SQL NULL
statement.setObject(8, nullableValue);          // null is handled correctly

Parameters are numbered from 1. Setting fewer than the statement declares throws at execution time.

What cannot be a parameter

// Not allowed: identifiers are part of the query structure
// String sql = "SELECT * FROM notes ORDER BY ? ?";

// Validate against a fixed set instead
private static final Set<String> SORTABLE = Set.of("title", "created_at", "views_count");

public List<Note> findSorted(String column, boolean ascending) throws SQLException {
    if (!SORTABLE.contains(column)) {
        throw new IllegalArgumentException("Cannot sort by " + column);
    }
    String direction = ascending ? "ASC" : "DESC";
    String sql = "SELECT id, title FROM notes ORDER BY " + column + " " + direction;
    // safe: both parts came from a fixed allow list, never from input
}

Table names, column names and keywords cannot be bound. When one must vary, validate it against an allow list. Never escape it and hope.

An IN clause

public List<Note> findByIds(List<Long> ids) throws SQLException {
    if (ids.isEmpty()) {
        return List.of();
    }

    String placeholders = ids.stream().map(id -> "?").collect(Collectors.joining(", "));
    String sql = "SELECT id, title FROM notes WHERE id IN (" + placeholders + ")";

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
        for (int i = 0; i < ids.size(); i++) {
            statement.setLong(i + 1, ids.get(i));
        }
        // ...
    }
}

The generated text contains only question marks, so nothing from the input reaches the query. Note that a different list size produces a different statement, which reduces the benefit of statement caching.

LIKE searches

String sql = "SELECT id, title FROM notes WHERE title LIKE ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "%" + term + "%");   // wildcards belong in the VALUE
}

Building LIKE '%" + term + "%' into the SQL would be injectable again. The wildcards go into the bound value.

The other benefits

// One parse, many executions
try (PreparedStatement statement = connection.prepareStatement(
        "UPDATE notes SET views_count = views_count + 1 WHERE id = ?")) {

    for (long id : ids) {
        statement.setLong(1, id);
        statement.executeUpdate();
    }
}

Defence in depth

• Bound parameters for every value. This is the actual defence.
• Allow lists for anything structural, such as a sort column.
• Least privilege: the application account should not be able to drop tables.
• Validation of ranges and formats, which improves errors but is not a security control on its own.
• Generic error messages: never return raw SQL errors to a user.

catch (SQLException e) {
    logger.severe("Query failed: " + e.getMessage());     // full detail in the log
    throw new StorageException("Could not complete the request", e);   // generic outward
}

Things that are not fixes

A safe repository method

public Optional<Note> findBySlug(String slug) throws SQLException {
    String sql = "SELECT id, title, views_count FROM notes WHERE slug = ? AND status = ?";

    try (Connection connection = dataSource.getConnection();
         PreparedStatement statement = connection.prepareStatement(sql)) {

        statement.setString(1, slug);
        statement.setString(2, "published");

        try (ResultSet rows = statement.executeQuery()) {
            if (!rows.next()) {
                return Optional.empty();
            }
            return Optional.of(new Note(
                    rows.getLong("id"),
                    rows.getString("title"),
                    rows.getInt("views_count")));
        }
    }
}

Common mistakes

• Using PreparedStatement but still concatenating one value into the SQL.
• Trying to bind a table or column name.
• Putting the % wildcards into the query text instead of the parameter.
• Assuming an ORM removes the risk; a raw query string inside one is just as vulnerable.
• Returning the database error message to the user.
• Running the application as a database administrator.

Best practices

• Bind every value, without exception.
• Validate structural fragments against an allow list.
• Grant the application account the minimum privileges it needs.
• Log details internally and return generic messages outward.
• Review any place where SQL is built with +.

Practice

1. Write a vulnerable query, exploit it with a crafted value, then fix it with a bound parameter.
2. Explain why parameter binding is stronger than escaping.
3. Implement a sort that accepts a column name safely.
4. Build a parameterised IN clause for a list of identifiers.
5. Why is a stored procedure not automatically safe?

Conclusion

Bound parameters keep data out of the query structure, which is what makes injection impossible rather than merely difficult. Bind every value, allow list anything structural, and never assemble SQL from input.