Insert text with single quotes in PostgreSQL

547
votes

I have a table test(id,name).

I need to insert values like: user's log, 'my user', customer's.

 insert into test values (1,'user's log');
 insert into test values (2,''my users'');
 insert into test values (3,'customer's');

I am getting an error if I run any of the above statements.

If there is any method to do this correctly please share. I don't want any prepared statements.

Is it possible using sql escaping mechanism?

7
postgresqlinsertspecial-charactersquotes
Use whatever value escaping your client library provides. For more information you'll have to say how you're accessing the database. – Richard Huxton
@Richard Huxton database is accessed by java. – MAHI
So use the standard jdbc placeholders. Or explain why that's not the best choice. – Richard Huxton
@Richard Huxton i am not saying that's not best choice, i am searching if their exists any escaping method in sql to do so. – MAHI
Well, see @Claudix's reply below, but obviously value literals will need different escaping depending on their type postgresql.org/docs/current/static/datatype.html – Richard Huxton

7 Answers

941
votes

String literals

Escaping single quotes ' by doubling them up -> '' is the standard way and works of course:

'user's log'     -- incorrect syntax (unbalanced quote)
'user''s log'

Plain single quotes (ASCII / UTF-8 code 39), mind you, not backticks `, which have no special purpose in Postgres (unlike certain other RDBMS) and not double-quotes ", used for identifiers.

In old versions or if you still run with standard_conforming_strings = off or, generally, if you prepend your string with E to declare Posix escape string syntax, you can also escape with the backslash \:

E'user\'s log'

Backslash itself is escaped with another backslash. But that's generally not preferable.
If you have to deal with many single quotes or multiple layers of escaping, you can avoid quoting hell in PostgreSQL with dollar-quoted strings:

'escape '' with '''''
$$escape ' with ''$$

To further avoid confusion among dollar-quotes, add a unique token to each pair:

$token$escape ' with ''$token$

Which can be nested any number of levels:

$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$

Pay attention if the $ character should have special meaning in your client software. You may have to escape it in addition. This is not the case with standard PostgreSQL clients like psql or pgAdmin.

That is all very useful for writing plpgsql functions or ad-hoc SQL commands. It cannot alleviate the need to use prepared statements or some other method to safeguard against SQL injection in your application when user input is possible, though. @Craig's answer has more on that. More details:

Values inside Postgres

When dealing with values inside the database, there are a couple of useful functions to quote strings properly:

  • quote_literal() or quote_nullable() - the latter outputs the string NULL for null input. (There is also quote_ident() to double-quote strings where needed to get valid SQL identifiers.)
  • format() with the format specifier %L is equivalent to quote_nullable().
    Like: format('%L', string_var)
  • concat() or concat_ws() are typically no good for this purpose as those do not escape nested single quotes and backslashes.
49
votes

This is so many worlds of bad, because your question implies that you probably have gaping SQL injection holes in your application.

You should be using parameterized statements. For Java, use PreparedStatement with placeholders. You say you don't want to use parameterised statements, but you don't explain why, and frankly it has to be a very good reason not to use them because they're the simplest, safest way to fix the problem you are trying to solve.

See Preventing SQL Injection in Java. Don't be Bobby's next victim.

There is no public function in PgJDBC for string quoting and escaping. That's partly because it might make it seem like a good idea.

There are built-in quoting functions quote_literal and quote_ident in PostgreSQL, but they are for PL/PgSQL functions that use EXECUTE. These days quote_literal is mostly obsoleted by EXECUTE ... USING, which is the parameterised version, because it's safer and easier. You cannot use them for the purpose you explain here, because they're server-side functions.

Imagine what happens if you get the value ');DROP SCHEMA public;-- from a malicious user. You'd produce:

insert into test values (1,'');DROP SCHEMA public;--');

which breaks down to two statements and a comment that gets ignored:

insert into test values (1,'');
DROP SCHEMA public;
--');

Whoops, there goes your database.

35
votes

According to PostgreSQL documentation (4.1.2.1. String Constants):

 To include a single-quote character within a string constant, write two 
 adjacent single quotes, e.g. 'Dianne''s horse'.

See also the standard_conforming_strings parameter, which controls whether escaping with backslashes works.

28
votes

In postgresql if you want to insert values with ' in it then for this you have to give extra '

 insert into test values (1,'user''s log');
 insert into test values (2,'''my users''');
 insert into test values (3,'customer''s');
5
votes

you can use the postrgesql chr(int) function:

insert into test values (2,'|| chr(39)||'my users'||chr(39)||');
-1
votes
select concat('''','abc','''')