How to Escape Single Quote, Special Characters in MySQL

Last updated on August 13th, 2024 at 07:23 am

Sometimes you may need to store single quote, double quote, apostrophe, backticks and other special characters in MySQL columns. But they are also used to enclose strings and characters, as delimiters. So if used as-is, they will confuse MySQL and result in errors. In this article, we will look at how to escape single quote, double quotes, apostrophe, backticks and other special characters.

How to Escape Single Quote, Special Characters in MySQL

Here is a simple example to illustrate this problem. When you enter the following query it will not run. Instead, it will expect you to complete the query, due to incomplete single quotes.

mysql> select 'tes't' as a from sales limit 1;
     '>

Let us say you terminate the query by adding another single quote, you will see the following error message.

   '> ';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' as a from sales limit 1;
'' at line 1

There are several simple ways to escape quotes and special characters in MySQL. We will look at eachof these solutions one by one.

1. Using Backslash

You can easily escape single quotes, double quotes, apostrophe, backticks and other special characters by adding a backslash (\) before that character.

Here’s a MySQL query that escapes single quotes.

mysql> select 'test\'s' as test_string;
+-------------+
| test_string |
+-------------+
| test's      |
+-------------+

As you can see the single quote has been escaped and is displayed in query result.

Similarly, here is the MySQL query to escape double quotes

mysql> select 'test\"s' as test_string;
+-------------+
| test_string |
+-------------+
| test"s      |
+-------------+

Similarly, we can use backslash to escape single quotes and double quotes to insert values into MySQL table.

Let us say you have the following table escape_characters_demo(id, string)

mysql> create table escape_characters_demo(
       id int,
       string varchar(255)
       );

Now let us try inserting texts with single, backticks and double quotes and their combinations, using backslash.

mysql> mysql> insert into escape_characters_demo(id, string)
       values(1, 'test\'s'),
       (2, 'test\"s'),
       (3, 'test\`s'),
       (4, 'test\'s and best\'s'),
       (5, 'test\"s and best\"s'),
       (6, 'test\"s and best\'s');

mysql> select * from escape_characters_demo;
+------+-------------------+
| id   | string            |
+------+-------------------+
|    1 | test's            |
|    2 | test"s            |
|    3 | test`s            |
|    4 | test's and best's |
|    5 | test"s and best"s |
|    6 | test"s and best's |
+------+-------------------+

As you can see above, we are able to escape single quotes, double quotes, backticks, multiple single & double quotes, and even a combination of these, by adding a backslash before these special characters.

2. Using Opposite Quotes

Another method to escape quotes is to enclose them in a set of different quotes. For example, if your text contains single quotes then you can enclose them in double quotes and vice versa. This is because when your text contains single quote and if you enclose them in single quotes then MySQL will see them as 3 single quote, and the second single quote, that is, the one in your text, will be treated as end of single quotes.

On the other hand, when we enclose a text containing single quote within double quotes, then MySQL will treat the double quotes as delimiter and single quote as part of the text.

mysql> select "tes't" as a from sales limit 1;
+-------+
| a     |
+-------+
| tes't |
+-------+

mysql> select 'tes"t' as a from sales limit 1;
+-------+
| a     |
+-------+
| tes"t |
+-------+

3. Using Consecutive Quotes

Sometimes, you may not be able to escape quotes in your string using backslash or you may not be able to use opposite set of quotes. In such cases, you can use two consecutive quotes inside the text instead of one. Here the first quote will escape the second one.

mysql> select 'tes''t' as a from sales limit 1;
+-------+
| a     |
+-------+
| tes't |
+-------+

mysql> select "tes""t" as a from sales limit 1;
+-------+
| a     |
+-------+
| tes"t |
+-------+

Conclusion

In this article, we have learnt 3 different ways to escape single quotes, double quotes and special characters. You can use any of these methods as per your requirement. Hopefully, now you can easily escape special characters in your SELECT, INSERT and UPDATE queries.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.