Insert Text With Single Quotes in Postgresql

Insert text with single quotes in PostgreSQL

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:

• SQL injection in Postgres functions vs prepared queries

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.

Insert text dynamically with single quotes in PostgreSQL

PDO is probably much better solution, but you can also use '' for escaping:

$rs5 = $db->GetAll($sql);
foreach ($rs5 as $row) {
    $t1 = str_replace("'", "''", $row['prod_name']);
    $t2 = str_replace("'", "''", $row['prod_price']);
    $rs = $db->Execute("INSERT INTO trans_temp (name, price) VALUES ('$t1','$t2') ");
}

How to insert a value that contains an apostrophe (single quote)?

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

Escape all single quotes in postgresql

insert into table1(data) values
($$it's a string, it's got some single quotes$$)

Dollar quoting

A dollar-quoted string constant consists of a dollar sign ($), an optional "tag" of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign

Postgres Escape Single and Double Quotes in Text Field

You can escape double quotes by doing:

postgres=# SELECT REGEXP_REPLACE('this "is" a string', '"', '\"', 'g');
     regexp_replace    
  ----------------------
  this \"is\" a string
  (1 row)

For single quotes, the approach is similar, but you have to escape them using another single quote. So instead of having something like /', it should be ''. The query is:

postgres=# SELECT REGEXP_REPLACE('this ''is'' a string', '''', '\''', 'g');
    regexp_replace    
----------------------
 this \'is\' a string
(1 row)

Note the 'g' flag in the end, this forces it to replace all occurrences and not just the first one found.

You can also replace both single and double quotes in a single statement, although they are replaced with the same string (\" in this case).

postgres=# SELECT REGEXP_REPLACE('this "is" a ''normal'' string', '["'']', '\"', 'g');
         regexp_replace          
---------------------------------
 this \"is\" a \"normal\" string
(1 row)

---

Related Topics