When you are dealing with databases one of the most common things you have to do is to escape out all single quotes in text being entered into the DB to stop PostgreSQL/MySQL/*SQL having a cow and to prevent injection attacks.

In most languages this is trivial, take Perl for example

  1. my $sqlText =~ s/'/\\'/g;

Could it be that easy in Java … not on your life!

At first I thought, well, Java being written by smart people, they must have included somewhere in the vast Java API a simple function to escape SQL, like mysql_escape(String) in PHP. I tried to find such a function with Google, no joy, so then I read through every part of the API that I could think of that is remotely related to data bases, and found nothing. If there is such a function in Java it is too well hidden and I’d really appreciate it if someone could point it out to me!

So, that means I had to rely on Java’s REs … oh dear!

String.replaceAll() would SEEM to be the answer, and indeed it is, but it’s not as straight forward as one would expect, and the documentation is quite frankly poo! The documentation doesn’t in any way explain Java’s mad behavior!

Since a single quote should not be escaped in a Java String and a backslash should it is logical to conclude that the following would work:

  1. String escaped = unescaped.replaceAll("'", "\\'");

However, it doesn’t, that just results in replacing all single quotes with a single quote and nothing else, the backslash disappears!

The API says that the first string taken is an RE and not a normal string so you have to escape anything an RE would consider special, the docs say that the replacement is just a String, nothing fancy, just a String containing your replacement. Turns out the docs are misleading because the replacement is actually an RE too so it gets interpreted twice so you need to escape everything double like you would if you were using Perl to write JS to write HTML, hence, the following is the code to escape single quotes:

  1. String escaped = unescaped.replaceAll("'", "\\\\'");

Go figure!

This stupid bug has held up a project I am working on for about half a day. I have said it before and I will say it again, Java RE support is RUBBISH!