CODEWITHSUNDEEP
javamysqlsqlescapingspecial-characters
Eduardo Z.
Eduardo Z.

Reputation: 633

Java MySQL special characters in IN clause

Helllo guys,

I'm using Java to assemble a query string in execution time that's supposed to execute on the database as a prepared statement. Since I don't know beforehand the number of parameters in my IN clause, I can't use fixed parameters (a.k.a. '?' characters) so I create the IN clause sequence as a string in Java and replace it in my code, like so:

String statement = SQLUpdates.MYUPDATESTRING.replace("#INCLAUSE#", inList);

However, the string sequence in the variable inList contains several special characters that seem to break my select, notoriously the number sign character (#). Is there a way I can escape those either in Java or in my SQL Select String? I don't think I can use the ESCAPE clause after an IN clause, so what are my options here?

Upvotes: 0

Views: 597

Answers (1)

Eduardo Z.
Eduardo Z.

Reputation: 633

Turns out I had to escape a few special characters with backslashes. The characters causing trouble were both the number sign (#) and the backslash ().

The replacement code I used is as follows:

for (String key : keySet) {
   String key2 = key.replaceAll("\\\\", "\\\\\\\\");
   key2 = key2.replaceAll("#", "\\\\#");
   keyString.append("\"").append(key2).append("\"").append(", ");
}

The first line inside the FOR loop replaces all occurrences of a backslash with an escaped double backslash (\). Notice that the backslash has to be repeated several times because you have to escape it once in Java and once more for the Regular Expression. Therefore a single backslash becomes four backslashes (escaped twice) and a double backslash becomes a whooping eight backslashes. Trust me, it works.

The second line in the select escapes the number sign, which is for comments in MySQL. This is done afterwards because we don't want to escape the backslash used to escape the number sign.

The final line simply concatenates the string so that it looks like a comma separated list in which every element is enclosed by double quotes, such as this: "1", "2", "3" etc. and the final comma is deleted afterwards. The final list is, subsequently, inserted in the appropriate place in the select.

Upvotes: 1

Related Questions