Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 119 ยท Databases with JDBC

PreparedStatement and SQL Injection

Break into a login built with string concatenation, see why PreparedStatement parameters stop SQL injection, and handle LIKE, IN lists and ORDER BY safely in JDBC.

SQL injection happens when text supplied by a user becomes part of the structure of an SQL statement instead of staying a plain value. It is one of the oldest and most damaging security bugs on the web: attackers use it to log in without a password, read entire customer tables, and modify or delete data. It has been on the OWASP Top 10 list of critical web risks since the list began, and it still appears in real systems every year, almost always for the same reason: someone built SQL by gluing strings together.

In this lesson you will attack a deliberately vulnerable login and search feature running on a real database, understand exactly why the attacks work, and then make them impossible with PreparedStatement, which you met in CRUD with JDBC. Then you will handle the cases where beginners (and some seniors) still go wrong: LIKE searches, IN lists with a variable number of values, and dynamic ORDER BY columns, which parameters cannot cover.

Only attack your own systems. The techniques here are shown so you can recognize and prevent them. Testing them against systems you do not own or have no written permission to test is illegal in most countries.

A login built the wrong way

Here is login code that looks reasonable at first glance. It reads a username and password, builds a query and logs the user in if a row comes back. The input box is preset with a username and password; press Run first, then try your own values (one per line).

Java A vulnerable login

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

(The prompts appear on one line, because input from the Input box is not echoed.) The preset username is admin' -- and the password is nonsense, yet the program prints "Welcome, admin! Role: ADMIN". Look at the SQL it printed:

SQL
SELECT username, role FROM users WHERE username = 'admin' --' AND password = 'I forgot'

The quote after admin closes the string literal the programmer opened. -- starts an SQL comment, so the rest of the line, including the entire password check, is ignored by the database. The attacker did not guess a password; they rewrote the query. Change the input to maya and sunflower to see a normal login, and to maya and wrong to see a normal failure.

What attackers can do with it

Bypassing a login is only the start. Every place that concatenates input is a door into the whole database, with the permissions of your application's database user. This program runs four inputs against two vulnerable features: the login above and a product search. The first input is not even an attack.

Java Four inputs against concatenated SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Read the output case by case:

  1. O'Reilly is a perfectly honest search, and it crashes with a syntax error. Concatenated SQL is not only insecure, it is broken for real names like O'Brien and N'Diaye. Many injection holes are found exactly this way: an error page appears for an apostrophe.
  2. ' OR '1'='1 as the password makes the condition username = 'maya' AND password = '' OR '1'='1'. Because AND binds tighter than OR, that is "(no match) OR true", which is true for every row. The query returns all users with admin first, and a login that takes the first row lets the attacker in as admin without knowing any password.
  3. UNION SELECT appends a second query whose rows are mixed into the search results. The "products" listed are now every username and password in the database. With a little guesswork (or error messages), an attacker can dump any table this way.
  4. '; DROP TABLE product; -- ends the first statement and adds a second one. H2 runs both, and the final, ordinary search for lamp fails with "Table "PRODUCT" not found". The product catalog is gone.

Whether stacked statements like case 4 work depends on the driver. MySQL's Connector/J refuses them unless allowMultiQueries=true, while H2 runs both, and PostgreSQL's driver also accepts several statements in one plain Statement. But cases 2 and 3 work everywhere, and they are enough to take over an application.

Escaping by hand is not the fix

A tempting "fix" is to double every quote in the input (input.replace("'", "''")). Don't. It is easy to forget once in a large codebase, it depends on the database (MySQL also treats backslash as an escape character by default), and it does nothing for values that are not inside quotes at all:

Java
// id comes from a URL like /orders?id=17
String sql = "SELECT * FROM orders WHERE id = " + id;
// id = "17 OR 1=1"  ->  SELECT * FROM orders WHERE id = 17 OR 1=1   (every order, no quote needed)

Validation (such as parsing id with Long.parseLong) is a good extra layer, but the real fix is to stop building SQL from input.

The fix: parameters

With a PreparedStatement, the SQL text is fixed and contains only ? placeholders. User input is passed separately with setString, setLong and friends:

Java
String sql = "SELECT username, role FROM users WHERE username = ? AND password = ?";
try (PreparedStatement ps = db.prepareStatement(sql)) {
    ps.setString(1, username);
    ps.setString(2, password);
    try (ResultSet rows = ps.executeQuery()) { ... }
}

