Way to Insert Text Having ' (Apostrophe) into a SQL Table

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.

Cannot insert text having ' (apostrophe) into SQL Server table

First of all, you have two values in your VALUES part. One is textBoxTitle.Text and the other one is ''. But you provided just one column.

If that's true, you should delete '' part in your query. But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

If parameterized queries and statements creates any problem with single quote, use double single quotes for each.

• How do I escape a single quote in SQL Server?

Also use using statement to dispose your database connections and commands.

using(SqlConnection con = new SqlConnection(connString))
using(SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "INSERT INTO Book(Title) VALUES (@title)";
    cmd.Parameters.AddWithValue("@title", textBoxTitle.Text);
    con.Open();
    cmd.ExecuteNonQuery();
}

Adding an apostrophe into a dynamic SQL

Please use CHAR(39) instead of typing ' in your dynamic code directly.
Example:

declare @my_dynamic_sql nvarchar(max) = 'print char(39);';
exec(@my_dynamic_sql);

---

Related Topics