Backslash handling in MySQL

When you insert something with a backslash to MySQL, you will find in MySQL the backslash is not inserted actually.

Example

If you insert content read from the below file to MySQL:

$frac {2}{3}$

The content in MySQL will be $frac {2}{3}$, the backslash is not inserted.

This is an issue related to how MySQL handle backslashes. If NO_BACKSLASH_ESCAPES SQL mode is not enabled, MySQL interprets sequences begins with a backslash (“) that is known as the escape character within a string.

If the escape character is one of MySQL’s escape character list(see below table), it is interpreted as the corresponding escaped character. For all the other escape characters beyond the list, backslash is ignored.

Table: Special Character Escape Sequences

Escape Sequence	Character Represented by Sequence
“	An ASCII NUL (X'00') character
'	A single quote (') character
"	A double quote (") character
b	A backspace character
n	A newline (linefeed) character
r	A carriage return character
t	A tab character
Z	ASCII 26 (Control+Z); see note following the table
\	A backslash (“) character
%	A % character; see note following the table
_	A _ character; see note following the table

Now, you know why the backslash is not inserted. That’s because f in $frac {2}{3}$ is interpreted as f and “ is ignored.

If that’s what you want, use double backslashes \ to replace single backslash “.

Or if you enable NO_BACKSLASH_ESCAPES SQL mode, backslash becomes an ordinary character like any other, therefore no escape sequence will be interpreted.

Resources

MySQL Official

• MySQL string literals

• mysql_real_escape_string_quote()

• Server SQL Modes: NO_BACKSLASH_ESCAPED

  NO_BACKSLASH_ESCAPES

  Disable the use of the backslash character (“) as an escape character within strings and identifiers. With this mode enabled, backslash becomes an ordinary character like any other.

Other

• Stackoverflow: MySQL is not inserting a backslash

  Tells how to tame backslashes in MySQL.

  Create a file with below data

  1   aliens are on route
  2   scramble the nimitz
  3   its species 8472
  4   \\\\\\\\\
  5   Bonus characters:!@#$%^&*()_+=-[]|}{;'":/.?>, load data local infile '/home/el/foo/textfile.txt' into table penguin 
       fields terminated by 't' escaped by 'b' 
       lines terminated by 'n' (@col1, @col2) set id=@col1, 
       chucknorris=@col2;
  
  Query OK, 5 rows affected (0.00 sec)
  Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
  

  Select

  mysql> select * from penguin;
  +----+------------------------------------------------------------------+
  | id | chucknorris                                                      |
  +----+------------------------------------------------------------------+
  |  1 | aliens are on route                                              |
  |  2 | scramble the nimitz                                             |
  |  3 | its species 8472                                                |
  |  4 | \\\\\\\\\                                               |
  |  5 | Bonus characters:!@#$%^&*()_+=-[]|}{;'":/.?>,< anything but tab |
  +----+------------------------------------------------------------------+
  5 rows in set (0.00 sec)
  

• PHP function : mysql_escape_string() and mysql_real_escape_string()

  Before PHP 4.3.0, there are two functions mysql_escape_string() and mysql_real_escape_string() that escape a string for use in a mysql_query. In PHP 4.3.0, they have been deprecated and and the entire original MySQL extension was removed in PHP 7.0.0. Instead, use either the actively developed MySQLi or PDO_MySQL extensions. See also the MySQL: choosing an API guide and its related FAQ entry for additional information. Alternatives to this function include:

  • mysqli_escape_string()
  • PDO::quote()