What is idea behind escape characters?

Vladimir Buzalka
Vladimir Buzalka used Ask the Experts™
on
Dear Experts

I am starting with mySQL and I cannot understand all the concept of escape characters in strings.

What was idea behind this? I have feeling that it makes my life just more difficult if I want to assure that string which I have in variable is indeed stored in mySQL in exact form.

Many thanks for your advices

Vladimir
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer
Commented:
Just consider this case:

We have no escaping. The string delimiter is fixed to '. Write an INSERT statement which inserts "D'oh?!" into a table.



Same applies to object naming. Without delimiters here, we would be limited to a subset of ASCII (7bit) only.

Commented:
Hi Vladimir,
Adding a backslash character (\) before certain other characters forms a special command. For example:

\n "escapes" the literal letter "n" and instead is interpreted as a new line:

mysql> SELECT 'Paul nNeralich';
Paul nNeralich

mysql> SELECT 'Paul \nNeralich';
Paul
Neralich

Commented:
Can you give us an example of something you are trying to write to the database for which you feel you need to make use of escaping?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

David FavorFractional CTO
Distinguished Expert 2018
Commented:
You asked, *"What is idea behind escape characters?"*

Specifically, MariaDB/MySQL use some special characters in normal syntax.

Escaping is required, so these special characters can be... disabled as syntax + enabled as normal characters...

Without escaping, you could never write some characters as data.
Commented:
ste5an's example of handing the string   D'oh?!   is an excellent example. To illustrate David's point, we need the single quote in position 2 of this string to be treated as normal text, otherwise SQL will interpret it as the end of the string. Therefore the backslash is used to escape the syntax and treat that particular single quote as text. Consider these examples:

No good:
INSERT INTO HomerQuotes
(pkey, text)
VALUES
(1,'D'oh?!');

Above, SQL would interpret the string to bea single character: D
and not know what to do with the: oh?!'
Escaping that single quote solves the problem:

INSERT INTO HomerQuotes
(pkey, text)
VALUES
(1,'D\'oh?!');


(I first learned about the concept of escaping characters when I learned HP's flavor of Unix, HP-UX)
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
All the comments above explain the reasons behind escaping values.

However, if you're writing a script (such as PHP or C#) to insert into your database, then you can avoid the need to escape your variables altogether. You do this by writing prepared statements. Because your variables are never wrapped in quotes, there's no need to escape them.

Author

Commented:
Dear Experts

many thanks for your explanations, I think I can understand need for character escaping now.

Thanks a lot again

V

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial