Replacing apostrophes for an sql statements

[MS]

Hi,

When doing SQL statements apostrophes mark the start and end of a text
field, accordingly any apostrophe in the text must be handled.

So here's a code snippet showing how I handle this:

int isApostrophe = title.indexOf("'");
if (isApostrophe != -1)
{
title = title.replaceAll("'", "\\'");
}

I search to see if the text has an apostrophe, if there is a match in the
text (String title) replace all the apostrophes with "\\'" so that the
following conversion will take place:

"Matthew's test" should become "Matthew\'s test" and the sql, when run,
should be happy as it knows the apostrophe in the middle of the text is
not the end of the text.

BUT this is not happening "Matthew's test" is remaining the same. I'm
probably being stupid but I can't work out what's wrong with my code.

Please help,

MS

 

[Tilman Bohn]

[Followup-To set to clj.help]

In message <[email protected]>,
MS wrote on Mon, 21 Feb 2005 16:51:56 GMT:

[...]

int isApostrophe = title.indexOf("'");
if (isApostrophe != -1)
{
title = title.replaceAll("'", "\\'");
}

Three points:

First of all, look at the API documentation for Matcher's replaceAll(),
which is what the above really ends up being delegated to:

`Note that backslashes (\) and dollar signs ($) in the replacement
string may cause the results to be different than if it were being
treated as a literal replacement string. Dollar signs may be treated
as references to captured subsequences as described above, and
backslashes are used to escape literal characters in the replacement
string.'

What this means is that you want _two_ backslashes to appear in your
replacement String, which means putting four of them in the literal. So
summing up,

title = title.replaceAll("'", "\\\\'");

will do what you want.

Secondly, the indexOf() test is extraneous. It's safe to run replaceAll
on input that doesn't match, and both indexOf and replaceAll must scan the
whole input anyway so you're not buying anything by the extra test.

Finally, you should really look into PreparedStatement, which will
transparently escape any special characters (not just apostrophes) for
you.

 

[MS]

Many thanks.

What this means is that you want _two_ backslashes to appear in your
replacement String, which means putting four of them in the literal. So
summing up,

title = title.replaceAll("'", "\\\\'");

will do what you want.

I got confused but get it now.

Secondly, the indexOf() test is extraneous. It's safe to run replaceAll
on input that doesn't match, and both indexOf and replaceAll must scan the
whole input anyway so you're not buying anything by the extra test.

I've changed things accordingly, I thought it would save some processing,
instead of doubling it. --oops.

Finally, you should really look into PreparedStatement, which will
transparently escape any special characters (not just apostrophes) for
you.

Okay, I'll take a look.

Thanks,

MS

 

[Marcin Grunwald]

Hi,

When doing SQL statements apostrophes mark the start and end of a text
field, accordingly any apostrophe in the text must be handled.

So here's a code snippet showing how I handle this:

int isApostrophe = title.indexOf("'");
if (isApostrophe != -1)
{
title = title.replaceAll("'", "\\'");
}

I search to see if the text has an apostrophe, if there is a match in the
text (String title) replace all the apostrophes with "\\'" so that the
following conversion will take place:

"Matthew's test" should become "Matthew\'s test" and the sql, when run,
should be happy as it knows the apostrophe in the middle of the text is
not the end of the text.

BUT this is not happening "Matthew's test" is remaining the same. I'm
probably being stupid but I can't work out what's wrong with my code.

Please help,

MS

Use binding.
Example from PreparedStatement JavaDoc:
PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET
SALARY= ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)

Don't include title value into SQL query, just insert '?' and use
pstmt.setString(1, title);