I have a SQL statement that looks like this SELECT * FROM VAL WHERE VAL_VALUTA >= CHF ORDER BY VAL_VALUTA ASC LIMIT 10. The statement works fine and now I'm trying to use it in Java with PreparedStatement with the following code (I left out the connection and the try blocks)
String sqlQuery = "SELECT * FROM VAL WHERE VAL_VALUTA ? ? ORDER BY VAL_VALUTA ASC LIMIT ?";
PreparedStatement statement = connection.prepareStatement(sqlQuery);
statement.setString(1, searchDirection);
statement.setString(2, currencyCode);
statement.setInt(3, numberOfCurrenciesReturned);
I get the following output in the console when I print statement:
sql : 'SELECT * FROM VAL WHERE VAL_VALUTA ? ? ORDER BY VAL_VALUTA ASC LIMIT ?', parameters : ['>=','CHF',5]
It all looks fine but it returns nothing. If I hard code >= in sqlQuery then it works fine but I rather not do that since I need to change it for different conditions to <=.
Is there any way around this or do I need to hard code it? I'm using Java 8, DBCP2 and MariaDB.
First let's talk about why this is happening
There's a difference between the query itself and its parameters. >= is part of the query, 5 is a parameter.
Why is that? Well the prepared statement is trying to protect you against anything dangerous that might be passed in the query as a parameter. Say someone passes you something like IS NULL; DROP TABLE VAL_DATA ... instead of the string >=. You'll end up running SELECT * FROM VAL WHERE VAL_VALUTA IS NULL; DROP TABLE VAL_DATA;. This will kill the entire VAL_DATA table. To avoid such security risks (and honest mistakes), prepared statements escape the values of parameters, and tricking them into doing so by generating the query string dynamically is probably not the best idea, although that'll work and that's what most other answers suggest you do.
I think that the best and most secure solution is to have a few different queries for the different cases. There aren't that many cases.
String queryLE = "SELECT * FROM VAL WHERE VAL_VALUTA <= ? ORDER BY VAL_VALUTA ASC LIMIT ?";
String queryEQ = "SELECT * FROM VAL WHERE VAL_VALUTA == ? ORDER BY VAL_VALUTA ASC LIMIT ?";
etc..
This is simple, secure, and safe. There's no way to nuke the entire database just by passing a parameter.