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()
andmysql_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: