حقن SQL (SQL injection) مباشرةً

Written Explanation

The vulnerable method

Here is a search method written the tempting way, by gluing the title into the SQL text:

static List<String> findTitleUnsafe(Connection c, String title) throws SQLException {
    String sql = "SELECT title FROM books WHERE title = '" + title + "'";
    // ... run it with a Statement and read the titles
}

With an honest input the query is fine: WHERE title = 'Clean Code'. But the database never sees "a title". It sees one long string of SQL, and it cannot know which characters came from you and which from the user. If the user types a single quote, they can end the text value early and keep writing SQL.

Attack 1: always true

Input: x' OR '1'='1. The finished SQL is:

SELECT title FROM books WHERE title = 'x' OR '1'='1'

title = 'x' is false, but '1'='1' is true, and OR makes the whole condition true for every row. A search for one title returns the entire table. The same trick on a login query (WHERE name = '...' AND password = '...') can log an attacker in without a password.

Attack 2: reading another table

Input: x' UNION SELECT name FROM members --. The -- starts an SQL comment, which swallows the closing quote the code appended. The finished SQL is:

SELECT title FROM books WHERE title = 'x' UNION SELECT name FROM members --'

UNION adds the rows of a second query to the first. A book search now returns member names. In a real system this is how password hashes and personal data leak, from a table the search page was never meant to touch.

Attack 3: changing or destroying data

Input: x'; DROP TABLE scratch_notes; -- ends the first statement and starts a second one. With our driver, the query method threw Multiple ResultSets were returned by the query. but the table was dropped anyway: afterwards \dt scratch_notes reported that no such table exists. The exception arrived too late. This is why injection is described as a top web risk: the damage ranges from leaked rows to deleted tables.

Even honest input can break it

A visitor searching for a title with an apostrophe, such as O'Brien's Guide, needs no malicious intent. The unsafe query is title = 'O'Brien's Guide', and PostgreSQL answers ERROR: syntax error at or near "Brien". Concatenation is wrong for innocent data too.

The fix: parameters

String sql = "SELECT title FROM books WHERE title = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
    ps.setString(1, title);
    ...
}

The SQL text is now fixed: it never changes, whatever the user types. The value is handed over separately. PostgreSQL's protocol has separate steps for this: a Parse step carries only the query text with placeholders, and a later Bind step carries the values. The server treats a bound value purely as a value. The whole of x' OR '1'='1 is then just a very odd title, and no book has it.

That is the answer to this module's assessment question, in one sentence: parameters prevent injection because the value is sent as data, separate from the SQL, so it is never parsed as SQL.

Why "remove the quotes" is not a fix

A tempting patch is title.replace("'", ""). It might seem to help for text, but it fails in other places. Consider a lookup by number, written the same way:

String sql = "SELECT title FROM books WHERE id = " + idText;

There are no quotes to break out of, so stripping them does nothing. Input 0 OR 1=1 gives WHERE id = 0 OR 1=1, and every book comes back (we ran it). Also, blacklists miss cases and depend on the database dialect. The OWASP cheat sheet calls manual escaping strongly discouraged and puts parameterised queries first. The only reliable defence is to never build SQL text from input.

What parameters do not cover

• Names of tables, columns, or ORDER BY direction cannot be ?. If you need user-selected sorting, map the choice to a fixed value in code: if the user says "title", you append the literal "title" yourself, from a short allow-list of your own words, never the user's text.
• A ? filled with % in a LIKE query still matches everything: WHERE title LIKE ? with % returned all three books in our run. That is not injection (nothing escaped the value), only the wildcard doing its job.
• Parameters stop injection but do not decide who may see a record. Authorization is a separate job.
• Concatenating into the SQL before prepareStatement removes the protection. Only fixed text can be prepared safely.