java replace ' with \'

Question

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 \'?

I think your real problem is dynamically building SQL queries at runtime instead of using a PreparedStatement. Please show more of your code where you insert into the database, most likely it's no manual escaping is necessary at all. — Philipp Reichart
– Philipp Reichart, Commented Mar 11, 2012 at 10:45
I do not know what PreparedStatement are. I'm just creating a simple webpage. — user1163278
– user1163278, Commented Mar 11, 2012 at 10:53
Please check out JB Nizet's answers below, he gives sample code plus a link to the relevant section of the JDBC tutorial. This is important -- PreparedStatement almost always result in safer, cleaner and faster code. — Philipp Reichart
– Philipp Reichart, Commented Mar 11, 2012 at 10:56
Yeah, it sounds good. I just browsed trough it. I'm bookmarking it, and will be reading up on later. Thanks man — user1163278
– user1163278, Commented Mar 11, 2012 at 11:00
Pay close attention to what has been said here. If you're doing what it sounds like, your website is wide open to being hacked by SQL injection attacks. It's possible for a knowledgeable (ab)user to run arbitrary SQL against your database - including grabbing potentially sensitive details and/or deleting all your data. — dty
– dty, Commented Mar 11, 2012 at 11:22

JB Nizet · Accepted Answer · 2012-03-11 10:44:06Z

17

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.

answered Mar 11, 2012 at 10:44

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Pietzcker · Accepted Answer · 2012-03-11 15:21:03Z

7

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.

edited Mar 11, 2012 at 15:21

answered Mar 11, 2012 at 10:42

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

2 Comments

Philipp Reichart Over a year ago

No offense, this obviously answers the question, but I think it only works around the actual problem :)

Tim Pietzcker Over a year ago

@PhilippReichart: I agree, and in my opinion JB Nizet's answer should be accepted.

Collectives™ on Stack Overflow

java replace ' with \'

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related