Query parameter not recognized on PreparedStatement when used in combination with substring

I am using a PreparedStatement to execute queries from within my Java code:

String query = "select title from video where substring_index(path,'\\',-1) = ?;"
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1,value);

The call of setString(1,value) causes: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

When executed in the terminal, the query works fine. If I don't use the substring_index() function, it also works:

String query = "select title from video where path = ?;"

It seems that the ? does not get recognized as a parameter when I use it in combination with substring_index(). I am running MySQL 8.0.17 in combination and mysql-connector-java-8.0.13.jar.

Any ideas what the issue could be?

CodePudding user response：

The problem isn't so much with the substring_index function, it's with the escaping of the \ character.

The problem is that both Java and MySQL interpret the \ character as an escape character. If you add a line System.out.println(query); after the line String query = "...";, you will get the following output:

select title from video where substring_index(path,'\',-1) = ?;

This isn't the query you want to run against MySQL, because MySQL needs you to escape the backslash. '\' isn't a string in MySQL, it's the start of a string whose first character is an escaped '.

When you call ps.setString(1, value), the MySQL JDBC driver has to look for the corresponding ? in your string. It will however ignore any ? characters inside string literals, because you might actually want to look for question-mark characters in your data. However, your code has a string literal that doesn't end, and when the MySQL JDBC driver is scanning through your query looking for a ? character, it doesn't find the one in your query because at the point it finds your ? character, it appears to be within a string literal. Hence you get an error about your query appearing to not have any parameters.

If you want your MySQL query to return the titles of all videos where the last backslash-separated part of the path column is in your variable value, then you need to escape the backslash character twice. Try replacing the line

    String query = "select title from video where substring_index(path,'\\',-1) = ?;";

with

    String query = "select title from video where substring_index(path,'\\\\',-1) = ?;";