escape single quote special characters in mysql

How to Escape Single Quote, Special Characters in MySQL

Sometimes you may need to store single quote, double quote, apostrophe, backticks and other special characters in MySQL. 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

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.

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.

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!