Inserting string with single quotes from Java into Postgresql

Question

I'm inserting text from a Java application into a Postgresql database, but it all crashes when a ' char is encountered in the String. I've tried using replaceAll(" ' ", " \\' "); even diffrent variants of it with more \ chars, yet it still puts a single ' in the String without the escape sign.

Is there any way of replacing the ' with an \' in the String? Or another way of putting Strings containig single quotes into Postgresql?

I have a similar issue, I am using hibernate queries and when trying to insert if a field contains single quotes, an exception is thrown — user4458270
– user4458270, Commented Jun 13, 2015 at 2:50

Matthew Flaschen · Accepted Answer · 2009-06-04 22:44:37Z

15

You shouldn't have to worry about doing this manually if you're using prepared statements properly.

answered Jun 4, 2009 at 22:44

Matthew Flaschen

286k53 gold badges523 silver badges554 bronze badges

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

6 Comments

Joey Over a year ago

+1 for prepared statements. Those are really the proper way to do this.

Bert F Over a year ago

+1 for prepared statements from me too. Be careful about exposing yourself to SQL injection attacks.

Malthan Over a year ago

Thank you, I followed Your advice and changed it to a proper prepared statement, turned out I was using it the wrong way. This also resolved the issue.

Kevin Day Over a year ago

I'd recommend moving the accepted answer to this one. That way other folks will see the correct solution first. Nothing wrong, exactly, with the single quote escaping, but it's not generally a good practice to build SQL strings like that.

user4458270 Over a year ago

How do i go about solving similar issue when I am using hibernate saveOrUpdate(Object ), query and the issue occurs if one of the String fields in the Object contain a single quotes

|

Community · Accepted Answer · 2011-12-01 07:46:29Z

2

Most SQL implementations use '' (2 single quotes) to escape a single quote.

Example:

SELECT * FROM users WHERE f_name='foo''bar';

Or you could use double dollar sign:

Example:

SELECT * FROM users WHERE f_name=$$foo''bar$$;

Both statements would search for the string foo'bar

edited Dec 1, 2011 at 7:46

CommunityBot

11 silver badge

answered Jun 4, 2009 at 22:43

Matthew Vines

27.7k7 gold badges80 silver badges99 bronze badges

4 Comments

Bill Karwin Over a year ago

Yes, this is a feature of standard SQL and PostgreSQL supports this. See postgresql.org/docs/current/static/…

Thilo Over a year ago

But you still want to use bind variables and prepared statements.

Matthew Vines Over a year ago

I would agree that prepared statements are the way to go. But sometimes legacy code makes you choose your battles.

Matthew Flaschen Over a year ago

I doubt legacy code is an issue here. PostegreSQL has a JDBC driver.

Collectives™ on Stack Overflow

Inserting string with single quotes from Java into Postgresql

2 Answers 2

6 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related