Is it better to parameterize your Preparedstatement even if the value is fixed?

Question

I always read the blog Java Revisited and I’ve been reading the article today Why use Preparedstatement in Java JDBC - Example Tutorial. At a certain point the author talks about parameterization of queries, which is better, safer and more performative to use the parameterization offered by the API than concatenating Strings and using the examples below:

SQL Query 1: Preparedstatement using String concatenation

String loanType = getLoanType();
PreparedStatement pstmt = conn.prepareStatement("select banks from loan where loan_type = " + loanType);

SQL Query 2: Query parameterized using Preparedstatement

PreparedStatement pstmt = conn.prepareStatement("select banks from loan where loan_type = ?");
prestmt.setString(1, loanType);

He explains that when using the parameterization the JDBC driver will check the data and with String concatenated no, it will just run the SQL sent, ie if we have that famous OR 1 = 1 the query will always return true and there is the famous SQL Injection.

What was not very clear to me is because one is more performative than the other and more, this would be worth it in case I have an always static parameter too, for example:

PreparedStatement pstmt = conn.prepareStatement(select * from usuarios where ativo = 'S');

Or is it more performative to use as below:

PreparedStatement pstmt = conn.prepareStatement(select * from usuarios where ativo = ?);
pstmt.setString(1, "S");

Another question, in this case I think the security issue is the same, because as there is no parameter passage through the user there is no way to have the attack, right?

Answer 1

TL;DR

In most cases use Prepared Statements is better, safer and more performative. But it is not a rule.

How do the Prepared Statements

When you create a Prepared statement, the JDBC driver sends the query without the parameters to be compiled by the database server.

When executing the statement with the parameters set, the driver will send only the values and request the execution of query from the cache.

The advantage over performance (performance) occurs from the second execution, as the database server is already with the query compiled in cache. It does not need to interpret it again. In general, queries that run only once can use only the Statement.

The advantage in relation to security is that the database server receives the query and the parameters at different times, so there is no risk of mixing a user parameter with an SQL clause, resulting in a SQL Injection.

What are Parameters?

As to the question of what is or is not a parameter, whether or not it concatenates into the query, consider the origin and possibility of changing its value.

For example, in a method getClientesAtivos() with a clause WHERE status='ATIVO' it makes no sense to put the value 'ATIVO' in a parameter, since it does not change.

On the other hand, if the method was getClientes(String status), then you must set this value as a parameter.

Even if the value does not come from the user, if it vary, put it as a parameter, because the JDBC driver will only send the query once to the database server, this will improve the performance.

You’ve talked about performance and safety, but because Prepared Statements are better?

They’re better because they’re safer and more performative!

Answer 2

That using Prepared Statements is safer than concatenating the parameters directly into the query, that’s true. However, if the parameter is fixed, I do not see why to pass it as a parameter of Prepared Statement. For me you can enter directly in the query.

Unless in the future you want to make this parameter configurable, then I think it is worth leaving it in the method setString.