Preventing SQL Injection

SQL injection happens when input becomes part of the SQL text. Build a query as "... WHERE email = '" + email + "'" and a request carrying x' OR '1'='1 changes what the query means: the condition is now always true. Other payloads read different tables or change and delete data. It remains one of the most common serious flaws in web applications, and it almost always comes from string concatenation in one forgotten query.

The fix: bind values

Write the SQL with ? placeholders and pass input through PreparedStatement setters. The SQL text is fixed in your code, the values are bound as data (sent separately, or escaped by the driver itself), and they stay data whatever characters they contain. No escaping function is needed, and hand-written escaping is exactly what goes wrong, because quoting and backslash rules differ between databases and settings.

Using a PreparedStatement is not enough on its own. conn.prepareStatement("... '" + email + "'") is still concatenation; only the values passed to setters are safe.

What placeholders cannot do

A placeholder stands for a value. Table names, column names, ORDER BY columns and sort direction cannot be bound, and this is where injection creeps back into APIs that accept ?sort=price. Map the request value through an allowlist, a Map or an enum from the public name to the exact SQL fragment, and reject anything else with a 400. Never pass the raw value through, even after checking it against a regular expression.

LIKE searches

Binding stops injection, but inside a LIKE pattern % and _ are still wildcards. A search for 100% matches more than intended, and a search for % alone matches every row, which can mean an expensive full scan. Escape those characters in the value and declare the escape character in the SQL: WHERE name LIKE ? ESCAPE '!'. Choosing ! rather than a backslash avoids databases that treat backslash specially inside string literals.

Defence in depth

• Connect as a database user with only the rights the service needs. An injected statement that drops a table fails when the user has no right to drop tables.
• Do not return database error messages to API clients; they reveal table and column names. Log them and answer with a generic 500.
• The same rules hold above JDBC. JPQL, HQL and Spring's JdbcClient are safe with parameters and just as vulnerable to concatenation.
• Validate input against your business rules (length, format), but do not count on validation to stop injection. Binding does that.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Optional;
import javax.sql.DataSource;

record User(long id, String email) {}

final class UserLookup {
    private final DataSource dataSource;

    UserLookup(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Vulnerable: the email becomes part of the SQL text.
    Optional<User> findByEmailUnsafe(String email) throws SQLException {
        String sql = "SELECT id, email FROM users WHERE email = '" + email + "'";
        try (Connection conn = dataSource.getConnection();
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(sql)) {
            return rs.next() ? Optional.of(new User(rs.getLong(1), rs.getString(2))) : Optional.empty();
        }
    }

    // Safe: the SQL is fixed and the email is sent as a value.
    Optional<User> findByEmail(String email) throws SQLException {
        String sql = "SELECT id, email FROM users WHERE email = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, email);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(new User(rs.getLong(1), rs.getString(2))) : Optional.empty();
            }
        }
    }
}

String unsafe(String email) {
    return "SELECT id FROM users WHERE email = '" + email + "'";
}

void main() {
    IO.println(unsafe("[email protected]"));
    IO.println(unsafe("x' OR '1'='1"));
    IO.println(unsafe("x' OR 1=1 -- "));
    IO.println("SELECT id FROM users WHERE email = ?");
}

// Output:
// SELECT id FROM users WHERE email = '[email protected]'
// SELECT id FROM users WHERE email = 'x' OR '1'='1'
// SELECT id FROM users WHERE email = 'x' OR 1=1 -- '
// SELECT id FROM users WHERE email = ?

import java.util.Map;

static final Map<String, String> SORTS = Map.of(
    "name", "name ASC",
    "price", "price ASC",
    "-price", "price DESC",
    "newest", "created_at DESC");

String orderBy(String sortParam) {
    String fragment = SORTS.get(sortParam);
    if (fragment == null) {
        throw new IllegalArgumentException("unsupported sort: " + sortParam);
    }
    return "SELECT id, name, price FROM products ORDER BY " + fragment + ", id";
}

void main() {
    for (String sort : new String[] {"price", "newest", "price, (SELECT 1)"}) {
        try {
            IO.println(orderBy(sort));
        } catch (IllegalArgumentException e) {
            IO.println("400 Bad Request: " + e.getMessage());
        }
    }
}

// Output:
// SELECT id, name, price FROM products ORDER BY price ASC, id
// SELECT id, name, price FROM products ORDER BY created_at DESC, id
// 400 Bad Request: unsupported sort: price, (SELECT 1)

import java.util.List;

// '!' is the escape character, so it must be escaped first.
String escapeLike(String term) {
    return term.replace("!", "!!").replace("%", "!%").replace("_", "!_");
}

void main() {
    IO.println("SELECT id, name FROM products WHERE name LIKE ? ESCAPE '!'");
    for (String term : List.of("lamp", "100%", "a_b", "%")) {
        IO.println(term + " -> bind %" + escapeLike(term) + "%");
    }
}

// Output:
// SELECT id, name FROM products WHERE name LIKE ? ESCAPE '!'
// lamp -> bind %lamp%
// 100% -> bind %100!%%
// a_b -> bind %a!_b%
// % -> bind %!%%