How to Escape a Single Quote in SQL

1. Introduction

Handling single quotes in SQL queries is crucial across different database platforms.

In this tutorial, let’s explore various methods for escaping single quotes, specifically in SQL environments, including MS SQL, PostgreSQL, and MySQL database platforms.

Moreover, we’ll explore practical examples by demonstrating this scenario using the Baeldung University database schema and sample data.

2. Problem Statement & Sample Data Environment

In SQL databases, a syntax error arises when a single quote in a string literal isn’t properly escaped or handled in queries. This issue is critical when inserting names like John O’Liu into database tables, where we have to manage the special characters for a successful insertion:

> INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) VALUES
  (1001, 'John O'Liu', 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4);
psql:commands.sql:39: ERROR:  syntax error at or near "Liu"
LINE 2:   (1001, 'John O'Liu', 123345566, '2001-04-05', '2020-01-15'...

Above, we defined the Student table using the CREATE TABLE query. Then, we inserted data containing single quotes into the Student database table.

3. Doubling Single Quotes

Typically, doubling single quotes is the standard method for inserting data containing single quotes. In our case, we use the name John O”Liu, where the additional single quote is doubled (”) to ensure the query inserts the data correctly without syntax errors:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) VALUES
  (1001, 'John O''Liu', 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4);
INSERT 0 1

Here, the INSERT 0 1 indicates that one row was successfully inserted into the database. Next, we retrieve the record with the name John O’Liu using the SELECT query:

SELECT * FROM Student WHERE name = 'John O''Liu';
  id  |    name    | national_id | birth_date | enrollment_date | graduation_date | gpa 
------+------------+-------------+------------+-----------------+-----------------+-----
 1001 | John O'Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |   4
(1 row)

Furthermore, this method of doubling single quotes operates similarly on MSSQL and MySQL.

4. Using Dollar-Quoted Strings

Dollar-quoted strings are commonly utilized in PostgreSQL to manage names such as John O’Liu, which contain single quotes:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) VALUES
  (1001, $$John O'Liu$$, 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4);
INSERT 0 1

Also, this method is especially beneficial for inserting data that includes special characters, ensuring successful insertion and retrieval:

SELECT * FROM Student WHERE name = $$John O'Liu$$;
  id  |    name    | national_id | birth_date | enrollment_date | graduation_date | gpa 
------+------------+-------------+------------+-----------------+-----------------+-----
 1001 | John O'Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |   4
(1 row)

However, this method of using dollar-quoted strings isn’t applicable in MySQL and MSSQL.

5. Using NCHAR() Function in MSSQL

Now, let’s utilize the NCHAR() function to manage the name John O’Liu, which includes a single quote. We use the INSERT query to add the value to the Student table:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) VALUES
  (1001, 'John O' + NCHAR(39) + 'Liu', 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4);

Here, NCHAR(39) represents the ASCII character code for a single quote (‘). Concatenating ‘John O’, NCHAR(39), and ‘Liu’ forms the complete name John O’Liu. Next, we use a SELECT query with the NCHAR() function to retrieve the row values:

SELECT * FROM Student WHERE name = 'John O' + NCHAR(39) + 'Liu';
  id  |    name    | national_id | birth_date | enrollment_date | graduation_date | gpa 
------+------------+-------------+------------+-----------------+-----------------+-----
 1001 | John O'Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |   4
(1 row)

The query above successfully retrieves our desired row.

5.1. Using CHAR() in MySQL

In MySQL, CHAR(39) generates a single quote (‘), which is then used with the CONCAT() function to combine the strings ‘John O’ and ‘Liu’, along with the single quote:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) 
  VALUES (1001, CONCAT('John O', CHAR(39), 'Liu'), 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4);

Here, we use the CHAR() function with the INSERT query for successful insertion.

5.2. Using CHR() Function in PostgreSQL

Now, let’s utilize the CHR() function to manage the name John O’Liu, which includes a single quote. Here, CHR(39) represents the ASCII character code for a single quote (‘). Concatenating ‘John O’, CHR(39), and ‘Liu’ forms the complete name John O’Liu:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa) 
  VALUES (1001, 'John O' || CHR(39) ||'Liu', 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4);

Here, we use the CHR() function with the INSERT query for successful insertion.

6. Conclusion

In this article, we learned how to use single quotes in SQL, with different approaches depending on the database platform. In PostgreSQL, dollar-quoted strings or CHR() functions effectively handle single quotes, while MySQL and MSSQL use CHAR() or NCHAR(). However, doubling single quotes is accepted across all three platforms.