CODEWITHSUNDEEP
javamysqlreplace
user1163278
user1163278

Reputation: 421

java replace ' with \'

I'm working with mySQL. It can not handle if ' is in the String that is being added to the database.

I tried:

replaceAll("'","\\'")

and 

replaceAll("'","\'")

Any ideas how I would go about replacing ' with \'?

Upvotes: 3

Views: 1823

Answers (2)

Tim Pietzcker
Tim Pietzcker

Reputation: 336378

You need to escape the backslash twice, once for the string processing engine and once for the regex engine:

replaceAll("'","\\\\'")

Caveat: While this answers the question about how to insert a backslash into a string, it certainly should not be used in an attempt to thwart SQL injection attacks.

To clarify: Imagine someone submits a string where the apostrophe is already escaped. This regex would then lead to the apostrophe being unescaped (because now the backslash would become escaped). So actually you'd need this regex to escape an apostrophe only if preceded by an even number of backslashes. This means

replaceAll("(?<!\\\\)((?:\\\\\\\\)*)'", "$1\\\\'")

This is rapidly becoming as unmaintainable as it looks, and it still doesn't cover all cases.

Upvotes: 7

JB Nizet
JB Nizet

Reputation: 691943

Don't use String replacements to handle this. Instead, use a prepared statement and thus let the JDBC driver escape the parameters for you:

String sql = "select a.foo from a where a.bar = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, aStringWhichMightContainQuotes);
ResultSet rs = stmt.executeQuery();

This is the proper way to have database-independent, robust code, that is not vulnerable to SQL injection attacks. And it also make it more efficient if you execute the same query several times with different parameters.

See the JDBC tutorial for more information.

Upvotes: 17

Related Questions