使用 Prepared Statements

在 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 資料庫