使用预处理语句

在 Java 中查询 PostgreSQL 数据库

Miller Trujillo

Staff Software Engineer

Statements 的问题

String query = "SELECT * FROM books WHERE title = '" + title + "'";
stmt.executeQuery(query);

当将用户输入直接拼接进 SQL 时会发生 SQL 注入

String title = "' OR 1 = 1 --";

攻击者可篡改查询逻辑

String query = "SELECT * FROM books WHERE title = '' OR 1 = 1 --'";

SQL 注入还可能修改数据 🚫

SQL 注入

String query = "SELECT * FROM books WHERE title = '" + title + "'";
stmt.executeQuery(query);

title = "'; DELETE FROM books; --"

query = "SELECT * FROM books WHERE title = ''; DELETE FROM books; -- REST of query"

所有记录都会被删除 🔄

限制权限有帮助，但不足以防护
攻击者仍可查询敏感数据

预处理语句（Prepared Statements）

预编译的 SQL，使用占位符

String sql = "SELECT * FROM books WHERE title = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "Alice in Wonderland");

ResultSet rs = pstmt.executeQuery();

参数会被转义并当作数据处理，永不作为可执行 SQL 🔒

处理多个参数

String sql = "SELECT * FROM books WHERE title = ? AND publication_year = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "Alice in Wonderland");
pstmt.setInt(2, 1998);

ResultSet rs = pstmt.executeQuery();

每种数据类型都有对应的 setter 方法

让我们来练习！

在 Java 中查询 PostgreSQL 数据库