How to escape single quote in sql which is causing' quoted string not properly terminated '?

Question

I am trying to read terms from a database (>10K) and I'm using that term in another query. I'm getting the following error in Oracle:

quoted string not properly terminated'

I did

term.replaceAll("'", "\\'");

but that doesn't seem to do the job from me. Besides, these terms are tokens from documents when they are converted to text. Is there a regular expression that can overcome this problem?

The exact SQL query is:

String sql = "Select * from indexDB where (DocID=" + d.getDocId() + "and Term='" + term + "')";

I'm using Java. The replacement doesn't work for me.

Solution

You can escape a single quote by repeating it:

term.replaceAll("'","''");

An even better option would be a parameterized query. For an example, we'd have to know your client language.

Comments

[Kiquenet]

More special characters in Oracle, no only the character ' ?