Rahul Mehta
All records shown instead of one

Introduction to SQL Escape

January 24, 2023 by

This article will provide an introduction to SQL escape characters for using special characters and their characteristics.

Introduction

In SQL Server, MySQL, and other databases, data is stored in huge amounts. Relatively, it is being processed using various ways. A few of the basics are using queries, procedures, functions, and more. SQL developers usually use many special characters as well as wildcards in queries as an expression to form a wide variety of criteria. Some of the characters can’t be interpreted as “AS-IS” by the processing engine and thus special instruction needs to be added. This instruction will flag those characters to escape and ensure the process of correct characteristics. Thus, they are generally known as “Escape” characters. Most widely they are used with strings or text-based data processing. There are various ways of using escape characters. In this article, we will look at some of the examples to understand the use of SQL escape characters in detail.

Setting up a database instance

To understand the use of SQL escape characters, we need to set up an instance of a database that uses SQL as a query language and supports escape characters as well. One can set up any database of choice that supports this criterion. In this article, we will be using the MySQL database on Azure. It’s one of the most popular open-source databases and supports SQL as the query language and supports escape characters as well. To work with MySQL, we would need a query editor using which we would be executing SQL commands on the database instance. MySQL Workbench is a freely available IDE that works very well with MySQL. We will be using this tool for all the examples in this article. It is assumed that a MySQL database instance, as well as MySQL Workbench, is also installed.

Working with SQL escape characters

If you are working with a freshly created instance, it won’t have a new database created. It is advisable to create a separate database for this example. Though one can also opt to use an existing database instance. Data is hosted in tables and SQL queries would process or query data. So, we need to create at least one table with some sample data in it.

We need a table with at least one field that hosts data as text/varchar/string or similar text-based data types as most of these characters would work well with such datatypes. An employee is one of the very commonly found entities in any typical enterprise data model. Here, I have created an employee table with just one field name ename, for the sake of simplicity.

Shown below is a list of typical escape sequences or characters in MySQL. These are very standard SQL escape characters and not just specific to MySQL. While looking at this list, one may wonder where such characters may get used. For example, while querying JSON data or formatting JSON data for storing or storing any form of code, one may need to add a backslash. While storing addresses, one may want to format each line of the address in a new line, so that it can be readily displayed on the front end without the need for formatting it every time. The percent character “%” is a wild card character that is generally used in many functions that accepts regular expression patterns. One common example of such a function is the LIKE operator. Let’s say that we are storing mathematical formulas which include characters like +, -, and %. If we want to query a formula that involves the “%” character, we may end up using the LIKE operator and in the absence of the SQL escape character, we will face difficulty in easily querying the desired formulas using a regular expression pattern. So, there are a variety of use cases where SQL escape characters can be of great help. We will look at the use of some of these characters through some examples.

SQL Escape sequence

The backslash (\) character is used to form an escape sequence for all the special characters. This is a standard SQL coding convention. For some, it retains its original purpose, and for others, it introduces new functionality. Let’s see a few examples of both.

Let us start with simple examples. Let us say, we want to insert textual values in the ename field with some special characters. One may want to add a single quote in case of words like “I’ll”, or use “%” for mathematical formulas and likewise. To simulate different situations, let’s add a few textual values with special characters. If we use the below code, we will encounter an error as the query engine won’t be able to parse the syntax correctly.

Instead, we can use the escape sequence for the single quote as shown below to make it work

Single Quote record inserted

The above code will ensure that the single inverted quote is taken as a simple string and not the starting/ending of any expression. Like the above example, let us say if you want to put double inverted quotes then the below code will not work. One may wonder why enclosed values with double quotes would be instead of a single quote as shown in the above example. Some developers prefer enclosing values in double quotes, and MySQL supports that as well. So, if a developer is using double quotes while executing the INSERT command as shown below, three double quotes would confuse the query engine to parse this command and we would encounter an error again.

So instead, we can use the escape sequence as shown below for the extra double quote to make the instruction explicit for the query engine to parse the inner double quote as text instead of the closing quote. With the SQL escape sequence in the value, now the command would execute successfully, and the value would get inserted into the table as shown below.

Double Quote record inserted

Other special characters can be used similarly with a backslash to be treated like a simple character. Now let’s see some examples of functional characters.

Let’s say we want to store a name in two lines, then like HTML code we could use the ‘\n’ character. This is to simulate the example we discussed earlier, where one may need to store lines of an address in different lines. Below is a sample of storing a name in two lines

And the output will be:

Two lines record showing in one line in workbench

Now we can see in the above image that the last record is not in two lines. However, it is stored in two lines. To see the actual effect, I would recommend copying the last record into a text editor like notepad and it would look something like the below. The reason one won’t be able to see the data formatted in the below manner right in the result pane of the query editor is due to the reason that query editor shows data in a grid, where the formatting cannot be shown in multi-line or textarea kind of format.

Record showing two different lines in notepad

In the above image, we can see the that second record after “\n” is in the second line. Let’s take another example. Let’s say in some cases we would want some space between two words. Similar to HTML we have ‘\t tab space which we can use below

However similar to earlier, it will have no effect in MySQL workbench but it will in notepad as below

No tab space showing in workbench

Tab space being shown in notepad

Thus, there are escape characters that will have an impact and will provide functionality as well. Moving on next, developers mostly use the LIKE operator in the WHERE clause in which escape characters are used to fetch the appropriate results. Let’s take an example. In the below image, we have the first record which is “HenryI_”. Assume we would like to fetch the recording ending with the “_” character. Usually below code is expected to work,

However, it will have below results rather than the correct record fetched. The reason is due to the “_” character not being considered correctly.

All records shown instead of one

To get the results correctly, one should use backlash with an underscore as below:

Only relevant records are shown

Let’s take another example of using a second record as an example to fetch “HenryII%”. To get the exact record one must use below code for to retrieve the exact records.

Conclusion

  • Use escape characters with wild characters and patterns to achieve a different purpose.
  • Use backlash, single inverted quotes, and other operators as escape characters.
  • Ensure checking of operator support with the database before using one.
Rahul Mehta
Latest posts by Rahul Mehta (see all)
216 Views