SQL injection and safe queries

How the attack works

// DANGEROUS: building SQL from user input
String email = request.getParameter("email");
String sql = "SELECT * FROM users WHERE email = '" + email + "'";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

If a user types the email ' OR '1'='1, the SQL becomes SELECT * FROM users WHERE email = '' OR '1'='1'. The condition is always true, so the query returns EVERY user. Other inputs can add UNION SELECT to read other tables, or comment out the rest of a query with --. SQL injection has been in the OWASP Top 10 list of web security risks for many years (as part of "Injection").

The fix: PreparedStatement parameters

String sql = "SELECT id, email, role FROM users WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, email);          // sent separately as DATA, never parsed as SQL
    try (ResultSet rs = ps.executeQuery()) { … }
}

With a prepared statement, the SQL text is fixed and sent to the database first; the parameter values are sent separately and treated purely as values. Whatever the user types, even quotes and SQL keywords, can only ever be compared as an email string.

What parameters cannot do

Placeholders work for VALUES, not for table names, column names or keywords like ASC/DESC. If users can choose a sort column, map their choice through a fixed allow-list in Java (switch on "price" → "price", anything else → "name"), never paste their text into SQL.

String orderBy = switch (sortParam) {
    case "price" -> "price";
    case "newest" -> "created_at DESC";
    default -> "name";
};
String sql = "SELECT id, name, price FROM products WHERE category = ? ORDER BY " + orderBy;   // safe: only allowed values

Defence in depth

• Least privilege: the application's database user should have only the permissions it needs (no DROP TABLE, no access to unrelated schemas).
• Validate input: an order id should be a positive number before it reaches the database.
• Never log passwords, OTPs, card numbers or full SQL with sensitive values.
• Use ORMs and query builders (JPA, Spring Data, jOOQ) correctly: they parameterise for you, but string-built native queries are still vulnerable.