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

Navigation
12

I always read the blog Java Revisited and I was reading the article today Why use PreparedStatement in Java JDBC - Example Tutorial . At one point the author talks about parameterization of queries, it is better, safer and more performative to use the parameterization offered by the API than concatenate strings and uses 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);

It explains that when using the parameterization the JDBC driver will check the data and with String concatenated not, it will only execute the sent SQL, 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, that would be worth it if I have an always static parameter too, for example: 

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

Or it's 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, as there is no parameter passing through the user there is no way to have the attack, right?

    
asked by anonymous 14.02.2014 / 19:25

2 answers

7

TL; DR

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

How Prepared Statements Work

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

When you run statement with the parameters set, the driver will send values only and request query from < in> cache .

The performance advantage ( performance ) occurs from the second execution because the database server already has the query compiled into cache . He does not need to interpret it again. In general, queries that execute only once can only use Statement .

The security advantage 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 a SQL clause, resulting in a SQL Injection .

What are Parameters ?

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

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

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

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

Have you talked about performance and security, but why are Prepared Statements

better?

They're better because they're safer and more performative, by the way!

    
14.02.2014 / 19:51
6

Using Prepared Statements is safer than concatenating the parameters directly in the query, this is true. However, if the parameter is fixed, I do not see why passing it as a parameter in the Prepared Statement. For me, you can enter the query directly.

Unless you want to leave this parameter in the future, then I think it's worthwhile to leave it in the setString method.

    
14.02.2014 / 19:29
How does the Xor Swap algorithm work? What is the meaning of a "Manifest" file in programming?