SQL Escape Single Quote

An escape character refers to a special character that tells the SQL preprocessor to process the character as a literal string instead of interpreting it to a pre-defined operation.

For example, strings in SQL are denoted by enclosing them inside a pair of single quotes. However, if you need to add a quoted string, it is impossible without the use of escape characters. This is because SQL will interpret the second single quote character as a closing block to a string.

Take the example shown below:

'that's his house';

If you attempt to add, such as a string to SQL, it will result in an error.

SQL Escape Single Quote

The way to avoid this issue is by using escape characters. These are a set of characters that implement special meaning when used appropriately.

Common SQL escape characters include:

1. \’ – Escape single quote
2. \” – Escape double quote
3. \b – backspace character
4. \m – newline character
5. \r – carriage return
6. \\ – Escape backslash character
7. \% – Escape percentage sign
8. \t – Tab character

These are several familiar escape characters supported by database engines. Keep in mind that various database vendors may implement custom escape characters. Feel free to check the documentation for your database engine to learn more.

The following example shows how to escape a single quote character in SQL:

'that\'s his house';

In some database engines, the above syntax is not supported. For example, running the query following in PostgreSQL will fail:

SELECT 'that\'s his house';

For that case, both PostgreSQL and SQL Server allow you to escape single-quoted string using the syntax shown below:

SELECT 'that''s his house';

This should escape the single quote and print the following:

     ?COLUMN?
------------------
 that's his house

We can also use a mix of double quotes and single quotes to achieve the same result. Remember that this method is only supported in database engines where you can create strings using double-quotes. An example is the MySQL database engine.

An example is shown below:

SELECT "that's his house" AS string;

Since we use double quotes to denote the string, the single quote is automatically treated as part of the literal string.

Conclusion

In this article, we explored escape characters in SQL databases and how to use them to escape single-quote characters. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.

from https://ift.tt/fRn8eOZ