Using Prepared Statements

Querying a PostgreSQL Database in Java

Miller Trujillo

Staff Software Engineer

The problem with Statements

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

SQL injection happens when user input is embedded directly into SQL

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

Attackers can change query logic

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

SQL Injection can modify our data 🚫

SQL Injection

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"

All the records will be deleted 🔄

Limiting permissions helps, but isn't enough
Attackers can still query sensitive data

Prepared Statements

Precompiled SQL statement that uses placeholders

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

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

ResultSet rs = pstmt.executeQuery();

Parameters are escaped and treated as data, never executable SQL 🔒

Working with multiple parameters

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();

Each data type has its own setter method

Let's practice!

Querying a PostgreSQL Database in Java