How Do I Insert Values with Apostrophes into an SQL Database?

Insert a value containing single quotes in SQL database

Since single quotes serve as delimiters for string data in SQL, inserting values ​​containing single quotes into SQL tables can be tricky.

Example:

Consider the following SQL INSERT statement:

<code class="language-sql">INSERT INTO Person (First, Last) VALUES ('Joe', 'O'Brien')</code>

This statement will throw an error because the single quote after the "O" is interpreted as the end of the value.

Solution: Escape single quotes

To insert a value containing a single quote, you need to escape the character by doubling the single quote.

<code class="language-sql">INSERT INTO Person (First, Last) VALUES ('Joe', 'O''Brien')</code>

In this example, the single quote after the "O" is escaped by another single quote.

Applies to SELECT queries

The same escaping rules also apply to SELECT queries. For example, to find people with the last name "O'Brien":

<code class="language-sql">SELECT First, Last FROM Person WHERE Last = 'O''Brien'</code>

Technical Description

Single quote is a special character in SQL because it indicates the beginning and end of string data. To use a single quote as part of a string value, its special character status needs to be escaped. This is usually done by doubling the characters.

Notes

When dealing with data that may contain special characters, it is a good idea to escape these characters before inserting them into the database. This helps prevent SQL injection attacks and other vulnerabilities. In code, frameworks and tools should handle this for you.

The above is the detailed content of How Do I Insert Values with Apostrophes into an SQL Database?. For more information, please follow other related articles on the PHP Chinese website!