Подготовленные запросы

В прошлом уроке значения были вписаны прямо в текст SQL. В настоящей программе они приходят извне — из формы, от пользователя, из файла. Склеивать их со строкой запроса опасно, поэтому для запросов с данными используют подготовленные запросы — PreparedStatement. Во всех примерах урока база создаётся методом openShop() в конце файла: таблица products с тремя товарами, как в прошлом уроке.

Параметры

В тексте подготовленного запроса вместо значений стоят знаки ? — параметры. Запрос создают методом conn.prepareStatement(sql), затем подставляют значения методами setString, setInt, setDouble и выполняют. Номера параметров начинаются с 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"));
            }
        }
    }
}
 
// Тестовая база: таблица 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 ('Чай', 4.5), ('Кофе', 7.25), ('Какао', 5.0)");
    }
    return conn;
}

Результат:

Чай — $4.5
Какао — $5.0

Один и тот же PreparedStatement можно выполнять много раз с разными значениями: база разбирает запрос один раз, и повторные вызовы работают быстрее.

SQL-инъекция

Если вклеить ввод пользователя прямо в SQL, хитро составленная строка изменит смысл запроса. Это называется SQL-инъекцией — одна из самых частых уязвимостей. Здесь «имя товара» превращает условие в «всегда истина»:

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("Склейка нашла: " + 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("Параметр нашёл: " + safe.getInt(1));
    }
}
 
// Тестовая база: таблица 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 ('Чай', 4.5), ('Кофе', 7.25), ('Какао', 5.0)");
    }
    return conn;
}

Результат:

SELECT count(*) FROM products WHERE title = '' OR '1'='1'
Склейка нашла: 3
Параметр нашёл: 0

Склейка выдала все товары — так злоумышленник мог бы прочитать чужие данные или удалить таблицу. С параметром строка целиком считается значением: товара с таким странным названием нет. Правило простое: данные — только через ?.

Добавление, изменение и удаление

INSERT, UPDATE и DELETE выполняют методом executeUpdate — он возвращает число затронутых строк. По нему видно, нашлось ли что изменять:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openShop()) {
        PreparedStatement insert = conn.prepareStatement("INSERT INTO products (title, price) VALUES (?, ?)");
        insert.setString(1, "Сок");
        insert.setDouble(2, 3.2);
        IO.println("Добавлено: " + 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("Подешевело: " + update.executeUpdate());
 
        PreparedStatement delete = conn.prepareStatement("DELETE FROM products WHERE title = ?");
        delete.setString(1, "Лимонад");
        IO.println("Удалено: " + delete.executeUpdate());
    }
}
 
// Тестовая база: таблица 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 ('Чай', 4.5), ('Кофе', 7.25), ('Какао', 5.0)");
    }
    return conn;
}

Результат:

Добавлено: 1
Подешевело: 3
Удалено: 0

Товара «Лимонад» нет, поэтому DELETE ничего не удалил — это не ошибка, а результат 0.

Сгенерированные ключи

После INSERT часто нужен id новой строки, который назначила база. Его просят заранее — флагом Statement.RETURN_GENERATED_KEYS — и забирают методом 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("Сок", "Морс")) {
            ps.setString(1, title);
            ps.setDouble(2, 3.5);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                IO.println(title + " получил id " + keys.getLong(1));
            }
        }
    }
}
 
// Тестовая база: таблица 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 ('Чай', 4.5), ('Кофе', 7.25), ('Какао', 5.0)");
    }
    return conn;
}

Результат:

Сок получил id 4
Морс получил id 5

Частая ошибка

Параметр ? заменяет только значение. Имя таблицы или столбца, ключевое слово SQL через параметр передать нельзя — запрос не разберётся:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openShop()) {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM ? WHERE price < ?");
    }
}
 
// Тестовая база: таблица 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 ('Чай', 4.5), ('Кофе', 7.25), ('Какао', 5.0)");
    }
    return conn;
}

Результат (строки стека драйвера опущены):

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

Если имя таблицы или порядок сортировки выбирает пользователь, не вклеивайте его как есть: сверьте со списком допустимых вариантов, например через switch, и подставляйте только известные имена.

Спасибо, что читаете!

Параметры

SQL-инъекция

Добавление, изменение и удаление

Сгенерированные ключи

Частая ошибка

Спасибо, что читаете!