How does Java's PreparedStatement work?

Question

I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.

Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the ? in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the ?s in the queries instead of actual values? I am quite confounded.

Solution 1

If you have variables use the '?'

int temp = 75;
PreparedStatement pstmt = con.prepareStatement(
    "UPDATE test SET num = ?, due = now() ");
pstmt.setInt(1, temp); 
pstmt.executeUpdate():

Produces an sql statment that looks like:

UPDATE test SET num = 75, due = now();

Solution 2

If you have a variable that comes from user input, it's essential that you use the ? rather than concatenating the strings. Users might enter a string maliciously, and if you drop the string straight into SQL it can run a command you didn't intend.

I realise this one is overused, but it says it perfectly:

Comments

[Adeel Ansari]

I believe, you mean SQL Injections.

[Mark Rotteveel]

This is not correct for most database systems. Most database systems will prepare the query text, and then send the parameter values on execute. This will never produce a statement that literally contains the parameter value.