How to Escape a Single Quote in MySQL

MySQL is an open-source relational database management system (RDBMS) that is widely used for managing and organizing structured data. MySQL is designed to run on various operating systems, including Windows, Linux, macOS, and others, providing flexibility in deployment. It can handle databases of different sizes and scales well, making it suitable for both small-scale projects and large enterprise-level applications.

Strings can be formed by enclosing text in quotes. However in a case when quotes are themselves required in a string, then we need to escape them. In this article, we are going to see how we can escape a single quote in MySQL using various techniques and methods examples, and so on.

Single Quote in MySQL

In MySQL, single quotes play a vital role in denoting string literals. However, there are situations where including an actual single quote within a string becomes necessary. This article explores methods to escape single quotes and maintain the integrity of your data.

Setting Up Environment

Let us start by creating a table and insert some values in it. Suppose we have the following customer table. The following query creates a table and inserts a few records in it.

Query:

-- Create the customer table
CREATE TABLE customer (
    id INT,
    name VARCHAR(20)
);

-- Insert sample values into the customer table
INSERT INTO customer VALUES
    ('1', 'John Doe'),
    ('2', 'Jane Smith'),
    ('3', 'Bob Johnson');

Output:

Using Quotes in MySQL

Quotes are used to form strings in MySQL. A string is like a constant value which is the same for all rows. The strings can be used to add contextual information to values for better understanding. We can form strings using quotes as follows:

Query:

-- Using quotes to form strings in MySQL
SELECT ‘String value’

Output:

Explanation: In the above example, we just returned a string using a SELECT statement. The column name is also the constant value and if there had been multiple rows, the value would have been replicated in all the rows (it is seen in the following example).

Note: The use of quotes is the same as in SQL Server.

Quotes select the text in the form of a string. We cannot enclose column names in quotes as it will result in a string with that value instead. See the following example.

Query:

-- Display the contents of the customer table
SELECT Name, 'Name' FROM customer;

Output:

Explanation: As we can see the column name when enclosed just returns a constant value in each row. This allows to form strings that can be used to change column values and make things more understandable for e.g. - we can make a view over a table and add description in specific columns to make things understandable.

How to Escape Quotes in MySQL

Sometimes we might need to put quotes inside the text. We can do it in the following two ways.

Method 1: Enclose Them in the Opposite Set

When a text is enclosed in single quotes then the second single quote has a special meaning and serves as the delimeter for the text. However enclosing the text in double quotes removes the special meaning of single quotes and treats them as normal characters. We can enclose single quotes in double quotes and vice versa. In the following example, we will put single quotes around a value.

Query:

-- Using double quotes to include a single quote in a string
SELECT " 'Name' " FROM customer;

Output:

Explanation: Thus enclosing in double quotes helps to escape single quotes.

Method 2: Write Them Twice Consecutively

In some cases, when you cannot use double quotes, you can escape single quotes inside single quotes by writing them along with each other. Every single quote escapes the next one (except the border ones).
Query:

SELECT '''Name' FROM customer;

Output:

Explanation: As you can see, we enclosed single quotes inside single quotes by writing them consecutively.

Usage

A good usage of string is to add information to columns. The following example combines the values from two columns to make a single info columns. See the example below.

Query:

-- Combining values from two columns into a new info column
SELECT CONCAT(name, '''s id is ', id) AS info FROM customer;

Output:

Explanation: The above example combines the information from two columns into a single info column for easier understanding.

Conclusion

Single quotes are used to form strings in MySQL. The strings are constant values which remain same for all rows. However to put quotes inside quotes is impossible and we need ways to escape the quotes. The first method to escape single quotes is to enclose the text in double quotes. The second method is to put single quotes twice in the text. In this article we have understand the two ways through which we can escape the single quotes in MySQL.