Binding parameters for inserting constants in query?

Question

Using bound parameters (using ?) in Prepared Statement is a popular strategy for inserting parameters in SQL queries at runtime. My question is, should this strategy be used for inserting constants into queries? I am talking about constants that are not related to the data input by user in any way. Is there any reason why I shouldn't use these constants directly in an SQL query, but use a Prepared Statement instead?

To show what I am talking about through code:

public static final String x = "xxx";

getJdbcTemplate.update("SELECT * FROM TABLE WHERE COLUMN = ?", x);

vs

getJdbcTemplate.update("SELECT * FROM TABLE WHERE COLUMN = '" + x + "'");

If it's not a constant within the SQL text itself, then I'd opt for the bind parameter, I'd rather have static SQL text, than dynamically generated SQL text. Future readers won't have to be figuring out if the code is vulnerable to SQL injection, having to track back to figure out that `x` is actually "safe" for inclusion. If we could avoid the java string constant, and just put "`'xxx'`" directly into the SQL string, I'd do that. (So, yes, this strategy should be used for inserting constants into queries. It's easier on future readers/maintainers, not vulnerable to SQL Injection ) — spencer7593, Oct 12 '18 at 18:23
@spencer7593 "I'd rather have static SQL text, than dynamically generated SQL..." - but isn't using bound parameters dynamic SQL? But your point about future readers is valid. Thanks. — lebowski, Oct 12 '18 at 18:27
`"SELECT * FROM TABLE WHERE COLUMN = ?"` is a string constant. There's nothing dynamic in the SQL text. With server side prepares, the SQL text is sent to the database server without the values in the text. The values are supplied separately. (It is true that with MySQL emulated prepares on the client side, the JDBC driver is going to take the bind parameters and the SQL text and generate a SQL statement. But that's the driver doing that, not my code. My code is ready for server side prepared statements.) — spencer7593, Oct 12 '18 at 18:32
You never want to use the second method. Will kill oracle performance on doing a hard parse for each unique sql with the embeded literal. — OldProgrammer, Oct 12 '18 at 18:52

score 0 · Answer 1 · answered Oct 12 '18 at 19:05

When you write a query like this,

update("SELECT * FROM TABLE WHERE COLUMN = " + x,);

instead of this,

update("SELECT * FROM TABLE WHERE COLUMN = ?", x);

The first query does not get benefited by the pre-compilation of the query.

Instead when you use ? in the query, and say the query in production is run hundreds to thousands to million times, the pre compiled query (query which contains ?) gets re-used again and again avoiding the re-compilation of query and this phenomenon makes it much better performance wise, than the query that contains hard-coded data.

As the query contains a hardcoded data, which makes the query different everytime because hardcoded data will mostly vary everytime with the query, requiring compilation of query everytime making performance hit.

But if you just have to run the query few times then you won't gain any significant performance gain.

Binding parameters for inserting constants in query?

1 Answers1