عرض عملي: حقن SQL ثم إصلاحه بـ PreparedStatement

Written Explanation

The scenario

A search page offers two fields: title and minimum copies. Everything from a web page arrives as text, so a beginner keeps both as String:

String sql = "SELECT id, title, isbn, copies FROM books WHERE title = '" + title
           + "' AND copies >= " + minCopies;

The title is wrapped in quotes because it is text. The number is not, because SQL numbers need no quotes. That difference matters in the demo. Our test data: Clean Code (2 copies), Effective Java (1), Java Basics (0), and members Mona and Omar.

Step 1: the honest call and the first attack

Honest input ("Clean Code", "1") gives the SQL ... title = 'Clean Code' AND copies >= 1 and returns one book. Now the attack from lesson 4, with -- to comment out the rest of the line (including our AND copies part):

title = x' OR '1'='1' --      minCopies = 0
SQL: ... WHERE title = 'x' OR '1'='1' --' AND copies >= 0
2 attack: [Clean Code, Effective Java, Java Basics]   (all three books)

Step 2: the planned mistake, "just remove the quotes"

A developer who has heard "quotes are the problem" adds one line:

static String stripQuotes(String s) { return s.replace("'", ""); }
// then: build the SQL from stripQuotes(title) and stripQuotes(minCopies)

Re-running the same attack now:

SQL: ... WHERE title = 'x OR 1=1 --' AND copies >= 0
3 patched vs 2: []

It looks fixed: the apostrophes vanished, so the whole input stayed inside the quoted title and matched nothing. A developer who stops here ships a hole. The number field has no quotes to remove:

minCopies = 0 OR 1=1
SQL: ... WHERE title = 'Clean Code' AND copies >= 0 OR 1=1
4 patched vs numeric: [Clean Code, Effective Java, Java Basics]   (everything again)

Because AND binds tighter than OR, the trailing OR 1=1 makes the whole condition true. And the UNION attack needs no apostrophes either:

minCopies = 5 UNION SELECT id, name, name, 1 FROM members
5 patched vs union: [Book[id=3, title=Mona ...], Book[id=4, title=Omar ...]]

The attacker matched the four columns of our SELECT and the member names came back inside Book objects. (A first attempt with a quoted 'x' fails after quote stripping, with column "x" does not exist; the attacker just uses a column name instead. The lesson: a filter makes attacks slightly harder, not impossible.) In general, blacklists, hand-written escaping, and "clean the input" functions depend on guessing every trick in advance. The OWASP cheat sheet calls manual escaping strongly discouraged and recommends parameterised queries first.

Step 3: the real fix

static List<Book> findSafe(Connection c, String title, String minCopies) throws SQLException {
    int min;
    try {
        min = Integer.parseInt(minCopies);
    } catch (NumberFormatException e) {
        throw new IllegalArgumentException("minCopies must be a whole number");
    }
    String sql = "SELECT id, title, isbn, copies FROM books WHERE title = ? AND copies >= ?";
    List<Book> out = new ArrayList<>();
    try (PreparedStatement ps = c.prepareStatement(sql)) {
        ps.setString(1, title);
        ps.setInt(2, min);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                out.add(new Book(rs.getInt("id"), rs.getString("title"),
                        rs.getString("isbn"), rs.getInt("copies")));
            }
        }
    }
    return out;
}

Two defences work together, and it helps to see which does what:

1. Parameters. The SQL text is a fixed constant. The title is bound with setString, the number with setInt. PostgreSQL receives the query text and the values in separate protocol messages (Parse, then Bind), so a value is never parsed as SQL.
2. Input validation. A text field that should be a number is converted to an int before it gets near the database. Anything else is rejected with a clear message for the user. Validation is good practice (it gives a friendly error), but it is not the security measure; the parameters are.

Now every attack again:

safe: []                                           (title attack: just an odd title, no match)
safe: rejected: minCopies must be a whole number   (0 OR 1=1)
safe: rejected: minCopies must be a whole number   (UNION ...)
safe normal: [Book[id=12, title=Clean Code, isbn=111, copies=2]]

The honest call still works, and no attack returns data it should not.

The rule, stated plainly

Every value goes through a ? parameter, always, with no exceptions for "safe-looking" values. What cannot be a parameter (a column name or sort direction) is chosen from a short list of literals in your code, never copied from the user. Lesson 4 explains this.