Should I be using PreparedStatements for all my database inserts in Java?

Question

What is the recommended method for escaping variables before inserting them into the database in Java?

As I understand, I can use PreparedStatement.setString() to escape the data, but PreparedStatement seems somewhat impractical if I don't plan to run the same query ever again.. Is there a better way to do it without preparing every query?

Answer 1

Yes, use prepared statements for everything.

1. They're parsed once.

2. They're immune from SQL injection attacks.

3. They're a better design because you have to think about your SQL and how it's used.

If you think they're only used once, you aren't looking at the big picture. Some day, your data or your application will change.

---

Edit.

Why do prepared statements make you think about your SQL?

• When you assemble a string (or simply execute a literal block of text) you aren't creating a new PreparedStatement object. You're just executing SQL -- it can be done very casually.

• When you have to create (and save) a PreparedStatement, you have to think just a tiny bit more about encapsulation, allocation of responsibility. The preparation of a statement is a stateful event prior to doing any SQL processing.

The extra work is small, but not insignificant. It's what causes people to start thinking about ORM and a data caching layer, and things like that to optimize their database access.

With Prepared statements, database access is less casual, more intentional.