The IN list built from an array of ids
A hard Java puzzle in the sql and jdbc mistakes family. One line below contains a real bug. Try to spot it first, then read why it fails and how to fix it.
Highlighted: line 6. Play this one as a puzzle without the answer.
What is wrong
Joining the ids into the SQL text is injection if any id comes from a request, even when the value looks like an identifier. A single quote in one element ends the string and lets the caller append SQL.
The fix
How to avoid it next time
Generate one placeholder per element and bind each value. Split very large lists into batches.
Want the background? Read JDBC and prepared statements in the Java concepts library.
About sql and jdbc mistakes
Building SQL by string concatenation invites injection, and a handful of subtle JDBC habits leak connections, load too much data or commit half a change.
In code review: Search for plus signs inside strings passed to executeQuery, createQuery or createNativeQuery. Parameters belong in placeholders, never in the text.
More puzzles in this family
Frequently asked questions
What is the bug in "The IN list built from an array of ids"?
Joining the ids into the SQL text is injection if any id comes from a request, even when the value looks like an identifier. A single quote in one element ends the string and lets the caller append SQL. The faulty line is line 6: String sql = "select * from orders where id in ('" + String.join("','", ids) + "')";
How do I fix it?
Replace it with: String marks = String.join(",", java.util.Collections.nCopies(ids.size(), "?")); PreparedStatement ps = c.prepareStatement("select * from orders where id in (" + marks + ")"); for (int i = 0; i < ids.size(); i++) ps.setString(i + 1, ids.get(i));. Generate one placeholder per element and bind each value. Split very large lists into batches.
How do I catch sql and jdbc mistakes in code review?
Search for plus signs inside strings passed to executeQuery, createQuery or createNativeQuery. Parameters belong in placeholders, never in the text.
Is there a new puzzle every day?
Yes. The daily Spot the Bug puzzle picks one of these snippets for each UTC day, with the difficulty set by the weekday. Your streak is stored only in your browser.