How to Insert a Value That Contains an Apostrophe (Single Quote)

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.

Way to insert text having ' (apostrophe) into a SQL table

In SQL, the way to do this is to double the apostrophe:

'he doesn''t work for me'

However, if you are doing this programmatically, you should use an API that accepts parameters and escapes them for you automatically. Programmatically escaping and using string concatenation to assemble a query yourself is a sure way to end up with SQL injection vulnerabilities.

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.

How do I escape a single quote in SQL Server?

Single quotes are escaped by doubling them up, just as you've shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:

DECLARE @my_table TABLE (
    [value] VARCHAR(200)
)

INSERT INTO @my_table VALUES ('hi, my name''s tim.')

SELECT * FROM @my_table

Results

value
==================
hi, my name's tim.

How to insert a value that contains apostrophe?

Strings need to be placed between single quotes, as you probably already know. Also, if the type contains a quote, you need to double that. That makes for the following line:

SELECT @Sql += N' AND Type = ''' + REPLACE(convert(varchar(50),@type),'''','''''')+'''';

Insert a value containing single quotes in MySQL

You need to use \ (Escape) character to insert single quotes and double quotes.

INSERT INTO table_name(`clomn1`) VALUES ('Ali said, "This is Ashok\'s Pen."')

---

Related Topics