Statement vs PreparedStatement (SQL Injection)

SQL injection is an attack in which a user types text that changes the meaning of your SQL statement. It happens when a program builds SQL by joining strings and includes user input directly. The input was meant to be a value, but the database reads part of it as SQL. Attackers have used it to log in without passwords, read private data and delete tables, and it has stayed on lists of the most serious web security problems for many years. The defense is simple and complete: use a PreparedStatement with placeholders. In this lesson, you will watch an injection attack succeed against a Statement, see the same attack fail against a PreparedStatement, and compare the two.

Example: Breaking a Login Check

The program below checks a user name and password against an account table, first by joining strings, then with placeholders. The "password" the attacker types is ' OR '1'='1.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class Injection {
    static boolean loginUnsafe(Connection con, String user, String password)
            throws Exception {
        String sql = "SELECT COUNT(*) FROM account WHERE username = '" + user
            + "' AND password = '" + password + "'";
        System.out.println("  SQL sent: " + sql);
        try (Statement st = con.createStatement();
             ResultSet rs = st.executeQuery(sql)) {
            rs.next();
            return rs.getInt(1) > 0;
        }
    }
 
    static boolean loginSafe(Connection con, String user, String password)
            throws Exception {
        String sql = "SELECT COUNT(*) FROM account "
            + "WHERE username = ? AND password = ?";
        try (PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, user);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getInt(1) > 0;
            }
        }
    }
 
    public static void main(String[] args) throws Exception {
        try (Connection con = DriverManager.getConnection("jdbc:h2:mem:bank")) {
            try (Statement st = con.createStatement()) {
                st.execute("CREATE TABLE account ("
                    + "username VARCHAR(30), password VARCHAR(30))");
                st.executeUpdate(
                    "INSERT INTO account VALUES ('navika', 'S3cret!')");
            }
            String attack = "' OR '1'='1";
 
            System.out.println("Statement, correct password:");
            System.out.println("  logged in? "
                + loginUnsafe(con, "navika", "S3cret!"));
            System.out.println("Statement, attack string as password:");
            System.out.println("  logged in? "
                + loginUnsafe(con, "navika", attack));
 
            System.out.println("PreparedStatement, correct password: "
                + loginSafe(con, "navika", "S3cret!"));
            System.out.println("PreparedStatement, attack string:    "
                + loginSafe(con, "navika", attack));
        }
    }
}

Statement, correct password:
  SQL sent: SELECT COUNT(*) FROM account WHERE username = 'navika' AND password = 'S3cret!'
  logged in? true
Statement, attack string as password:
  SQL sent: SELECT COUNT(*) FROM account WHERE username = 'navika' AND password = '' OR '1'='1'
  logged in? true
PreparedStatement, correct password: true
PreparedStatement, attack string:    false

Explanation:

1. With the correct password, the SQL is what the programmer intended.
2. With the attack string, look at the SQL that was sent. The attacker's first quote closed the password text early. What follows, OR '1'='1', became part of the command. Since '1'='1' is always true, the WHERE clause matches every row, the count is above zero, and the attacker is logged in without knowing the password.
3. With PreparedStatement, the whole attack string is sent as the password value. The database looks for an account whose password is literally ' OR '1'='1, finds none, and the login fails.

What Else an Attacker Can Do

Statement vs PreparedStatement

Is Escaping Quotes Enough?

No. Hand-written escaping is easy to get wrong: the rules differ between databases and character sets, and numeric values need no quotes to be attacked (id = 1 OR 1=1). Placeholders don't rely on escaping at all. The SQL command is fixed before any value arrives.

Other Layers of Defense

The example table stores a plain-text password only to keep the attack easy to see. Real systems must never do that.

Common Mistakes

Thinking "our users wouldn't do that". Attacks are automated; every public form is tested within hours.

Using PreparedStatement but still joining strings. con.prepareStatement("... WHERE name = '" + name + "'") is as unsafe as a Statement.

Relying on escaping or on blocking certain words. Attackers know the ways around them.

Protecting only the login form. Any query that includes outside input is a target: search boxes, sort options, web service parameters.

Interview Corner

Practice

1. Easy: Try the attack string as the user name instead of the password in loginUnsafe, and explain the SQL that is sent.
2. Harder: Write a search method that takes a sort column from the caller. Make it safe by accepting only names from a fixed Set such as name and marks.

Key Takeaways