Prepared statements

In the previous lesson the values were written right into the SQL text. In a real program they come from outside — from a form, from a user, from a file. Gluing them into the query string is dangerous, so queries with data use prepared statements — PreparedStatement. In every example of this lesson the database is created by the openShop() method at the end of the file: a products table with three products, as in the previous lesson.

Parameters

In the text of a prepared statement, ? signs — parameters — stand in place of values. The statement is created with conn.prepareStatement(sql), then values are filled in with setString, setInt, setDouble, and the statement is run. Parameter numbers start at 1:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openShop();
         PreparedStatement ps = conn.prepareStatement("SELECT title, price FROM products WHERE price < ? ORDER BY id")) {
        ps.setDouble(1, 6.0);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                IO.println(rs.getString("title") + " — $" + rs.getDouble("price"));
            }
        }
    }
}
 
// Test database: a products table with three products
Connection openShop() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
        st.executeUpdate("INSERT INTO products (title, price) VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)");
    }
    return conn;
}

Output:

Tea — $4.5
Cocoa — $5.0

The same PreparedStatement can be run many times with different values: the database parses the query once, and repeated calls are faster.

SQL injection

If user input is glued straight into SQL, a cleverly crafted string changes the meaning of the query. This is called SQL injection — one of the most common vulnerabilities. Here the "product name" turns the condition into "always true":

import java.sql.*;
 
void main() throws SQLException {
    String input = "' OR '1'='1";
 
    try (Connection conn = openShop(); Statement st = conn.createStatement()) {
        String sql = "SELECT count(*) FROM products WHERE title = '" + input + "'";
        IO.println(sql);
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        IO.println("Gluing found: " + rs.getInt(1));
 
        PreparedStatement ps = conn.prepareStatement("SELECT count(*) FROM products WHERE title = ?");
        ps.setString(1, input);
        ResultSet safe = ps.executeQuery();
        safe.next();
        IO.println("Parameter found: " + safe.getInt(1));
    }
}
 
// Test database: a products table with three products
Connection openShop() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
        st.executeUpdate("INSERT INTO products (title, price) VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)");
    }
    return conn;
}

Output:

SELECT count(*) FROM products WHERE title = '' OR '1'='1'
Gluing found: 3
Parameter found: 0

Gluing returned every product — an attacker could read other people's data or drop the table this way. With a parameter the whole string counts as a value: there's no product with such a strange name. The rule is simple: data goes only through ?.

Inserting, updating and deleting

INSERT, UPDATE and DELETE are run with executeUpdate — it returns the number of affected rows. That tells you whether anything was found to change:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openShop()) {
        PreparedStatement insert = conn.prepareStatement("INSERT INTO products (title, price) VALUES (?, ?)");
        insert.setString(1, "Juice");
        insert.setDouble(2, 3.2);
        IO.println("Added: " + insert.executeUpdate());
 
        PreparedStatement update = conn.prepareStatement("UPDATE products SET price = price * ? WHERE price > ?");
        update.setDouble(1, 0.9);
        update.setDouble(2, 4.0);
        IO.println("Cheaper: " + update.executeUpdate());
 
        PreparedStatement delete = conn.prepareStatement("DELETE FROM products WHERE title = ?");
        delete.setString(1, "Lemonade");
        IO.println("Deleted: " + delete.executeUpdate());
    }
}
 
// Test database: a products table with three products
Connection openShop() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
        st.executeUpdate("INSERT INTO products (title, price) VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)");
    }
    return conn;
}

Output:

Added: 1
Cheaper: 3
Deleted: 0

There's no "Lemonade" product, so DELETE removed nothing — that's not an error, just a result of 0.

Generated keys

After an INSERT you often need the id the database assigned to the new row. You ask for it in advance — with the Statement.RETURN_GENERATED_KEYS flag — and fetch it with getGeneratedKeys():

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openShop();
         PreparedStatement ps = conn.prepareStatement(
                 "INSERT INTO products (title, price) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) {
        for (String title : List.of("Juice", "Smoothie")) {
            ps.setString(1, title);
            ps.setDouble(2, 3.5);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                IO.println(title + " got id " + keys.getLong(1));
            }
        }
    }
}
 
// Test database: a products table with three products
Connection openShop() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
        st.executeUpdate("INSERT INTO products (title, price) VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)");
    }
    return conn;
}

Output:

Juice got id 4
Smoothie got id 5

Common mistake

A ? parameter replaces only a value. A table or column name or an SQL keyword can't be passed as a parameter — the query won't parse:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openShop()) {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM ? WHERE price < ?");
    }
}
 
// Test database: a products table with three products
Connection openShop() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price REAL)");
        st.executeUpdate("INSERT INTO products (title, price) VALUES ('Tea', 4.5), ('Coffee', 7.25), ('Cocoa', 5.0)");
    }
    return conn;
}

Output (the driver's stack lines are omitted):

Exception in thread "main" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "?": syntax error)
	at Main.main(Main.java:5)

If the user chooses a table name or sort order, don't glue it in as is: check it against a list of allowed options, for example with a switch, and insert only known names.

Thanks for reading!

Parameters

SQL injection

Inserting, updating and deleting

Generated keys

Common mistake

Thanks for reading!