Why this is safe, and not just "escaped better":

  • The database (or driver) parses the statement first, with placeholders. At that point its structure is decided: one SELECT, one table, two conditions joined by AND. Nothing that arrives later can add a UNION, a comment or a second statement.
  • The values are then sent as data. With server-side prepared statements (PostgreSQL's driver, for example), they travel in a separate protocol message from the SQL text. Drivers that prepare on the client side (MySQL Connector/J by default) escape them according to their type, the connection's character set and the server's settings, which is exactly the correctness you cannot guarantee by hand.
  • To the database, admin' -- is now just a strange username with an apostrophe in it, and no user has that name.

Here are the same four inputs against the parameterized versions:

Java The same inputs with PreparedStatement

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Now O'Reilly finds the mug, every attack simply finds no rows, the product table survives, and the real login still works. The SQL never changed shape. Note that in search, the % wildcards are concatenated into the value, not into the SQL. That is fine: the value is still bound as a parameter.

Make it a rule you can check. "No string concatenation or formatted() in SQL that contains a variable" is easy to verify in code review, and static analysis tools (SpotBugs, Sonar, Error Prone) flag it automatically (Code Quality and Static Analysis).

LIKE searches: wildcards in the input

Parameters stop injection, but in a LIKE pattern the characters % (any text) and _ (any single character) still have a meaning inside the value. A user who searches for % matches every row, and a search for 100% or file_name does not mean what the user typed. On a large table a pattern such as %a%b%c%d% can also be slow. The fix is to escape the wildcards in the user's text and tell the database which escape character you used with an ESCAPE clause:

Java Safe LIKE with an ESCAPE clause

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Without escaping, report_ also matches report-2026.pdf (the _ matches the dash), 100% also matches growth 1000.xlsx, and a bare % returns every document. With escaping, each search matches only the names that literally contain what the user typed, so % finds just the one file whose name really contains a percent sign. An explicit ESCAPE character keeps this portable: databases disagree about whether backslash is a default escape for LIKE, so don't rely on one.

IN lists with a variable number of values

A filter such as "products in these categories" needs WHERE category IN (...) with a list whose length you only know at runtime. A single ? cannot stand for a whole list, so beginners concatenate the values, and the injection hole is back. Two safe options:

  1. Generate one placeholder per value: the number of ? changes, but no input ever enters the SQL text.
  2. Bind an array with = ANY(?), supported by H2 and PostgreSQL (not MySQL). The SQL text stays identical for any list size, which also helps statement caching.
Java Safe IN lists

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Both methods return the two lamps and the two seating products, and the hostile "category" is just a value that matches nothing. Notice what does go into the SQL text in byPlaceholders: only question marks, generated by your code. That is the general principle: SQL may be built dynamically from trusted pieces you write yourself, never from input. Also note the empty-list guard, since IN () is a syntax error. For very long lists (thousands of values), use a temporary table or the array form instead; databases limit the number of parameters per statement.

Dynamic ORDER BY, column and table names

Parameters can only stand for values. They cannot replace identifiers (column and table names), keywords such as ASC and DESC, or ORDER BY expressions. ORDER BY ? is either rejected or sorts by a constant, so nothing happens. Yet "sort by the column the user clicked" is a common feature, and it is the most common place injection survives in otherwise careful code.

The fix is an allow-list: map the user's choice to SQL fragments you wrote yourself, and reject everything else. An enum or a Map makes that natural:

Java Sorting safely with an allow-list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The two pages sorted by price show the cheapest two products, then the next two; newest maps to the added_on column, which the client never needs to know; and the injected subquery is rejected before any SQL is built. The direction is also never copied from input: anything other than desc becomes ASC. The LIMIT and OFFSET values, on the other hand, are ordinary parameters.

Beyond JDBC: injection everywhere

The same rule applies to every layer that builds queries:

  • JPA and Hibernate: JPQL and HQL can be injected exactly like SQL if you concatenate ("FROM User u WHERE u.name = '" + name + "'"). Use named parameters (:name with setParameter) or the Criteria API (Querying with JPQL and the Criteria API). Native queries are plain SQL.
  • Spring Data: derived query methods and @Query with parameters are safe; string-built queries are not.
  • Stored procedures are safe only if they don't build dynamic SQL inside from their arguments.
  • Second-order injection: data that was stored safely can still be dangerous later, if some other code (a report, a batch job) concatenates it into SQL. "It came from our own database" is not a reason to trust it.

And add defense in depth, so a single mistake is not a catastrophe:

  • Least privilege: the application's database user should not own the schema. It needs SELECT, INSERT, UPDATE and DELETE on its tables, not DROP, and certainly not superuser rights. Run migrations with a separate account.
  • Don't show database errors to users. Log them; return a generic message. Detailed errors help attackers map your schema.
  • Hash passwords with a slow, salted algorithm and never store them as plain text like the demo above (Web Security Basics). Then even a successful data leak does not reveal passwords directly.
  • Validate input for type and format (a numeric ID, an email address) as an extra layer, not as the main protection.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A login query is built as "... WHERE username = '" + u + "' AND password = '" + p + "'". The attacker enters the username admin' --. Why does the login succeed?

    The input ends the username literal early, then turns the rest of the statement into a comment. Only username = 'admin' is left.
  2. 2. Why does a PreparedStatement parameter prevent injection?

    Parsing happens with placeholders. The bound value is only ever treated as a value of the parameter's type, however many quotes or keywords it contains.
  3. 3. A user searches for 50% with WHERE name LIKE ? and the value "%" + text + "%". What happens?

    Parameters stop injection, but the LIKE operator still interprets % and _ in the pattern value. Escape them to match literally.
  4. 4. How should you implement a user-selectable sort column?

    Parameters can't replace identifiers. An allow-list guarantees that only SQL you wrote yourself reaches the statement.
  5. 5. Which of these is still vulnerable to SQL injection?

    JPQL built by concatenation is injectable just like SQL. Use :email with setParameter instead.

Summary

  • SQL injection happens when input becomes part of an SQL statement's structure. It lets attackers bypass logins (' OR '1'='1, admin' --), read other tables (UNION SELECT) and, with some drivers, run extra statements (; DROP TABLE).
  • Concatenated SQL is also simply broken for honest input such as O'Reilly. Escaping quotes by hand is fragile and useless for unquoted values like id = 17 OR 1=1.
  • PreparedStatement parameters fix it: the statement is parsed with ? placeholders, and values are bound as data. Use them for every value, without exception.
  • In LIKE patterns, escape %, _ and the escape character, and add an explicit ESCAPE clause.
  • For IN lists, generate one ? per value (guarding the empty list) or bind an array with = ANY(?) where supported.
  • Identifiers and keywords (columns, tables, ASC/DESC) cannot be parameters: use an allow-list of SQL fragments you wrote. Build SQL dynamically only from trusted pieces.
  • The rule applies to JPQL, HQL and native queries too. Add least privilege, generic error messages, password hashing and input validation as defense in depth.

Next: Transactions with JDBC, where you group several statements into one all-or-nothing unit and see what happens when a money transfer fails halfway.